Restore Database And Rename Database Files Example
-
- 1. About
- 2. Disclaimer And Copyright
- 2. Example
- 2.1 Create a backup of the database
- 2.2 Restore the spfile from autobackup
- 2.3 Create a pfile and modify the location of the control files
- 2.4 Create a spfile and restore the control files
- 2.5 Retrieve all database file names, including redo log and temp files:
- 2.6 Restore the database
- 2.7 Open the database and verify the result
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
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
댓글 0
번호 | 제목 | 날짜 | 조회 수 |
---|---|---|---|
4 | Tibero 관리 3 | 2023.10.25 | 13 |
3 | Tibero test sql문, SCHEMA_OBJECT_SAMPLE | 2023.10.25 | 9 |
2 | Red Hat Enterprise Linux Certification Matrix for Dell PowerEdge Servers | 2023.11.02 | 14 |
1 | tibero 7 install for linux | 2023.11.06 | 20 |