DBA Tips Archive for Oracle

Using RMAN Incremental Backups to Roll Forward a Physical Standby Database - (10g)

by Jeff Hunter, Sr. Database Administrator

Contents

• Introduction
• Physical Standby Database Lags Far Behind the Primary Database
• Physical Standby Database Has Nologging Changes On a Subset of Datafiles
• Physical Standby Database Has Widespread Nologging Changes
• About the Author

Introduction

There are several scenarios that can occur in an Oracle Data Guard configuration which renders a physical standby database as either unusable or invalid. For example, an extended network failure can occur between the primary and standby machines which cause the standby database to fall significantly far behind the primary database. If the necessary archived redo log files needed to synchronize the physical standby database with the primary database are no longer available, log gap resolution will be unable to resolve the gap in the redo stream to catch up the standby.

Another popular scenario exists when a DML or DDL operation is performed on the primary database using the NOLOGGING or UNRECOVERABLE clause and the FORCE LOGGING database option is not enabled on the primary. If the FORCE LOGGING database option is not enabled, then Oracle will not log the entire operation in the redo log and thus, never send the modified data to the standby database for recovery. While this doesn't necessarily render the entire standby database as unusable, it does invalidate the standby for those tables affected and may require substantial DBA administrative activities to repair. Although you can specify the ALTER DATABASE FORCE LOGGING statement on the primary database to override the NOLOGGING clause and prevent this type of damage to occur in the future, this statement will not repair a standby database that has already been invalidated.

In each of the scenarios listed above, one solution would be to simply delete and formally re-create the physical standby database to enter it back into the Data Guard configuration. Depending on the size of the database, rebuilding the standby database could be an expensive operation as it would potentially involve a lot of time and resources making this a nonviable option.

This guide presents a quick and efficient alternative to rebuilding a physical standby database in Oracle Database 10g Release 2 by using RMAN incremental backups to roll forward and resynchronize a physical standby database with the primary database. Using the RMAN BACKUP INCREMENTAL FROM SCN command, you can create an incremental backup on the primary database that starts at the standby database's current SCN, which can then be used to roll the standby database forward in time.

It is assumed that a primary and one physical standby database is already configured in order to perform the steps described in this guide. The examples used in this guide will make use of the Oracle Data Guard configuration described in the article below:

• Data Guard Configuration Example - (Oracle 10g, Physical Standby)

Physical Standby Database Lags Far Behind the Primary Database

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolvable archive gap. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.

1. On the physical standby database, stop the managed recovery process (MRP):

   SQL> alter database recover managed standby database cancel;

2. On the physical standby database, find the SCN which will be used for the incremental backup at the primary database, as the backup must be created from this SCN forward.

   SQL> select current_scn from v$database; CURRENT_SCN ----------- 3744425

   Record the SCN returned from the above query for the next step.

3. Using RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step.

   [oracle@vmlinux1 ~]$ rman target sys/MySysPassword@modesto RMAN> backup incremental from scn 3744425 database 2> format '/u04/oracle/dg_staging/ForStandby_%U' tag 'FORSTANDBY';

   RMAN does not consider this type of independent incremental backup as part of a backup strategy at the source database. Hence: The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database The backup is not cataloged at the source database The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups. As shown in the above above example, it is possible to direct the backup sets to a specific directory using the FORMAT '<destination>' option. You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database, you must catalog it at the standby. Backups on tape cannot be cataloged.

4. Using an OS remote copy utility, transfer all backup sets created on the primary system to the physical standby machine. Note that there may be more than one backup file created and that all backup pieces of the incremental backup must be manually copied to the physical standby database before you catalog them. For example, to scp the RMAN incremental backup sets from the primary database machine vmlinux1 to the physical standby database running on machine vmlinux2, you would run the following command:

   [oracle@vmlinux1 ~]$ scp /u04/oracle/dg_staging/ForStandby_* vmlinux2:/u04/oracle/dg_staging/ ForStandby_2fm656du_1_1 100% 168MB 7.7MB/s 00:22 ForStandby_2gm656h7_1_1 100% 10MB 9.8MB/s 00:01

