I still find confusion out there about the LOGGING and NOLOGGING clauses when performing DML and DDL operations, the reality is that the NOLOGGING clause will work only on particular conditions, but all regular inserts,updates and deletes will still log the operations.
The benefits of the NOLOGGING option are:
- Will save disk space when the archive option is enabled.
- Will largely reduce I/O on the redologs.
- Will reduce the time it takes to complete the operation.
Please note that NOLOGGING operations will only reduce -not eliminate- the logging.
Lets see an example -
02 |
SQL> create table logging_example nologging as select * from dba_objects where 1=2; |
08 |
SQL> set autotrace on statistics |
12 |
SQL> alter system flush buffer_cache; |
18 |
SQL> insert into logging_example select * from dba_objects; |
31 |
670 bytes sent via SQL*Net to client |
32 |
586 bytes received via SQL*Net from client |
33 |
3 SQL*Net roundtrips to / from client |
39 |
SQL> alter system flush buffer_cache; |
45 |
SQL> insert /*+ append */ into logging_example select * from dba_objects; |
58 |
654 bytes sent via SQL*Net to client |
59 |
604 bytes received via SQL*Net from client |
60 |
3 SQL*Net roundtrips to / from client |
We can see that there is a big difference on the redo size generated by each insert, there are many post and articles on the internet that show the speed benefits when using the NOLOGGING option, but here I mainly want to clarify that a regular insert (no APPEND hint) will still generate redologs even if the table have been created with the NOLOGGING option.
What happens to the data after a restore when a nologging operation was performed on it?
I will present some scenarios to show the consequences when we need to perform a restore after a nologging transaction, this way we will know what to expect and we can better prepare ourselves in case of a disaster.
I took a full database backup, now I will create several tables with different options to see what happens after a restore, you might see some surprises here!
Scenarios:
- Table “create as select” with the nologging option (table_ctas_nologging).
- Regular table “create as select” (table_ctas_logging)
- A nologging table created empty, and a regular (logging) insert (table_ctas_nologging_insert)
- Table created with nologging, then two inserts, one with and one without logging (table_insert_mixed)
- Regular logging table, with a nologging index (table_ctas_index_nologging)
01 |
SQL> create table table_ctas_nologging nologging as select * from dba_objects; |
05 |
SQL> create table table_ctas_logging as select * from dba_objects; |
09 |
SQL> create table table_ctas_nologging_insert nologging as select * from dba_objects where 1=2; |
13 |
SQL> insert into table_ctas_nologging_insert select * from dba_objects; |
21 |
SQL> create table table_insert_mixed nologging as select * from dba_objects where 1=2; |
25 |
SQL> insert into table_insert_mixed select * from dba_objects; |
29 |
SQL> insert into table_insert_mixed select /*+ append */ * from dba_objects; |
37 |
SQL> select count (*) from table_insert_mixed; |
43 |
SQL> create table table_ctas_index_nologging as select * from dba_objects; |
47 |
SQL> create index IDXNOLOG on table_ctas_index_nologging (object_id) nologging; |
Now I will shutdown the database and restore the tablespace from the backup.
Next is an extract from RMAN
03 |
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 25 17:32:20 2010 |
05 |
Copyright (c) 1982, 2007, Oracle. All rights reserved. |
07 |
connected to target database : ORCL (DBID=1247573001) |
09 |
RMAN> shutdown immediate |
11 |
using target database control file instead of recovery catalog |
14 |
Oracle instance shut down |
18 |
Oracle instance started |
21 |
Total System Global Area 285212672 bytes |
23 |
Fixed Size 1267068 bytes |
24 |
Variable Size 155191940 bytes |
25 |
Database Buffers 125829120 bytes |
26 |
Redo Buffers 2924544 bytes |
28 |
RMAN> restore tablespace users; |
30 |
Starting restore at 25-AUG-10 |
31 |
using target database control file instead of recovery catalog |
32 |
allocated channel: ORA_DISK_1 |
33 |
channel ORA_DISK_1: sid=152 devtype=DISK |
35 |
channel ORA_DISK_1: starting datafile backupset restore |
36 |
channel ORA_DISK_1: specifying datafile(s) to restore from backup set |
37 |
restoring datafile 00004 to +DATA/orcl/datafile/users.259.719792191 |
38 |
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2010_08_25/nnndf0_tag20100825t171657_0.272.727982219 |
39 |
channel ORA_DISK_1: restored backup piece 1 |
40 |
piece handle=+DATA/orcl/backupset/2010_08_25/nnndf0_tag20100825t171657_0.272.727982219 tag=TAG20100825T171657 |
41 |
channel ORA_DISK_1: restore complete, elapsed time : 00:00:05 |
42 |
Finished restore at 25-AUG-10 |
44 |
RMAN> recover tablespace users; |
46 |
Starting recover at 25-AUG-10 |
47 |
using channel ORA_DISK_1 |
49 |
starting media recovery |
50 |
media recovery complete, elapsed time : 00:00:05 |
52 |
Finished recover at 25-AUG-10 |
54 |
RMAN> alter database open ; |
Now lets see the status of the tables:
1 |
SQL> select count (*) from table_ctas_nologging ; |
2 |
select count (*) from table_ctas_nologging |
5 |
ORA-01578: ORACLE data block corrupted (file # 4, block # 404) |
6 |
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191' |
7 |
ORA-26040: Data block was loaded using the NOLOGGING option |
That doesn’t look good, lets see the next table
1 |
SQL> select count (*) from table_ctas_logging ; |
Good, no problem here, the next scenario is more interesting, the table was created with the NOLOGGING option, but the inserts were done without the APPEND hint
1 |
SQL> select count (*) from table_ctas_nologging_insert; |
Good, no problem here, now let’s see our table with half data inserted with logging and half with nologging
1 |
SQL> select count (*) from table_insert_mixed; |
2 |
select count (*) from table_insert_mixed |
5 |
ORA-01578: ORACLE data block corrupted (file # 4, block # 4363) |
6 |
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191' |
7 |
ORA-26040: Data block was loaded using the NOLOGGING option |
Wow, the whole table is unredable!
Now lets see the table with the NOLOGGING index .
1 |
<pre>SQL> select count (*) from table_ctas_index_nologging; |
Ok, thats nice, the table is accessible, but what happend if we try to use the index?
1 |
SQL> select object_id from table_ctas_index_nologging where object_id=1; |
2 |
select object_id from table_ctas_index_nologging where object_id=1 |
5 |
ORA-01578: ORACLE data block corrupted (file # 4, block # 2821) |
6 |
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191' |
7 |
ORA-26040: Data block was loaded using the NOLOGGING option |
I tried to rebuil the index but I was still getting the same error message, at the end I was forced to drop it and recreate it.
Conclusions:
- Use the NOLOGGING option only on temporary/working/staging tables.
- Always perform a backup after a NOLOGGING operation.
- Unless explicitly indicated, DDLs like CTAS and DMLs like inserts will log all operations.
FROM ORACLE DOCUMENTATION:
NOLOGGING
is supported in only a subset of the locations that support LOGGING
. Only the following operations support the NOLOGGING
mode:
DML:
- Direct-path
INSERT
(serial or parallel) resulting either from an INSERT
or a MERGE
statement. NOLOGGING
is not applicable to any UPDATE
operations resulting from the MERGE
statement.
- Direct Loader (SQL*Loader)
DDL:
CREATE
TABLE
… AS
SELECT
CREATE
TABLE
… LOB_storage_clause
… LOB_parameters
… NOCACHE
| CACHE
READS
ALTER
TABLE
… LOB_storage_clause
… LOB_parameters
… NOCACHE
| CACHE
READS
(to specify logging of newly created LOB columns)
ALTER
TABLE
… modify_LOB_storage_clause
… modify_LOB_parameters
… NOCACHE
| CACHE
READS
(to change logging of existing LOB columns)
ALTER
TABLE
… MOVE
ALTER
TABLE
… (all partition operations that involve data movement)
ALTER
TABLE
… ADD
PARTITION
(hash partition only)
ALTER
TABLE
… MERGE
PARTITIONS
ALTER
TABLE
… SPLIT
PARTITION
ALTER
TABLE
… MOVE
PARTITION
ALTER
TABLE
… MODIFY
PARTITION
… ADD SUBPARTITION
ALTER
TABLE
… MODIFY
PARTITION
… COALESCE
SUBPARTITION
CREATE
INDEX
ALTER
INDEX
… REBUILD
ALTER
INDEX
… REBUILD
[SUB]PARTITION
ALTER
INDEX
… SPLIT
PARTITION
For objects other than LOBs, if you omit this clause, then the logging attribute of the object defaults to the logging attribute of the tablespace in which it resides.
For LOBs, if you omit this clause, then:
- If you specify
CACHE
, then LOGGING
is used (because you cannot have CACHE
NOLOGGING
).
- If you specify
NOCACHE
or CACHE
READS
, then the logging attribute defaults to the logging attribute of the tablespace in which it resides.
NOLOGGING
does not apply to LOBs that are stored internally (in the table with row data). If you specify NOLOGGING
for LOBs with values less than 4000 bytes and you have not disabled STORAGE
IN
ROW
, then Oracle ignores the NOLOGGING
specification and treats the LOB data the same as other table data.