0. background
The purpose for this testing is to try restore/recover the database to remote host with rman backup
i am using 1 vmware linux box, single ORCL oracle instance on ASM storage (to make things more complicated:))
On the source db, i have a user “jehan”, a table “test” , with 3 rows as below:
SQL> select * from jehan.test;
COL1
———-
good
best
worst
1. backup source db
[oracle@myrh5 trace]$ rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Tue Aug 14 17:56:25 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1270514474)
RMAN> backup database;
Starting backup at 14-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=155 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/orcl/ds01.dbf
input datafile file number=00002 name=+DATA/orcl/sysaux01.dbf
input datafile file number=00001 name=+DATA/orcl/system01.dbf
input datafile file number=00006 name=+DATA/orcl/cms01.dbf
input datafile file number=00003 name=+DATA/orcl/undotbs01.dbf
input datafile file number=00005 name=+DATA/orcl/example01.dbf
input datafile file number=00004 name=+DATA/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/nnndf0_tag20120814t175642_0.387.791315803 tag=TAG20120814T175642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-AUG-12
channel ORA_DISK_1: finished piece 1 at 14-AUG-12
piece handle=+DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911 tag=TAG20120814T175642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-12
2. delete source db “ORCL” in dbca
3. startup target database with no mount by using default init.ora file (note, you don’t need to have target db created beforehand)
[oracle@myrh5 trace]$ export ORACLE_SID=ORCL
[oracle@myrh5 trace]$ rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Wed Aug 15 09:51:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid 1270514474;
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/home/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora’
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 158662656 bytes
Fixed Size 2211448 bytes
Variable Size 92275080 bytes
Database Buffers 58720256 bytes
Redo Buffers 5455872 bytes
4. restore the spfile from backup to pfile
RMAN> restore spfile to pfile ‘/home/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora’ from ‘+DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911′;
Starting restore at 15-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-AUG-12
5. make the path for auditing according to pfile parameters (*.audit_file_dest=’/home/u01/app/oracle/admin/orcl/adump’), you have to do this otherwise the rman can’t start database with nomount
mkdir -p /home/u01/app/oracle/admin/orcl/adump
Note: also if you want to put the control file in different path, modify the pfile for the paths now
6. now start database with no mount with pfile (which will provide the correct control file location)
RMAN> startup nomount pfile=’?/dbs/initORCL.ora’;
7. now restore the controlfile
RMAN>restore controlfile from ‘+DATA/orcl/backupset/2012_08_14/ncsnf0_tag20120814t175642_0.386.791315911’;
Starting restore at 15-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/orcl/control01.ctl
output file name=+DATA/orcl/control02.ctl
Finished restore at 15-AUG-12
8. now we have the control files, we can start start database mount
RMAN> set dbid 1270514474;
executing command: SET DBID
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
9. Let the restore begin!
RMAN> restore database;
Starting restore at 15-AUG-12
Starting implicit crosscheck backup at 15-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 15-AUG-12
Starting implicit crosscheck copy at 15-AUG-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-AUG-12
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_315.385.791317055
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_316.384.791317069
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_317.383.791317085
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_318.382.791317099
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_319.381.791317113
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_320.380.791317127
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_321.379.791317139
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_322.378.791317153
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_323.377.791317171
File Name: +data/ORCL/archivelog/2012_08_14/thread_1_seq_324.376.791334027
File Name: +data/ORCL/BACKUPSET/2012_08_14/ncsnf0_TAG20120814T175642_0.386.791315911
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 +DATA/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/cms01.dbf
channel ORA_DISK_1: restoring datafile 00007 to +DATA/orcl/ds01.dbf
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2012_08_14/nnndf0_tag20120814t175642_0.387.791315803
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2012_08_14/nnndf0_tag20120814t175642_0.387.791315803 tag=TAG20120814T175642
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
Finished restore at 15-AUG-12
Note: if you want to restore the datafile to different location, have to do the path mapping like this (not for temp tablespace datafile), after restore done, also run “SWITCH DATAFILE ALL;” for updating rman catalog in control file:
set newname for datafile 1 to “/u01/oradata/system01.dbf”;
set newname for datafile 2 to “/u01/oradata/sysaux01.dbf”;
…
10. recover the database
RMAN> recover database;
Starting recover at 15-AUG-12
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 315 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_315.385.791317055
archived log for thread 1 with sequence 316 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_316.384.791317069
archived log for thread 1 with sequence 317 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_317.383.791317085
archived log for thread 1 with sequence 318 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_318.382.791317099
archived log for thread 1 with sequence 319 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_319.381.791317113
archived log for thread 1 with sequence 320 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_320.380.791317127
archived log for thread 1 with sequence 321 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_321.379.791317139
archived log for thread 1 with sequence 322 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_322.378.791317153
archived log for thread 1 with sequence 323 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_323.377.791317171
archived log for thread 1 with sequence 324 is already on disk as file +DATA/orcl/archivelog/2012_08_14/thread_1_seq_324.376.791334027
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_315.385.791317055 thread=1 sequence=315
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_316.384.791317069 thread=1 sequence=316
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_317.383.791317085 thread=1 sequence=317
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_318.382.791317099 thread=1 sequence=318
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_319.381.791317113 thread=1 sequence=319
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_320.380.791317127 thread=1 sequence=320
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_321.379.791317139 thread=1 sequence=321
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_322.378.791317153 thread=1 sequence=322
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_323.377.791317171 thread=1 sequence=323
archived log file name=+DATA/orcl/archivelog/2012_08_14/thread_1_seq_324.376.791334027 thread=1 sequence=324
unable to find archived log
archived log thread=1 sequence=325
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/15/2012 10:40:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 325 and starting SCN of 7484710
Note: the last error is fine, before we mount the database, we can use:
alter database mount;
set until scn or set until time
to specify the scn to avoid this error.
11. open database
RMAN> alter database open resetlogs;
database opened
Note: from 11gR2, after open database resetlogs, system will automatically create online redo log file and temp datafile.
12. Verify the data
[oracle@myrh5 trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 15 10:47:53 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from jehan.test;
COL1
———-
good
best
worst
DBA Sensation 