Implication on Standby if table created with Nologging

Description

The implications to Dbvisit Standby if there is a table that has been created with nologging option

Example

Opening the standby database will not have any issues. The problem will occur when attempting to access data in a table or object that was loaded/created as part of nologging operations.

For example, when I have performed nologging operations on a table big" (example creating indexes with nologging) and I open the standby read only or activating it, I get the following:

SQL> connect / as sysdba 
  Connected. 
SQL> alter database open read only;  
  Database altered. 
SQL> select count(1) from big;  
  COUNT(1) 
  ---------- 
      216639  
SQL> select \* from big where object\_id=10; 
select \* from big where object\_id=10 
                \* 
ERROR at line 1: 
ORA-01578: ORACLE data block corrupted (file # 1, block # 86378) 
ORA-01110: data file 1: '/u01/app/oracle/oradata/devdb/system01.dbf' 
**ORA-26040: Data block was loaded using the NOLOGGING option**  

**SQL> alter database activate standby database;**  
  **Database altered.**  

SQL> shutdown immediate; 
ORA-01109: database not open  

Database dismounted.
ORACLE instance shut down. 
SQL> startup 
ORACLE instance started.  

Total System Global Area  626327552 bytes 
Fixed Size            2215944 bytes 
Variable Size          465571832 bytes 
Database Buffers      155189248 bytes 
Redo Buffers            3350528 bytes 
Database mounted. 
Database opened. 
SQL> select count(1) from big;  

  COUNT(1) 
---------- 
    216639  

SQL> select \* from big where object\_id=10; 
select \* from big where object\_id=10 
              \* 
ERROR at line 1: 
ORA-01578: ORACLE data block corrupted (file # 1, block # 86378) 
ORA-01110: data file 1: '/u01/app/oracle/oradata/devdb/system01.dbf' 
**ORA-26040: Data block was loaded using the NOLOGGING option**

So in summary, the standby will still be able to open read only or activate it, but the data being accessed that was loaded with nologging, will be corrupt - logical corruption.

Note: If there are any NOLOGGING operations, the Standby database can be synchronised completely with the primary database again using the synchronize command.

Prevention is better than the cure. Therefore, always set your database to force logging:

ALTER DATABASE force logging;

Dbvisit even warns you if you haven't done so already.

Arjen Visser May 22, 2015 13:54

We're here to help!

Comments