Cloning Database and Oracle Homes Complete using RMAN

Cloning Database and Oracle Homes Complete using RMAN:

===================================================================

Steps for CLONING USING RMAN RECOVERY WITH ANY STORAGE MANAGER backups:

1. Set the Storage Manager & Database environment variables. (Specific to the Storage Management Used)

. oraenv

2. Create the dump directories. Copy the init.ora from source and modify the dump locations, control file locations and archive log location.

3. Startup nomount the DB with this modified pfile in the destination server

RMAN target /

RMAN> startup nomount;

Oracle instance started

Total System Global Area     654311424 bytes

Fixed Size                     2074960 bytes

Variable Size                234882736 bytes

Database Buffers             394264576 bytes

Redo Buffers                  23089152 bytes

4. Set the DBID of source,

RMAN> set DBID 3483462655

executing command: SET DBID

5. Took the latest backup piece which included the controlfile. (Information abt backup tag can be derived from “list backup” command.

RMAN> run

{

allocate channel ch1 type 'SBT_TAPE';

restore controlfile from 'SID_27kki6bc_1_1';

release channel ch1;

}

2> 3> 4> 5> 6>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: sid=155 devtype=SBT_TAPE

channel ch1: NMO v4.5.0.0

Starting restore at 20-JUL-09

channel ch1: restoring control file

channel ch1: restore complete, elapsed time: 00:00:28

output filename=/backup/SID/control/control01.ctl

output filename=/backup/SID/control/control02.ctl

Finished restore at 20-JUL-09

released channel: ch1

6. Mount the DB,

RMAN> alter database mount;

database mounted

RMAN>

7. Take the file_id and file_name from source database as follows,

SQL> column file_name form a60

SQL> select file_id,file_name from dba_data_files;

         1 /data/oracle/d01/SID/data/system01.dbf

         2 /data/oracle/u01/SID/data/undo01.dbf

         3 /data/oracle/d01/SID/data/sysaux01.dbf

         4 /data/oracle/d01/SID/data/usr01.dbf

         5 /data/oracle/d02/SID/data/small_data01.dbf

         6 /data/oracle/d03/SID/data/small_indx01.dbf

         7 /data/oracle/d02/SID/data/wrkdata01.dbf

         8 /data/oracle/d03/SID/data/wrkindx01.dbf

         9 /data/oracle/d02/SID/data/data01.dbf

        10 /data/oracle/d03/SID/data/indx01.dbf

        11 /data/oracle/d02/SID/data/lob01.dbf

        12 /data/oracle/d02/SID/data/objdata01.dbf

        13 /data/oracle/d03/SID/data/objindx01.dbf

        14 /data/oracle/d02/SID/data/lnkdata01.dbf

        15 /data/oracle/d03/SID/data/lnkindx01.dbf

15 rows selected.

8. Prepare the restore script as follows,

run{

allocate channel ch1 type 'SBT_TAPE';

allocate channel ch2 type 'SBT_TAPE';

allocate channel ch3 type 'SBT_TAPE';

allocate channel ch4 type 'SBT_TAPE';

set newname for datafile 1 to '/backup/SID/data/system01.dbf';

set newname for datafile 2 to '/backup/SID/data/undo01.dbf';

set newname for datafile 3 to '/backup/SID/data/sysaux01.dbf';

set newname for datafile 4 to '/backup/SID/data/usr01.dbf';

set newname for datafile 5 to '/backup/SID/data/small_data_01.dbf';

set newname for datafile 6 to '/backup/SID/data/small_indx01.dbf';

set newname for datafile 7 to '/backup/SID/data/wrkdata01.dbf';

set newname for datafile 8 to '/backup/SID/data/wrkindx01.dbf';

set newname for datafile 9 to '/backup/SID/data/data01.dbf';

set newname for datafile 10 to '/backup/SID/data/indx01.dbf';

set newname for datafile 11 to '/backup/SID/data/lob01.dbf';

set newname for datafile 12 to '/backup/SID/data/objdata01.dbf';

set newname for datafile 13 to '/backup/SID/data/objindx01.dbf';

set newname for datafile 14 to '/backup/SID/data/lnkdata01.dbf';

set newname for datafile 15 to '/backup/SID/data/lnkindx01.dbf';

RESTORE DATABASE;

switch datafile all;

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

}

RMAN> run{

2> allocate channel ch1 type 'SBT_TAPE';

3> allocate channel ch2 type 'SBT_TAPE';

4> allocate channel ch3 type 'SBT_TAPE';

5> allocate channel ch4 type 'SBT_TAPE';

6> set newname for datafile 1 to '/backup/SID/data/system01.dbf';

7> set newname for datafile 2 to '/backup/SID/data/undo01.dbf';

8> set newname for datafile 3 to '/backup/SID/data/sysaux01.dbf';

9> set newname for datafile 4 to '/backup/SID/data/usr01.dbf';

10> set newname for datafile 5 to '/backup/SID/data/small_data_01.dbf';

11> set newname for datafile 6 to '/backup/SID/data/small_indx01.dbf';

12> set newname for datafile 7 to '/backup/SID/data/wrkdata01.dbf';

13> set newname for datafile 8 to '/backup/SID/data/wrkindx01.dbf';

14> set newname for datafile 9 to '/backup/SID/data/data01.dbf';

15> set newname for datafile 10 to '/backup/SID/data/indx01.dbf';

16> set newname for datafile 11 to '/backup/SID/data/lob01.dbf';

17> set newname for datafile 12 to '/backup/SID/data/objdata01.dbf';

18> set newname for datafile 13 to '/backup/SID/data/objindx01.dbf';

19> set newname for datafile 14 to '/backup/SID/data/lnkdata01.dbf';

20> set newname for datafile 15 to '/backup/SID/data/lnkindx01.dbf';

21> RESTORE DATABASE;

22> switch datafile all;

23> release channel ch1;

24> release channel ch2;

25> release channel ch3;

26> release channel ch4;

27> }

allocated channel: ch1

channel ch1: sid=155 devtype=SBT_TAPE

channel ch1: NMO v4.5.0.0

allocated channel: ch2

channel ch2: sid=154 devtype=SBT_TAPE

channel ch2: NMO v4.5.0.0

allocated channel: ch3

channel ch3: sid=153 devtype=SBT_TAPE

channel ch3: NMO v4.5.0.0

allocated channel: ch4

channel ch4: sid=152 devtype=SBT_TAPE

channel ch4: NMO v4.5.0.0

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-JUL-09

channel ch1: starting datafile backupset restore

channel ch1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /backup/SID/data/usr01.dbf

restoring datafile 00007 to /backup/SID/data/wrkdata01.dbf

restoring datafile 00015 to /backup/SID/data/lnkindx01.dbf

channel ch1: reading from backup piece SID_1qkkfgsa_1_1

channel ch1: restored backup piece 1

piece handle=SID_1qkkfgsa_1_1 tag=SID

channel ch1: restore complete, elapsed time: 00:12:45

channel ch1: starting datafile backupset restore

channel ch1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /backup/SID/data/system01.dbf

restoring datafile 00010 to /backup/SID/data/indx01.dbf

restoring datafile 00011 to /backup/SID/data/lob01.dbf

restoring datafile 00012 to /backup/SID/data/objdata01.dbf

channel ch1: reading from backup piece SID_1rkkfgsa_1_1

channel ch1: restored backup piece 1

piece handle=SID_1rkkfgsa_1_1 tag=SID

channel ch1: restore complete, elapsed time: 00:12:45

channel ch1: starting datafile backupset restore

channel ch1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /backup/SID/data/undo01.dbf

restoring datafile 00003 to /backup/SID/data/sysaux01.dbf

restoring datafile 00005 to /backup/SID/data/small_data_01.dbf

restoring datafile 00009 to /backup/SID/data/data01.dbf

channel ch1: reading from backup piece SID_1okkfgs9_1_1

channel ch1: restored backup piece 1

piece handle=SID_1okkfgs9_1_1 tag=SID

channel ch1: restore complete, elapsed time: 00:14:36

channel ch1: starting datafile backupset restore

channel ch1: specifying datafile(s) to restore from backup set

restoring datafile 00006 to /backup/SID/data/small_indx01.dbf

restoring datafile 00008 to /backup/SID/data/wrkindx01.dbf

restoring datafile 00013 to /backup/SID/data/objindx01.dbf

restoring datafile 00014 to /backup/SID/data/lnkdata01.dbf

channel ch1: reading from backup piece SID_24kki58g_1_1

channel ch1: restored backup piece 1

piece handle=SID_24kki58g_1_1 tag=SID

channel ch1: restore complete, elapsed time: 00:11:55

Finished restore at 20-JUL-09

datafile 1 switched to datafile copy

input datafile copy recid=16 stamp=692727208 filename=/backup/SID/data/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=17 stamp=692727208 filename=/backup/SID/data/undo01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=18 stamp=692727208 filename=/backup/SID/data/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=19 stamp=692727208 filename=/backup/SID/data/usr01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=20 stamp=692727208 filename=/backup/SID/data/small_data_01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=21 stamp=692727208 filename=/backup/SID/data/small_indx01.dbf

datafile 7 switched to datafile copy

input datafile copy recid=22 stamp=692727208 filename=/backup/SID/data/wrkdata01.dbf

datafile 8 switched to datafile copy

input datafile copy recid=23 stamp=692727208 filename=/backup/SID/data/wrkindx01.dbf

datafile 9 switched to datafile copy

input datafile copy recid=24 stamp=692727208 filename=/backup/SID/data/data01.dbf

datafile 10 switched to datafile copy

input datafile copy recid=25 stamp=692727208 filename=/backup/SID/data/indx01.dbf

datafile 11 switched to datafile copy

input datafile copy recid=26 stamp=692727208 filename=/backup/SID/data/lob01.dbf

datafile 12 switched to datafile copy

input datafile copy recid=27 stamp=692727208 filename=/backup/SID/data/objdata01.dbf

datafile 13 switched to datafile copy

input datafile copy recid=28 stamp=692727208 filename=/backup/SID/data/objindx01.dbf

datafile 14 switched to datafile copy

input datafile copy recid=29 stamp=692727208 filename=/backup/SID/data/lnkdata01.dbf

datafile 15 switched to datafile copy

input datafile copy recid=30 stamp=692727208 filename=/backup/SID/data/lnkindx01.dbf

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

9. Recover the database,

RMAN> recover database;

Starting recover at 20-JUL-09

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: NMO v4.5.0.0

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

starting media recovery

channel ORA_SBT_TAPE_1: starting archive log restore to default destination

channel ORA_SBT_TAPE_1: restoring archive log

archive log thread=1 sequence=3012

channel ORA_SBT_TAPE_1: reading from backup piece 22kkfkfm_1_1

channel ORA_SBT_TAPE_1: restored backup piece 1

piece handle=22kkfkfm_1_1 tag=TAG20090718T205239

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:36

archive log filename=/backup/SID/arch/SID_1_3012_660337855.dbf thread=1 sequence=3012

unable to find archive log

archive log thread=1 sequence=3013

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/20/2009 16:15:40

RMAN-06054: media recovery requesting unknown log: thread 1 seq 3013 lowscn 80567494

10. Sequence 3013 is not found in the backupset. We should switch log before taking the archive logs backup.

RMAN> run

{

allocate channel ch1 type 'SBT_TAPE';

restore archivelog sequence 3013;

release channel ch1;

}2> 3> 4> 5> 6>

released channel: ORA_SBT_TAPE_1

released channel: ORA_DISK_1

allocated channel: ch1

channel ch1: sid=155 devtype=SBT_TAPE

channel ch1: NMO v4.5.0.0

Starting restore at 20-JUL-09

released channel: ch1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/20/2009 16:25:46

RMAN-20242: specification does not match any archive log in the recovery catalog

11. Copied the archive log file with sequence 3013 from source manually and do the recovery as follows,

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 80567494 generated at 07/18/2009 20:52:37 needed for thread 1

ORA-00289: suggestion : /backup/SID/arch/SID_1_3013_660337855.dbf

ORA-00280: change 80567494 for thread 1 is in sequence #3013

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 80622051 generated at 07/19/2009 20:37:24 needed for thread 1

ORA-00289: suggestion : /backup/SID/arch/SID_1_3014_660337855.dbf

ORA-00280: change 80622051 for thread 1 is in sequence #3014

ORA-00278: log file '/backup/SID/arch/SID_1_3013_660337855.dbf' no

longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> !echo $ORACLE_SID

SID

12. Tried to open the db,

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00344: unable to re-create online log

'/data/oracle/l01/SID/data/redo01.log'

ORA-27040: file create error, unable to create file

IBM AIX RISC System/6000 Error: 2: No such file or directory

13. Renamed all the redo log files,

SQL> alter database rename file '/data/oracle/l01/SID/data/redo01.log' to '/backup/SID/redo/redo01.log';

Database altered.

SQL> alter database rename file '/data/oracle/l01/SID/data/redo02.log' to '/backup/SID/redo/redo02.log';

Database altered.

SQL> alter database rename file '/data/oracle/l01/SID/data/redo03.log' to '/backup/SID/redo/redo03.log';

Database altered.

14. Opened the database,

SQL> alter database open resetlogs;

Database altered.

CLONING USING RMAN INCOMPLETE RECOVERY (DISK BACKUPS) With Shell script automation.

Make sure backup location must be same in Source and Destination

Sample Set Newname commands syntax with RESTORE DATABASE.

cat rman_restore_SID.sql

run{

set newname for datafile 1 to '/13FEB2009/18FEB2009/SID/d01/SID_system_01.dbf';

set newname for datafile 2 to '/13FEB2009/18FEB2009/SID/u01/SID_undo_01.dbf';

set newname for datafile 3 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_reflexis_01.dbf';

set newname for datafile 4 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_reflexis_02.dbf';

set newname for datafile 5 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_idx_01.dbf';

set newname for datafile 6 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_idx_02.dbf';

set newname for datafile 7 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_workload_01.dbf';

set newname for datafile 8 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_workload_02.dbf';

set newname for datafile 9 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_workload_03.dbf';

set newname for datafile 10 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrtask_01.dbf';

set newname for datafile 11 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrtask_02.dbf';

set newname for datafile 12 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrtask_03.dbf';

set newname for datafile 13 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_survey_01.dbf';

set newname for datafile 14 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_survey_02.dbf';

set newname for datafile 15 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_notes_01.dbf';

set newname for datafile 16 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_notes_02.dbf';

set newname for datafile 17 to '/13FEB2009/18FEB2009/SID/d01/SID_ca_tools_01.dbf';

set newname for datafile 18 to '/13FEB2009/18FEB2009/SID/d01/SID_tools_01.dbf';

set newname for datafile 19 to '/13FEB2009/18FEB2009/SID/d01/SID_users_01.dbf';

set newname for datafile 20 to '/13FEB2009/18FEB2009/SID/d01/SID_statspack_01.dbf';

set newname for datafile 21 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrproj_01.dbf';

set newname for datafile 22 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrproj_02.dbf';

set newname for datafile 23 to '/13FEB2009/18FEB2009/SID/d01/SID_tools_02.dbf';

set newname for datafile 24 to '/13FEB2009/18FEB2009/SID/d01/SID_users_02.dbf';

set newname for datafile 25 to '/13FEB2009/18FEB2009/SID/d01/SID_users_03.dbf';

RESTORE DATABASE;

switch datafile all;

}

Sample Restore.ksh shell script to automate restore

Cat rman_restore_SID.ksh

#!/bin/ksh

export ORACLE_HOME=/upgrade/oracle/9.2.0

export ORACLE_SID=SID

export PATH=$PATH:$ORACLE_HOME/bin

DTIME="`date +%m%d%y`_`date +%H%M%S`"

rman log=/backup/rman_backup/SID/rman_restore_${ORACLE_SID}_$DTIME.log<<EOF

connect target /

@/backup/rman_backup/SID/rman_restore_SID.sql

exit;

EOF

cat /backup/rman_backup/SID/rman_restore_${ORACLE_SID}_$DTIME.log|mail -s "$ORACLE_SID RMAN restore completed" emailaddress@domainname.com

1. copy the Rman Backup files from Source /backup/rman_backup/SID to Destination

-rw-r-----   1 oracle   dba      2335178752 Dec 11 01:11 SID_20081211_673146452_6282_1.dbf

-rw-r-----   1 oracle   dba      3261030400 Dec 11 01:12 SID_20081211_673146452_6283_1.dbf

-rw-r-----   1 oracle   dba        33603584 Dec 11 01:12 SID_20081211_673146720_6285_1.dbf

-rw-r-----   1 oracle   dba        33079296 Dec 11 01:12 SID_20081211_673146727_6286_1.dbf

-rw-r-----   1 oracle   dba      4751187968 Dec 11 01:12 SID_20081211_673146452_6281_1.dbf

-rw-r-----   1 oracle   dba      6508011520 Dec 11 01:13 SID_20081211_673146452_6284_1.dbf

-rw-r-----   1 oracle   dba         7667712 Dec 11 01:13 SID_20081211_673146811_6287_1.ctl

-rw-r-----   1 oracle   dba      1399425536 Dec 11 01:15 SID_20081211_673146813_6291_1.arc

-rw-r-----   1 oracle   dba      1670883328 Dec 11 01:15 SID_20081211_673146813_6290_1.arc

2.Make an entry for SID in /etc/oratab in the destination server

3.Copy the initSID.ora file from source to $ORACLE_HOME/dbs in tb168 and make necessary changes

4.Create the required directories in Destination for *dump,control,data,log,temp,undo and archive files:

5.Restore DB:

Set the Environment

rman target /

RMAN> startup nomount

RMAN> restore controlfile from '/backup/rman_backup/SID/SID_20090218_679108563_7372_1.ctl';

RMAN> alter database mount;

RMAN> run

{

allocate channel ch1 type disk;

allocate channel ch2 type disk;

allocate channel ch3 type disk;

set newname for datafile 1 to '/13FEB2009/18FEB2009/SID/d01/SID_system_01.dbf';

set newname for datafile 2 to '/13FEB2009/18FEB2009/SID/u01/SID_undo_01.dbf';

set newname for datafile 3 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_reflexis_01.dbf';

set newname for datafile 4 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_reflexis_02.dbf';

set newname for datafile 5 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_idx_01.dbf';

set newname for datafile 6 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_idx_02.dbf';

set newname for datafile 7 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_workload_01.dbf';

set newname for datafile 8 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_workload_02.dbf';

set newname for datafile 9 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_workload_03.dbf';

set newname for datafile 10 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrtask_01.dbf';

set newname for datafile 11 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrtask_02.dbf';

set newname for datafile 12 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrtask_03.dbf';

set newname for datafile 13 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_survey_01.dbf';

set newname for datafile 14 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_survey_02.dbf';

set newname for datafile 15 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_notes_01.dbf';

set newname for datafile 16 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_notes_02.dbf';

set newname for datafile 17 to '/13FEB2009/18FEB2009/SID/d01/SID_ca_tools_01.dbf';

set newname for datafile 18 to '/13FEB2009/18FEB2009/SID/d01/SID_tools_01.dbf';

set newname for datafile 19 to '/13FEB2009/18FEB2009/SID/d01/SID_users_01.dbf';

set newname for datafile 20 to '/13FEB2009/18FEB2009/SID/d01/SID_statspack_01.dbf';

set newname for datafile 21 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrproj_01.dbf';

set newname for datafile 22 to '/13FEB2009/18FEB2009/SID/d01/SID_tbs_csstrproj_02.dbf';

set newname for datafile 23 to '/13FEB2009/18FEB2009/SID/d01/SID_tools_02.dbf';

set newname for datafile 24 to '/13FEB2009/18FEB2009/SID/d01/SID_users_02.dbf';

set newname for datafile 25 to '/13FEB2009/18FEB2009/SID/d01/SID_users_03.dbf';

RESTORE DATABASE;

switch datafile all;

release channel ch1;

release channel ch2;

release channel ch3;

}

6.Recover DB by applying Archive log files

. oraenv

SID

 recover database until time '2009-02-18 15:00:00' using backup controlfile parallel 4;

ORA-00279: change 248404673 generated at 02/13/2009 12:40:37 needed for thread 1

ORA-00289: suggestion : /13FEB2009/SID/a01/SID_1_1015.arc

ORA-00280: change 248404673 for thread 1 is in sequence #1015

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ENTER

7.Once this is completed please Open the database with RESETLOGS

 Alter database open resetlogs.

RMAN> alter database open resetlogs;

Changing the Database to No Archiving Mode :-

Init.ora parameters:-

---------------------

LOG_ARCHIVE_START=FALSE

Database:-

----------

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

archive log list;

alter system switch logfile;

alter database backup controlfile to trace;

shutdown immediate;

startup;

HOW TO CLONE ORACLE HOME

1. Take a TAR of the Source Oracle Home

cd /u01/app/oracle/product/10.2.0.2/db_q1_0

mkdir /u80/clone

cd /u01/app/oracle/product/10.2.0.2/db_q1_0

tar -cvf /u80/clone/db_q1_0.tar .

2. Create a Home directory and extract the tar file there in Destination server

cd /u01/app/oracle/product/10.2.0.2

mkdir db_q1_1

cd /u01/app/oracle/product/10.2.0.2/db_q1_1

tar -xvf /u80/clone/db_q1_0.tar

3. Use Clone.pl to link that Oracle Home

export ORACLE_HOME=/u01/app/oracle/product/10.2.0.2/db_q1_1

export PATH=$ORACLE_HOME:$PATH

cd $ORACLE_HOME/clone/bin

perl clone.pl ORACLE_HOME="/u01/app/oracle/product/10.2.0.2/db_q1_1" ORACLE_HOME_NAME="ora10202clone"

4. Run root.sh

Run the below script as "root" user

/u01/app/oracle/product/10.2.0.2/db_q1_1/root.sh

Detail Description (Example) (FOR RAC Environment)

1.As root, create a tar file of the RAC database Oracle home.

cd /u01/app/oracle/product/10.2.0.2/

tar -cvf /tmp/db_q3_0.tar .

2.Copy the RAC database tar from the backedup source to the target Oracle home, as root, uncompress the tar file.

cd /u01/app/oracle/product/

mkdir 10.2.0.2_clone

cd /u01/app/oracle/product/10.2.0.2_clone

tar -xvf /tmp//tmp/db_q3_0.tar

3.Run OUI in clone mode, as described in Oracle Universal Installer and OPatch User's Guide.

export ORACLE_HOME= /u01/app/oracle/product/10.2.0.2/<new_home_name>

export PATH= $ORACLE_HOME:$PATH

cd $ORACLE_HOME/clone/bin

perl clone.pl  ORACLE_HOME="/u01/app/oracle/product/10.2.0.2_clone" ORACLE_HOME_NAME="ora10202clone"

4.Run root scripts

Run below script as "root" user

/u01/app/oracle/product/10.2.0.2_clone/db_q3_0/root.sh

5.Repeat steps 1 through 7 for on each node ie,(node1 and node2)

Concurrent request running abnormally for very long time

Concurrent request running abnormally for very long time without completion:

1. Identified the long running query by taking trace of the concurrent request
2. Executed the tuning advisor for the problematic query using the following steps

Create Tuning Task:
===============
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '0j3dqg3a8aj8c',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/


SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100

--***************************
--TO GET SUMMARY INFORMATION
--***************************

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

--***************************
--TO GET DETAILED INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;

Sql queries to check ACTIVE / INACTIVE Sessions:

Sql queries to check ACTIVE / INACTIVE Sessions:

Total Count of sessions

select count(s.status) TOTAL_SESSIONS

from gv$session s;

Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) "INACTIVE SESSIONS > 1HOUR "

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status='INACTIVE';

COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='ACTIVE';

TOTAL SESSIONS COUNT ORDERED BY PROGRAM

col program for a30

select s.program,count(s.program) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.program;

TOTAL COUNT OF SESSIONS ORDERED BY MODULE

col module  for a30

prompt TOTAL SESSIONS

select s.module,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.module;

TOTAL COUNT OF SESSIONS ORDERED BY ACTION

col action for a30

prompt TOTAL SESSIONS

select s.action,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.action;

INACTIVE SESSIONS

prompt INACTIVE SESSIONS

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE

prompt INACTIVE SESSIONS

select count(s.status) INACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE PROGRAMS

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, v$process p

where  p.addr=s.paddr and

s.status='INACTIVE';

INACTIVE PROGRAMS with disk reads

prompt INACTIVE SESSIONS

select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

GROUP BY S.PROGRAM;

INACTIVE SESSIONS COUNT WITH PROGRAM

col program for a30

prompt TOTAL INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.program

order by 2 desc;

TOTAL INACTIVE SESSIONS MORE THAN 1HOUR

col program for a30

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

TOTAL INACTIVE SESSIONS GROUP BY  MODULE

col program for a60

COL MODULE FOR A30

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.module,count(s.module) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.module;

INACTIVE SESSION DETAILS MORE THAN 1 HOUR

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

