Impact of NOLOGGING on an Oracle Dataguard Standby
In below we will run few of the test cases NOLOGGING option at dataguard environment.
Oracle recommended for Dataguard Standby databases is to run "alter database force logging;" to make sure all database transactions on the primary database are logged which will be applied to the standby.
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
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
NOLOGGING 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)
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)
## At 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 = 'NOLOG';
SQL> truncate table nolog;
Table truncated.
SQL> alter database force logging;
Database altered.
SQL> alter tablespace users logging;
Tablespace altered.
SQL> alter table nolog logging;
Table altered.
SQL> set autotrace ON statistics
SQL> insert /*+ APPEND */
into nolog select 'this is a nologging NOLOG' 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 nolog nologging;
Table altered.
SQL> truncate table nolog;
Table truncated.
SQL> set autotrace ON statistics
SQL> insert /*+ APPEND */
into nolog select 'this is a nologging nolog' 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 Physical Standby ##
SQL> select * from nolog;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 26, block # 42)
ORA-01110: data file 26:
'/u01/oradata/orcl/datafile/o1_mf_users_3vty8wdy_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> analyze table nolog validate structure cascade;
analyze table nolog validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 26, block # 31)
ORA-01110: data file 26:
'/u01/oradata/orcl/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.
# 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 = 'NOLOG';
SQL> truncate table nolog;
Table truncated.
SQL> alter database force logging;
Database altered.
SQL> alter tablespace users logging;
Tablespace altered.
SQL> alter table nolog logging;
Table altered.
SQL> set autotrace ON statistics
SQL> insert /*+ APPEND */
into nolog select 'this is a nologging NOLOG' 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 nolog nologging;
Table altered.
SQL> truncate table nolog;
Table truncated.
SQL> set autotrace ON statistics
SQL> insert /*+ APPEND */
into nolog select 'this is a nologging nolog' 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 Physical Standby ##
SQL> select * from nolog;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 26, block # 42)
ORA-01110: data file 26:
'/u01/oradata/orcl/datafile/o1_mf_users_3vty8wdy_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> analyze table nolog validate structure cascade;
analyze table nolog validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 26, block # 31)
ORA-01110: data file 26:
'/u01/oradata/orcl/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.