5. After copying the incremental backup pieces to the physical standby machine, connect to the physical standby database using RMAN and catalog those incremental backup pieces. Then recover the standby database with the cataloged incremental backup pieces.

   [oracle@vmlinux2 ~]$ rman target sys/MySysPassword@turlock RMAN> catalog start with '/u04/oracle/dg_staging/ForStandby'; RMAN> recover database noredo;

6. Connect to the primary database using RMAN and create a standby control file backup.

   RMAN> backup current controlfile for standby format '/u04/oracle/dg_staging/ForStandbyCTRL.bck';

7. Using an OS remote copy utility, transfer the standby control file backup created on the primary system to the physical standby database machine. For example, to scp the standby control file backup from the primary database machine vmlinux1 to the physical standby database running on machine vmlinux2, you would run the following command:

   [oracle@vmlinux1 ~]$ scp /u04/oracle/dg_staging/ForStandbyCTRL.bck vmlinux2:/u04/oracle/dg_staging/ ForStandbyCTRL.bck 100% 10MB 9.8MB/s 00:01

8. Shut down the physical standby database and startup nomount:

   RMAN> shutdown; RMAN> startup nomount;

9. Connect to the physical standby database using RMAN and restore the standby control file.

   RMAN> restore standby controlfile from '/u04/oracle/dg_staging/ForStandbyCTRL.bck';

10. Shut down the physical standby database again and startup mount:

    RMAN> shutdown; RMAN> startup mount;

11. If the primary and standby database data file directories are identical, skip to step 12. If the primary and standby database data file directories are different, then in RMAN, connect to the physical standby database, catalog all standby data files, and switch the standby database to use the just-cataloged data files.

    For example, if the primary database data file directory is /u02/oradata/MODESTO/datafile/ and the standby database data file directory is /u02/oradata/TURLOCK/datafile/, then run the following statement in RMAN to catalog all standby data files while connected to the physical standby database:

    RMAN> catalog start with '/u02/oradata/TURLOCK/datafile/';

    If the standby database has other database data files not included in the directory specified above, they too will need to be cataloged. In this particular example, I had standby database data files not only in the /u02/oradata/TURLOCK/datafile/ directory (above), but also in the /u05/oradata/TURLOCK directory. Every database data file will need to be cataloged in order to create recoverable copies. Attempting to switch the standby database without cataloging every data file will result in an error. For example,

    RMAN> switch database to copy; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of switch to copy command at 03/01/2011 18:11:57 RMAN-06571: datafile 8 does not have recoverable copy SQL> select file#, name from v$datafile 2 where name not like '/u02/oradata/TURLOCK/datafile/%'; FILE# NAME ------- ------------------------------------ 8 /u05/oradata/TURLOCK/data02.dbf 9 /u05/oradata/TURLOCK/data03.dbf RMAN> catalog datafilecopy '/u05/oradata/TURLOCK/data02.dbf'; RMAN> catalog datafilecopy '/u05/oradata/TURLOCK/data03.dbf'; RMAN> list copy; specification does not match any archive log in the recovery catalog List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time Name ------- ---- - -------------------- ---------- -------------------- ---- 5 1 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_system_6p58khk7_.dbf 1 2 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_undotbs1_6p58khol_.dbf 3 3 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_sysaux_6p58khm3_.dbf 7 4 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_example_6p58khqd_.dbf 2 5 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_users_6p58khrt_.dbf 4 6 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_data1_6p58kht7_.dbf 6 7 A 01-MAR-2011 20:08:55 3752351 01-MAR-2011 20:02:22 /u02/oradata/TURLOCK/datafile/o1_mf_data2_6p58khvn_.dbf 8 8 A 01-MAR-2011 20:09:14 3752351 01-MAR-2011 20:02:22 /u05/oradata/TURLOCK/data02.dbf 9 9 A 01-MAR-2011 20:09:27 3752351 01-MAR-2011 20:02:22 /u05/oradata/TURLOCK/data03.dbf

    After all standby database data file copies have been cataloged, switch the standby database to use the just-cataloged data files.

    RMAN> switch database to copy;

