Here’s a scenario I’ve been asked about a few times.
The DBA has backed up the database using RMAN and Data Domain DDBoost and dutifully stored details of the backup in the RMAN catalog.
But then he needs to restore and recover the database to a new server without the RMAN catalog.
This can happen, for example, if an entire data center goes down. Many Data Domain users use the automatic replication option, whereby Data Domain will replicate RMAN backupsets from one device to another, often to a remote data center, but without the original RMAN catalog to inspect, how do we restore and recover our database?
The process is actually fairly straightforward. This post will walk through the steps.
On the target side, first we need to start an instance. Since there is no database, we can only perform a “startup nomount” operation. If you do not have a copy of the original INIT.ORA or SPFILE, create a new one with just enough directives to get the instance to start up.
[oracle@rstemc64]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 17:47:43 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile=$ORACLE_HOME/dbs/initxio11wsb.ora ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size 2254344 bytes Variable Size 520096248 bytes Database Buffers 1358954496 bytes Redo Buffers 6045696 bytes
Now we can launch RMAN and restore the control files from the Data Domain DDBoost backup. The script to restore the control files is shown below:
set dbid=2386171380;
run {
allocate channel c1 device type sbt parms 'BLKSIZE=1048576,SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=dd0205_boost,BACKUP_HOST=rstdd0204mgmt.emc.com,ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)';
restore controlfile from 'c-2386171380-20160304-06';
release channel c1;
}
In the script you can see that we first set the DBID of the database we want to restore.
We then allocate the sbt channel inside in the run block, and use the “restore controlfile from” syntax to tell RMAN which control file backup to use to restore the control files.
Note that the Data Domain we are restoring from here – dd0204mgmt.emc.com – is not the same Data Domain the original backup was sent to. RMAN actually does not care, it will simply ask the Data Domain for the named backupset, and the Data Domain will find it from the replicated backups.
Executing this script should yield the following:
[oracle@rstemc64]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 4 18:00:33 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: XIO11WSB (not mounted) RMAN> @restore_boost_nocat_cf.rcv using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=68 device type=SBT_TAPE channel c1: Data Domain Boost API Starting restore at 05-MAR-16 channel c1: restoring control file channel c1: restore complete, elapsed time: 00:00:03 output file name=+XIO11WSA_FRA/xio11wsb/controlfile/current.256.905647195 output file name=+XIO11WSA_DATA/xio11wsb/controlfile/current.260.905647197 Finished restore at 05-MAR-16 released channel: c1 RMAN> **end-of-file**
Make a note of the control files that were restored. In this example they are +XIO11WSA_FRA/xio11wsb/controlfile/current.256.905647195 and +XIO11WSA_DATA/xio11wsb/controlfile/current.260.905647197.
We now need to adjust our dummy instance to use these restored control files. We need to shut it down and the edit the INIT.ORA file we created to start it up.
[oracle@rstemc64]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 18:05:19 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> exit
With the instance down, we need to edit the INIT.ORA file and add an entry for the restored control files:
*.audit_file_dest='/u01/app/oracle/admin/xio11wsb/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='+XIO11WSA_FRA/xio11wsb/controlfile/current.256.905647195','+XIO11WSA_DATA/xio11wsb/controlfile/current.260.905647197' *.db_block_size=8192 *.db_create_file_dest='+XIO11WSA_DATA' *.db_domain='' *.db_name='xio11wsb'
Now we can restart the recovery instance and mount it:
[oracle@rstemc64]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 18:14:40 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount pfile=$ORACLE_HOME/dbs/initxio11wsb.ora ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size 2254344 bytes Variable Size 520096248 bytes Database Buffers 1358954496 bytes Redo Buffers 6045696 bytes Database mounted. SQL>
The restored control files can now act as our catalog. They have information on the backups of the database up to the RMAN record keep time parameter, which defaults to seven days.
Next we should restore the SPFILE. The one used to launch the recovery instance might be missing important settings from the production database, such as characterset information, compatibility settings and so on.
Launch RMAN and query for backups of the SPFILE.
[oracle@rstemc64]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 4 18:25:41 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: XIO11WSB (DBID=2386171380, not open)
RMAN> list backup of spfile completed after 'sysdate-1';
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7925 Incr 0 1.00M SBT_TAPE 00:00:01 04-MAR-16
BP Key: 8178 Status: AVAILABLE Compressed: NO Tag: SP_20160304_2258
Handle: XIO11WSB_sp_ihqvlv6e_1_1.bk Media: dd0205_boost
SPFILE Included: Modification time: 04-MAR-16
SPFILE db_unique_name: XIO11WSB
RMAN>
Using the restored controlfile, RMAN now knows that there is a backup of the SPFILE in file XIO11WSB_sp_eoqv4uvb_1_1.bk. We can now restore the SPFILE with the following RMAN commands:
run {
allocate channel c1 device type sbt parms 'BLKSIZE=1048576,SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=dd0205_boost,BACKUP_HOST=rstdd0204mgmt.emc.com,ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)';
restore spfile from 'XIO11WSB_sp_ihqvlv6e_1_1.bk';
release channel c1;
}
Executing this script should restore the SPFILE.
[oracle@rstemc64]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 4 18:37:41 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: XIO11WSB (DBID=2386171380, not open) RMAN> @restore_boost_nocat_sp.rcv using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=68 device type=SBT_TAPE channel c1: Data Domain Boost API Starting restore at 04-MAR-16 Starting implicit crosscheck backup at 04-MAR-16 Crosschecked 562 objects Finished implicit crosscheck backup at 04-MAR-16 Starting implicit crosscheck copy at 04-MAR-16 Crosschecked 4 objects Finished implicit crosscheck copy at 04-MAR-16 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= channel c1: restoring spfile from AUTOBACKUP XIO11WSB_sp_ihqvlv6e_1_1.bk channel c1: SPFILE restore from AUTOBACKUP complete Finished restore at 04-MAR-16 released channel: c1 RMAN> **end-of-file** RMAN>
Now we have the SPFILE back, we need to restart the recovery instance so that the SPFILE is in use.
[oracle@rstemc64]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 19:12:15 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1887350784 bytes
Fixed Size 2254344 bytes
Variable Size 570427896 bytes
Database Buffers 1308622848 bytes
Redo Buffers 6045696 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilexio11wsb.o
ra
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +XIO11WSA_DATA/xio11wsb/contro
lfile/current.260.869709301, +
XIO11WSA_FRA/xio11wsb/controlf
ile/current.256.869709301
You can see that the recovery instance is now using the SPFILE we restored, but since that SPFILE is from the production backup, the control_files parameter is now pointing to the wrong control files.
We need to update this to point at where the control files were restored to:
SQL> alter system set control_files='+XIO11WSA_FRA/xio11wsb/controlfile/current.256.905647195','+XIO11WSA_DATA/xio11wsb/controlfile/current.260.905647197' scope=spfile; System altered. SQL> shutdown ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1887350784 bytes Fixed Size 2254344 bytes Variable Size 570427896 bytes Database Buffers 1308622848 bytes Redo Buffers 6045696 bytes Database mounted.
We have restored the control files and the SPFILE, and our recovery instance is started and mounted. We can now use RMAN to query the last available backup:
[oracle@rstemc64]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 4 19:33:27 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: XIO11WSB (DBID=2386171380, not open)
RMAN> list backup of database completed after 'sysdate-1';
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7927 Incr 0 21.71G SBT_TAPE 00:01:08 04-MAR-16
List of Datafiles in backup set 7927
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 0 Incr 78845983 04-MAR-16 +XIO11WSA_DATA/xio11wsb/datafile/soe.258.905648075
Backup Set Copy #1 of backup set 7927
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
SBT_TAPE 00:01:08 04-MAR-16 NO L0_20160304_2258
List of Backup Pieces for backup set 7927 Copy #1
BP Key Pc# Status Media Piece Name
------- --- ----------- ----------------------- ----------
8181 1 AVAILABLE dd0205_boost XIO11WSB_df_i9qvlv53_1_1.bk
8180 2 AVAILABLE dd0205_boost XIO11WSB_df_i9qvlv53_2_1.bk
RMAN>
In this example you can see that the backup we want was tagged with “L0_20160304_2258”. We can use this to specify exactly which backup to use for the recovery.
In the following script note that the channel allocate commands have been moved outside of the run block. This is possible as the database is now mounted.
We are using a parallelism setting of 4 for our restore, an have instructed RMAN to restore the database from the backup tagged with “L0_20160229_1209”.
configure channel device type sbt maxopenfiles 4 parms 'BLKSIZE=1048576,SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libddobk.so,ENV=(STORAGE_UNIT=dd0205_boost,BACKUP_HOST=rstdd0204mgmt.emc.com,ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)';
configure device type sbt backup type to backupset parallelism 4;
run {
restore database from tag L0_20160304_2258;
}
Executing this script should yield output similar to this:
[oracle@rstemc64]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 4 19:39:31 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: XIO11WSB (DBID=2386171380, not open)
RMAN> run {
2>
3> restore database from tag L0_20160304_2258;
4>
5> }
Starting restore at 05-MAR-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=131 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Domain Boost API
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=192 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Domain Boost API
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=8 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Data Domain Boost API
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=69 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Data Domain Boost API
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=193 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=9 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=70 device type=DISK
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00002 to +XIO11WSA_DATA/xio11wsb/datafile/sysaux.257.869709245
channel ORA_SBT_TAPE_1: reading from backup piece XIO11WSB_df_icqvlv53_1_1.bk
channel ORA_SBT_TAPE_2: starting datafile backup set restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_2: restoring datafile 00003 to +XIO11WSA_DATA/xio11wsb/datafile/undotbs1.258.869709245
channel ORA_SBT_TAPE_2: reading from backup piece XIO11WSB_df_ibqvlv53_1_1.bk
channel ORA_SBT_TAPE_3: starting datafile backup set restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_3: restoring datafile 00001 to +XIO11WSA_DATA/xio11wsb/datafile/system.256.869709245
channel ORA_SBT_TAPE_3: reading from backup piece XIO11WSB_df_ieqvlv66_1_1.bk
channel ORA_SBT_TAPE_4: starting datafile backup set restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_4: restoring datafile 00006 to +XIO11WSA_DATA/xio11wsb/datafile/slob.270.875128257
channel ORA_SBT_TAPE_4: reading from backup piece XIO11WSB_df_iaqvlv53_1_1.bk
channel ORA_SBT_TAPE_3: piece handle=XIO11WSB_df_ieqvlv66_1_1.bk tag=L0_20160304_2258
<output removed to aid clarity>
channel ORA_SBT_TAPE_1: reading from backup piece XIO11WSB_df_i9qvlv53_2_1.bk
channel ORA_SBT_TAPE_1: piece handle=XIO11WSB_df_i9qvlv53_2_1.bk tag=L0_20160304_2258
channel ORA_SBT_TAPE_1: restored backup piece 2
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:15
channel ORA_SBT_TAPE_3: piece handle=XIO11WSB_df_i9qvlv53_1_1.bk tag=L0_20160304_2258
channel ORA_SBT_TAPE_3: restored backup piece 1
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:05:31
channel ORA_SBT_TAPE_2: piece handle=XIO11WSB_df_ibqvlv53_1_1.bk tag=L0_20160304_2258
channel ORA_SBT_TAPE_2: restored backup piece 1
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:06:27
channel ORA_SBT_TAPE_4: piece handle=XIO11WSB_df_iaqvlv53_1_1.bk tag=L0_20160304_2258
channel ORA_SBT_TAPE_4: restored backup piece 1
channel ORA_SBT_TAPE_4: restore complete, elapsed time: 00:06:47
Finished restore at 05-MAR-16
RMAN>
RMAN> **end-of-file**
RMAN has restored the database datafiles, but we still can’t open the database yet, as we have not performed any recovery. In we check in the recovery instance, we can see that several data files are listed as needing recovery.
[oracle@rstemc64]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 4 19:58:33 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
2 ONLINE ONLINE 78552288 03-MAR-16
3 ONLINE ONLINE 78552287 03-MAR-16
6 ONLINE ONLINE 78552286 03-MAR-16
We can now use RMAN to complete the recovery of the database:
[oracle@rstemc64]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Mar 5 01:06:09 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: XIO11WSB (DBID=2386171380, not open) RMAN> recover database; Starting recover at 05-MAR-16 using target database control file instead of recovery catalog allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=69 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Data Domain Boost API allocated channel: ORA_SBT_TAPE_2 channel ORA_SBT_TAPE_2: SID=131 device type=SBT_TAPE channel ORA_SBT_TAPE_2: Data Domain Boost API allocated channel: ORA_SBT_TAPE_3 channel ORA_SBT_TAPE_3: SID=192 device type=SBT_TAPE channel ORA_SBT_TAPE_3: Data Domain Boost API allocated channel: ORA_SBT_TAPE_4 channel ORA_SBT_TAPE_4: SID=8 device type=SBT_TAPE channel ORA_SBT_TAPE_4: Data Domain Boost API allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=130 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=191 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=9 device type=DISK starting media recovery channel ORA_SBT_TAPE_1: starting archived log restore to default destination channel ORA_SBT_TAPE_1: restoring archived log archived log thread=1 sequence=9640 channel ORA_SBT_TAPE_1: reading from backup piece XIO11WSB_al_ilqvlv9c_1_1.bk channel ORA_SBT_TAPE_1: piece handle=XIO11WSB_al_ilqvlv9c_1_1.bk tag=AL_L0_20160304_2258 channel ORA_SBT_TAPE_1: restored backup piece 1 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:01 archived log file name=+XIO11WSA_FRA/xio11wsb/archivelog/2016_03_05/thread_1_seq_9640.280.905648783 thread=1 sequence=9640 channel default: deleting archived log(s) archived log file name=+XIO11WSA_FRA/xio11wsb/archivelog/2016_03_05/thread_1_seq_9640.280.905648783 RECID=9910 STAMP=905648782 unable to find archived log archived log thread=1 sequence=9641 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/05/2016 01:06:23 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9641 and starting SCN of 78846503
RMAN restored all the archivelogs required to recover the database, which in our case is just one.
We can now check that the recovery is complete and the database is ready to open:
[oracle@rstemc64]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 5 01:06:51 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 78846503 generated at 03/04/2016 23:00:59 needed for thread 1
ORA-00289: suggestion : +XIO11WSA_FRA
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'XIO11WSB'
ORA-00280: change 78846503 for thread 1 is in sequence #9641
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
And that’s it. Our database is fully recovered and operational again, despite the loss of the RMAN catalog database.
Data Domain replication adds a powerful capability to recovery from catastrophic disasters, protecting Oracle RMAN backups locally and remotely. As production DBAs we need to be sure our RMAN backups are replicated to remote locations to fully protect the database assets.
And as with all backups, be sure to test your strategy on a periodic basis. A backup that has never been tested is no backup at all.
the gruffdba 
Pingback: Restoring and Recovering Oracle using RMAN and Data Domain DDBoost when the catalog is lost | Wissam Dagher
Pingback: DDBoost & Oracle RMAN – Lots of Tech and a bit of Health