Oracle 26ai LOGGING and NOLOGGING Backup Implications

Oracle AI Database 26ai supports the LOGGING and NOLOGGING attributes for tables, partitions, indexes, LOB segments, materialized views, and other objects where the applicable SQL statement permits the logging clause. These attributes affect how much redo Oracle generates for certain object-creation, direct-path loading, and data-movement operations.

NOLOGGING is not a general switch that disables redo for an object. Conventional-path inserts, updates, deletes, and ordinary transactional work still generate redo even when a table's stored attribute is NOLOGGING. Reduced redo is available only to a documented subset of operations, and only when the operation actually uses the required execution method.

The tradeoff is recoverability. A qualifying NOLOGGING operation can write new or changed blocks without writing all the redo needed to reconstruct those blocks. If a DBA later restores a backup taken before the operation and applies redo through that time, the recovery process can identify affected ranges as logically corrupt. A new full or incremental backup of the affected data files is therefore required when the changed data must be recoverable.

An object's visible logging attribute is only one level of the policy. A tablespace supplies the default for objects created without an explicit logging clause. A table, partition, index, or LOB can have a more specific attribute. Database-level or tablespace-level force logging overrides a lower-level NOLOGGING attribute while the force-logging policy is effective.

Inspect the database setting before approving a reduced-redo operation:

SELECT force_logging
FROM   v$database;

In Oracle AI Database 26ai, FORCE_LOGGING can report NO, YES, STANDBY NOLOGGING FOR LOAD PERFORMANCE, or STANDBY NOLOGGING FOR DATA AVAILABILITY. It is not always a simple Boolean.

Review tablespace defaults and force-logging settings:

SELECT tablespace_name, logging, force_logging
FROM   dba_tablespaces
ORDER BY tablespace_name;

The tablespace LOGGING column contains LOGGING or NOLOGGING. Changing that default does not rewrite the stored attribute of every existing object. Object dictionary views commonly use YES and NO instead:

SELECT owner, table_name, logging
FROM   dba_tables
WHERE  logging = 'NO'
ORDER BY owner, table_name;

SELECT owner, index_name, logging
FROM   dba_indexes
WHERE  logging = 'NO'
ORDER BY owner, index_name;

Partition and LOB settings can differ from the parent table, so inspect the corresponding partition and LOB views for those designs. When reporting across a CDB, connect to the root, use the appropriate CDB_* views, and include CON_ID. A PDB-local query does not describe every container.

Oracle supports reduced redo only for particular operations. Important examples include serial or parallel direct-path INSERT, SQL*Loader direct-path loads, CREATE TABLE ... AS SELECT, index creation and supported index rebuilds, table moves, and partition operations that move data. Selected LOB operations also have logging choices subject to SecureFiles and BasicFiles rules.

Conventional inserts, updates, and deletes do not become nonlogged merely because the table says NOLOGGING. Data dictionary changes and other required metadata remain logged. Oracle also writes minimal redo that can identify invalidated extents or block ranges even when it omits the complete data needed to reconstruct them.

SQL*Loader still provides direct-path and utility-specific unrecoverable behavior, but a load does not require a DBA to run an old internal catldr.sql script. Do not disable database archiving as routine load tuning. Archive mode is a database-wide recovery-policy decision, not a convenient performance option for one job.

The following example requests a direct-path insert into a staging table whose attribute permits reduced redo:

ALTER TABLE sales_stage NOLOGGING;

INSERT /*+ APPEND */ INTO sales_stage
SELECT * FROM sales_source;

COMMIT;

The APPEND hint requests direct-path behavior. A hint is not an unconditional guarantee. Object features, constraints, triggers, parallel DML configuration, transaction state, and other restrictions can cause Oracle to choose another path or reject the requested operation. If the redo volume and recovery response matter operationally, test the exact statement and verify its actual behavior.

Direct-path processing can format blocks outside the conventional buffer-cache path and can improve a large load, but the benefit is workload and platform dependent. Avoid promises such as a 30 percent improvement or a runtime reduction of one half. Storage performance, redo bandwidth, parallelism, indexes, compression, Data Guard, and force logging all influence the result.

CREATE TABLE ... AS SELECT, or CTAS, can combine object creation with a direct-path population:

CREATE TABLE sales_stage
NOLOGGING
AS
SELECT *
FROM sales_source;

Oracle logs creation of the dictionary object, while the data population can use minimal redo. Before promoting this table, validate row counts, constraints, indexes, grants, statistics, dependent objects, application behavior, and the recovery plan. A lesson about logging should not normalize dropping a production table and renaming its replacement without those controls.