12. The same situation exists with the redo log files that existed with the data file directories. If the primary and standby database redo log directories are identical, skip to step 13. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories. For example,

    [oracle@vmlinux2 ~]$ rm -f /u02/oradata/TURLOCK/onlinelog/* [oracle@vmlinux2 ~]$ rm -f /u03/flash_recovery_area/TURLOCK/onlinelog/*

    In addition, ensure that the LOG_FILE_NAME_CONVERT initialization parameter has been set to convert the directory paths. As an example, you would include the following in your parameter file on the physical standby database to convert the directory paths from MODESTO to TURLOCK:

    LOG_FILE_NAME_CONVERT='/MODESTO/','/TURLOCK/'

13. On the standby database, clear all of the online redo log and standby redo log groups.

    # ---[ Check Online / Standby Redo Log Groups ] --- # SQL> select distinct group#, type from v$logfile order by group#; GROUP# TYPE ---------- ------- 1 ONLINE 2 ONLINE 3 ONLINE 4 STANDBY 5 STANDBY 6 STANDBY 7 STANDBY # ---[ Online Redo Log Groups ] --- # SQL> alter database clear logfile group 1; SQL> alter database clear logfile group 2; SQL> alter database clear logfile group 3; # ---[ Standby Redo Log Groups ] --- # SQL> alter database clear logfile group 4; SQL> alter database clear logfile group 5; SQL> alter database clear logfile group 6; SQL> alter database clear logfile group 7;

    Verify the new online redo log and standby redo log members were successfully created on the physical standby.

    [oracle@vmlinux2 ~]$ ls -l /u02/oradata/TURLOCK/onlinelog/* -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:15 /u02/oradata/TURLOCK/onlinelog/o1_mf_1_6pv6l03v_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:15 /u02/oradata/TURLOCK/onlinelog/o1_mf_2_6pv6lc9x_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u02/oradata/TURLOCK/onlinelog/o1_mf_3_6pv6lko6_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u02/oradata/TURLOCK/onlinelog/o1_mf_4_6pv6lqjv_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u02/oradata/TURLOCK/onlinelog/o1_mf_5_6pv6lzqb_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u02/oradata/TURLOCK/onlinelog/o1_mf_6_6pv6m5xv_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u02/oradata/TURLOCK/onlinelog/o1_mf_7_6pv6mdtd_.log [oracle@vmlinux2 ~]$ ls -l /u03/flash_recovery_area/TURLOCK/onlinelog/* -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:15 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_1_6pv6l6j9_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:15 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_2_6pv6ldcn_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_3_6pv6llpo_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_4_6pv6lrc2_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_5_6pv6m0kk_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_6_6pv6m6rg_.log -rw-r----- 1 oracle oinstall 52429312 Mar 1 20:16 /u03/flash_recovery_area/TURLOCK/onlinelog/o1_mf_7_6pv6mfng_.log

14. After successfully clearing all standby redo log groups, re-enable Flashback database on the physical standby database.

    SQL> alter database flashback off; SQL> alter database flashback on;

15. Finally, on the physical standby database, restart the managed recovery process (MRP).

    SQL> alter database recover managed standby database using current logfile disconnect;

Physical Standby Database Has Nologging Changes On a Subset of Datafiles

An often asked question is what are the consequences of running a DML or DDL operation on a primary database in a Data Guard configuration using the NOLOGGING clause and the FORCE LOGGING database option is not enabled on the primary. When a user submits a NOLOGGING operation, although a redo log record does get written to the redo log of the primary, there is no data associated with this record. In this type of scenario, if the FORCE LOGGING database option is not enabled, then Oracle will not log the entire operation in the redo log and thus, never send the modified data to the standby database for recovery. While this doesn't necessarily render the entire standby database as unusable, it does invalidate the standby for those tables affected and may require substantial DBA administrative activities to repair. For example, the NOLOGGING operation will render the data blocks on the standby database as logically corrupt because of the missing or incomplete redo log entries. If the standby database is ever opened in read only mode or worse yet, switches to the primary role, errors will occur when trying to access those objects (data blocks) that were previously written with the NOLOGGING option. Attempting to access logically corrupt data blocks on the standby database will throw an error similar to the following:

ORA-01578: ORACLE data block corrupted (file # 5, block # 12) ORA-01110: data file 5: '/u02/oradata/TURLOCK/datafile/o1_mf_users_6p58khrt_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option

Although you can specify the ALTER DATABASE FORCE LOGGING statement on the primary database to override the NOLOGGING clause and prevent this type of damage to occur in the future, this statement will not repair a standby database that has already been invalidated.

This section provides step-by-step instructions to roll forward a physical standby database for which NOLOGGING changes have been applied to a small subset of the database. First though, let's logically corrupt the physical standby database which will be used as a test case to perform those recovery steps.

# ---[ From the Primary Database ] --- # SQL> alter database no force logging; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------- NO SQL> create table scott.emp_nl nologging as select * from scott.emp where 1=0; Table created. SQL> insert /*+ append */ into scott.emp_nl select * from scott.emp; <--- NOLOGGING OPERATION 1000000 rows created. SQL> commit; Commit complete. SQL> alter system switch logfile; <--- FORCE CHANGES TO STANDBY System altered. # ---[ From the Physical Standby Database ] --- # SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open read only; Database altered. SQL> select emp_id from scott.emp_nl; select emp_id from scott.emp_nl * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 12) ORA-01110: data file 5: '/u02/oradata/TURLOCK/datafile/o1_mf_users_6pxkfo9d_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option

