1. About

 

The following demonstrates how to use RMAN to restore database ORCL112 using a different file directory structure.

 

System: Oracle Linux 7.3 (VM VirtualBox), Oracle Database 11.2.0.1

 

 

 

This document is the result of my private research and was written in the hope to provide useful information for members of the Oracle community. It is not meant to be a reference or to substitute any documentation provided by Oracle. Use on your own risk.

 

The information herein is provided under the terms and conditions of http://www.oracle.com/us/legal/terms/index.html.

Please do not plagiarize.

 

Copyright 2018, Dude! @ Oracle Community

 

 

2. Example

 

2.1 Create a backup of the database


Set the Oracle environment:

 

 
  1. [ oracle@701 ~]$ . oraenv > /dev/null <<< ORCL112  
 

 

 
  1. [oracle@701 ~]$ rman target /  
  2. connected to target database: ORCL112 (DBID=3497253474)  
 

 

Note the DBID!

 

Configure Controlfile Autobackup ON:

 

 
  1. RMAN> configure controlfile autobackup on;  
 

 

 
  1. RMAN> backup database plus archivelog delete input;  
 

 

If you restoring on a different server, verify the following:

 

The server has access to the RMAN backups, for example: /u03/fast_recovery_area.

$ORACLE_HOME/dbs/orapwORCL112 password file has been copied.

/etc/oratab includes an entry for ORCL112.

 

2.2 Restore the spfile from autobackup

 

 

 
  1. oracle@702 ~]$ . oraenv > /dev/null <<< ORCL112  
 

 

 
  1. [oracle@702 ~]$ cd $ORACLE_HOME/dbs  
 

 

Note: If you are restoring on the same server, shutdown ORCL112 and delete the init or spfile first.

 

 
  1. [oracle@702 dbs]$ rman target /  
  2. connected to target database (not started)  
  3.   
  4. RMAN> set DBID 3497253474    
  5. RMAN> startup nomount  
  6.   
  7. startup failed: ORA-01078: failure in processing system parameters  
  8. starting Oracle instance without parameter file for retrieval of spfile  
  9.   
  10. RMAN> restore spfile from autobackup  
  11.       db_recovery_file_dest='/u03/orafra' db_name='ORCL112';   
  12.   
  13. recovery area destination: /u03/orafra  
  14. database name (or database unique name) used for search: ORCL112  
  15. channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp found in the recovery area  
  16. channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827  
  17. channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp  
  18. channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete  
  19. Finished restore at 27-AUG-18  
 

 


2.3 Create a pfile and modify the location of the control files

 

 
  1. [oracle@702 dbs]$ echo "create pfile from spfile;" | sqlplus / as sysdba  
 

 

Edit initORCL112.ora and modfiy location of controlfiles. E.g. /tmp/control01.ctl

 

2.4 Create a spfile and restore the control files

 

 
  1. [oracle@702 dbs]$ echo "create spfile from pfile;" | sqlplus / as sysdba  
 

 

 
  1. [oracle@702 dbs]$ rman target /  
  2.   
  3. RMAN> startup force nomount  
  4. (load new spfile)  
  5.   
  6. RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;  
  7.   
  8. recovery area destination: /u03/orafra  
  9. database name (or database unique name) used for search: ORCL112  
  10. channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp found in the recovery area  
  11. channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827  
  12. channel ORA_DISK_1: restoring control file from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp  
  13. channel ORA_DISK_1: control file restore from AUTOBACKUP complete  
  14. Finished restore at 27-AUG-18  
  15.   
  16. Restore the second controlfile, as defined in init parameter file:  
  17.   
  18. RMAN> restore controlfile to '/tmp/control02.ctl' from autobackup;  
  19.   
  20. RMAN> startup force mount    
 

 

You may have to reset the incarnation, in case the restore may not find any backups:

 

For example: RMAN> reset database incarnation to 4;

 

RMAN> List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL112  3497253474       PARENT  1          24-AUG-13

2       2       ORCL112  3497253474       PARENT  925702     14-FEB-17

3       3       ORCL112  3497253474       PARENT  2041934    25-JUL-17

4       4       ORCL112  3497253474       CURRENT 2402794    27-AUG-18

 

 

