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

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


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 16-July-2015*** 
***Checked for relevance on 27-Oct-2016***

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

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:
A look-a-like procedure is documented in :
   Oracle® Data Guard Concepts and Administration 11g Release 1 (11.1) Part Number B28294-03
   Section 13.4 Recovering After the NOLOGGING Clause Is Specified
   http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/scenarios.htm#i1015738

DETAILS

STEPS

1. 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. Recreate the Standby Controlfile following:
Note 459411.1 Steps to recreate a Physical Standby Controlfile
13. Remove the incremental backups from the standby system:
RMAN> DELETE BACKUP TAG 'FOR STANDBY';
14. Manually remove the incremental backups from the primary system. For example, the following example uses the Linux rm command:
% rm /tmp/ForStandby_*
15. Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Note: Starting from 12c we can use RECOVER DATABASE...FROM SERVICE clause in RMAN to generate, transfer and apply the incremental backup in a single step. Please refer below document for examples:

Note 1987763.1 ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C 

 

2. 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. Recreate the Standby Controlfile following:
Note 459411.1 Steps to recreate a Physical Standby Controlfile
9.Remove the incremental backups from the standby system:
RMAN> DELETE BACKUP TAG 'FOR STANDBY';
10.Manually remove the incremental backups from the primary system. For example, the following removes the backups using the Linux rm command:
% rm /tmp/ForStandby_*
11.Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Note:

If the affected files belong to a READ ONLY tablespace, those files will be ignored during backup. To bypass the issue, at Primary Database, switch the tablespace from read only to read write and back to read only again :
SQL> alter tablespace read write ;
SQL> alter tablespace read only ;
============================================================

 ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 7.1.6.0 to 12.1.0.2 [Release 7.1.6 to 12.1]
Information in this document applies to any platform.

PURPOSE

This note is intended to describe how Oracle reports a corruption caused by a NOLOGGING operation with ORA-1578 / ORA-26040 and how to fix the errors.
IMPORTANT
When ORA-26040 is not produced along with ORA-1578 the block is then corrupt by a different reason and RMAN Block Media Recovery can be used to repair the corruption. Refer to Doc ID 1578.1

SCOPE

This document is intended for Customers and Oracle Support.

DETAILS

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

If the associated redo/archived log file is used to RECOVER the data files, Oracle invalidates such blocks and the error ORA-26040 along with error ORA-1578 are reported by SQL statements in the next block reads.

Errors Example:
SQL> select * from test_nologging;

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)
ORA-01110: data file 4: '/oradata/users.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The NOLOGGING attribute is stored in column LOGGING in data dictionary views like:

DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc.

LOGGING='NO' indicates NOLOGGING.
 

The block is then marked as Soft Corrupt meaning that the next block read will report the ORA-1578/ORA-26040 errors.


DATAPUMP parameter DISABLE_ARCHIVE_LOGGING

The DATAPUMP impdp parameter DISABLE_ARCHIVE_LOGGING:Y disables the LOGGING definition during import which will generate NOLOGGING operations for the table and index; if the associated datafile is restored and recovered, subsequent statement will fail with error ORA-1578 and ORA-26040.
"If the database is in FORCE LOGGING mode, then the DISABLE_ARCHIVE_LOGGING option will not disable logging when indexes and tables are created".  
An example of this parameter during import is:
impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=dp transform=disable_archive_logging:y

RMAN/DBV and Corrupt Blocks by NOLOGGING

DBV prints the generic message DBV-200 in rdbms versions lower than 10.2.0.4 and error DBV-201 in versions greater or equal to 10.2.0.4  Doc ID 5031712.8:
DBV-00200: Block, dba 46137428, already marked corrupted
DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

The "VALIDATE" RMAN command is used to identify NOLOGGING blocks and populates the view v$database_block_corruption (versions lower than 12c) and v$nonlogged_block (12c and greater).  In the next example the validate output reports 933 blocks marked as corrupt in datafile 4 then v$database_block_corruption or v$nonlogged_block will be updated with that information:

RMAN> VALIDATE DATABASE;
...
.....
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     933            1            6401            2275124 
  File Name: /oracle/dbs/users.dbf

In versions lower than 10.2.0.5 and 11.1.0.7,  RMAN validate reports it with a generic message like:
10.2.0.4 and lower, 11.1.0.6, 11.1.0.7:

The VALIDATE RMAN command reports the NOLOGGING blocks v$database_block_corruption with CORRUPTION_TYPE=LOGICAL
In version 10.2.0.5 or in 11.2.0.1 and forward, RMAN has been enhanced to report it with CORRUPTION_TYPE=NOLOGGING. Reference Doc ID 7396077.8 :
10.2.0.5 and 11.2.0.1+:

The VALIDATE RMAN command reports the NOLOGGING blocks in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING
In version 12c and forward RMAN validate no longer populates view v$database_block_corruption; instead the new view v$nonlogged_block is updated:
12c:
RMAN validate reports it in v$nonlogged_block

RMAN backups do not fail due to NOLOGGING corrupt blocks.  In general RMAN does not fails with soft corrupt blocks so the MAXCORRUPT clause is not necessary in such cases.  In such cases the backup will contain the soft corrupt block and a restore will leave the corruption as when the backup was made.
When there is a generic message besides the error ORA-26040, a block dump might be taken and see if the byte 0xff is along the block or if the block is associated to a segment, try to read it with a SQL statement for which errors ORA-1578/ORA-26040 will be produced as the block is corrupt due to recovery with a NOLOGGING operation.

Monitoring NOLOGGING Operations


The RMAN command "REPORT UNRECOVERABLE" reports when a data file has been changed by a NOLOGGING operation and the datafile has not been backed up since then. Example:
RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4    full or incremental     /oracle/dbs/users.dbf

V$DATAFILE has several columns that are updated when a NOLOGGING operation takes place and parameter db_unrecoverable_scn_tracking is set to true (default value); db_unrecoverable_scn_tracking is not available in 10g.  Reference the next V$DATAFILE columns in our Oracle Database Reference Documentation:
UNRECOVERABLE_CHANGE#     
UNRECOVERABLE_TIME     
FIRST_NONLOGGED_SCN 
FIRST_NONLOGGED_TIME


In 11.2.0.4 or 12.1.0.2+ the MRP in a Standby Database can report NOLOGGING changes in the alert log when event 16490 is set to level 1.  The alert log is updated with these informative messages:
ORA-16490 "logging invalidated blocks on standby due to invalidation redo"

"INVD_BLKS: Invalidating (file , bno )"  
"fname: 'Datafile name'. rdba: ..."
  

Identify when a block was marked as NOLOGGING

To identify when a block was marked as NOLOGGING, use the block scn in the trace file or use the value in column CORRUPTION_CHANGE# in v$database_block_coruption to translate it to a timestamp:


Use the block scn from a trace file
Example from trace file:

  Start dump data blocks tsn: 60 file#: 4 minblk 84 maxblk 84
  buffer tsn: 3 rdba: 0x02c00054 (11/84)
  scn: 0x0771.4fa24eb5 seq: 0xff flg: 0x04 tail: 0x4eb500ff

Take 0x0771.4fa24eb5, remove the '.' and convert 0x07714fa24eb to decimal which is 511453045995
Use the value in column CORRUPTION_CHANGE# in v$database_block_coruption
If RMAN validate is run the view v$database_block_coruption is populated with corruption_type='NOLOGGING' (10.2.0.5 and 11.2.0.1+) and column CORRUPTION_CHANGE# has the decimal scn value.
Get the SCN Timestamp
To get the timestamp use any of these methods:
select scn_to_timestamp(&&decimal_scn)
from dual;

If RMAN validate was run:

select file#, block#, scn_to_timestamp(CORRUPTION_CHANGE#)
from v$database_block_corruption
where CORRUPTION_TYPE='NOLOGGING';
In 12c:
select file#, block#, scn_to_timestamp(NONLOGGED_START_CHANGE#)
from v$nonlogged_block;
If error ORA-08181 is produced query gv$archived_log or gv$log_history to get a time interval approximation:
alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';

select first_time, next_time
from   gv$archived_log
where  &decimal_scn between first_change# and next_change#;

OR

select first_time
from   gv$log_history
where  &decimal_scn between first_change# and next_change#;

if RMAN validate was run:
alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';
select file#, block#, first_time, next_time
from   v$archived_log, v$database_block_corruption
where  CORRUPTION_CHANGE# between first_change# and next_change#
  and CORRUPTION_TYPE='NOLOGGING';

OR

select file#, block#, first_time
from   v$log_history, v$database_block_corruption
where  CORRUPTION_CHANGE# between first_change# and next_change#
  and CORRUPTION_TYPE='NOLOGGING';
In 12c:
alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';
select file#, block#, first_time, next_time
from v$nonlogged_block, v$archived_log
where NONLOGGED_START_CHANGE# between first_change# and next_change#;
OR
select file#, block#, first_time
from v$nonlogged_block, v$log_history
where NONLOGGED_START_CHANGE# between first_change# and next_change#;

