Leveraging Oracle 11g Data Guard for Database Backup and RecoveryJuly 23, 2009 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:
This article will demonstrate how to:
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 CatalogBecause 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:
Data Guard: Recommended Best Practices For RMAN Backup and RecoveryNow 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:
Recovery Catalog Registration of Data Guard DatabasesNow 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:
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:
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 DatabaseMy 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 BackupsStarting 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 BackupsNow 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:
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:
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:
$> 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;
...
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 StepsAs 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:
References and Additional ReadingWhile 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 |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
