Physical Backups  «Prev  Next»

Lesson 11 Implications of the logging and nologging modes
Objective Identify the backup implications of logging and nologging modes.

Implications of logging and nologging Modes

Logging and nologging are two modes you can set for database objects, such as tables, partitions, and indexes. If the database objects are set to logging mode, which is the default, all the changes made to them are fully recorded in redo logs. If you turn off logging mode for these objects and modify them, only minimal redo is generated. Tables, indexes, or partitions may be set to nologging mode for loading a large block of data with the direct-load INSERT operation. When the nologging mode is set for a direct-load, the insert statements are not logged in the redo logs, and thus, the loading process is faster.

Direct-load or Direct Path Load

Direct-load, also known as direct path load, is a process of formatting Oracle data blocks and writing the data blocks directly to the database files. You can, for example, choose to do direct-load operations with SQL*Loader. The direct-load does not compete with other users for database resources so it can usually load data faster.
Oracle SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads.
These include:
  1. Use Direct Path Loads: The conventional path loader essentially loads the data by using standard insert statements. The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format. The fact that SQL is not being issued makes the entire process much less taxing on the database. There are certain cases, however, in which direct path loads cannot be used (clustered tables). Direct path loads bypass the Oracle buffer cache and write data directly to the database files, which can significantly improve performance. Direct path loads are typically used for loading large amounts of data, such as data from a data warehouse or a flat file. They can also be used to load data from an external table into a regular table. To prepare the database for direct path loads, the script
    $ORACLE_HOME/rdbms/admin/catldr.sql.sql 
    

    must be executed.
  2. Disable Indexes and Constraints: For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader
  3. Use a Larger Bind Array: For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance. The size of the bind array is specified using the bindsize parameter. The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row. Also see the columnarrayrows and streamsize parameters.
  4. Use ROWS=n : For conventional data loads only, rows specifies the number of rows per commit and is related to bindsize. Issuing fewer commits will enhance performance, and the larger rows parameter affects performance (see benchmark below).
  5. Use Parallel Loads : Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently.
    $ sqlldr control=first.ctl  parallel=true direct=true 
    $ sqlldr control=second.ctl parallel=true direct=true 
    
  6. Use Fixed Width Data. Fixed width data format saves Oracle some processing when parsing the data. The savings can be tremendous, depending on the type of data and number of rows.
  7. Disable Archiving During Load. While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.
  8. Use unrecoverable. The unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs. This option is available for direct path loads only.
    The unrecoverable option (unrecoverable load data) still exists in Oracle. It was introduced in Oracle 7 and is still available in Oracle 19c. The unrecoverable option can be used to improve the performance of data loads by disabling the writing of redo log entries. However, it is important to note that using the unrecoverable option can make the database more vulnerable to data loss in the event of a failure. To use the unrecoverable option, you can specify the `UNRECOVERABLE` clause when using the `SQL*Loader` utility to load data. For example, the following command would load data from the `data.csv` file into the `emp` table using the unrecoverable option:
    sqlldr userid=scott/tiger control=loader.ctl data=data.csv UNRECOVERABLE
    

    It is important to note that the unrecoverable option should only be used in limited circumstances, such as when loading large amounts of data and the database is regularly backed up. Here are some of the risks associated with using the unrecoverable option:
    • If the database crashes before the data load is complete, the data that was loaded will not be recoverable.
    • If the database is corrupted before the data load is complete, the data that was loaded may also be corrupted.
    • If the database is backed up before the data load is complete, the backup will not contain the loaded data.

    If you need to use the unrecoverable option, be sure to take steps to mitigate the risks, such as backing up the database regularly and testing the backups to ensure that they are recoverable.

Direct-load INSERT with nologging

When you are performing the direct-load INSERT in the nologging mode, data is inserted without generating redo information. Even though some minimal logging is still done, recovery is almost impossible without further back up. In contrast, data dictionary changes are always fully logged.

Nologging Mode

You can specify nologging mode for direct-load INSERT by setting the NOLOGGING attributes for the affected table, partition, tablespace, or index by using the ALTER command. The code below contains an example of nologging mode for direct-load INSERT.

ALTER TABLE emp NOLOGGING;
ALTER TABLESPACE user_data NOLOGGING;
ALTER TABLE emp MODIFY PARTITION emp_history NOLOGGING;

The nologging option is a great way to speed-up inserts and index creation. It bypasses the writing of the redo log, significantly improving performance. However, this approach is quite dangerous if you need to roll-forward through this time period during a database recovery. In nologging mode you are running without a safety net when you run nologging operations and you must:
  1. Backup before and after - You must take a backup, both before and after all nologging operations
  2. Only nologging operations during the nologging window - Between the backups (the nologging processing window), ONLY nologging operations should be run in the middle of this "backup sandwich".

