Nologging operations affect standby database

Nologging can be very beneficial for it dramatically reduces redo log generation, thought minimal redo information (that invalidate all blocks directly written by nologging operations) is still needed . In a data guard environment, however, it will affect standby recovery capability since no redo changes are logged during nologging operations on primary database.

Nologging operations

Nologging operations can be invoked by the following operations:

1. insert /*+ APPEND */ into .. select * from .. ;
2. create table .. nologging as select * from .. ;
3. create index .. on table(col_list) nologging;
4. sqlloader operation in direct path mode
5. load LOBs where segment characteristics have NOLOGGING.

Nologging can occur only if the following are true

Nologging can be effective on objects only if all the following are true:

1. database is set to NO FORCE LOGGING (default mode)


select force_logging from v$database;

FORCE_LOGGING
-------------
NO

Otherwise,

alter database no force logging;

2. tablespace is set to NO FORCE LOGGING (default mode)

select force_logging from dba_tablespaces where tablespace_name = 'USERS';

FORCE_LOGGING
-------------
NO

Otherwise,

alter tablespace users no force logging;

3. object level

select LOGGING from user_tables where table_name = 'TEST_T';

LOG
---
NO

Otherwise,

alter table test_t nologging;

Nologging operation affects standby database

Redo logs are crucial for media recovery operation and redo applying on standby database. When nologging operations happened on primary database, no changes will be applied to standby database since such changes have never been logged in first place. It will result in data integration problem between primary database and its standbys.

On primary let’s enable NOLOGGING on a table

alter table queue_t nologging;

Do a direct load insert into the table

insert /*+ APPEND */ into queue_t select sysdat, 'NEW_VALUE'  from dual;
commit;
select count(*) from queue_t;

  COUNT(*)
----------
     75526

On standby database we will not receive this error until we query on the same table

select count(*) from queue_t;
select count(*) from queue_t
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1282)
ORA-01110: data file 6: '/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Proactively detect nologging operations

we can detect nologging operations before getting ORA-01578 ORACLE data block corruptedby viewing v$datafile on primary database.

select file#, name, UNRECOVERABLE_CHANGE#, UNRECOVERABLE_TIME
  from v$datafile df;

     FILE# NAME                                               UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
---------- -------------------------------------------------- --------------------- -------------------
         1 /app/oracle/oradata/DB11R2/system01.dbf                                0
         2 /app/oracle/oradata/DB11R2/sysaux01.dbf                                0
         3 /app/oracle/oradata/DB11R2/undotbs01.dbf                               0
         4 /app/oracle/oradata/DB11R2/users01.dbf                           3578349 2010-11-07 13:39:54
         5 /app/oracle/oradata/DB11R2/guang_tbs_01.dbf                            0
         6 /app/oracle/oradata/DB11R2/guang_tbs_02.dbf                      4533272 2011-02-08 15:21:39
         7 /app/oracle/oradata/DB11R2/users02.dbf                                 0
         8 /app/oracle/oradata/DB11R2/users03.dbf                           3578349 2010-11-07 13:39:54

Redo Apply processes invalidation redo logs generated by nologging operations, it will mark relevant blocks as corrupted block. Once you query on data that references these blocks, you will get ORA-01578. By issuing DBVERIFY agaist the datafiles on physical standby database, you can get a list of corrupted blocks


dbv file=/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf

DBVERIFY - Verification starting : FILE = /app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf

DBV-00201: Block, DBA 25167019, marked corrupt for invalid redo application
DBV-00201: Block, DBA 25167020, marked corrupt for invalid redo application
DBV-00201: Block, DBA 25167021, marked corrupt for invalid redo application
DBV-00201: Block, DBA 25167022, marked corrupt for invalid redo application
DBV-00201: Block, DBA 25167023, marked corrupt for invalid redo application
DBV-00201: Block, DBA 25167024, marked corrupt for invalid redo application
DBV-00201: Block, DBA 25167025, marked corrupt for invalid redo application

Repair physical standby database from nologging operations

Once you have detected data corruption errors on physical standby, it’s recommended to take an immediate backup of the primary database. The physical standby database stained by nologging operations can be repaired by the following steps:

On standby database stop the managed recovery process:

recover managed standby database cancel;

Offline the datafile containing relevant corrupted blocks on physical standby

alter database datafile '/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf' offline drop;

Restart managed recovery process on physical standby

recover managed standby database using current logfile disconnect;

Make a copy of corresponding datafile on primary database using RMAN and copy it to standby database


rman target sys@primary

backup as copy datafile '/app/oracle/oradata/DB11R2/guang_tbs_02.dbf' format '/app/oracle/oradata/DB11R2/guang_tbs_02_bak.dbf';

cp /app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf /app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf.bad
cp /app/oracle/oradata/DB11R2/guang_tbs_02_bak.dbf /app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf

stop the managed recovery process on physical standby

recover managed standby database cancel;

Online the copied datafile on physical standby. It should be mentioned that if the standby is running as Active Data Guard(11g), you have to shut it down and restart it in mont mode first, then take the datafile online. otherwise you will receive error: ORA-01113: file  needs media recovery.

alter database datafile '/app/oracle/oradata/DB11R2DR/guang_tbs_02.dbf' online;

Restart managed recovery process on physical standby

recover managed standby database using current logfile disconnect;

Prevention physical standby from data corruption caused by nologging operations

Though data corruption on physical standby can be repaired, it’s better to avoid nologging operations on primary database in first place.


alter database force logging;

Advertisements

2 Responses to “Nologging operations affect standby database”

  1. Great explanation with total clarity 🙂

Trackbacks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: