Redo Logging Modes Overview

Redo logging controls how much information Oracle writes to on line redo log files during data changes. Three principal modes exist:

• LOGGING: Default mode where all DML and object creation actions generate redo entries. Suitable for most database objects; NOLOGGING is generally discouraged except for specific cases like bulk endex builds or large data loads.
• FORCE LOGGING: Ensures all operations—regardless of individual object settings—produce redo. Can be enabled at database or tablespace level. This mode overrides NOLOGGING within the scope and is required for Data Guard environments to guarantee recoverability.
• NOLOGGING: Minimizes redo generation for supported operations. It does not disable logging entirely but reduces volume substantially.

Enforcement hierarchy: Database-level FORCE LOGGING supersedes tablespace-level FORCE LOGGING, which in turn overrides object-level NOLOGGING. When neither database nor tablespace uses force mode, precedence from low to high is: object → tablespace → database.

Interaction with Archive Log Mode

Archive mode (ARCHIVELOG) and no-archive mode (NOARCHIVELOG) define whether filled redo logs are archived before reuse. In archive mode, a background process (ARCn) copies each full redo log to an archive destination, preserving redo history. In no-archive mode, logs are overwritten without copying, risking data loss if recovery past the last backup is needed.

Logging mode is orthogonal to archive mode: both can operate under either archive setting. How ever, only LOGGING and FORCE LOGGING support media recovery in archive mode; NOLOGGING operations compromise full recovery because they omit redo data.

Applying LOGGING Mode

As the default, LOGGING applies to permanent database objects unless overridden. Temporary tablespaces never generate redo for their operations.

Applying NOLOGGING Mode

Use NOLOGGING to reduce redo during specific heavy operations:

• SQL*Loader direct path loads
• INSERT /*+ APPEND */ ... SELECT ...
• CTAS (CREATE TABLE AS SELECT)
• Partition maintenance: ALTER TABLE ... MOVE/SPLIT/MERGE/ADD
• Index creation: CREATE INDEX
• Index partition maintenance: ALTER INDEX ... MOVE/SPLIT/MERGE/ADD

Example of append-mode insertion:

INSERT /*+ APPEND */ INTO target_tab(col_a, col_b)
SELECT fld_x, fld_y FROM source_tab;

In ARCHIVELOG mode:

• If the table is LOGGING, redo is generated regardless of append hint.
• If the table is NOLOGGING, redo is suppressed only when using append hint.

Enabling FORCE LOGGING Mode

Activates comprehensive redo capture across the database or a tablespace, ignoring any local NOLOGGING directives (except for temporary segments).

Enable at database level:

ALTER DATABASE FORCE LOGGING;

Enable at tablespace level:

ALTER TABLESPACE ts_example FORCE LOGGING;

Disable at database level:

ALTER DATABASE NO FORCE LOGGING;

Disable at tablespace level:

ALTER TABLESPACE ts_example NO FORCE LOGGING;

Checking Current Logging Settings

Database-level status:

SELECT log_mode, force_logging FROM v$database;

Tablespace-level status:

SELECT tablespace_name, logging, force_logging FROM dba_tablespaces;

Object-level status:

SELECT table_name, logging FROM user_tables;

Switching Between Logging Modes

Database force logging:

-- Enable
ALTER DATABASE FORCE LOGGING;
-- Disable
ALTER DATABASE NO FORCE LOGGING;

Tablespace force logging:

-- Enable
ALTER TABLESPACE ts_example FORCE LOGGING;
-- Disable
ALTER TABLESPACE ts_example NO FORCE LOGGING;

Object-level change:

-- Set object to no-logging
ALTER TABLE tbl_sample NOLOGGING;
-- Revert to logging
ALTER TABLE tbl_sample LOGGING;

Managing LOB Segment Logging

Assess LOB storage usage:

SELECT SUM(bytes)/1024/1024/1024 AS total_lob_gb FROM user_segments
WHERE segment_type = 'LOBSEGMENT';

Quantify NOLOGGING LOB size:

SELECT SUM(seg.bytes)/1024/1024/1024 AS nolog_lob_gb
FROM user_segments seg
JOIN user_lobs lob ON seg.segment_name = lob.segment_name
WHERE seg.segment_type = 'LOBSEGMENT'
  AND lob.logging = 'NO';

List NOLOGGING LOBs with tablespace and size:

SELECT seg.segment_name, seg.bytes/1024/1024 AS mb_size, seg.tablespace_name
FROM user_segments seg
JOIN user_lobs lob ON seg.segment_name = lob.segment_name
WHERE seg.segment_type = 'LOBSEGMENT'
  AND lob.logging = 'NO';

List LOGGING LOBs similarly by changing condition to lob.logging = 'YES'.

Count of LOBs per logging type:

SELECT COUNT(*) FROM user_lobs WHERE logging = 'NO';
SELECT COUNT(*) FROM user_lobs WHERE logging = 'YES';