The nologging clause IS NOT A SQL HINT, and the NOLOGGING clause is quite convoluted and dependent on several factors.
  1. Database noarchivelog mode: If your database is in "noarchivelog" mode and you are no using the APPEND hint for inserts, you WILL STILL generate redo logs.
  2. Database archivelog mode: If you are in archivelog mode, the table must be altered to nologging mode AND the SQL must be using the APPEND hint. Else, redo WILL be generated.
You can use nologging for batch inserts into tables and for creating indexes:

  1. You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed.
alter table customer nologging;
insert /*+ append */ into customer values ('hello',';there');
  1. You can create indexes with nologging - The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery. Using nologging with create index can speed index creation by up to 30%.

create index newidx ... nologging;
  1. Other nologging options - Only the following operations can make use of the NOLOGGING option:

   alter table...move partition
   alter table...split partition
   alter index...split partition
   alter index...rebuild
   alter index...rebuild partition
   create table...as select
   create index
   direct load with SQL*Loader
   direct load INSERT (using APPEND)

Be very careful using UNRECOVERABLE clause and the NOLOGGING clause when performing CREATE INDEX or CREATE TABLE AS SELECT (CTAS) commands. The CTAS with NOLOGGING or UNRECOVERABLE will send the actual create statement to the redo logs (this information is needed in the data dictionary), but all rows loaded into the table during the operation are NOT sent to the redo logs. With NOLOGGING , although you can set the NOLOGGING attribute for a table, partition, index, or tablespace, NOLOGGING mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute. It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load. The NOLOGGING clause is a wonderful tool since it often halves run times, but you need to remember the danger. For example, a common practice is to reorganize very large tables is to use CTAS:

Create table  
   new_customer 
tablespace  
   new_ts  
NOLOGGING  
as 
   select * from customer;
Drop table customer;
Rename new_customer to customer;

However, you must be aware that a roll-forward through this operation is not possible, since there are no images in the archived redo logs for this operation. Hence, you MUST take a full backup after performing any NOLOGGING operation.

The nologging mode has a performance advantage because it generates much less redo information. On the other hand, because the redo logs do not contain the information about the inserted values during the period when the table was in nologging mode, the database is not recoverable from the last backup. You are responsible for backing up the data after a nologging INSERT operation in order to be able to perform successful media recovery. Thus, upon the completion of the direct-load operation on a table, you should reset the table to logging mode and immediately backup the datafiles containing the table.

Direct-Load Insert

A direct-load insert can be used to append data to a table from another table. This method of insertion can bypass the buffer cache and directly write the data blocks to the data file. The direct-load insert can be performed in parallel. If the table is in NOLOGGING mode, it will not generate redo entries. To enable direct-load inserts, you must specify the APPEND hint if the table is in serial mode. If the table is in parallel mode, the APPEND hint is optional. A hint is used to specify your own data retrieval path rather than using the one calculated by the Oracle optimizer. A hint is specified between /*+ and */ .
If there are triggers or referential integrity constraintson the table, Oracle ignores the APPEND hint and does a conventional insert. There is no special keyword to enable or disable direct-load inserts in parallel mode. To improve load speed, indexes can be dropped and rebuilt after the load completes. During direct-load insert, exclusive locks are obtained on the table; no DML activity can be performed. The following examples illustrate how to use direct-load insert. When the table is in serial mode, that is, the degree of parallelism is 1, to enable the direct-load insert you must specify the APPEND hint.

ALTER TABLE MON_TRANS NOPARALLEL;
INSERT /*+ APPEND */ INTO MON_TRANS
SELECT * FROM DAILY_TRANS;

To disable logging the direct-load inserts to the redo log file, the table should be defined in NOLOGGING mode.
ALTER TABLE MON_TRANS NOLOGGING;

When the database is in ARCHIVELOG mode, if a media recovery is done, Oracle cannot load the records to the table from the archive log files. Therefore, you must make a backup of the table or tablespace when the direct-load insert is complete. If the database is in NOARCHIVELOG mode, having the table in NOLOGGING mode does not have any implications and can tremendously improve the performance of direct-load inserts. In serial direct-load insert, Oracle inserts data blocks above the high-water mark of the table segment. The data is visible to other users only when a commit is issued, which then moves the high-water mark to the new position.

Features of logging and nologging options

Logging Nologging
All changes recorded to redo logs Minimal redo information recorded in redo logs
Fully recoverable from last backup Not recoverable from last backup
No additional backup required Requires additional backup

The next lesson discusses backing up "read-only" tablespaces.