Restore and Recover database to a new host

Restore and Recover database to a new host



In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.


1) Machine(Source)


RMAN> backup database;
Starting backup at 06-MAY-08using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbfinput datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbfinput datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbfinput datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbfinput datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbfinput datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbfinput datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbfinput datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbfchannel ORA_DISK_1: starting piece 1 at 06-MAY-08channel ORA_DISK_1: finished piece 1 at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:35Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 06-MAY-08

2)Transfer this two backup pieces to target machine(From Neptune) bash-3.00$
scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/Password:o1_mf_nnndf_TAG20080 100% *********************************************** 525 MB 00:59bash-3.00
$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/Password:o1_mf_s_654016132_42 100% *********************************************** 6976 KB 00:00
3)Determine the DBID of source machine()

SQL> select dbid from v$database;DBID----------3386862614

4)Now perform task on target machine(Saturn here).First set ORACLE_SID,-bash-3.00
$export ORACLE_SID=dbase1

Then connect to rman,-bash-3.00

$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database (not started)

5)Set DBID and restore spfile to pfile.

RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfileOracle instance started

Total System Global Area 159383552 bytesFixed Size 2019224 bytesVariable Size 67108968 bytesDatabase Buffers 83886080 bytesRedo Buffers 6369280 bytes

RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';

Starting restore at 06-MAY-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkpchannel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location 6)start the instance with pfile.

RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';

Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytesVariable Size 109055720 bytesDatabase Buffers 92274688 bytesRedo Buffers 6365184 bytes

7)Restore controlfile and mount the database.

RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08using channel ORA_DISK_1
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output filename=/oradata2/DBase1/control01.ctloutput filename=/oradata2/DBase1/control02.ctloutput filename=/oradata2/DBase1/control03.ctlFinished restore at 06-MAY-08

RMAN> ALTER DATABASE MOUNT;
database mountedreleased channel: ORA_DISK_1

8)From SQL*Plus determine the data file and redo log file name.

SQL> COLUMN NAME FORMAT a70SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE2 UNION3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME---------- ------------------------------------------------------------1 /oradata2/data1/dbase1/redo01.log1 /oradata2/data1/dbase1/system01.dbf2 /oradata2/data1/dbase1/redo02.log2 /oradata2/data1/dbase1/undotbs01.dbf3 /oradata2/data1/dbase1/redo03.log3 /oradata2/data1/dbase1/sysaux01.dbf4 /oradata2/data1/dbase1/users01.dbf5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.

RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiecebackup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp recid=33 stamp=65398295

RMAN> list backup;
List of Backup Sets===================
BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------32 Full 525.67M DISK 00:01:31 06-MAY-08BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkpList of Datafiles in backup set 32File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.

RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

11)Open the Database resetlogs option.

RMAN> alter database open resetlogs;