Check the rman backup details from catalog

set pagesize 5000;
set linesize 400;
col OBJECT_TYPE for a10;
col STATUS for a15;
col operation for a15;
select DB_KEY,DB_NAME,STATUS,START_TIME,END_TIME,INPUT_BYTES/1024/1024/1024 INPUT_BYTES,OUTPUT_BYTES/1024/1024/1024 OUTPUT_BYTES,OBJECT_TYPE,OPERATION from rc_rman_status where  START_TIME >= (sysdate-1) and OBJECT_TYPE not like '%ARCH%' and OPERATION not in ('DELETE','LIST') order by db_name,START_TIME;

 

RMAN clonning with Duplicate database option

rman target sys/abc123@ibpausa1 catalog rman/rman_oncdbmp@rmanmna2 auxiliary /

run
{
set until time "to_date('30-APR-2010 05:30','DD-MON-YYYY hh24:MI')";
allocate auxiliary channel C1 device type disk;
SET NEWNAME FOR DATAFILE 1 to '/oracle/data/dbpa/usa1/vol30/dbpausa1.system.a001.dbf';
SET NEWNAME FOR DATAFILE 2 to '/oracle/data/dbpa/usa1/vol40/dbpausa1.undo.a001.dbf';
SET NEWNAME FOR DATAFILE 3 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.sbpa0001.a001.dbf';
SET NEWNAME FOR DATAFILE 4 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.users.a001.dbf';
SET NEWNAME FOR DATAFILE 5 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.tools.a001.dbf';
SET NEWNAME FOR DATAFILE 6 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a001.dbf';
SET NEWNAME FOR DATAFILE 7 to '/oracle/data/dbpa/usa1/vol20/dbpausa1.xbpa0001.a001.dbf';
SET NEWNAME FOR DATAFILE 9 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a002.dbf';
SET NEWNAME FOR DATAFILE 10 to '/oracle/data/dbpa/usa1/vol11/dbpausa1.sbpa0002.a003.dbf';
SET NEWNAME FOR DATAFILE 11 to '/oracle/data/dbpa/usa1/vol10/dbpausa1.sysaux.a001.dbf';
SET NEWNAME FOR TEMPFILE 1 to '/oracle/data/dbpa/usa1/vol50/dbpausa1.tps00001.a001.dbf';
duplicate target database to dbpausa1;
}

log_file_name_convert =("/oracle/data/ibpa/usa1/", "/oracle/data/dbpa/usa1/")
db_file_name_convert =("/oracle/data/ibpa/usa1/", "/oracle/data/dbpa/usa1/")


ORACLE noteid's:


Note 228257.1 - RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G
Note 259694.1 - Oracle10G RMAN Database Duplication
Bug 5327865 - CLONING THE DB USING 10G OEM "SEARCH FOR HOST" RESULTS IN (NO ITEMS FOUND)

Clonning Pre steps..........................

PRE -STEPS
set lines 200
set pages 9999
col owner for a20
col db_link for a30
col username for a15
col host for a40
col created for a12
spool db_details.lst
select name from v$database;
select * from global_name;
select * from dba_db_links;
select name from v$controlfile;
select member from v$logfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;
spool off
spool create_db_links.sql
select 'create DATABASE LINK '||NAME|| ' connect to '|| userid || ' identified by '|| password || ' using '||''''|| host ||''''||'; ' FROM
sys.link$ where name='EXTPROC_CONNECTION.CISCO.COM';
spool off
spool alter_user.sql
select ' alter user '||username||' identified by values xxxxx'||password|| 'xxxxx;' from dba_users;
spool off
Set verify off
Set space 0
set feedback off;
set echo off;
set pages 1000;
set lines 150;
spool create_synonym.sql
Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR mailto:'%7C%7Ctable_owner%7C%7C'.'%7C%7Ctable_name%7C%7C'@'%7C%7Cdb_link%7C%7C';' from dba_synonyms where db_link
is not null and table_owner is not null;
Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR mailto:'%7C%7Ctable_name%7C%7C'@'%7C%7Cdb_link%7C%7C';' from dba_synonyms where db_link is not null and
table_owner is null;
spool off;