NOARCHIVELOG and NOLOGGING in SYSAUX tablespace / AWR, EM, etc

If the database is running on rdbms versions 11.1.0.6 or 11.1.0.7 or 11.2.0.1, ORA-1578 and ORA-26040 can be produced due to NOLOGGING for DIRECT PATH operations after a manual RECOVER DATABASE in a NOARCHIVELOG mode database even if FORCE LOGGING is enabled in the database or even if LOGGING is defined for the segment.

This is most commonly seen in AWR or EM objects (Enterprise Manager) stored in the SYSAUX tablespace.

Reference Doc Id 1071869.1 for details and solution.  Note that the database may currently be in a version greater than 11.1 or 11.2.0.1 but the problem could be introduced before upgrade from one of those affected versions.
The restriction has been lifted in 11.2.0.2+ and this issue did not occur in 10g.

 

Changes by RDBMS versions

RDBMS VersionChange
10.2.0.4+DBverify reports a NOLOGGING block with error "DBV-00201: Block, DBA , marked corrupt for invalid redo application"
10.2.0.5, 11.2.0.1+RMAN validate reports the NOLOGGING block in v$database_block_corruption with corruption_type='NOLOGGING'
11g+Parameter db_unrecoverable_scn_tracking is introduced. 
11.1.0.6, 11.1.0.7, 11.2.0.1ORA-1578 and ORA-26040 can be produced due to NOLOGGING for DIRECT PATH operations after a manual RECOVER DATABASE in a NOARCHIVELOG mode databaseeven if FORCE LOGGING is enabled.  The restriction has been lifted in 11.2.0.2+ and problem did not happen in 10g.
11.2.0.4, 12.1.0.2+Managed Physical Standby Recovery (MRP) can report NOLOGGING changes in the alert log when event 16490 is set to level 1.  The alert log is updated with message:

ORA-16490 "logging invalidated blocks on standby due to invalidation redo"

This improvement was introduced by the fix of Bug 16319519
12cRMAN validate no longer populates view v$database_block_corruption; instead the new view v$nonlogged_block is updated

SOLUTION


Note that the data inside the affected blocks is not salvageable. Methods like "Media Recovery" or "RMAN blockrecover" will not fix the problem unless the data file was backed up after the NOLOGGING operation was registered in the Redo Log.

Is error after RMAN DUPLICATE?

If the error is after a RMAN DUPLICATE or RESTORE, enable FORCE LOGGING at SOURCE database and perform the DUPLICATE or RESTORE (after new BACKUP) steps again:
alter database force logging;

Is error produced in a PHYSICAL STANDBY Database?

If the error is produced in a PHYSICAL STANDBY database, the option is to restore the affected file from the primary database (only if the problem is not present in the PRIMARY) and to avoid the problem from being introduced there is the option to force logging in the PRIMARY database with:
alter database force logging;
If the same datafile in primary has other different blocks marked as nologging whereas in the current standby those blocks are not marked as nologging then manual intervention to skip those corrupt blocks in both databases, either using event 10231 or dbms_repair, might be required before copying the file from primary to standby; the final result will be a merged table from primary and standby databases.  Nologging blocks in the primary database could be the result of a recovered primary database either from backup or because it was a former standby database and is now primary after switchover.
In order to resolve the errors and if it is not an INDEX the segment can be recovered from a backup like an export dump or from another source. If backups are not available the segment might be recreated following the next steps:

Identify the affected segment

Identify the affected segment  as described in Doc ID 819533.1 or identify all the corrupt objects as described in Doc ID 472231.1, then:
Is it a FREE Block?
If the NOLOGGING Block is a FREE Block (the associated extent is in dba_free_space), which could be discovered by running DBVerify with error DBV-00201 or shown in view v$database_block_corruption, there is the option to wait until the block is reused which will automatically re-format the block or force re-formatting the block using Doc ID 336133.1
Is it an INDEX?
If it is an INDEX, drop and create the index
Is it a TABLE?
If it is a TABLE, procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements; Doc ID 556733.1 has a DBMS_REPAIR example.  Then decide to re-create the segment:
by moving the table: alter table &table_name move; 
OR
by saving the data (export, Create Table as Select, etc) and then truncate or drop/create.
Is it a LOB?
If it is a LOB use Doc ID 293515.1

When the issue is fixed by dropping the segment, the block is marked as free and may be later allocated for a different segment; it may remain marked as NOLOGGING.  A DML/query will not fail; the block will be re-formatted with no errors when it is reused by the new segment.  At that time if the corruption is still reported in v$database_block_corruption, run a rman validate to clear that view.

