Leveraging Oracle 11g Data Guard for Database Backup and Recovery

Synopsis. Oracle Data Guard makes it possible to back up a production database using a valid physical standby database as the target for the backups, and those same backups can be used to restore and recover a production database. This article – the fourth in this ongoing series – demonstrates how Oracle Database 11g expands these features to virtually guarantee against any data losses while simultaneously offloading the “heavy lifting” of database backups to a production database’s corresponding standby environment.

In the prior article in this series, I showed how simple it was to:

• Configure data protection modes between the primary and standby databases
• Set up Oracle Enterprise Manager Grid Control for management of a Data Guard environment
• Perform role transitions using Oracle Enterprise Manager Grid Control

This article will demonstrate how to:

• Use a recovery catalog to catalog backups made on the standby database
• Create incrementally updateable image copy backups from a standby database
• Implement block change tracking on a standby database

There’s little debate in the Oracle DBA community that Recovery Manager (RMAN) provides the most accurate, most effective, and fastest methods to back up, restore, and recover a database. However, the processing that goes on “under the covers” when RMAN actually backs up an Oracle database is decidedly non-trivial. Even if a DBA is utilizing Oracle 10g’s recommended best practice known as incrementally updateable image copies when backing up her production database, every database block in each datafile must be backed up at least once at incremental level 0 before incremental level 1 backups can be created.

One of the most impressive features that Oracle Data Guard has offered since early Oracle 9i is the prospect of offloading the “heavy lifting” of database backup creation from the production (i.e. primary) database to a corresponding physical standby database within the Data Guard configuration. Oracle 11g has made some refinements to these capabilities that make this option even more attractive; I’ll point these new features out as I demonstrate the prerequisites, methodology, and best practices for using RMAN to protect production data in an Oracle 11g Data Guard environment.

An Unavoidable Requirement: A Recovery Catalog

Because backups are being made on the standby server, there’s one unavoidable requirement: A recovery catalog must be used to record any backups so that the primary database server (or any other standby database, for that matter!) can locate and then use the backups for media recovery.

To illustrate, I invoked DBCA to create a new Oracle 11g (11.1.0.6) database named RCAT based upon the standard “seed” database. I then applied the code shown in Listing 4.1 to:

• Create a new tablespace named RCAT_111 as the container for the production database’s recovery catalog
• Create a new user account called RCAT that has been GRANTed the RECOVERY CATALOG OWNER privilege
• Create the RMAN recovery catalog via the CREATE CATALOG command

Data Guard: Recommended Best Practices For RMAN Backup and Recovery

Now that I’ve set up a repository for storing RMAN backup metadata for my Data Guard configuration, I’ll next register the production (primary) database within the recovery catalog so that I can configure the primary database and its corresponding physical standby database for efficient RMAN backup operations. But first, let’s discuss Oracle’s recommended best practices for configuring the Data Guard primary and physical standby databases that will participate in a RMAN backup and recovery strategy:

• Unique name settings. Each database in an Oracle 11g Data Guard configuration should already have a different identity for the DB_UNIQUE_NAME initialization parameter so that Data Guard Broker (DGB) can easily identify and control the database’s various availability and state settings. This is especially crucial when setting up RMAN in Data Guard because the Recovery Catalog will record database-specific RMAN settings for each unique DB_UNIQUE_NAME value.
• SPFILEs. Both databases should already be using a server parameter file (SPFILE) to record any changes to the overall Data Guard configuration during switchover and failover.
• Flash Recovery Area. A Flash Recovery Area (FRA) should be configured for each database so disk backups can be stored and managed effectively. If best practices are followed, the FRA should be large enough for one complete image copy of each datafile, plus more than enough space for Flashback logs.
• Flashback Logging. Oracle 11g makes especially good use of Flashback Database features; once it’s enabled, it may not even be necessary to restore the database’s datafiles to perform a business-driven incomplete recovery. Therefore, Oracle recommends configuring Flashback Logging mode for all databases in a Data Guard configuration.

