Redo nologging

Introduction
----------------
This paper covers the subject of reducing redo generation (LOGGING) using NOLOGGING. It is a follow up to the Redo reduction with maximum Recoverability paper. The first paper covers some useful subjects about redo generation which are not mentioned in this paper.

Important points about NOLOGGING
--------------------------------
Oracle gave the user the ability to limit LOGGING (redo generation) on tables and indexes by setting them in NOLOGGING mode but before going into how to limit the redo generation it is important to note the following points about LOGGING:
• NOLOGGING is designed to handle bulk inserts for data which can be re-produced.
• LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9iR2. If this command is run then logging is not disabled on any database object regardless of this object logging mode.
• Any change to the database dictionary will cause redo generation.
• Data which are not logged can not be recovered. The data should be backed up after the modification.
• NOLOGGING does not apply to UPDATE, DELETE or normal INSERT.
• Tables and indexes should be set back to LOGGING mode when the NOLOGGING is no longer needed.
• NOLOGGING is not needed for Direct Path Insert if the database is in NOArchivelog mode.

Disabling Logging
----------------
Logging can be disabled at the table level or the tablespace level. If it is done at the tablespace level then every newly created index or table in this tablespace will be in NOLOGGING mode. A table or an index can be create with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING;. It is important to note that just because an index or a table was created with NOLOGGING does not mean that redo generation has been stopped for this table or index. NOLOGGING is active in the following situations and while running one of the following commands but not after that. This is not a full list:
o direct load (SQL*Loader)
o direct load INSERT (using APPEND hint)
o CREATE TABLE ... AS SELECT
o CREATE INDEX
o ALTER TABLE MOVE
o ALTER TABLE ... MOVE PARTITION
o ALTER TABLE ... SPLIT PARTITION
o ALTER TABLE MERGE PARTITION
o ALTER TABLE MODIFY PARTITION ADD SUBPARTITON
o ALTER TABLE MODIFY PARTITION COALESCE SUBPARTITON
o ALTER INDEX ... SPLIT PARTITION
o ALTER INDEX ... REBUILD
o ALTER INDEX ... REBUILD PARTITION

Logging is stopped only while one of the commands above is running so if a user runs:
ALTER INDEX foo_idx NOLOGGING ;
The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.
Here is another example to make the point clear:
Create table tab_nolog NOLOGGING(….);
All the following statements will generate redo despite the fact the table is in NOLOGGING mode:
---------------
Insert into tab_nolog .., Update tab_nolog set …, delete from tab_nolog ..
The following WILL NOT generate redo (apart from dictionary changes and indexes):
Insert /*+ APPNED +/ ..
Alter table tab_nolog move ..
Alter table tab_nolog move partition …

Using NOLOGGING
---------------
To activate the NOLOGGING for one of the ALTER commands above add the NOLOGGIN clause after the end of the ALTER command. For example :
Alter table foo move partition part1 tablespace ts1 NOLOGGING;
The same applies for CREATE INDEX but for CREATE TABLE the NOLOGGING should come after the table name. Example:
Create table tab_with_nologging NOLOGGING as select * from big_tab;
It is a common mistake to add the NOLOGGING at the end because oracle will consider it an alias and the table creation will generate a lot of logging.

To user Direct Path Load in SQL * Loader you must run the $ORACLE_HOME/rdbms/admin/catldr.sql script before your first sqlldr is run in direct path mode. To run sqlldr in direct path mode use direct=true. For further information refer to Conventional and Direct Path Loads
Direct Path Insert is discussed bellow.

DIRECT PATH INSERT
-----------------
To use Direct Path Insert use the /*+ APPEND */ hint as follow:
INSERT /*+ APPEND */ into … SELECT …
When direct path insert is used oracle does the following:
 Format the data to be inserted as oracle blocks.
 Insert the blocks above the High Water Mark (HWM)
 When commit takes place the HWM is moved to the new place. The process is done bypassing the buffer cache.

