MADHU ORACLE APPS & RAC DBA: 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

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;