Table of contents
Preamble
There’s been a long debate with teammates about usage of NOLOGGING in our Oracle databases. Some application manager have discovered (and experienced) that some objects may be unrecoverable when using this clause on the opposite few DBAs wanted to audit all their databases to simply remove it. The performance gain using it well is obvious but to really understand what’s really doing this NOLOGGING option and the (bad) consequences of its usage I had to come back to official documentation. The default logging mode is the one of the tablespace (and the one of the tablespace is LOGGING).
In this blog post I will focus on what I rate the standard DML/DDL commands we execute the most i.e. INSERT, Create Table As Select (CTAS) and CREATE INDEX.
Few evidences:
- Standard INSERT and UPDATE are always logged whatever LOGGING/NOLOGGING option chosen.
- Direct-path INSERT (APPEND or APPEND_VALUES hints) supported by NOLOGGING option.
- Direct Loader (SQL*Loader) supported by NOLOGGING option.
Testing has been done on Oracle Linux Server release 6.3 using Oracle 11.2.0.3. My database is in archivelog mode as I’m doing datafile recovery…
As clearly written by Tom Kyte don’t be shy when creating your test table. In clear if your NOLOGGING test table is too small then Oracle will generate redo for it and bypass the NOLOGGING option… The post also contains a very interesting table comparing when redo are generated with database in archivelog/noarchivelog mode and insert method used.
To perform this test I have created an USERS tablespace with one datafile:
SQL> SELECT FILE_ID,FILE_NAME FROM dba_data_files WHERE TABLESPACE_NAME='USERS'; FILE_ID FILE_NAME ---------- -------------------------------------------------- 4 /u01/app/oracle/oradata/orcl/users01.dbf |
When the tablespace is still empty I have taken a backup using RMAN and:
RMAN> backup datafile 4; Starting backup at 15-FEB-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: starting piece 1 at 15-FEB-13 channel ORA_DISK_1: finished piece 1 at 15-FEB-13 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_02_15/o1_mf_nnndf_TAG20130215T122853_8kw6zotn_.bkp tag=TAG20130215T122853 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-FEB-13 Starting Control File Autobackup at 15-FEB-13 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_02_15/o1_mf_n_807452934_8kw6zqj6_.bkp comment=NONE Finished Control File Autobackup at 15-FEB-13 |
To simulate the crash, shutdown the database, delete at OS level the datafile located at /u01/app/oracle/oradata/orcl/users01.dbf, startup the database that will complains for:
ORA-01157: cannot identify/LOCK data FILE 4 - see DBWR trace FILE ORA-01110: data FILE 4: '/u01/app/oracle/oradata/orcl/users01.dbf' |
You can anyway start it with:
SQL> ALTER DATABASE datafile 4 OFFLINE; DATABASE altered. SQL> ALTER DATABASE OPEN; DATABASE altered. |
Then recover using Data Recovery Advisor (DRA) is as simple as:
list failure [detail] advise failure repair failure |
At the end, as you guess, the recover script generated by DRA is:
# restore and recover datafile restore datafile 4; recover datafile 4; |
You will then just need to issue under RMAN (or SQL by the way):
RMAN> sql 'alter database datafile 4 online'; |
Or else you get:
ORA-00376: FILE 4 cannot be read AT this TIME ORA-01110: data FILE 4: '/u01/app/oracle/oradata/orcl/users01.dbf' |
Nologging and CTAS
For the test case I create a table and an index both in nologging. I measure in parallel the redo size generated:
SQL> SET lines 200 pages 200 SQL> SELECT name, a.VALUE FROM v$sesstat a, v$sysstat b WHERE b.statistic# = a.statistic# AND b.name = 'redo size' AND a.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1); NAME VALUE ---------------------------------------------------------------- ---------- redo SIZE 664 SQL> CREATE TABLE test1 nologging TABLESPACE users AS SELECT * FROM dba_objects; TABLE created. SQL> CREATE INDEX test1_idx_OWNER ON test1(owner) nologging TABLESPACE users; INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'YJAQUIER', tabname => 'TEST1'); PL/SQL PROCEDURE successfully completed. SQL> SELECT name, a.VALUE FROM v$sesstat a, v$sysstat b WHERE b.statistic# = a.statistic# AND b.name = 'redo size' AND a.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1); NAME VALUE ---------------------------------------------------------------- ---------- redo SIZE 287668 |
Doing the same SQL block in LOGGING mode would generate instead 10,808,868 bytes of redo (37 times more !!).
Let’s simulate the crash as we have seen in preamble section and select on our test table. One important thing to note here is that table and index will still be valid after the recover even if by selecting them you will generate an error:
SQL> SELECT * FROM yjaquier.test1 WHERE owner='YJAQUIER'; SELECT * FROM yjaquier.test1 WHERE owner='YJAQUIER' * ERROR AT line 1: ORA-01578: ORACLE data block corrupted (FILE # 4, block # 1595) ORA-01110: data FILE 4: '/u01/app/oracle/oradata/orcl/users01.dbf' ORA-26040: Data block was loaded USING the NOLOGGING OPTION SQL> SELECT SEGMENT_TYPE, OWNER || '.' || SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 4 AND 1595 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1; SEGMENT_TYPE OWNER||'.'||SEGMENT_NAME ------------------ ---------------------------------------------------------------------------------------------------------------- INDEX YJAQUIER.TEST1_IDX_OWNER SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 4 168 8 2382810 NOLOGGING 4 177 15 2382837 NOLOGGING SQL> SELECT table_name,status,logging FROM dba_tables WHERE table_name='TEST1'; TABLE_NAME STATUS LOG ------------------------------ -------- --- TEST1 VALID NO SQL> SELECT INDEX_NAME,status,LOGGING FROM dba_indexes WHERE table_name='TEST1'; INDEX_NAME STATUS LOG ------------------------------ -------- --- TEST1_IDX_OWNER VALID NO |
As only the index seems corrupted let’s drop it:
SQL> DROP INDEX TEST1_IDX_OWNER; INDEX dropped. SQL> SELECT * FROM test1 WHERE owner='YJAQUIER'; SELECT * FROM test1 WHERE owner='YJAQUIER' * ERROR AT line 1: ORA-01578: ORACLE data block corrupted (FILE # 4, block # 171) ORA-01110: data FILE 4: '/u01/app/oracle/oradata/orcl/users01.dbf' ORA-26040: Data block was loaded USING the NOLOGGING OPTION SQL> SELECT SEGMENT_TYPE, OWNER || '.' || SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 4 AND 171 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1; SEGMENT_TYPE OWNER||'.'||SEGMENT_NAME ------------------ ---------------------------------------------------------------------------------------------------------------- TABLE YJAQUIER.TEST1 |
Worst situation here the index and the table are corrupted. If not a big deal for the index it is an issue for the table if you are not in a DataWareHouse environment.
Remark
If you create the table in LOGGING and index in NOLOGGING then the index is obviously still corrupted but you can still access to table figures that are not corrupted.
Nologging and insert command
For INSERT command the test table I’m gonna use is the following:
DROP TABLE test1; CREATE TABLE test1(id NUMBER, descr VARCHAR2(50)) NOLOGGING TABLESPACE users; |
To see that only direct-path INSERT are concerned by NOLOGGING mode, if you load this test table with below block I have already used in my Adaptive Cursor Sharing (ACS) post:
DECLARE i NUMBER; nbrows NUMBER; BEGIN i:=1; nbrows:=50000; LOOP EXIT WHEN i>nbrows; IF (i=1) THEN INSERT INTO test1 VALUES(1,RPAD('A',49,'A')); ELSE INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A')); END IF; i:=i+1; END LOOP; COMMIT; END; / |
If we apply same opus operandi:
SQL> CREATE INDEX test1_idx_id ON test1(id) NOLOGGING TABLESPACE users; INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'YJAQUIER', tabname => 'TEST1'); PL/SQL PROCEDURE successfully completed. SQL> SELECT name, a.VALUE FROM v$sesstat a, v$sysstat b WHERE b.statistic# = a.statistic# AND b.name = 'redo size' AND a.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1); NAME VALUE ---------------------------------------------------------------- ---------- redo SIZE 15568652 |
This is proven by successful restore of the table (index still corrupted obviously)… To force index usage either use an hint (INDEX) or use id = 1 in your select statement on test1.
To do direct-path insert I replace my insert statement by:
INSERT /*+ append */ INTO test1 VALUES(nbrows,RPAD('A',49,'A')); |
But it didn’t work and then found in official documentation:
The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to “APPEND_VALUES Hint”.
When replacing append hint by append_values hint and committing at the end of the block I got:
ORA-12838: cannot read/modify an object after modifying it in parallel |
Again it is in official documentation:
You cannot query or modify data inserted using direct-path INSERT immediately after the insert is complete. If you attempt to do so, an ORA-12838 error is generated. You must first issue a COMMIT statement before attempting to read or modify the newly-inserted data.
So had to use below non-optimal block:
DECLARE i NUMBER; nbrows NUMBER; BEGIN i:=1; nbrows:=50000; LOOP EXIT WHEN i>nbrows; IF (i=1) THEN INSERT /*+ append_values */ INTO test1 VALUES(1,RPAD('A',49,'A')); COMMIT; ELSE INSERT /*+ append_values */ INTO test1 VALUES(nbrows,RPAD('A',49,'A')); COMMIT; END IF; i:=i+1; END LOOP; END; / |
Execution time was much longer (19 minutes instead of 13 seconds) and number of extents moved from 19 (4 MB) to 121 (400 MB) in my local autoallocate tablespace.
If we perform same commands and measure redo size:
SQL> CREATE INDEX test1_idx_id ON test1(id) NOLOGGING TABLESPACE users; INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'YJAQUIER', tabname => 'TEST1'); PL/SQL PROCEDURE successfully completed. SQL> SELECT name, a.VALUE FROM v$sesstat a, v$sysstat b WHERE b.statistic# = a.statistic# AND b.name = 'redo size' AND a.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1); NAME VALUE ---------------------------------------------------------------- ---------- redo SIZE 83720616 |
Redo size generated is 5 times greater !! Which is exactly the opposite of what we want. I would say it is worst of both world as we have more redo and table and index are not recoverable…
There is apparently a bug with this append_values 11gR2 new hint (see references) with no clear solution so far… Also few references on Internet on this but in Expert PL/SQL Practices: for Oracle Developers and DBAs book they advise to use FORALL PL/SQL command when working with append_values hint so created the alternative PL/SQL block:
DECLARE i NUMBER; nbrows NUMBER; TYPE type1 IS TABLE OF VARCHAR2(50); array1 type1 := type1(); BEGIN nbrows:=50000; FOR i IN 1..nbrows LOOP array1.extend; array1(i):=RPAD('A',49,'A'); END LOOP; INSERT /*+ append_values */ INTO test1 VALUES(1,array1(1)); COMMIT; FORALL i IN 2..array1.COUNT INSERT /*+ append_values */ INTO test1 VALUES (nbrows,array1(i)); COMMIT; END; / |
Execution time moved to 4 seconds and redo generated is equal to 384 KB so clearly as expected (smaller execution time and less redo generated). Table and index are still corrupted after media recovery but this was planned…
Tablespaces logging attributes
There are two logging attribute for a tablespace, the default logging mode for newly created object (if you change it, it does not affect already created objects) and the force logging mode (can also be specified at database level, that has preference over the one set at tablespace level) for generation of redo information even if users specify NOLOGGING in supported NOLOGGING operations:
SQL> SELECT TABLESPACE_NAME,LOGGING,FORCE_LOGGING FROM dba_tablespaces WHERE TABLESPACE_NAME='USERS'; TABLESPACE_NAME LOGGING FOR ------------------------------ --------- --- USERS LOGGING NO |
This could be used if you want to be 100% sure that you media recovery will not let objects with corrupted blocks…
RMAN and nologging operations
RMAN has a very interesting reporting function for nologging operations called report unrecoverable. To test it I empty my users tablespace and backup it:
RMAN> backup datafile 4; Starting backup at 15-FEB-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: starting piece 1 at 15-FEB-13 channel ORA_DISK_1: finished piece 1 at 15-FEB-13 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2013_02_15/o1_mf_nnndf_TAG20130215T175913_8kwtc1r9_.bkp tag=TAG20130215T175913 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-FEB-13 Starting Control File Autobackup at 15-FEB-13 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2013_02_15/o1_mf_n_807472754_8kwtc3bo_.bkp comment=NONE Finished Control File Autobackup at 15-FEB-13 RMAN> report unrecoverable; Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- |
If I create a table with logging attribute:
SQL> CREATE TABLE test1 logging TABLESPACE users AS SELECT * FROM dba_objects; TABLE created. |
Still no issue:
RMAN> report unrecoverable; Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- |
If I now create a table with nologging attribute:
SQL> CREATE TABLE test2 nologging TABLESPACE users AS SELECT * FROM dba_objects; TABLE created. |
Then RMAN is able to track it:
RMAN> report unrecoverable; using target database control file instead of recovery catalog Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- 4 full or incremental /u01/app/oracle/oradata/orcl/users01.dbf |
At this stage you know that you must perform another backup of users01.dbf datafile to be 100% sure your media recovery will not let objects in an unrecoverable state…
References
- logging_clause
- Improving INSERT Performance with Direct-Path INSERT
- APPEND Hint (Direct-Path) Insert with Values Causes Excessive Space Usage on 11G [ID 842374.1]