Use the following steps to roll forward the logically corrupt physical standby database.

1. List the files that have had NOLOGGING changes applied by querying the V$DATAFILE view on the physical standby database. For example:

   SQL> select file#, first_nonlogged_scn from v$datafile where first_nonlogged_scn > 0; FILE# FIRST_NONLOGGED_SCN ---------- ------------------- 4 3832195 5 3831710

2. Stop Redo Apply on the physical standby database.

   SQL> alter database recover managed standby database cancel;

3. On the physical standby database, offline the datafiles (recorded in step 1) that have had NOLOGGING changes. Taking these datafiles offline ensures redo data is not skipped for the corrupt blocks while the incremental backups are performed.

   SQL> alter database datafile 4 offline for drop; SQL> alter database datafile 5 offline for drop;

4. Start Redo Apply on the physical standby database.

   SQL> alter database recover managed standby database using current logfile disconnect;

5. Connect to the primary database as the RMAN target, create an incremental backup for each datafile listed in the FIRST_NONLOGGED_SCN column (recorded in step 1). For example:

   [oracle@vmlinux1 ~]$ rman target sys/MySysPassword@modesto RMAN> backup incremental from scn 3832195 datafile 4 2> format '/u04/oracle/dg_staging/ForStandby_%U' tag 'FOR STANDBY'; RMAN> backup incremental from scn 3831710 datafile 5 2> format '/u04/oracle/dg_staging/ForStandby_%U' tag 'FOR STANDBY';

6. Using an OS remote copy utility, transfer all backup sets created on the primary system to the physical standby machine. Note that there may be more than one backup file created and that all backup pieces of the incremental backups must be manually copied to the physical standby database before you catalog them. For example, to scp the RMAN incremental backup sets from the primary database machine vmlinux1 to the physical standby database running on machine vmlinux2, you would run the following command:

   [oracle@vmlinux1 ~]$ scp /u04/oracle/dg_staging/ForStandby_* vmlinux2:/u04/oracle/dg_staging/ ForStandby_38m69khc_1_1 100% 144KB 144.0KB/s 00:00 ForStandby_39m69ki1_1_1 100% 108MB 7.2MB/s 00:15

7. After copying the incremental backup pieces to the physical standby machine, connect to the physical standby database using RMAN and catalog those incremental backup pieces.

   [oracle@vmlinux2 ~]$ rman target sys/MySysPassword@turlock RMAN> catalog start with '/u04/oracle/dg_staging/ForStandby';