select count(1) from dba_synonyms where db_link is not null and table_owner is null;

restore database , catalog start with option

catalog start with

CATALOG START WITH '/fs1/datafiles/';

connect target /;
startup force nomount;
restore spfile from '${CTL_PIECE}';
shutdown immediate;
startup nomount;
restore controlfile from '${CTL_PIECE}';
shutdown immediate;
startup mount;
catalog start with '${BACKUP_LOC}' noprompt;
restore database;
recover database;
sql 'alter database open resetlogs';
EOF

RMAN restore commands

restore archivelog from logseq=100 until logseq=110;
restore archivelog from time='15-JUN-05' until time='20-JUN-05';

run {
set archivelog destination to '/oracle/data/dsed/mna1/vol90/';
allocate channel ch1 type disk;
restore archivelog
from logseq 51066
until logseq 51076;
}

run {
set archivelog destination to '';
allocate channel ch1 type disk;
restore archivelog
from logseq 49751
until logseq 49770;
}

RMAN list commands

IST BACKUP;
LIST BACKUP OF DATABASE;
LIST BACKUP SUMMARY;
LIST INCARNATION;
LIST BACKUP BY FILE;
LIST COPY OF DATABASE ARCHIVELOG ALL;
LIST COPY OF DATAFILE 1, 2, 3;
LIST BACKUP OF DATAFILE 11 SUMMARY;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
LIST BACKUPSET OF DATAFILE 1;
LIST BACKUP OF ARCHIVELOG all;
LIST EXPIRED BACKUP;

 list backup of database completed between '01-MAY-07' and '07-MAY-07' ;

 list backup of database;

 list backup of database summary completed between '01-MAY-07' and '07-MAY-07';

 list backup of archivelog all;

list backup of archivelog from time  '05-MAY-07';

list backup of archivelog high sequence=10796;

list backup of archivelog low sequence=10796;
      LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 10966;
list backup of archivelog from time '26-JUN-07';
backup archivelog until time 'SYSDATE-6' delete input;
backup archivelog sequence between 6 and 29 delete input;
list backup of archivelog time between '17-JUN-07' and '18-JUN-07';
list backup of database summary TAG 'clone_db_bk_full';
list backupset completed between '25-OCT-07' and '30-OCT-07' TAG 'clone_db_bk_full';

To get the report in HTML format

conn as sysdba SQL> set markup html on;
spool 1212703_preprod.xls

Get DDL ................

set verify off
set long 100000
set lines 132
set pages 0
prompt 'Pl Enter the Input in Capital Letters............'
accept owner char prompt 'Enter Owner : '
accept object_type char prompt 'Enter Object Type [TABLE/INDEX/MATERIALIZED VIEW/PACKAGE/PROCEDURE/FUNCTION] : '
accept object_name char prompt 'Enter Object Name : '
spool get_ddl
select dbms_metadata.get_ddl('&&object_type','&&object_name','&&owner') from dual;
spool off

No . of Insertions in the Database

 select substr(sql_text,instr(sql_text,'INTO "PRO'),50) Inserts,
         rows_processed,
         round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
         trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
  from   sys.v_$sqlarea
  where  upper(sql_text) like 'INSERT %' and sql_text like '%SL%';

UNIX Commands For ORACLE

list commands:

ls –ltr

cat alert_LIN1.log | grep -i ORA-

cat /proc/cpuinfo

process commands:

ps –ef |grep “ora_”

ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID

ps -ef |awk '{print $1}'|sort -u

change dir :

cd ..

view command:

vi

view

more

Find the CPU count :

grep -c processor /proc/cpuinfo

uname –X

/usr/sbin/psrinfo |wc –l

/usr/sbin/psrinfo -p

Memory chechecking

dmesg | grep Memory

cat /proc/meminfo