2.5 Retrieve all database file names, including redo log and temp files:

 

 
  1. [ oracle@702 dbs]$ sqlplus / as sysdba <<'EOI'  
  2. COLUMN NAME FORMAT a60  
  3. SELECT FILE#, NAME FROM V$DATAFILE  
  4. UNION  
  5. SELECT GROUP#, MEMBER FROM V$LOGFILE  
  6. UNION  
  7. SELECT FILE#, NAME FROM V$TEMPFILE;  
  8. EOI  
  9.   
  10.   
  11.      1 /u02/oradata/orcl112/redo01.log  
  12.      1 /u02/oradata/orcl112/system01.dbf  
  13.      1 /u02/oradata/orcl112/temp01.dbf  
  14.      2 /u02/oradata/orcl112/redo02.log  
  15.      2 /u02/oradata/orcl112/sysaux01.dbf  
  16.      3 /u02/oradata/orcl112/redo03.log  
  17.      3 /u02/oradata/orcl112/undotbs01.dbf  
  18.      4 /u02/oradata/orcl112/users01.dbf  
  19.      5 /u02/oradata/orcl112/example01.dbf  
 

 

 

2.6 Restore the database

 

 
  1. [oracle@702 dbs]$ rman target /  
 

 

Find the lowest SCN in the archivelog backups to avoid recovering using the online redo

logs, which do not exist on the new server.

 

