Scops
Docs Download

Oracle problems

NOLOGGING With a Standby: ORA-26040 Blocks, and the Fix

3 November 2026

A load ran with NOLOGGING on the primary, an index was rebuilt that way, or a table was moved, and it was faster, because the blocks were written without redo. The standby receives redo, and only redo, so for those blocks it received a note that they changed and nothing of what they contain. Nothing complains for weeks. Then a report runs on the Active Data Guard standby, or a switchover makes it the primary, and the first read of those blocks fails with ORA-01578, naming the file and block, and ORA-26040, saying why: the block was loaded with the NOLOGGING option. Oracle's Data Guard guide is blunt about the default logging mode: it leads to the loaded data being missing from the standby, which requires manual intervention to fix. The queries below find the damage on both sides, and the first fix is one statement.

The logging mode of the primary

SELECT name, log_mode, force_logging
FROM   v$database;

Oracle's reference lists the values of FORCE_LOGGING: NO, no logging mode has been enabled for the database; YES, FORCE LOGGING mode is enabled; and the two standby modes, STANDBY NOLOGGING FOR LOAD PERFORMANCE and STANDBY NOLOGGING FOR DATA AVAILABILITY. A database with a standby and NO in this column has the problem, whether or not it has happened yet.

What ran unlogged on the primary, and when

SELECT file#, name, unrecoverable_change#, unrecoverable_time
FROM   v$datafile
WHERE  unrecoverable_time IS NOT NULL
ORDER  BY unrecoverable_time DESC;

UNRECOVERABLE_CHANGE# and UNRECOVERABLE_TIME are the last unrecoverable change made to each datafile; Oracle's reference notes they are updated when an unrecoverable operation completes, and only in ARCHIVELOG mode. A time after the standby was created is a hole in the standby; a time after the last backup is a hole in the primary's own recoverability as well, until the next backup. The objects that invite it:

SELECT owner, table_name AS object_name, 'TABLE' AS type
FROM   dba_tables WHERE logging = 'NO'
UNION ALL
SELECT owner, index_name, 'INDEX'
FROM   dba_indexes WHERE logging = 'NO'
UNION ALL
SELECT tablespace_name, NULL, 'TABLESPACE'
FROM   dba_tablespaces WHERE logging = 'NOLOGGING'
ORDER  BY 3, 1, 2;

A direct-path insert, a CREATE TABLE AS SELECT, an index rebuild or a table move skips redo when the object or its tablespace is NOLOGGING, and Data Pump can ask for the same with its DISABLE_ARCHIVE_LOGGING transform. With FORCE LOGGING on the database none of these settings matter, which is the point of it.

What the standby is missing

SELECT file#, name, first_nonlogged_scn, first_nonlogged_time
FROM   v$datafile
WHERE  first_nonlogged_scn > 0;

Run on the standby, where Oracle's reference says these two columns are to be checked. The block ranges themselves:

SELECT file#, block#, blocks, nonlogged_start_change#,
       nonlogged_end_change#, object#, reason
FROM   v$nonlogged_block
ORDER  BY file#, block#;

V$NONLOGGED_BLOCK lists the ranges of nonlogged datafile blocks recorded in the control file: the first block and the number of blocks in each range, the SCNs at which they became nonlogged, and the object they belong to, which DBA_OBJECTS turns into a name on the primary by its DATA_OBJECT_ID. The view is in the control file, so it answers on a mounted standby as well as an open one.

The fix, in order

  1. Turn on FORCE LOGGING, today. ALTER DATABASE FORCE LOGGING on the primary; the Data Guard guide says FORCE LOGGING mode prevents any load operation from being performed in a nonlogged manner, and that this can slow the load because the loaded data must be copied into the redo logs. That is the only cost, it falls only on loads that were skipping redo, and every one of them was a hole in the standby. A standby that is worth running is worth this.
  2. Repair the standby. With redo apply stopped, RMAN's RECOVER DATABASE NONLOGGED BLOCK on the standby extends recovery to the nonlogged blocks listed in V$NONLOGGED_BLOCK: RMAN validates the ranges first, then recovers them, taking the blocks from the primary. Then restart redo apply, and check that the view is empty. The older repair still works: restore the affected datafiles to the standby from a primary backup taken after the load.
  3. Or rewrite the object with redo. With FORCE LOGGING on, ALTER INDEX ... REBUILD or ALTER TABLE ... MOVE on the primary writes every block again, with redo this time, and the standby applies them. For an index this is often quicker than the block repair, and the index is better for it.
  4. Back up the primary. An UNRECOVERABLE_TIME newer than the last backup means a restore of the primary would hit the same blocks. A backup of the datafiles after the load closes that hole; the standby needs its own repair regardless.
  5. If the load must stay fast, use the modes made for a standby. The guide describes STANDBY NOLOGGING FOR DATA AVAILABILITY as sending the loaded data to each standby through its own connection, with the commit delayed until all the standbys have applied it, and STANDBY NOLOGGING FOR LOAD PERFORMANCE as the same, except that the load can stop sending if the network cannot keep up. Both are ALTER DATABASE SET STANDBY NOLOGGING FOR ..., and both are better than a hole.
  6. Clear the attributes. Objects and tablespaces left at NOLOGGING do nothing under FORCE LOGGING, but they are a trap for the day someone turns it off for a migration and forgets; ALTER TABLE ... LOGGING and ALTER TABLESPACE ... LOGGING remove it.

See it coming

Radar Engine, the detection layer of Scops, reads the unrecoverable times of the datafiles of every database that has a standby, so a NOLOGGING load is a finding the same day, with the file and the time, while the fix is an index rebuild rather than a failed switchover months later:

DetectorClockWhen it fires
NOLOGGING changesNOLOGGING changes in 7 days, with a standby.Warning.
Apply stopped on standbyRedo apply idle for hours.Warning. With a growing gap, not planned: Critical.

The second row is for the repair itself: redo apply has to be stopped for it, and a standby left that way is the next outage. Scops reads both sides through a read-only account, with nothing installed on either server and no Diagnostics Pack, on Standard Edition 2 as on Enterprise Edition.

Questions

What does ORA-26040 mean?
The block being read was written on the primary with NOLOGGING, so no redo described its contents and the standby, or a restored datafile, marks it as nonlogged. It comes with ORA-01578, which names the file and block. The data is intact on the primary; the copy is missing.

Does FORCE LOGGING slow the database down?
Only the loads that were skipping redo: the Data Guard guide says the loaded data must then be copied into the redo logs. Ordinary inserts, updates and deletes were always logged, and nothing changes for them.

How do I find NOLOGGING operations on the primary?
V$DATAFILE's UNRECOVERABLE_CHANGE# and UNRECOVERABLE_TIME record the last unrecoverable operation on each datafile, in ARCHIVELOG mode. DBA_TABLES, DBA_INDEXES and DBA_TABLESPACES show which objects are set to NOLOGGING.

Can the standby be repaired without rebuilding it?
Yes. RMAN's RECOVER DATABASE NONLOGGED BLOCK on the standby, with redo apply stopped, recovers the ranges listed in V$NONLOGGED_BLOCK; restoring the affected datafiles from a primary backup taken after the load, or rebuilding the object on the primary under FORCE LOGGING, also works.

Related problems

Sources