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)In neptune machine(Source)

RMAN> backup database;

Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08

Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished 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:59
bash-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(Neptune here)

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 2008
Copyright (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 parameters
LRM-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 spfile
Oracle instance started

Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo 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-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished 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 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo 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-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08

RMAN> ALTER DATABASE MOUNT;

database mounted
released channel: ORA_DISK_1

8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE

File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /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 backuppiece
backup 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-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 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;

database opened.