Restoring and Recovering Oracle using RMAN and Data Domain DDBoost when the catalog is lost

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.

Advertisements

One thought on “Restoring and Recovering Oracle using RMAN and Data Domain DDBoost when the catalog is lost

  1. Pingback: Restoring and Recovering Oracle using RMAN and Data Domain DDBoost when the catalog is lost | Wissam Dagher

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s