Disaster Recovery: Restore Database from One Server to another Server when Different Location

Disaster Recovery: Restore Database from One Server to another Server when Different Location

Step 1: Source DB Node 1
I just created user and allocate default one new tablespace
SQL> create tablespace rmantest datafile 'd:\backup\rman01.dbf' size 100m;
Tablespace created.
SQL> create user rmantest identified by rmantest default tablespace rmantest;
User created.
SQL> grant connect,resource to rmantest;
Grant succeeded.
Step 2: Backup Current Database with archivelog
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> run{
2> allocate channel d1 type disk;
3> backup format 'D:\restore\backup\data_t%t_s5s_p%p' database plus archivelog;
4> release channel d1;
5> }
allocated channel: d1
channel d1: SID=24 device type=DISK
Starting backup at 13-MAR-11
3 Mohamed Azar | http://mohamedazar.wordpress.com
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=745666576
input archived log thread=1 sequence=3 RECID=2 STAMP=745669135
channel d1: starting piece 1 at 13-MAR-11
channel d1: finished piece 1 at 13-MAR-11
piece handle=D:\RESTORE\BACKUP\DATA_T745669135_S5S_P1 tag=TAG20110313T101855 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
Finished backup at 13-MAR-11
Starting backup at 13-MAR-11
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\MAZAR\ORADATA\RMANTEST\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\MAZAR\ORADATA\RMANTEST\SYSAUX01.DBF
input datafile file number=00005 name=D:\BACKUP\RMAN01.DBF
input datafile file number=00003 name=C:\APP\MAZAR\ORADATA\RMANTEST\UNDOTBS01.DBF
input datafile file number=00004 name=C:\APP\MAZAR\ORADATA\RMANTEST\USERS01.DBF
channel d1: starting piece 1 at 13-MAR-11
channel d1: finished piece 1 at 13-MAR-11
piece handle=D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1 tag=TAG20110313T101903 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:05
Finished backup at 13-MAR-11
Starting backup at 13-MAR-11
4 Mohamed Azar | http://mohamedazar.wordpress.com
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=3 STAMP=745669209
channel d1: starting piece 1 at 13-MAR-11
channel d1: finished piece 1 at 13-MAR-11
piece handle=D:\RESTORE\BACKUP\DATA_T745669210_S5S_P1 tag=TAG20110313T102009 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAR-11
Starting Control File and SPFILE Autobackup at 13-MAR-11
piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\RMANTEST\AUTOBACKUP\2011_03_13\O1_MF_S_745669211_6QRW1DF0_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13-MAR-11
released channel: d1
Step 3: Again I just created one table and inserted one row for check Consistent data available after a restored database to another node.
SQL> conn rmantest/rmantest
Connected.
SQL> create table a(city varchar2(20));
Table created.
SQL> insert into a values('riyadh');
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
5 Mohamed Azar | http://mohamedazar.wordpress.com
SQL> alter system switch logfile;
System altered.
Step 4:
* Unfortunately I don’t have another node, So I just deleted my database and I try to restore database into another location.
* If you are going to restore database to another node, you need to copy bakup files and all archivelog files into one location.
Copy backup files
Copy All Archivelog files
Copy Control files & SPfiles backup .
The location like below
D:\restore\backup>dir
Volume in drive D has no label.
Volume Serial Number is 3861-730C
Directory of D:\restore\backup
13-Mar-11 12:53 PM <DIR> .
13-Mar-11 12:53 PM <DIR> ..
13-Mar-11 12:52 PM <DIR> back
13-Mar-11 10:18 AM 64,855,040 DATA_T745669135_S5S_P1
13-Mar-11 10:20 AM 1,022,410,752 DATA_T745669143_S5S_P1
13-Mar-11 10:20 AM 23,552 DATA_T745669210_S5S_P1
13-Mar-11 09:36 AM 44,904,960 O1_MF_1_2_6QRSGZ0J_.ARC
13-Mar-11 10:18 AM 19,949,056 O1_MF_1_3_6QRVYYKX_.ARC
13-Mar-11 10:20 AM 22,016 O1_MF_1_4_6QRW19PB_.ARC
13-Mar-11 10:28 AM 262,144 O1_MF_1_5_6QRWJTQ9_.ARC
13-Mar-11 10:20 AM 9,830,400 O1_MF_S_745669211_6QRW1DF0_.BKP
8 File(s) 1,162,257,920 bytes
3 Dir(s) 90,152,288,256 bytes free
D:\restore\backup>
6 Mohamed Azar | http://mohamedazar.wordpress.com
Step 5: Create New Instance and Create Oracle Password file
C:\Windows\system32>oradim -new -sid rmantest
Instance created.
C:\Windows\system32>orapwd file=C:\app\mazar\product\11.2.0\dbhome_1\database\PWDrmantest.ora password=rman123
Step 6: Connect rman and startup nomount without initialization file.
C:\Windows\system32>rman target sys/rman123
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 13 10:35:07 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\APP\MAZAR\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITRMANTEST.ORA'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1373264 bytes
Variable Size 75500464 bytes
Database Buffers 75497472 bytes
Redo Buffers 6647808 bytes
Step 7: Restore spfile from backup
RMAN> restore spfile from 'D:\restore\backup\O1_MF_S_745669211_6QRW1DF0_.BKP';
Starting restore at 13-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
7 Mohamed Azar | http://mohamedazar.wordpress.com
channel ORA_DISK_1: SID=96 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP D:\restore\backup\O1_MF_S_745669211_6QRW1DF0_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-MAR-11.
Step 8: Create pfile from spfile
Enter user-name: sys/rman123 as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='d:\restore\initrman.ora' from spfile;
File created.
Step 9 : Shutdown db
RMAN> shutdown immediate
Oracle instance shut down
Step 10 : Edit Pfile
Now You need to edit Initialization file, Your new controlfile location, dumpfile location. Here I just Edit Controlfile location only because I need to restore different location.
*.control_files='D:\restore\control01.ctl','D:\restore\control02.ctl'
Step 11: Startup db using pfile
Enter user-name: sys/rman123 as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='D:\restore\initrman.ora';
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
8 Mohamed Azar | http://mohamedazar.wordpress.com
Variable Size 201327056 bytes
Database Buffers 327155712 bytes
Redo Buffers 5804032 bytes
SQL> create spfile from pfile='D:\restore\initrman.ora';
File created.
Step 12 : Again I shutdown db and restarted using spfile
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
RMAN> startup nomount
Oracle instance started
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 201327056 bytes
Database Buffers 327155712 bytes
Redo Buffers 5804032 bytes
Step 13: Restore Controlfile
RMAN> restore controlfile from 'D:\restore\backup\O1_MF_S_745669211_6QRW1DF0_.BKP';
Starting restore at 13-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\RESTORE\CONTROL01.CTL
output file name=D:\RESTORE\CONTROL02.CTL
Finished restore at 13-MAR-11.
9 Mohamed Azar | http://mohamedazar.wordpress.com
Step 14: Mount the database
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1.
Step 15: Rename Logfile to new Location.
SQL> alter database rename file 'C:\APP\MAZAR\ORADATA\RMANTEST\REDO03.LOG' to 'D:\restore\REDO03.LOG';
Database altered.
SQL> alter database rename file 'C:\APP\MAZAR\ORADATA\RMANTEST\REDO02.LOG' to 'D:\restore\REDO02.LOG';
Database altered.
SQL> alter database rename file 'C:\APP\MAZAR\ORADATA\RMANTEST\REDO01.LOG' to 'D:\restore\REDO01.LOG';
Database altered.
Step 16 : catalog backuppiece
RMAN> catalog start with 'd:\restore\backup';
searching for all files that match the pattern d:\restore\backup
List of Files Unknown to the Database
=====================================
File Name: D:\RESTORE\backup\DATA_T745669135_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669143_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669210_S5S_P1
File Name: D:\RESTORE\backup\O1_MF_1_2_6QRSGZ0J_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_3_6QRVYYKX_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_4_6QRW19PB_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_5_6QRWJTQ9_.ARC
File Name: D:\RESTORE\backup\O1_MF_S_745669211_6QRW1DF0_.BKP
Do you really want to catalog the above files (enter YES or NO)? y
10 Mohamed Azar | http://mohamedazar.wordpress.com
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\RESTORE\backup\DATA_T745669135_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669143_S5S_P1
File Name: D:\RESTORE\backup\DATA_T745669210_S5S_P1
File Name: D:\RESTORE\backup\O1_MF_1_2_6QRSGZ0J_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_3_6QRVYYKX_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_4_6QRW19PB_.ARC
File Name: D:\RESTORE\backup\O1_MF_1_5_6QRWJTQ9_.ARC
File Name: D:\RESTORE\backup\O1_MF_S_745669211_6QRW1DF0_.BKP
Step 17: Restore DB & Recover DB
RMAN> run{
2> set newname for datafile 1 to 'd:\restore\system01.dbf';
3> set newname for datafile 2 to 'd:\restore\sysaux01.dbf';
4> set newname for datafile 3 to 'd:\restore\undotbs01.dbf';
5> set newname for datafile 4 to 'd:\restore\users01.dbf';
6> set newname for datafile 5 to 'd:\restore\rman01.dbf';
7> restore database;
8> switch datafile all;
9> recover database;
10> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 13-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
11 Mohamed Azar | http://mohamedazar.wordpress.com
channel ORA_DISK_1: restoring datafile 00001 to d:\restore\system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to d:\restore\sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to d:\restore\undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to d:\restore\users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to d:\restore\rman01.dbf
channel ORA_DISK_1: reading from backup piece D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1
channel ORA_DISK_1: piece handle=D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1 tag=TAG20110313T101903
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 13-MAR-11
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=745678470 file name=D:\RESTORE\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=745678470 file name=D:\RESTORE\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=745678470 file name=D:\RESTORE\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=745678470 file name=D:\RESTORE\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=745678470 file name=D:\RESTORE\RMAN01.DBF
Starting recover at 13-MAR-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC
archived log for thread 1 with sequence 5 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC thread=1 sequence=4
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/13/2011 12:54:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 967587
12 Mohamed Azar | http://mohamedazar.wordpress.com
So you Need to fix this solution like below,
RMAN> run{
2> set until sequence 6 thread 1;
3> set newname for datafile 1 to 'd:\restore\system01.dbf';
4> set newname for datafile 2 to 'd:\restore\sysaux01.dbf';
5> set newname for datafile 3 to 'd:\restore\undotbs01.dbf';
6> set newname for datafile 4 to 'd:\restore\users01.dbf';
7> set newname for datafile 5 to 'd:\restore\rman01.dbf';
8> restore database;
9> switch datafile all;
10> recover database;
11> }
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 13-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to d:\restore\system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to d:\restore\sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to d:\restore\undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to d:\restore\users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to d:\restore\rman01.dbf
channel ORA_DISK_1: reading from backup piece D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1
channel ORA_DISK_1: piece handle=D:\RESTORE\BACKUP\DATA_T745669143_S5S_P1 tag=TAG20110313T101903
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 13-MAR-11
13 Mohamed Azar | http://mohamedazar.wordpress.com
Starting recover at 13-MAR-11
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC
archived log for thread 1 with sequence 5 is already on disk as file D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_4_6QRW19PB_.ARC thread=1 sequence=4
archived log file name=D:\RESTORE\BACKUP\O1_MF_1_5_6QRWJTQ9_.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-MAR-11
RMAN> alter database open resetlogs;
database opened
RMAN>
Step 18: Now My Database was restored successfully, So I need to check whether my data available or not.
C:\Users\mazar>set oracle_sid=rmantest
C:\Users\mazar>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 13 12:58:53 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys/rman123 as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn rmantest/rmantest;
Connected.
SQL> select * from tab;
14 Mohamed Azar | http://mohamedazar.wordpress.com
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
A TABLE
SQL> select * from a;
CITY
--------------------
riyadh
SQL>