As part of disaster recovery exercise or to test the validity of a RMAN backup, a full restore and recovery of databases can be performed on scratch or test servers utilizing the production RMAN backups which have been restored from the tape backups on these test or scratch servers.
The following assumptions are made in this note:
- The RMAN backups have been restored from disk backups to the same backup location on the new server as the production server where the backup was originally taken suppose the backup taken on the production server location:/u01/fra/PROD18C/autobackup/2018_08_17/
then create the same folder on the new server and copy the backup on the new server on the same location.
- Consider the identical directory structure of production has been created on the new server such as the database files (data, control files, redo log files), bdump, cdump, udump and adump locations etc.
- Controlfile autobackup must be enabled.
Steps required to restore the backup of a production database (prod18c) on a backup server:-
- Restore the spfile from the autobackup
- Restore the controlfile from the autobackup
- Restore database
- Recover the database
- Open the database with resetlogs
Step 1:- Restore the spfile from the autobackup
[oracle@18c ~]$ rman target / Recovery Manager: Release 188.8.131.52.0 - Production on Fri Aug 17 23:31:35 2018 Version 184.108.40.206.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> set DBID=3984767297 executing command: SET DBID RMAN> startup force nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/initprod18c.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073740616 bytes Fixed Size 8665928 bytes Variable Size 281018368 bytes Database Buffers 780140544 bytes Redo Buffers 3915776 bytes RMAN> restore spfile from autobackup Starting restore at 17-AUG-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK recovery area destination: /u01/fra database name (or database unique name) used for search: PROD18C channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817 channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 17-AUG-18 RMAN> shutdown immediate; Oracle instance shut down
Step 2:-Startup with new spfile and restore the controlfile from autobackup
SQL> startup nomount; ORACLE instance started. Total System Global Area 1660943808 bytes Fixed Size 8658368 bytes Variable Size 1056964608 bytes Database Buffers 587202560 bytes Redo Buffers 8118272 bytes SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.0 [oracle@18c ~]$ rman target / Recovery Manager: Release 22.214.171.124.0 - Production on Fri Aug 17 23:35:56 2018 Version 126.96.36.199.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD18C (not mounted) RMAN> set dbid=3984767297 executing command: SET DBID RMAN> restore controlfile from autobackup; Starting restore at 17-AUG-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK recovery area destination: /u01/fra database name (or database unique name) used for search: PROD18C channel ORA_DISK_1: AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180817 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/PROD18C/control01.ctl output file name=/u01/app/oracle/oradata/PROD18C/control02.ctl Finished restore at 17-AUG-18
Step 3:- Mount and restore the database.
RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> restore database; Starting restore at 17-AUG-18 Starting implicit crosscheck backup at 17-AUG-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 17-AUG-18 Starting implicit crosscheck copy at 17-AUG-18 using channel ORA_DISK_1 Finished implicit crosscheck copy at 17-AUG-18 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/fra/PROD18C/autobackup/2018_08_17/o1_mf_s_984438075_fqg1m3bh_.bkp 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 /u01/app/oracle/oradata/PROD18C/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD18C/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD18C/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD18C/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_17/o1_mf_nnndf_TAG20180817T230028_fqg1knnf_.bkp tag=TAG20180817T230028 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 17-AUG-18
Step 4:-Recover the database
RMAN> recover database; Starting recover at 17-AUG-18 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 28 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc archived log file name=/u01/fra/PROD18C/archivelog/2018_08_17/o1_mf_1_28_fqg1m1qo_.arc thread=1 sequence=28 unable to find archived log archived log thread=1 sequence=29 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/17/2018 23:42:09 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29 and starting SCN of 1755524
The recovery will fail at a point where it cannot restore any more archived redo log files.In this case the last archived log file which has been backed up is sequence 28.
This information can be obtained by issuing a LIST BACKUP OF ARCHIVELOG ALL command.So RMAN will fail when it tries to apply sequence 29….
Alternatively you can use Incomplete recovery (Cancel based) from the SQL prompt and open the database using Resetlogs option.
Step 5:- Open the database using resetlogs option
SQL> alter database open resetlogs; Database altered.
Note:- Take the backup of database again because we have done incomplete recovery and open resetlogs option to open the database.
Catch Me On:- Hariprasath Rajaram
FB Page: https://www.facebook.com/dbahariprasath/?