Indexes created or rebuilt with NOLOGGING have a similar tradeoff. If recovery later marks a nonlogged index logically corrupt but the underlying table is valid, the index can usually be dropped and rebuilt. Nonlogged table data is more serious because the underlying rows may need to be reloaded from a trusted source or restored from a post-operation backup.

Suppose RMAN backs up a data file, and a direct-path load later changes blocks in that file under NOLOGGING. The older backup does not contain those new blocks. The redo stream contains dictionary and invalidation information, but it may not contain the complete block changes. If the file is restored and recovered through the load, Oracle can mark the affected ranges logically corrupt.

This does not automatically make every file in the CDB unrecoverable. The damage is associated with the affected blocks, data files, and objects. Media recovery can continue, while access to an affected object can report errors such as ORA-01578. The correct response depends on what was changed:

• rebuild an index when its valid base table can reproduce it;
• recreate disposable staging data from a trusted source;
• restore an affected data file from a backup taken after the operation;
• apply an incremental backup that captured the changed blocks; or
• use an approved Data Guard nonlogged-block recovery procedure when applicable.

Flashback Database also needs consideration. Flashing back to a point during a nonlogged operation can leave affected objects or files with block corruption. If the business must move through that interval reliably, perform the operation with full logging or ensure that the recovery design has an appropriate backup and tested reconstruction path.

Changing the table back to LOGGING is useful for future operations:

ALTER TABLE sales_stage LOGGING;

This statement is not retroactive. It does not generate missing redo for the completed load. Enabling FORCE LOGGING after the load also does not repair the historical gap. The changed blocks must be captured in a new backup if they are valuable.

RMAN can identify data files that have experienced an unrecoverable operation since their most recent backup:

REPORT UNRECOVERABLE;

From the CDB root, an appropriately privileged common user can report across the target database. For PDB-specific work, connect in the appropriate context or use the supported PDB form. Treat this report as a check, not as a replacement for the load record and post-operation workflow.

After a successful nonlogged operation, back up all affected data files. The backup can be full or incremental because an incremental backup can capture changed blocks even though the operation did not write complete redo. For example:

BACKUP TABLESPACE user_data;

The actual runbook can back up selected data files, a tablespace, a PDB, or the whole database. A full database backup is not universally required. The required scope is the set of data files containing every affected segment. Verify that the backup completed and that the recovery chain also protects the control file, SPFILE, archived redo, encryption keys, credentials, and destination configuration.

V$NONLOGGED_BLOCK can display ranges of nonlogged blocks recorded in the control file. RMAN validation, restore, recovery, Flashback Database, and media-recovery operations maintain this information. In the database states supported by RMAN, a DBA can reconcile these ranges with the actual files:

VALIDATE DATABASE NONLOGGED BLOCK;

This diagnostic command does not replace the immediate backup. It helps determine whether recorded ranges still correspond to nonlogged blocks and updates the related information. Repair or standby recovery is a separate procedure that must follow the failure scenario and Oracle documentation.

Database-level force logging prevents eligible operations from omitting the redo needed for media recovery:

ALTER DATABASE FORCE LOGGING;

An object can continue to display a NOLOGGING attribute in the dictionary, but the active force-logging policy overrides it. Changing this database policy can wait for unlogged direct writes to finish and can affect load performance. Plan the change around concurrent jobs rather than toggling it casually for one session.

A primary database with physical standbys needs an explicit policy because a conventional nonlogged load can leave data missing at the standby. Oracle AI Database 26ai provides two specialized alternatives for qualifying Active Data Guard configurations:

• STANDBY NOLOGGING FOR DATA AVAILABILITY coordinates loaded blocks with qualifying standbys and favors synchronization, even when this can slow or alter the load behavior.
• STANDBY NOLOGGING FOR LOAD PERFORMANCE favors primary load speed and permits a qualifying standby to fetch missing blocks as managed recovery encounters invalidation records.

ALTER DATABASE SET STANDBY NOLOGGING
  FOR DATA AVAILABILITY;

ALTER DATABASE SET STANDBY NOLOGGING
  FOR LOAD PERFORMANCE;

These modes cannot be active at the same time as FORCE LOGGING. They depend on compatible standby, managed-recovery, and licensing conditions and have additional operational restrictions. Use the Oracle 26ai Data Guard documentation and a tested runbook before enabling either mode. They coordinate standby handling; they do not eliminate the need for backups.

NOLOGGING can improve selected bulk operations, but it exchanges redo work for explicit recovery work. Use it only when the source can be reconstructed, the effective policy is understood, affected files are identified, and a post-operation backup is part of the same approved workflow. The next lesson discusses backing up read-only tablespaces.