REFERENCES

NOTE:7396077.8 - Bug 7396077 - RMAN does not differentiate NOLOGGING corrupt blocks that produce ORA-1578/ORA-26040
NOTE:819533.1 - How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
NOTE:1071869.1 - ORA-1578 ORA-26040 in 11g for DIRECT PATH with NOARCHIVELOG even if LOGGING is enabled
NOTE:293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
NOTE:556733.1 - DBMS_REPAIR SCRIPT
NOTE:472231.1 - How to identify all the Corrupted Objects in the Database with RMAN
NOTE:290161.1 - The Gains and Pains of Nologging Operations

  ==================================================================

THE GAINS AND PAINS OF NOLOGGING OPERATIONS

Overview

Whereas a logged INSERT operation has to generate redo for every change data or undo block, nologging operations indicate that the database operation is not logged in the online redo log file. Even though a small invalidation redo record¹ is still written to the online redo log file, nologging operations skip the redo generation of the corresponding inserted data.  Nologging can be extremely beneficial for the following reasons:
  • data written to the redo is minimized dramatically
  • time to insert into a large table or index or LOB can be reduced dramatically
  • performance improves for parallel creation of large tables or indices
However, NOLOGGING is intended for configurations in which media recovery or the recovery of the corresponding object is not important. Thus, if the disk or tape or storage media fails, you will not be able to recover your changes from the redo because the changes were never logged.  

Nologging operations are invoked by any of the following:
  • SQL*Loader direct load operations
  • Direct load INSERT operations from CREATE TABLE | INDEX or INSERT commands
  • Loading into an object containing LOB data when its object's segment characteristic is NOCACHE NOLOGGING
For databases in ARCHIVELOG mode, nologging operations can only occur for a particular object if and only if:
  • Database allows for nologging (ALTER DATABASE NO FORCE LOGGING) and
  • Tablespace allows for nologging (ALTER TABLESPACE NO FORCE LOGGING) and
  • Object allows for nologging (ALTER TABLE NOLOGGING)
This paper will cover the following topics:
  • examples of nologging operations
  • prevention of nologging operations
  • detection of nologging operations on the primary and standby databases
  • repair of nologged changes on the physical and logical standby databases

Examples of nologging operations

Below is a list of examples that can be used for testing purposes.   The database must be in ARCHIVELOG mode and the segment must explicitly be set to NOLOGGING and must allow nologging operations to see the effect of nologging changes:
 
    1. insert /*+ APPEND */ into scott.emp select * from sys.emp2;
    2. create table emp nologging as select * from sys.emp;
    3. create index emp_i on emp(empno) nologging;
    4. sqlload operation with unrecoverable option

Prevention of nologging operations

When a standby database exists or if you want all transactions to be recoverable on a database, tablespace or object-wide perspective, it is recommended that you prevent nologging operations by issuing the relevant options.  These options include:
  • ALTER DATABASE FORCE LOGGING (database level) or
  • ALTER TABLESPACE FORCE LOGGING (tablespace level) on the relevant tablespaces you want to protect or
  • [CREATE | ALTER] TABLE LOGGING (example of object level) on the relevant objects you want to protect
This ensures that all transactions are logged and can be recovered through media recovery or Redo Apply or SQL Apply assuming appropriate data type support.
Force Logging is not required to use Data Guard. However without it, DBAs should expect additional administration in maintaining the affected objects. The next sections will cover detection and correction.

Detection of Nologging Operations On the Primary and Standby Databases

On the primary database, you can monitor for the most recent nologging operation that occurred in the database by issuing the following query:
 
         SELECT NAME, UNRECOVERABLE_CHANGE#,              
         TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')
         FROM V$DATAFILE;

The above primary database's query dictates when the most recent nologging operation occurred and when the invalidation redo was written to the redo.    