Recovery Catalog Registration of Data Guard Databases

Now that the Data Guard prerequisites are satisfied, I’ll turn my attention to registering and configuring my Data Guard databases in the Recovery Catalog. Listing 4.2 shows the commands I used to handle the steps I’ve summarized below:

Registering the Primary Database. First, I’ll connect to the recovery catalog database (RCAT) in CATALOG mode, then to my primary database (ORCL_PRIMARY) in TARGET mode. I’ll then issue the REGISTER DATABASE; command to complete the primary database’s registration. At this point, the recovery catalog is now aware of only my primary database, but that will change momentarily.

Settings for All Databases. There are several RMAN settings that I want to be common for all databases in my Data Guard configuration, so I’ll set them up next:

• Oracle recommends setting a retention policy based on a point-in-time recovery window – for example, 48 hours (CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;).
• I’ll issue the command CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; to establish an appropriate deletion policy for archived redo logs. This setting tells Oracle 11g to make sure that all archived redo logs are kept until they have been shipped and applied on all standby databases in the Data Guard configuration. The other (mutually exclusive) option for this setting is SHIPPED TO ALL STANDBY; that setting tells Oracle 11g to retain all archived redo logs just until they have been received but not necessarily applied on all standby databases in the Data Guard configuration.
• Finally, I’ll set up corresponding connect identifiers for each database’s DB_UNIQUE_NAME. Note that Oracle 11g expects these connect identifiers to correspond to aliases in each database server’s TNSNAMES.ORA network configuration file.

By the way, at this point RMAN acknowledges a configuration for my physical standby database (ORCL_STDBY1) because I’ve configured its connect identifier. This means that if I so desire, I can register and set up for standby database configurations in the recovery catalog before the databases even exist – a pretty neat feature.

Settings for the Physical Standby Database. Since ORCL_STDBY1 is going to handle all backups for my Data Guard environment, I’ll configure its RMAN settings a bit differently from the default settings. First I’ll exit my current RMAN session, then reconnect the recovery catalog and the physical standby database, so that I can issue configuration commands for just that database:

• So that any database structural changes that affect my production database’s control file – for example, the addition of a new tablespace, datafile, or online redo log group -- are captured immediately, I’ll enable control file autobackup via the CONFIGURE CONTROLFILE AUTOBACKUP ON; command.
• To prevent the unnecessary backup of a datafile that’s unchanged since it was last backed up, I’ll turn on backup optimization with the CONFIGURE BACKUP OPTIMIZATION ON; command.
• Finally, I’ll follow Oracle’s recommended deletion frequency for archived redo logs by issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES ON DISK command. Note that if there are multiple standby databases in the Data Guard configuration, Oracle recommends choosing just one that will participate as the key backup and recovery site. Oracle also strongly suggests configuring the archivelog deletion policy for any “non-participating” standby databases to an identical setting.

Enabling a Channel for Tape Backups. Finally, preparing for offsite backups are still an important part of any disaster recovery plan, and they are no less crucial in a Data Guard environment. Oracle therefore recommends setting up a tape channel (SBT) using either Oracle Secure Backup or your already-existing tape management system. It’s also important to set up this channel because even though RMAN can create database backups from the physical standby database on either disk or tape, only the backups that have been written to tape are available to the primary database for restoration and recovery. However, we’ll investigate another recovery method that I like to call the emergency image copy option to work around this requirement, so I’m going to bypass this requirement in this illustration. (For more information on Oracle Secure Backup, see my prior article series that explains in detail how to set it up.)

Confirming the Configuration. To verify that all these pieces are in place, I’ll reconnect to the Recovery Catalog and the primary database and confirm the database-specific configuration settings. Note that the SHOW ALL command can now leverage DB_UNIQUE_NAME automatically to categorize them:

[oracle@11gPrimary ~]$ echo $ORACLE_SID
orcl_primary
[oracle@11gPrimary ~]$ rman target / catalog rcat/rcat@rcat

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Jul 16 11:21:57 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1210321736)
connected to recovery catalog database

RMAN> show all for db_unique_name all;

starting full resync of recovery catalog
full resync complete

RMAN configuration parameters for database with db_unique_name ORCL_PRIMARY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE DB_UNIQUE_NAME 'orcl_primary' CONNECT IDENTIFIER  'orcl_primary';
CONFIGURE DB_UNIQUE_NAME 'orcl_stdby1' CONNECT IDENTIFIER  'orcl_stdby1';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/snapcf_orcl.f';

RMAN configuration parameters for database with db_unique_name ORCL_STDBY1 are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE DB_UNIQUE_NAME 'orcl_primary' CONNECT IDENTIFIER  'orcl_primary';
CONFIGURE DB_UNIQUE_NAME 'orcl_stdby1' CONNECT IDENTIFIER  'orcl_stdby1';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/snapcf_orcl.f';

Creating RMAN Backups On the Physical Standby Database

My RMAN configuration tasks are at last complete, so it’s time to create an appropriate backup plan for my Data Guard configuration. I’ll use Oracle’s recommended backup approach - incrementally-updateable image copy backups – so that I’ll need to take an incremental level 0 backup of my primary database’s datafiles only once. Listing 4.3 shows the results of the initial iteration of this backup strategy executed from my physical standby database (ORCL_STDBY1). Note that I’ve added a small tablespace named LMT_MINI for some future experimentation with the different types of recovery made possible through this backup strategy.

Implementing Block Change Tracking for Speedier RMAN Incremental Backups

Starting in Oracle 11g, it’s also now possible to implement block change tracking (BCT) to speed creation of incrementally-updateable image copy backups on a physical standby database. For example:

$> export ORACLE_SID=orcl_stdby1
$> export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
$> sqlplus / as sysdba

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
2>   USING FILE '/u01/app/oracle/oradata/stdby/blkchgtrk.f';

SQL> Database altered.

