In Oracle Database NOLOGGING option used together with DIRECT mode speeds up transactions executed in a database because it limits information that Oracle writes about such transactions into redo logs and finally impacts size of generated archivelogs.

That’s because Oracle just writes basic information about executed transaction without modified data to redo logs. Such transactions are called UNRECOVERABLE because in case of database recovery archivelogs don’t have real data only informations about executed transactions.

It can impact later restores and this article shows it.

New tablespace TEST_TBLSP is created

CREATE TABLESPACE test_tblsp
DATAFILE 'D:\APP\ORACLE\ORADATA\ORA12C\test_tblsp01.DBF'
SIZE 100m;

and table TEST_TBL with LOGGING option in the tablespace

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 NUMBER
) 
LOGGING
TABLESPACE test_tblsp;

let’s backup the tablespace

RMAN> BACKUP TABLESAPCE test_tblsp;

Starting backup at 01-MAR-15
...
Finished backup at 01-MAR-15

Transaction executed in DIRECT mode for table with LOGGING generates a lot of redo logs

SET AUTOTRACE ON

INSERT /*+ APPEND */ INTO test_tbl
SELECT level, level FROM dual
CONNECT BY LEVEL < 10001;

...
 153988  redo size
 148248  redo size for direct writes
...

COMMIT; 

SELECT count(*) FROM test_tbl;   

COUNT(*) 
----------      
      1000

Following command in RMAN doesn’t report any unrecoverable transactions in the tablespace yet

RMAN> REPORT UNRECOVAREBALE TABLESPACE test_tblsp;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

Now it’s time to set NOLOGGING for table

ALTER TABLE test_tbl NOLOGGING;

so similar transaction in DIRECT mode but with NOLOGGING generates much smaller redo than previous transaction

INSERT /*+ APPEND */ INTO test_tbl
SELECT LEVEL, LEVEL FROM dual
CONNECT BY LEVEL < 10001;
              
COMMIT;

...
 15876  redo size 
     120  redo size for direct writes
...

select count(*) from test_tbl;   

COUNT(*) 
----------      
      2000

However this time unrecoverable transaction is reported by RMAN

RMAN> REPORT UNRECOVERABLE TABLESPACE test_tblsp;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
8    full or incremental D:\APP\ORACLE\ORADATA\ORA12C\TEST_TBLSP01.DBF

or via sqlplus

sqlplus / as sysdba

sqlplus> SELECT file#,unrecoverable_change# FROM v$datafile;

FILE#  UNRECOVARABLE_CHANGE#
------ ----------------------
8      11360679072405

so if you would try to restore the tablespace restore will finish without error. DBA is happy man 🙂 and reports full success.

RMAN> ALTER TABLESPACE test_tblsp OFFLINE;
RMAN> RESTORE TABLESPACE test_tblsp;
Starting restore at 28-FEB-15
...
Finished restore at 28-FEB-15
RMAN> RECOVER TABLESPACE test_tblsp;
Starting recover at 28-FEB-15
...
Finished recover at 28-FEB-15
RMAN> ALTER TABLESPACE test_tblsp ONLINE;

but selecting data from table TEST_TBL will rise error

SELECT COUNT(*) 
FROM test_tbl;
ORA-01578: ORACLE data block corrupted (file # 8, block # 294)
ORA-01110: data file 8:
'D:\APP\ORACLE\ORADATA\ORA12C\TEST_TBLSP01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
01578. 00000 -  "ORACLE data block corrupted (file # %s, block # %s)"

To avoid this problem you can force to LOGGING data to archivelogs on one of the level

• tablespace – it overrides any NOLOGGING settings for any segment created in the tablespace

ALTER TABLESPACE test_tblsp FORCE LOGGING;

SELECT
  tablespace_name,
  force_logging
FROM dba_tablespaces
WHERE tablespace_name='TEST_TBLSP';

TABLESPACE_NAME                FORCE_LOGGING
------------------------------ -------------
TEST_TBLSP                      YES

•  database level – it overrides any NOLOGGING settings for any segment created in any the tablespace

ALTER DATABASE FORCE LOGGING;

SELECT force_logging
FROM v$database;

FORCE_LOGGING                          
---------------------------------------
YES

once force logging is turned on it’s recommended to make backups for unrecoverable datafiles

RMAN> backup datafile 8;
Starting backup at 28-FEB-15
...
Finished backup at 28-FEB-15

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

use following command to turn it off on database level

ALTER DATABASE NO FORCE LOGGING;

use following command to turn it off on tablespace level

ALTER TABLESPACE test_tblsp NO FORCE LOGGING;

NOTE – turning off FORCE LOGGING on a tablespace level has no impact when DATABASE level is still turned on.

Have a fun 🙂

Tomasz