It is clear that direct load is useful for bulk inserts. Using it to insert few hundred records at a time can have bad effect on space and performance.
It is very important to understand how Direct Path Inserts affects redo generation. As mentioned above it does not affect indexes but it is affected by the following factors:
• The database Archivelog mode.
• Using the /*+ APPEND */ hint.
• The LOGGING mode of the table.
• The FORCE LOGGING mode of the database (from 9i R2).

If the database is in FORCE LOGGING mode then Oracle will treat the table as if it was in LOGGING mode regardless of its mode. To find out if the database is in FORCED LOGGING or not run:
select FORCE_LOGGING from v$database ;
If the /*+ APPEND */ Hint is not used then the insertion will generate the normal amount of redo regardless of the other factors.
This table will show the relation between ARCHIVELOG mode and having the table in LOGGING mode when the /*+ APPEND */ hint is used. This does not include index redo nor does it include data dictionary changes redo

LOGGING MODE ARCHIVELOG NOARCHIVELOG
LOGGING Redo No Redo
NOLOGGING No Redo No Redo


Reducing Logging For Bulk DML
-----------------------------
-----------------------------
Bulk Inserts
------------
To load bulk data using Direct Path.
o set table in nologging mode. Alter table table_name nologging;
o alter index index_name unusable ;
o alter session set skip_unusable_indexes=true ;(*)
o Insert /*+ APPEND */ into table_name select …
o Alter index index_name rebuild nologging;
o Alter table table_name logging ;
o Alter index index_name logging ;
o Backup the data.

(*)skip_unusable_indexes is an instance initialization parameter in 10g and it default to true. Before 10g, skip_unusable_indexes needs to be set in a session or the user will get an error. It is a good practice to set it in a session, regardless of the database version, when the above is done.


There is no direct way (at the time of writing this document) of reducing redo generation for bulk update and delete. The user needs to reduce the workload on the database.

Bulk Delete:
------------
1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); THE NOLOGGING COMES AFTER THE TABLE_NAME NOT AT THE END OF THE STATEMENT.
2. Insert /*+ Append */ into new_table select the records you want to keep from current_table.
3. Create the indexes on the new table with NOLOGGING (*)
4. Create constraints, grants etc.
5. Drop current_table.
6. Rename new_table to current.
7. Alter new_table and indexes logging.
8. Backup the data.

(*) If the data left is so small or there are a lot of dependencies on the table (views, procedures, functions) the following steps can be used instead of 3-6 above
3. Disable constrains on current_table;
4. Truncate current_table;
5. make indexes unusable;
6. alter current table NOLOGGING ;
7. Insert /*+ APPEND */ into current_table select * from new_table ;
8. commit;
9. rebuild indexes with NOLOGGING;
10. enable constraints
11. Put current table and indexes in LOGGING mode
12. backup the data
13. drop table new_table;

Bulk Update:
-----------
Follow the steps for bulk Delete but integrate the update within the select statement. Lets say that you want to update the value column in the goods table by increasing it by 10% the statement will be like:
1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); THE NOLOGGING COMES AFTER THE TABLE_NAME NOT AT THE END OF THE STATEMENT.
2. Insert /*+ Append */ into new_table select (update statement eg: col1, col2* 1.1,…)
3. Create the indexes on the new table with NOLOGGING (*)
4. Create constraints, grants etc.
5. Drop current_table.
6. Rename new_table to current.
7. Alter new_table and indexes logging.
8. Backup the data.

Backup And Nologging
-------------------
It is important that the data loaded using NOLOGGING can be loaded again if you need to. If the database crashed before backing up the new data then this data can not be recovered.
Here are the two scenarios of backing up:
Export (exp or expdp)
This method will allow you to recover the loaded data up to the point the export was taken but not later.

Hot Backup
----------
In order to recover any additional data or modification to the table you bulk inserted into using NOLOGGIN the least you need to do is a hot backup of that tablespace. Remember you still generate redo for DML on the table when it is in NOLOGGING mode but you are strongly advised to put it in LOGGING mode in case you run one of the operations mentioned in the Disabling Logging section.