Oracle NOLOGGING: When the Backup Says “Success” but the Restore Is Unusable

The incident

This case touches on a very common pain point. Rubrik reported the Oracle backups as valid; however, a restore test of the environment showed that, after a point-in-time recovery (PITR), the database reported a number of corrupted blocks:

ORA-01578: ORACLE data block corrupted (file # .., block # ..)
ORA-01110: data file 4: '*DISKGROUP/…'
ORA-26040: Data block was loaded using the NOLOGGING option

Once the database was opened, accessing certain tables raised errors, and the restore turned out to be unusable.

The cause

When errors ORA-01578 (or ORA-28304, if TDE is enabled) and ORA-26040 appear together, we are dealing with “controlled” corruption: the application ran operations in NOLOGGING mode, which generate no redo and are therefore, in effect, deliberately unrecoverable.

Whether an operation generates redo depends on the combination of three settings: the table's logging mode, the insert type (direct-path with the APPEND hint, or conventional), and the database's archiving mode. The table summarizes the possible cases when FORCE LOGGING is not enabled:

 

A classic example: the database is in ARCHIVELOG mode, the table is in NOLOGGING mode, and a direct-path insert is run with the /*+ APPEND */ hint. The loaded data generates no REDO: the operation is fast, but it cannot be recovered in the event of media recovery.

Typically, the database administrator (DBA), in agreement with the application team, can set NOLOGGING mode at the level of:

• tablespace
• table
• index
• partition
• subpartition

At the statement level, by contrast, the following operations can run in NOLOGGING mode:

• Direct-path INSERT (APPEND hint)
• CREATE TABLE … AS SELECT (CTAS)
• CREATE INDEX
• ALTER TABLE … MOVE
• ALTER INDEX … REBUILD

In itself, this option is a useful feature when used deliberately, but it becomes a serious problem when used extensively on data that cannot be reloaded.

Remediation

If you have no control over the code, you can enable FORCE LOGGING at one of these levels:

• database
• pluggable database (PDB)
• tablespace

so that it extends to all the objects they contain.

With FORCE LOGGING enabled, Oracle generates redo even for NOLOGGING operations, so they no longer cause problems in PITR restores or in recovery from snapshots.

 

How to diagnose “silent corruption”

Checking whether your backups contain blocks corrupted by NOLOGGING operations is essential, because the corruption does not surface during the backup, only after the restore, when the data is accessed.

There are two main ways to check:

1. Functional restore testing (empirical method)

The safest method is to schedule periodic restore tests. Note: it is not enough for the restore to complete successfully and for the database to open (OPEN). You need to actually read the data in critical tables, with queries that scan all their blocks, or run application tests: error ORA-01578 appears only when a corrupted block is accessed. A simple SELECT COUNT(*) may not be enough, because Oracle can answer it by reading an index without touching the table blocks; it is better to force a full scan, for example with SELECT /*+ FULL(t) */ COUNT(*) FROM table_name t;

2. Technical validation with RMAN

For a faster, more targeted check without manual application tests, you can run RMAN directly on the newly restored database (after the PITR recovery). The procedure depends on the Oracle version:

• A) Oracle 12.2 and later — on a restored database (after the PITR recovery), run the dedicated command: RMAN> VALIDATE DATABASE NONLOGGED BLOCK;

This command scans the data files and populates the dynamic view v$nonlogged_block. To see the results, query the view in SQL: SELECT * FROM v$nonlogged_block;

• B) Versions earlier than 12.2 — the dedicated syntax is not available, so use the generic validation command: RMAN> VALIDATE DATABASE;

Then query the view that records corrupted blocks:

For Oracle 11g and 12.1
SELECT * FROM v$database_block_corruption 
WHERE CORRUPTION_TYPE = 'NOLOGGING';

For versions earlier than 11g (NOLOGGING blocks are reported as LOGICAL corruption)
SELECT * FROM v$database_block_corruption 
WHERE CORRUPTION_TYPE = 'LOGICAL';

It is crucial to understand that backup software (such as Rubrik, Veeam, etc.) will never report this error. For RMAN, and therefore for the backup software, a block marked as NOLOGGING is not physical corruption: it is a formally valid and consistent block whose changes Oracle deliberately chose not to record in the redo. As a result, a “green” backup dashboard does not guarantee that this problem is absent.