8. Stop Redo Apply on the physical standby database.

   SQL> alter database recover managed standby database cancel;

9. Online the datafiles on the standby database that were taken offline in step 3.

   SQL> alter database datafile 4 online; SQL> alter database datafile 5 online;

10. While connected to the physical standby database as the RMAN target, apply the incremental backup sets.

    RMAN> recover datafile 4, 5 noredo;

11. Re-query the V$DATAFILE view on the standby database to verify there are no datafiles with nologged changes. The following query should return zero rows:

    SQL> select file#, first_nonlogged_scn from v$datafile where first_nonlogged_scn > 0; no rows selected

12. Remove the incremental backups from the physical standby system.

    RMAN> delete backup tag 'FOR STANDBY';

13. Manually remove the incremental backups from the primary system. Remember that RMAN does not consider this type of independent incremental backup as part of a backup strategy at the source database and thus, does no catalog it. For example, the following example uses the Linux rm command:

    [oracle@vmlinux1 ~]$ rm -f /u04/oracle/dg_staging/ForStandby_*

14. Start Redo Apply on the physical standby database.

    SQL> alter database recover managed standby database using current logfile disconnect;

Physical Standby Database Has Widespread Nologging Changes

Use the following steps to roll forward a logically corrupt physical standby database for which NOLOGGING changes have been applied to a large portion of the database.

1. Query the V$DATAFILE view on the standby database to record the lowest FIRST_NONLOGGED_SCN.

   SQL> select min(first_nonlogged_scn) from v$datafile where first_nonlogged_scn > 0; MIN(FIRST_NONLOGGED_SCN) ------------------------ 3834700

2. Stop Redo Apply on the physical standby database.

   SQL> alter database recover managed standby database cancel;

3. Connect to the primary database as the RMAN target, create an incremental backup from the lowest FIRST_NONLOGGED_SCN (recorded in step 1). For example:

   [oracle@vmlinux1 ~]$ rman target sys/MySysPassword@modesto RMAN> backup incremental from scn 3834700 database 2> format '/u04/oracle/dg_staging/ForStandby_%U' tag 'FOR STANDBY';

4. Using an OS remote copy utility, transfer all backup sets created on the primary system to the physical standby machine. Note that there may be more than one backup file created and that all backup pieces of the incremental backup must be manually copied to the physical standby database before you catalog them. For example, to scp the RMAN incremental backup sets from the primary database machine vmlinux1 to the physical standby database running on machine vmlinux2, you would run the following command:

   [oracle@vmlinux1 ~]$ scp /u04/oracle/dg_staging/ForStandby_* vmlinux2:/u04/oracle/dg_staging/ ForStandby_3am69r2k_1_1 100% 218MB 9.9MB/s 00:22 ForStandby_3bm69r4v_1_1 100% 10MB 4.9MB/s 00:02

5. After copying the incremental backup pieces to the physical standby machine, connect to the physical standby database using RMAN and catalog those incremental backup pieces.

   [oracle@vmlinux2 ~]$ rman target sys/MySysPassword@turlock RMAN> catalog start with '/u04/oracle/dg_staging/ForStandby';

6. While connected to the physical standby database as the RMAN target, apply the incremental backups

   RMAN> recover database noredo;

7. Re-query the V$DATAFILE view on the standby database to verify there are no datafiles with nologged changes. The following query should return zero rows:

   SQL> select file#, first_nonlogged_scn from v$datafile where first_nonlogged_scn > 0; no rows selected

8. Remove the incremental backups from the physical standby system.

   RMAN> delete backup tag 'FOR STANDBY';

9. Manually remove the incremental backups from the primary system. Remember that RMAN does not consider this type of independent incremental backup as part of a backup strategy at the source database and thus, does no catalog it. For example, the following example uses the Linux rm command:

   [oracle@vmlinux1 ~]$ rm -f /u04/oracle/dg_staging/ForStandby_*

10. Start Redo Apply on the physical standby database.

    SQL> alter database recover managed standby database using current logfile disconnect;

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.