However, please be aware that there is a bug (#7613481) reported in Oracle 11.1.0.7 that could cause incomplete image copy backups to be created on a physical standby database if BCT has been enabled. See MetaLink Document #756435.1, Block Change Tracking On Physical Standby Might Cause Incorrect Backups, for complete details.

Primary Database Recovery Using Physical Standby Database Backups

Now to the grist of the matter: how to utilize backups taken on the physical standby database for restoration and recovery of datafiles on the primary database? Let’s take the most common scenario first:

Scenario 1: Recovering from the Loss of a Data File on the Primary Database Using Existing RMAN Backups. In this situation, a datafile on the primary database has been corrupted, damaged, or even deleted, and the DBA needs to restore and recover it as quickly as possible to return that datafile’s tablespace to “fighting strength” so that applications can continue to use the data. To simulate this situation on the primary database, I’ve destroyed the datafile that comprises the LMT_MINI tablespace that I created just for this demonstration.

Since our chosen backup strategy has already created a valid backup of the affected datafile, all that I’ll need to do is:

• take the affected tablespace offline,
• restore the datafile on the primary database from an RMAN backup taken on the physical standby database,
• recover the datafile, and
• bring the tablespace online again.

What I really love about RMAN is that it can tell me exactly how it plans to tackle this problem by simply adding the PREVIEW directive to the RESTORE and RECOVER commands. Here’s the result of this simulated restoration and recovery:

RMAN> list copy of database for db_unique_name orcl_stdby1;

List of Datafile Copies for database with db_unique_name ORCL_STDBY1
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
6616    1    A 2009-07-17 12:16:48 4255727    2009-07-12 17:00:46
        Name: /u01/app/oracle/flash_recovery_area/ORCL_STDBY1/datafile/o1_mf_system_561dfsx8_.dbf
        Tag: INCRUPD

6615    2    A 2009-07-17 12:15:14 4255727    2009-07-12 17:00:46
        Name: /u01/app/oracle/flash_recovery_area/ORCL_STDBY1/datafile/o1_mf_sysaux_561dbc4s_.dbf
        Tag: INCRUPD

6617    3    A 2009-07-17 12:18:03 4255727    2009-07-12 17:00:46
        Name: /u01/app/oracle/flash_recovery_area/ORCL_STDBY1/datafile/o1_mf_undotbs1_561djnpd_.dbf
        Tag: INCRUPD

6619    4    A 2009-07-17 12:18:50 4255727    2009-07-12 17:00:46
        Name: /u01/app/oracle/flash_recovery_area/ORCL_STDBY1/datafile/o1_mf_users_561dmspw_.dbf
        Tag: INCRUPD

6618    5    A 2009-07-17 12:18:28 4255727    2009-07-12 17:00:46
        Name: /u01/app/oracle/flash_recovery_area/ORCL_STDBY1/datafile/o1_mf_example_561dlv6y_.dbf
        Tag: INCRUPD

6620    6    A 2009-07-17 12:19:06 4255727    2009-07-12 17:00:46
        Name: /u01/app/oracle/flash_recovery_area/ORCL_STDBY1/datafile/o1_mf_lmt_mini_561dnb6v_.dbf
        Tag: INCRUPD


RMAN> restore tablespace lmt_mini preview;

Starting restore at 2009-07-17 12:21:48
using channel ORA_DISK_1

datafile 6 will be created automatically during restore operation

List of Archived Log Copies for database with db_unique_name ORCL_PRIMARY
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
6704    1    240     A 2009-07-17 10:44:52
        Name: /u01/app/oracle/flash_recovery_area/ORCL/log_240_1_682541003.arc

6705    1    241     A 2009-07-17 12:11:30
        Name: /u01/app/oracle/flash_recovery_area/ORCL/log_241_1_682541003.arc

6706    1    242     A 2009-07-17 12:11:31
        Name: /u01/app/oracle/flash_recovery_area/ORCL/log_242_1_682541003.arc

Media recovery start SCN is 4424271
Recovery must be done beyond SCN 4424271 to clear datafile fuzziness
Finished restore at 2009-07-17 12:21:48

RMAN> recover tablespace lmt_mini preview;

Starting recover at 2009-07-17 12:33:24
using channel ORA_DISK_1
using channel ORA_DISK_1

archived logs generated after SCN 4424346 not found in repository
Media recovery start SCN is 4424346
Recovery must be done beyond SCN 281474976710655 to clear datafile fuzziness
Finished recover at 2009-07-17 12:33:24

Of course, this is the opposite of what I expected RMAN should do! It’s using backup components from the primary database only to attempt a recovery, while the image copy backups on the physical standby database are not even considered.

This helps bring to light two key limits to the backup strategy that Oracle 11g has recommended:

• Only datafiles, archived redo logs, and server parameter files can be restored. What’s missing from these capabilities, of course, is the database’s control file … but that makes perfect sense, since any physical standby database’s control file will never be in an appropriate state for restoring the primary database’s control file. An obvious solution is to use the recovery catalog itself for restoration, and it’s only necessary to do so if all copies of the primary database’s control file have been lost. (Of course, if the DBA has followed Oracle’s recommended best practices for database configuration, this is virtually impossible.)
• More importantly, only those backups made on the physical standby database using a channel of type SBT (tape) can be restored directly to the primary database. This limitation also makes sense, as it’s entirely possible that the Flash Recovery Area of the physical standby database may not be accessible to the primary database. We’ll explore a solution in the next scenario that overcomes this limitation quite nicely.

Scenario 2: Recovering from the Loss of a Data File on the Primary Database Using “Emergency” RMAN Backups. Oracle 11g provides me with another novel technique for recovering my primary database’s missing or damaged datafile that I like to call the “emergency backup” solution: Instead of using an existing RMAN backup, I’ll simply create one on the primary database using the physical standby database’s datafile for the source of the RMAN backup. Here’s how it works:

1.) Build the “emergency backup.” First I’ll connect to the physical standby database as the TARGET of the RMAN operation and to the primary database with the AUXILIARY designation. Then, I’ll construct an RMAN backup from the physical standby database, but write that backup directly to the primary database’s file system.