Note: You can probably also use RMAN "RECOVER NOREDO" instead of "UNTIL SCN". This

           performs a recovery using archivelogs, but will not look for redo logs.

 

 

 
  1. RMAN> list backup of archivelog all;  
  2.   
  3.   List of Archived Logs in backup set 64  
  4.   Thrd Seq     Low SCN    Low Time  Next SCN   Next Time  
  5.   ---- ------- ---------- --------- ---------- ---------  
  6.   1    2       2403184    27-AUG-18 2403207    27-AUG-18  
  7.   
  8.   
  9. RMAN> run {  
  10.       ALLOCATE CHANNEL ch1 DEVICE TYPE disk;  
  11.       set until scn 2403184;  
  12.       SET NEWNAME FOR DATAFILE 1 TO '/tmp/system01.dbf';  
  13.       SET NEWNAME FOR DATAFILE 2 TO '/tmp/sysaux01.dbf';  
  14.       SET NEWNAME FOR DATAFILE 3 TO '/tmp/undotbs01.dbf';  
  15.       SET NEWNAME FOR DATAFILE 4 TO '/tmp/users01.dbf';  
  16.       SET NEWNAME FOR DATAFILE 5 TO '/tmp/example01.dbf';  
  17.       SET NEWNAME FOR TEMPFILE 1 TO '/tmp/temp01.dbf';  
  18.       sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo01.log''   
  19.                                    TO ''/tmp/redo01.log'' ";  
  20.       sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo02.log''  
  21.                                    TO ''/tmp/redo02.log'' ";  
  22.       sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo03.log''  
  23.                                    TO ''/tmp/redo03.log'' ";  
  24.       restore database;  
  25.       switch datafile all;  
  26.       switch tempfile all;  
  27.       recover database;  
  28. }  
  29.   
  30. allocated channel: ch1  
  31. channel ch1: SID=133 device type=DISK  
  32.   
  33. executing command: SET until clause  
  34.   
  35. executing command: SET NEWNAME  
  36.   
  37. executing command: SET NEWNAME  
  38.   
  39. executing command: SET NEWNAME  
  40.   
  41. executing command: SET NEWNAME  
  42.   
  43. executing command: SET NEWNAME  
  44.   
  45. executing command: SET NEWNAME  
  46.   
  47. sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo01.log''                                    TO ''/tmp/redo01.log''   
  48.   
  49. sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo02.log''                                   TO ''/tmp/redo02.log''   
  50.   
  51. sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo03.log''                                   TO ''/tmp/redo03.log''   
  52.   
  53. Starting restore at 27-AUG-18  
  54. Starting implicit crosscheck backup at 27-AUG-18  
  55. Crosschecked 8 objects  
  56. Finished implicit crosscheck backup at 27-AUG-18  
  57.   
  58. Starting implicit crosscheck copy at 27-AUG-18  
  59. Finished implicit crosscheck copy at 27-AUG-18  
  60.   
  61. searching for all files in the recovery area  
  62. cataloging files...  
  63. cataloging done  
  64.   
  65. List of Cataloged Files  
  66. =======================  
  67. File Name: /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp  
  68. File Name: /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp  
  69.   
  70.   
  71. channel ch1: starting datafile backup set restore  
  72. channel ch1: specifying datafile(s) to restore from backup set  
  73. channel ch1: restoring datafile 00001 to /tmp/system01.dbf  
  74. channel ch1: restoring datafile 00002 to /tmp/sysaux01.dbf  
  75. channel ch1: restoring datafile 00003 to /tmp/undotbs01.dbf  
  76. channel ch1: restoring datafile 00004 to /tmp/users01.dbf  
  77. channel ch1: restoring datafile 00005 to /tmp/example01.dbf  
  78. channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_nnndf_TAG20180827T125255_fr7p07m7_.bkp  
  79. channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_nnndf_TAG20180827T125255_fr7p07m7_.bkp tag=TAG20180827T125255  
  80. channel ch1: restored backup piece 1  
  81. channel ch1: restore complete, elapsed time: 00:01:15  
  82. Finished restore at 27-AUG-18  
  83.   
  84. datafile 1 switched to datafile copy  
  85. input datafile copy RECID=7 STAMP=985267288 file name=/tmp/system01.dbf  
  86. datafile 2 switched to datafile copy  
  87. input datafile copy RECID=8 STAMP=985267288 file name=/tmp/sysaux01.dbf  
  88. datafile 3 switched to datafile copy  
  89. input datafile copy RECID=9 STAMP=985267288 file name=/tmp/undotbs01.dbf  
  90. datafile 4 switched to datafile copy  
  91. input datafile copy RECID=10 STAMP=985267288 file name=/tmp/users01.dbf  
  92. datafile 5 switched to datafile copy  
  93. input datafile copy RECID=11 STAMP=985267288 file name=/tmp/example01.dbf  
  94.   
  95. renamed tempfile 1 to /tmp/temp01.dbf in control file  
  96.   
  97. Starting recover at 27-AUG-18  
  98.   
  99. starting media recovery  
  100.   
  101. channel ch1: starting archived log restore to default destination  
  102. channel ch1: restoring archived log  
  103. archived log thread=1 sequence=34  
  104. channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0lcz_.bkp  
  105. channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0lcz_.bkp tag=TAG20180827T131010  
  106. channel ch1: restored backup piece 1  
  107. channel ch1: restore complete, elapsed time: 00:00:01  
  108. archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_34_fr7qotql_.arc thread=1 sequence=34  
  109. channel ch1: starting archived log restore to default destination  
  110. channel ch1: restoring archived log  
  111. archived log thread=1 sequence=1  
  112. channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0mnx_.bkp  
  113. channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0mnx_.bkp tag=TAG20180827T131010  
  114. channel ch1: restored backup piece 1  
  115. channel ch1: restore complete, elapsed time: 00:00:01  
  116. archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_1_fr7qow9l_.arc thread=1 sequence=1  
  117. channel default: deleting archived log(s)  
  118. archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_34_fr7qotql_.arc RECID=95 STAMP=985267290  
  119. channel default: deleting archived log(s)  
  120. archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_1_fr7qow9l_.arc RECID=96 STAMP=985267292  
  121. media recovery complete, elapsed time: 00:00:00  
  122. Finished recover at 27-AUG-18  
  123. released channel: ch1  
 

 

2.7 Open the database and verify the result

 

 
  1. RMAN> alter database open resetlogs;  
  2.   
  3. database opened  
  4.   
  5. sqlplus / as sysdba <<'EOI'  
  6. COLUMN NAME FORMAT a60  
  7. SELECT FILE#, NAME FROM V$DATAFILE  
  8. UNION  
  9. SELECT GROUP#, MEMBER FROM V$LOGFILE  
  10. UNION  
  11. SELECT FILE#, NAME FROM V$TEMPFILE;  
  12. EOI  
  13.   
  14.      1 /tmp/redo01.log  
  15.      1 /tmp/system01.dbf  
  16.      1 /tmp/temp01.dbf  
  17.      2 /tmp/redo02.log  
  18.      2 /tmp/sysaux01.dbf  
  19.      3 /tmp/redo03.log  
  20.      3 /tmp/undotbs01.dbf  
  21.      4 /tmp/users01.dbf  
  22.      5 /tmp/example01.dbf