Once Redo Apply (or Media Recovery) processes the invalidation redo, it marks all the corresponding data blocks corrupt.  You will detect encounter corrupted blocks on the physical standby database when you query any data that references these data blocks.   You will receive the following errors:  

                ORA-01578: ORACLE data block corrupted (file # 3, block # 514)
                ORA-01110: data file 3: '/u01/lto_linux9206/dbs/users.dbf'
                ORA-26040: Data block was loaded using the NOLOGGING option

You can proactively catch some of these corrupted blocks on Redo Apply (or media recovery) instance by running DBVERIFY on the data files. 
            
        $ dbv file=users.dbf
        DBVERIFY - Verification starting : FILE = users.dbf
        DBV-00200: Block, dba 12583426, already marked corrupted
        DBV-00200: Block, dba 12583427, already marked corrupted
        DBV-00200: Block, dba 12583428, already marked corrupted
SQL apply ignores the invalidation redo since it cannot convert it to any reasonable SQL; so, the logical standby will not receive any immediate errors.   If future transactions reference the missing data, then apply slave will receive an ORA-01403 in the alert.log.   For example, the following UPDATE statement failed on the logical standby because it was referencing  'nologged' rows that do not exist on the logical standby database.

        LOGSTDBY stmt: update "SCOTT"."NOLOG"
          set
            "SAL" = 810
          where
            "EMPNO" = 7369 and
            "ENAME" = 'SMITH' and
            "JOB" = 'CLERK' and
            "MGR" = 7902 and
            "HIREDATE" = TO_DATE('17-DEC-80', 'DD-MON-RR') and
            "SAL" = 800 and
            "COMM" IS NULL and
            "DEPTNO" = 20 and
            ROWID = 'AAAAAAAAEAAAACRAAA'
        LOGSTDBY status: ORA-01403: no data found
        LOGSTDBY PID 21733, oracle@dlsun1917 (P004)
        LOGSTDBY XID 0x0001.010.00000cf3, Thread 1, RBA 0x038b.00000826.1a4
        Tue Nov  2 18:26:51 2004
        Errors in file /private/oracle/app/admin/tens/bdump/tens_lsp0_20328.trc:
        ORA-12801: error signaled in parallel query server P004
        ORA-01403: no data found
        LOGSTDBY Reader P003 pid=27 OS id=21729 stopped

Currently in Oracle 9i and Oracle 10gR1, only the primary database V$DATAFILE view reflects nologging operations..   In 10gR2, the V$DATAFILE view will be enhanced to include information regarding when an invalidation redo is applied and the aforementioned corrupted blocks are written to the corresponding data file on a Redo Apply (or media recovery or standby) instance.  

Repair of Nologged Changes on the Physical and Logical Standby Databases

After a nologged operation on the primary is detected, it is recommended to create a backup immediately if you want to recover from this operation in the future.  However there are additional steps required if you have an existing physical or logical standby database.    This is crucial if you want to preserve the data integrity of your standby databases. 

For a physical standby database, Redo Apply will process the invalidation redo and mark the corresponding data blocks corrupt. 

For a physical standby database, follow these steps² to reinstantiate the relevant data files .

    1. stop Redo Apply (recover managed standby database cancel)
    2. offline corresponding datafile(s) (alter database datafile offline drop;)
    3. start Redo Apply (recover managed standby database disconnect)
    4. copy the appropriate backup datafiles over from the primary database (e.g. use RMAN to backup datafiles and copy them)
    5. stop Redo Apply (recover managed standby database cancel)
    6. online corresponding data files (alter database datafile online;)
    7. start Redo Apply (recover managed standby database disconnect)

For a logical standby database, SQL Apply skips over the invalidation redo completely; so, the subsequent corresponding table or index will not be updated.   However, future reference to missing data will result in ORA-1403 (no data found).   In order to resynchronize the table with the primary table, you need to re-create it from the primary database.   Follow the steps described in Oracle Data Guard Concepts and Administration, Chapter 'Managing a Logical Standby Database', and Section 'Adding or Re-Creating Tables On a Logical Standby Database'   Basically, you will be using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure.
=========================================================================

 Using RMAN Effectively In A Dataguard Environment. (Doc ID 848716.1)




 NOTE--> This information collected from Oracle. we will use for knowledge purpose and not for commercial usage. all rights reserved for Oracle only.
=================================================================

 NOTE:203980.1 - How To Use RMAN For Backup And Restore In A Standby Database Environment
NOTE:290814.1 - Rolling a Standby Forward using an RMAN Incremental Backup in 10g
NOTE:728053.1 - Configure RMAN to purge archivelogs after applied on standby
NOTE:290817.1 - Rolling a Standby Forward using an RMAN Incremental Backup in 9i
NOTE:374069.1 - Step by Step Guide to Create Physical Standby Using RMAN DUPLICATE (non ASM) on different / new host
NOTE:416310.1 - Reinstating a Physical Standby Using Backups Instead of Flashback
NOTE:602299.1 - Benefits and Usage of RMAN with Standby Databases
NOTE:836986.1 - Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup.
NOTE:958181.1 - Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes
NOTE:1531031.1 - Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary

 =====================================================================

Comments

Post a Comment

Oracle DBA Information