2.) Catalog the “emergency backup.” To force the primary database to recognize this backup as a valid target for restoration, I’ll then start up RMAN on the primary database, connect to the recovery catalog and the primary database, and catalog the new image copy backup via the CATALOG DATAFILECOPY command.

3.) Restore from the recataloged “emergency backup.” Finally, I’ll transform the newly cataloged image copy into a recoverable datafile using RMAN’s SET NEWNAME and SWITCH DATAFILE commands.

Listing 4.4 demonstrates this technique for the backup, restoration and recovery of the LMT_MINI tablespace on the ORCL_PRIMARY database using the ORCL_STDBY1 database as the source for the “emergency backup.”

Scenario 3: Recovering from the Loss of a Data File on the Physical Standby Database. Another nice feature of this backup strategy is that it also handles the loss of datafiles on the physical standby database itself. Just two possibilities need to be considered, and they depend upon whether all the archived redo logs needed for recovery of the missing or damaged datafile are already on disk on the physical standby:

• If all archived redo logs are present, then all that needs to be done is to restore the datafile from RMAN backup and then restart Redo Apply on the physical standby database. For example:

$> export ORACLE_SID=orcl_stdby1
$> export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
$> rman target / catalog rcat/rcat@rcat

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Jul 17 12:12:46 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1210321736, not open)
connected to recovery catalog database

RMAN>RUN {
    SQL "alter tablespace lmt_mini offline"; 
    RESTORE TABLESPACE lmt_mini;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13>
. . .
RMAN> exit;

$> sqlplus / as sysdba

...
SQL> ALTER TABLESPACE lmt_mini ONLINE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
   >USING CURRENT LOGFILE DISCONNECT;
...

• On the other hand, if all archived redo logs are not present, then I’d need to take a slightly different approach. First, I would restore the datafile from its RMAN backup on the physical standby database, but then recover it to an SCN, time, or log sequence number that is just slightly past the last transaction that was applied to the database. Then all I need to do is to restart Redo Apply as shown above. Oracle will reinitiate the application of redo to the restored and recovered datafile.

Wait! How Do I Back Up My Logical Standby Database?

I won’t be discussing how to back up a logical standby database as part of this article for two reasons. First, the same RMAN backup methodology I’ve discussed for the physical standby database will work just fine for a logical standby database.

Secondly, however, it’s crucial to remember that a logical standby database’s RMAN backups absolutely cannot be used to restore and recover a database file on its corresponding primary database because a logical standby database’s datafiles are not interchangeable with its primary database. Because a logical standby database accepts redo but then uses SQL Apply, not archived redo log change vectors, to apply the changed data, it’s therefore possible that the logical standby’s ROWIDs will not match those in the primary database. Also, a logical standby database may not even contain the same data as its counterpart primary database because SQL Apply can filter out Logical Change Records before they are applied.

Next Steps

As much as I love working with RMAN, it’s time to move on to something I find even more intriguing. In the next article in this series, I’ll explore:

• How to leverage Oracle 11g Data Guard Real Time Query features
• How Oracle 11g Data Guard Real Time Apply features work
• How Oracle 11g Data Guard snapshot standby database features simultaneously enable disaster recovery and quality assurance testing

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11g documentation for the deeper technical details of this article:

B28279-02 Oracle Database 11g New Features Guide

B28294-03 Oracle Database 11g Data Guard Concepts and Administration

B28295-03 Oracle Database 11g Data Guard Broker

B28320-01 Oracle Database 11g Reference Guide

B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference

Also, the following MetaLink documentation helps clarify this feature set:

756435.1 Block Change Tracking On Physical Standby Might Cause Incorrect Backups

» See All Articles by Columnist Jim Czuprynski