How to fix Nologging Changes

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes [ID 958181.1]

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes [ID 958181.1]
		Modified 24-OCT-2009     Type BULLETIN     Status REVIEWED

In this Document
  Purpose
  Scope and Application
  Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes
     STEPS
     ++ Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been  applied to a small subset of the database:
     ++ Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been applied to a large portion of the database:
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2
Information in this document applies to any platform.

Purpose

This document describes a method of rolling forward a standby database using incremental backups to fix the ORA-1578 and the ORA-26040 errors that were cuased due to Nologging/Unrecoverable operation.

Scope and Application

When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.

This kind of NOLOGGING/UNRECOVERABLE will mark the affected blocks as corrupt during the media recovery on the standby database.Now, when you either activate the standby database, or open the standby database with the read-only option, and attempt to read the range of blocks that are marked as "UNRECOVERABLE," you see error messages similar to the following:

  ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
  ORA-01110: data file 1: '/vobs/oracle/dbs/stdby/tbs_1.f'
  ORA-26040: Data block was loaded using the NOLOGGING option

In this article we will be checking the steps to fix the nologging changes have been applied to a small subset of the database and the  nologging changes have been applied to a large portion of the database:

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes

STEPS

++ Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been  applied to a small subset of the database:

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

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
FILE#      FIRST_NONLOGGED_SCN
---------- -------------------
         4              225979
         5              230184

2.Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.On the standby database, offline the datafiles (recorded in step 0) 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 standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

5.While connected 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 0). For example:

RMAN> BACKUP INCREMENTAL FROM SCN 225979 DATAFILE 4 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';
RMAN> BACKUP INCREMENTAL FROM SCN 230184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';

6.Transfer all backup sets created on the primary system to the standby system. (Note that there may be more than one backup file created.)

SCP /tmp/ForStandby_* standby:/tmp

7.While connected to the physical standby database as the RMAN target, catalog all incremental backup pieces. For example:

RMAN> CATALOG START WITH '/tmp/ForStandby_';

8.Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

9.Online the datafiles on the standby database

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.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;

12.Remove the incremental backups from the standby system:

RMAN> DELETE BACKUP TAG 'FOR STANDBY';

13.Manually remove the incremental backups from the primary system. For example, the following example uses the Linux rm command:

rm /tmp/ForStandby_*

14.Start Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

++ Follow this step-by-step procedure to roll forward a 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)
------------------------
                  223948

2.Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.While connected to the primary database as the RMAN target, create an incremental backup from the lowest FIRST_NONLOGGED_SCN (recorded in step 0)

RMAN> BACKUP INCREMENTAL FROM SCN 223948 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FOR STANDBY';

4.Transfer all backup sets created on the primary system to the standby system. (Note that more than one backup file may have been created.) The following example uses the scp command to copy the files:

scp /tmp/ForStandby_* standby:/tmp

5.While connected to the standby database as the RMAN target, catalog all incremental backup piece(s)

RMAN> CATALOG START WITH '/tmp/ForStandby_';

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

RMAN> RECOVER DATABASE NOREDO;

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

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

8.Remove the incremental backups from the standby system:

RMAN> DELETE BACKUP TAG 'FOR STANDBY';

9.Manually remove the incremental backups from the primary system. For example, the following removes the backups using the Linux rm command:

rm /tmp/ForStandby_*

10.Start Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

References

NOTE:794505.1 - ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution

---