INACTIVE PROGRAM  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

And s.program='&PROGRAM_NAME'

order by last_call_et;

INACTIVE MODULES  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

And s.module like '%order_cleanup_hazmat_v3.sql'

order by last_call_et;

INACTIVE JDBC SESSIONS

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,

s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR

SELECT COUNT(P.SPID)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

FORMS

TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%');

FORMS SESSIONS DETAILS

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600

order by spid;                      

col machine for a15

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

S.ACTION,s.process Client_Process,s.machine

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600;         

order by 4;                           

INACTIVE FORMS SESSIONS DETAILS

col program for a15

col last_call_et for 999.99

select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

order by last_call_et desc;

UNIQUE SPID

select unique(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

COUNT FORMS

select COUNT(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

ZERO HASH VALUE

select COUNT(p.spid)

from gv$session s,gv$process p

where

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME

select count(s.sid) from v$session S

where s.action like ('%&ACTION%')

AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%')

group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152

col spid for a10

col process_spid for a10

col user_name for a20

col form_name for a20

select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,

d.user_form_name "FORM_NAME"

from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,

apps.fnd_form_tl d

where

a.login_id=b.login_id

and c.user_name like 'JROMO'

and a.user_id=c.user_id

and trunc(b.start_time) >trunc(sysdate -11)

and trunc(b.end_time) is null

and b.form_id=d.form_id

and d.language='US';

INACTIVE FORM

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like ('FRM%')

and s.last_call_et > (3600*3)

order by last_call_et;

INACTIVE FORM SESSIONS 

col cli_proc for a9

COL AUDSID FOR A6

COL PID FOR A6

COL SID FOR A5

COL FORM_NAME FOR A25

COL USER_NAME FOR A15

col last_call_et for 9999.99

SELECT

-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */

( SELECT SUBSTR ( fu.user_name, 1, 20 )

FROM apps.fnd_user fu

WHERE fu.user_id = fl.user_id

) user_name,vs.status,

TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,

TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,

vs.last_call_et/3600 last_call_et,

SUBSTR ( fl.process_spid, 1, 6 ) spid,

SUBSTR ( vs.process, 1, 8 ) cli_proc,

SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,

SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,

SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,

SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,

SUBSTR ( vs.module || ' - ' ||

( SELECT SUBSTR ( ft.user_form_name, 1, 40 )

FROM apps.fnd_form_tl ft

WHERE ft.application_id = rf.form_appl_id

AND ft.form_id        = rf.form_id

AND ft.language       = USERENV('LANG')

), 1, 40 ) form_name

FROM apps.fnd_logins           fl,

gv$process            vp,

apps.fnd_login_resp_forms rf,

gv$session            vs

WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */

AND fl.login_type   = 'FORM'

AND fl.process_spid = vp.spid

AND fl.pid          = vp.pid

AND fl.login_id     = rf.login_id

AND rf.end_time    IS NULL

AND rf.audsid       = vs.audsid

and vs.status='INACTIVE'

ORDER BY

vs.process,

fl.process_spid;

ACTIVE

prompt ACTIVE SESSIONS

select count(s.status) ACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='ACTIVE';

MODULE

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr 

and s.MODULE like ('&MODULE_NAME_1HR%')

and s.last_call_et > ('&TIME_HRS' * 3600)

order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S

where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0

AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.machine like ('%&CLIENT_MACHINE%') AND

p.addr=s.paddr and

s.status='INACTIVE';

GROUP BY  program

col program for a60

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr  AND

s.machine like ('%&CLIENT_MACHINE%') AND

s.status='INACTIVE'

group by s.program;