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
2. Disclaimer And Copyright
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:
- [ oracle@701 ~]$ . oraenv > /dev/null <<< ORCL112
[ oracle@701 ~]$ . oraenv > /dev/null <<< ORCL112
- [oracle@701 ~]$ rman target /
- connected to target database: ORCL112 (DBID=3497253474)
[oracle@701 ~]$ rman target /
connected to target database: ORCL112 (DBID=3497253474)
Note the DBID!
Configure Controlfile Autobackup ON:
- RMAN> configure controlfile autobackup on;
RMAN> configure controlfile autobackup on;
- RMAN> backup database plus archivelog delete input;
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
- oracle@702 ~]$ . oraenv > /dev/null <<< ORCL112
oracle@702 ~]$ . oraenv > /dev/null <<< ORCL112
- [oracle@702 ~]$ cd $ORACLE_HOME/dbs
[oracle@702 ~]$ cd $ORACLE_HOME/dbs
Note: If you are restoring on the same server, shutdown ORCL112 and delete the init or spfile first.
- [oracle@702 dbs]$ rman target /
- connected to target database (not started)
-
- RMAN> set DBID 3497253474
- RMAN> startup nomount
-
- startup failed: ORA-01078: failure in processing system parameters
- starting Oracle instance without parameter file for retrieval of spfile
-
- RMAN> restore spfile from autobackup
- db_recovery_file_dest='/u03/orafra' db_name='ORCL112';
-
- recovery area destination: /u03/orafra
- database name (or database unique name) used for search: ORCL112
- channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp found in the recovery area
- channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827
- channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp
- channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
- Finished restore at 27-AUG-18
[oracle@702 dbs]$ rman target /
connected to target database (not started)
RMAN> set DBID 3497253474
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
starting Oracle instance without parameter file for retrieval of spfile
RMAN> restore spfile from autobackup
db_recovery_file_dest='/u03/orafra' db_name='ORCL112';
recovery area destination: /u03/orafra
database name (or database unique name) used for search: ORCL112
channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 27-AUG-18
2.3 Create a pfile and modify the location of the control files
- [oracle@702 dbs]$ echo "create pfile from spfile;" | sqlplus / as sysdba
[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
- [oracle@702 dbs]$ echo "create spfile from pfile;" | sqlplus / as sysdba
[oracle@702 dbs]$ echo "create spfile from pfile;" | sqlplus / as sysdba
- [oracle@702 dbs]$ rman target /
-
- RMAN> startup force nomount
- (load new spfile)
-
- RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;
-
- recovery area destination: /u03/orafra
- database name (or database unique name) used for search: ORCL112
- channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp found in the recovery area
- channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827
- channel ORA_DISK_1: restoring control file from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp
- channel ORA_DISK_1: control file restore from AUTOBACKUP complete
- Finished restore at 27-AUG-18
-
- Restore the second controlfile, as defined in init parameter file:
-
- RMAN> restore controlfile to '/tmp/control02.ctl' from autobackup;
-
- RMAN> startup force mount
[oracle@702 dbs]$ rman target /
RMAN> startup force nomount
(load new spfile)
RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;
recovery area destination: /u03/orafra
database name (or database unique name) used for search: ORCL112
channel ORA_DISK_1: AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180827
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
Finished restore at 27-AUG-18
Restore the second controlfile, as defined in init parameter file:
RMAN> restore controlfile to '/tmp/control02.ctl' from autobackup;
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:
- [ oracle@702 dbs]$ sqlplus / as sysdba <<'EOI'
- COLUMN NAME FORMAT a60
- SELECT FILE#, NAME FROM V$DATAFILE
- UNION
- SELECT GROUP#, MEMBER FROM V$LOGFILE
- UNION
- SELECT FILE#, NAME FROM V$TEMPFILE;
- EOI
-
-
- 1 /u02/oradata/orcl112/redo01.log
- 1 /u02/oradata/orcl112/system01.dbf
- 1 /u02/oradata/orcl112/temp01.dbf
- 2 /u02/oradata/orcl112/redo02.log
- 2 /u02/oradata/orcl112/sysaux01.dbf
- 3 /u02/oradata/orcl112/redo03.log
- 3 /u02/oradata/orcl112/undotbs01.dbf
- 4 /u02/oradata/orcl112/users01.dbf
- 5 /u02/oradata/orcl112/example01.dbf
[ oracle@702 dbs]$ sqlplus / as sysdba <<'EOI'
COLUMN NAME FORMAT a60
SELECT FILE#, NAME FROM V$DATAFILE
UNION
SELECT GROUP#, MEMBER FROM V$LOGFILE
UNION
SELECT FILE#, NAME FROM V$TEMPFILE;
EOI
1 /u02/oradata/orcl112/redo01.log
1 /u02/oradata/orcl112/system01.dbf
1 /u02/oradata/orcl112/temp01.dbf
2 /u02/oradata/orcl112/redo02.log
2 /u02/oradata/orcl112/sysaux01.dbf
3 /u02/oradata/orcl112/redo03.log
3 /u02/oradata/orcl112/undotbs01.dbf
4 /u02/oradata/orcl112/users01.dbf
5 /u02/oradata/orcl112/example01.dbf
2.6 Restore the database
- [oracle@702 dbs]$ rman target /
[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.
RMAN> list backup of archivelog all;
List of Archived Logs in backup set 64
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 2403184 27-AUG-18 2403207 27-AUG-18
RMAN> run {
ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
set until scn 2403184;
SET NEWNAME FOR DATAFILE 1 TO '/tmp/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/tmp/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/tmp/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/tmp/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/tmp/example01.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/tmp/temp01.dbf';
sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo01.log''
TO ''/tmp/redo01.log'' ";
sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo02.log''
TO ''/tmp/redo02.log'' ";
sql "ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo03.log''
TO ''/tmp/redo03.log'' ";
restore database;
switch datafile all;
switch tempfile all;
recover database;
}
allocated channel: ch1
channel ch1: SID=133 device type=DISK
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
executing command: SET NEWNAME
sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo01.log'' TO ''/tmp/redo01.log''
sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo02.log'' TO ''/tmp/redo02.log''
sql statement: ALTER DATABASE RENAME FILE ''/u02/oradata/orcl112/redo03.log'' TO ''/tmp/redo03.log''
Starting restore at 27-AUG-18
Starting implicit crosscheck backup at 27-AUG-18
Crosschecked 8 objects
Finished implicit crosscheck backup at 27-AUG-18
Starting implicit crosscheck copy at 27-AUG-18
Finished implicit crosscheck copy at 27-AUG-18
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985265630_fr7p1z55_.bkp
File Name: /u03/orafra/ORCL112/autobackup/2018_08_27/o1_mf_s_985266639_fr7q1j2m_.bkp
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /tmp/system01.dbf
channel ch1: restoring datafile 00002 to /tmp/sysaux01.dbf
channel ch1: restoring datafile 00003 to /tmp/undotbs01.dbf
channel ch1: restoring datafile 00004 to /tmp/users01.dbf
channel ch1: restoring datafile 00005 to /tmp/example01.dbf
channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_nnndf_TAG20180827T125255_fr7p07m7_.bkp
channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_nnndf_TAG20180827T125255_fr7p07m7_.bkp tag=TAG20180827T125255
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:01:15
Finished restore at 27-AUG-18
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=985267288 file name=/tmp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=985267288 file name=/tmp/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=985267288 file name=/tmp/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=985267288 file name=/tmp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=985267288 file name=/tmp/example01.dbf
renamed tempfile 1 to /tmp/temp01.dbf in control file
Starting recover at 27-AUG-18
starting media recovery
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=34
channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0lcz_.bkp
channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0lcz_.bkp tag=TAG20180827T131010
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_34_fr7qotql_.arc thread=1 sequence=34
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=1
channel ch1: reading from backup piece /u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0mnx_.bkp
channel ch1: piece handle=/u03/orafra/ORCL112/backupset/2018_08_27/o1_mf_annnn_TAG20180827T131010_fr7q0mnx_.bkp tag=TAG20180827T131010
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_1_fr7qow9l_.arc thread=1 sequence=1
channel default: deleting archived log(s)
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_34_fr7qotql_.arc RECID=95 STAMP=985267290
channel default: deleting archived log(s)
archived log file name=/u03/orafra/ORCL112/archivelog/2018_08_27/o1_mf_1_1_fr7qow9l_.arc RECID=96 STAMP=985267292
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-AUG-18
released channel: ch1
2.7 Open the database and verify the result
- RMAN> alter database open resetlogs;
-
- database opened
-
- sqlplus / as sysdba <<'EOI'
- COLUMN NAME FORMAT a60
- SELECT FILE#, NAME FROM V$DATAFILE
- UNION
- SELECT GROUP#, MEMBER FROM V$LOGFILE
- UNION
- SELECT FILE#, NAME FROM V$TEMPFILE;
- EOI
-
- 1 /tmp/redo01.log
- 1 /tmp/system01.dbf
- 1 /tmp/temp01.dbf
- 2 /tmp/redo02.log
- 2 /tmp/sysaux01.dbf
- 3 /tmp/redo03.log
- 3 /tmp/undotbs01.dbf
- 4 /tmp/users01.dbf
- 5 /tmp/example01.dbf