Friday, August 15, 2008

Test the impact of NOLOGGING on an Oracle Dataguard Standby

Recommended practice for Dataguard Standby databases is to run "alter database force logging;" to ensure all transactions on the primary database are logged and applied to the standby.

Following is a basic test with diagnostics to confirm redo volume reduction, unrecoverable transaction, and impact to Dataguard standby databases.

Basic results indicate :-
1. significant reduction in redo (there will still be UNDO REDO generated)
2. logical corruption in the tables in the standby database which will stop select statements until the table data is reloaded (e.g drop and recreate)

NOLOGGING is valid for the following operations :-

direct load (SQL*Loader)
direct load INSERT (using APPEND hint)
CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

# On the primary database
# check logging

select force_logging from v$database;
select tablespace_name from dba_tablespaces where logging = 'NOLOGGING';
select logging from user_tables where table_name = 'TEST';

SQL> truncate table test;
Table truncated.
SQL> alter database force logging;
Database altered.
SQL> alter tablespace users logging;
Tablespace altered.
SQL> alter table test logging;
Table altered.
SQL> set autotrace ON statistics
SQL> insert /*+ APPEND */
into test select 'this is a nologging test' from dual nologging; 2

1 row created.


Statistics
----------------------------------------------------------
165 recursive calls
7 db block gets
21 consistent gets
2 physical reads
8624 redo size
820 bytes sent via SQL*Net to client
780 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> set autotrace off
SQL> select file#, unrecoverable_change#, to_char(unrecoverable_time,'hh:mi') from v$datafile where UNRECOVERABLE_CHANGE# > 0;

FILE# UNRECOVERABLE_CHANGE# TO_CH
---------- --------------------- -----
26 2.4329E+10 02:48

SQL> alter database no force logging;

Database altered.

SQL> alter tablespace users nologging;

Tablespace altered.

SQL> alter table test nologging;

Table altered.

SQL> truncate table test;

Table truncated.

SQL> set autotrace ON statistics
SQL> insert /*+ APPEND */
into test select 'this is a nologging test' from dual nologging; 2

1 row created.


Statistics
----------------------------------------------------------
3 recursive calls
7 db block gets
2 consistent gets
2 physical reads
448 redo size
820 bytes sent via SQL*Net to client
780 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select file#, unrecoverable_change#, to_char(unrecoverable_time,'hh:mi') from v$datafile where UNRECOVERABLE_CHANGE# > 0;

FILE# UNRECOVERABLE_CHANGE# TO_CH
---------- --------------------- -----
26 2.4329E+10 02:51



# On the Dataguard standby

SQL> select * from test;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 26, block # 42)
ORA-01110: data file 26:
'/sltbko1/oradata/SLTBKO1/datafile/o1_mf_users_3vty8wdy_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

SQL> analyze table test validate structure cascade;
analyze table test validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 26, block # 31)
ORA-01110: data file 26:
'/sltbko1/oradata/SLTBKO1/datafile/o1_mf_users_3vty8wdy_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

!oerr ora 26040
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//* redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.