AIX $ /usr/sbin/lsattr -E -l sys0 -a realmem
Linux $ /usr/sbin/dmesg | grep "Physical:"
HP $ grep MemTotal /proc/meminfo
Solaris $ /usr/sbin/prtconf | grep "Memory size"
Tru64 $ /bin/vmstat -P | grep "Total Physical Memory"

Display RAM Memory size on Solaris
prtconf |grep -i mem

:

Display RAM memory size on AIX
First determine name of memory device
lsdev -C |grep mem
then assuming the name of the memory device is ‘mem0’
lsattr -El mem0


Display the number of CPU’s in Solaris
psrinfo -v | grep “Status of processor”|wc –l

Display the number of CPU’s in AIX
lsdev –C | grep Process|wc –l

cpu performance:

TOP

TOPAS

SAR

$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):

Time	%usr	%sys	%wio	%idle
11:57:31	72	28	0	0
11:57:41	70	30	0	0
11:57:51	70	30	0	0
11:58:01	68	32	0	0
11:58:11	67	33	0	0
11:58:21	65	28	0	7
11:58:31	73	27	0	0
11:58:41	69	31	0	0
Average	69	30	0	1

%usr: Percent of CPU in user mode

%sys: Percent of CPU in system mode

%wio: Percent of CPU running idle with a process waiting for block I/O

%idle: Percent of CPU that is idle

SAR  -u  <CPU report >

Sar –d <disk usage >

to create the file :

touch

cat

disk free:

df -k

disk usage:

du -sg

remove the file

rm -rf

remove dir:

rmdir

rm -rf

copy or move

cp <source to dist>

move <sorce to dest>

cp -r <inclusing sub dir>

last and first lines:

tail -n filename

head -n filename

echo <variable>

chmode -R 777 <filename>

chown oracle:dba <dir name>

chgrp filename

FIND and REMOVE :

find /oracle/sftw -name <filename>

find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;

FTP commands:

ftp servername

ftp>binary

get

put

cd

!pwd

pwd

bye

ls

Mail commands:

mail -s "Test mail"  "sss@gmail.com" < msg.log

who

kill -9 <process id>

hostname

uname -a

telnet <hostname>

ifconfig

jobs

sysreserve

User management :

useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user

• The "-G" flag specifies the primary group.
• The "-g" flag specifies the secondary group.
• The "-d" flag specifies the default directory.
• The "-m" flag creates the default directory.
• The "-s" flag specifies the default shell.

usermod -s /bin/csh my_user

userdel -r my_user

passwd my_user

wc –l ===è gives the count                         

COMPRESS the files :

Gzip <filename >

Zip <filename>

Compress <filename>

Gunzip < filename >

Unzip <filename>

Uncompress < filename >

SYSTEM statistics :

VMSTAT

DISPLAY < hostname > :<port no >

Export DISPLAY

Tar –cvf <filename>.tar <file/dir loc>

Tar –xvf <filrname>.tar

History

Date

Time

Whoiam

Diff

At

Crontab

Uptime --- server uptime

Export Exit Codes

INTRODUCTION
============
This is a short reference note describing the EXIT codes (errorlevel)
used by the Export and Import utilities.

EXIT CODES
==========
A. Successful export/import.
   -------------------------
   Result    : Export terminated successfully without warnings
               Import terminated successfully without warnings
   Exit Code : EX_SUCC
   Exit Level: 0
B. Successful export/import with warnings.
   ---------------------------------------
   Result    : Export terminated successfully with warnings
               Import terminated successfully with warnings
   Exit Code : EX_OKWARN
   Exit Level: 0 (for Unix platforms)
               1 (for Windows platform with Oracle9i, Oracle8i, and below)
               3 (for Windows platform with Oracle10g and higher)
C. Unsuccessful export/import.
   ---------------------------
   Result    : Export terminated unsuccessfully
               Import terminated unsuccessfully
   Exit Code : EX_FAIL
   Exit Level: 1 (for Unix platforms, and for Windows platforms with
                  Oracle10g and higher)
               3 (for Windows platform with Oracle9i, Oracle8i, and below)

CHECK
=====
To determine the errorlevel at the commandline after a command ends:
- Unix Bourne/Korn shell:
 echo $?
- Unix C Shell:
 echo $status
- Windows:
 echo %ERRORLEVEL%

echo %ORACLE_SID%

SET COMMANDS

ECHO {OFF|ON}
   Display commands as they are executed

HEA[DING] {OFF|ON}
   print column headings

LIN[ESIZE] {150|n}
   Width of a line (before wrapping to the next line)
   Earlier versions default to 80, Oracle 9 is 150

LONG {80|n}
   Set the maximum width (in chars) for displaying and copying LONG values.

PAGES[IZE] {14|n}
   The height of the page - number of lines.
   0 will suppress all headings, page breaks, titles

set timing on;
set time off;

DBMS_JOBS examples

--
-- Schedule a snapshot to be run on this instance every hour
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour
-- ------------------------------------------------------------
dbms_job.submit(
:jobno, 'statspack.snap;',
trunc(sysdate)+6/24,
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+9/24,
'trunc(SYSDATE+12/24,''HH'')',
TRUE,
:instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/144,'MI'),
'trunc(sysdate+1/144,''MI'')',
TRUE,
:instno);
-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE,''MONDAY''),
next_day(SYSDATE,''TUESDAY''),
next_day(SYSDATE,''WEDNESDAY''),
next_day(SYSDATE,''THURSDAY''),
next_day(SYSDATE,''FRIDAY'')
)
+1/24,''HH'')',
TRUE,
:instno);
commit;
end;
/

Piped EXPORT/IMPORT

mknod is used to create special files,

*  Create a block-type special file
*  Create a character-type special file
*  Create a FIFO (named pipe)


$mknod name b major minor
Creates a block-type special file
major, minor are device numbers

$mknod name c major minor
Creates a character-type special file

$mknod name p
Creates a FIFO named pipe.

Examples:
If datafiles are larger than 2GB, pipes can be used to read or extract information
from compressed datafiles.

In Oracle a table can contain more than 2GB of data, but lower versions of Solaris 2.x
doesn't support larger files. We can use unix pipes to export data and compress. Similar
way we can import data without uncomressing an export dump file.

Below is an example of how to use UNIX's named pipes for an exp/imp:

Creating a compressed export file

   $ mknod /tmp/exp_pipe p                     # Create pipe
   $ compress < /tmp/exp_pipe > export.dmp.Z & # Compress
   $ exp file=/tmp/exp_pipe &ltother options>   # Export to the pipe
 
Reading a compressed export file

   $ mknod /tmp/imp_pipe p                       # Create pipe
   $ uncompress < export.dmp.Z > /tmp/imp_pipe & # Uncompress
   $ imp file=/tmp/imp_pipe < other options >  # Import from the pipe
 
Exporting to tape via unix named pipes
  
   $ mknod /tmp/exp_pipe p                     # Create pipe
   $ dd if=/tmp/exp_pipe of=< tape device> &    # Write from pipe to tape
   $ exp file=/tmp/exp_pipe < other options >  # Export to the pipe 

Importing from tape via unix named pipes

   $ mknod /tmp/imp_pipe p                     # Create pipe
   $ dd of=/tmp/imp_pipe if=< tape device> &    # Write from tape to pipe
   $ imp file=/tmp/imp_pipe < other options >  # Import from the pipe 

SQL*Loading from tape via unix named pipes

   $ mknod /tmp/ldr_pipe p                         # Create pipe
   $ dd of=/tmp/ldr_pipe if=< tape device> &        # Write from tape to pipe
   $ sqlload data=/tmp/ldr_pipe < other options >  # Load from the pipe 

To Check Last analyzed table date ...........

select count(1),owner,trunc(last_analyzed) from dba_tables where owner not in ('SYS','SYSTEM','OUTLN','PERSTAT') group by owner,trunc(last_analyzed) order by owner,trunc(last_analyzed);