ORA-10567: Redo Log Inconsistent with Data Dictionary - Recovery Procedures

ORA-10567: Redo Log is Inconsistent with Data Dictionary

Error Overview

Error Text: ORA-10567: Redo is inconsistent with data file string

The ORA-10567 error is raised during database recovery when Oracle detects that the redo log being applied is inconsistent with the current state of the data file or data dictionary. It indicates that the redo stream does not match what Oracle expects based on the control file, data file headers, or SCN (System Change Number) history. This is a serious recovery error that typically occurs after incomplete recovery, improper RESETLOGS operations, redo log corruption, or complex Data Guard switchover or failover scenarios.

This error usually accompanies ORA-00283 (recovery session cancelled due to errors) or ORA-01110 (data file string) and requires careful investigation before attempting any recovery action. Proceeding incorrectly can result in permanent data loss.

Common Causes

1. Incomplete Recovery Followed by OPEN RESETLOGS

• Database opened with RESETLOGS after point-in-time recovery
• Archived redo logs from before the RESETLOGS applied to a database that has already been reset
• A previous incomplete recovery was performed and the database was opened, creating a new incarnation, but old backups from the prior incarnation are being applied

2. Redo Log or Archived Log Corruption

• Archived log file physically corrupted (bit rot, storage failure)
• Online redo log member corrupted while instance was running
• Log file copied to wrong location or replaced with a log from a different database
• Log sequence numbers out of order in the recovery set

3. Data Guard Switchover or Failover Issues

• Primary and standby SCNs diverged before a failover was completed cleanly
• Manual failover performed without first applying all available redo
• Standby opened read-write without a proper failover procedure, creating a divergent incarnation
• Redo applied in the wrong order during a manual recovery attempt

4. Wrong RMAN Backup Set Applied

• Restoring data files from one backup but applying archived logs from a different database or a different incarnation
• Mixed backup sets from different RMAN catalog repositories
• Partial restore where some data files come from different backup times

5. Control File Mismatch

• Control file recreated with NORESETLOGS when data files required RESETLOGS
• Using a backup control file from a different point in time than the data files
• Control file replaced with a copy from a different instance or test database

Diagnostic Queries

Check Database and Recovery Status

-- Current database state and SCN information
SELECT
  dbid,
  name,
  db_unique_name,
  open_mode,
  resetlogs_change#,
  resetlogs_time,
  prior_resetlogs_change#,
  prior_resetlogs_time,
  current_scn,
  checkpoint_change#
FROM v$database;

-- Current database incarnation history
SELECT
  incarnation#,
  resetlogs_change#,
  TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
  prior_resetlogs_change#,
  status
FROM v$database_incarnation
ORDER BY incarnation#;

Inspect Data File Headers

-- Compare data file header SCNs with control file expectations
SELECT
  file#,
  name,
  status,
  checkpoint_change#,
  TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
  resetlogs_change#,
  TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
  fuzzy,
  recovery,
  tablespace_name
FROM v$datafile_header
ORDER BY file#;

-- Compare control file expectations for data files
SELECT
  file#,
  name,
  status,
  checkpoint_change#,
  TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
  resetlogs_change#,
  unrecoverable_change#
FROM v$datafile
ORDER BY file#;

Check Redo Log and Archived Log State

-- Online redo log group status
SELECT
  l.group#,
  l.sequence#,
  l.bytes / 1024 / 1024     as size_mb,
  l.members,
  l.archived,
  l.status,
  l.first_change#,
  TO_CHAR(l.first_time, 'DD-MON-YYYY HH24:MI:SS') as first_time,
  l.next_change#
FROM v$log l
ORDER BY l.group#;

-- Recent archived logs and their SCN ranges
SELECT
  sequence#,
  first_change#,
  next_change#,
  TO_CHAR(first_time, 'DD-MON-YYYY HH24:MI:SS') as first_time,
  TO_CHAR(next_time,  'DD-MON-YYYY HH24:MI:SS') as next_time,
  archived,
  deleted,
  status,
  name
FROM v$archived_log
WHERE dest_id = 1
  AND standby_dest = 'NO'
ORDER BY sequence# DESC
FETCH FIRST 30 ROWS ONLY;

-- Check for gaps in archived log sequences
SELECT
  a.sequence# as seq,
  a.sequence# + 1 as expected_next,
  b.sequence# as actual_next
FROM v$archived_log a
LEFT JOIN v$archived_log b ON b.sequence# = a.sequence# + 1
  AND b.dest_id       = a.dest_id
  AND b.standby_dest  = a.standby_dest
  AND b.resetlogs_id  = a.resetlogs_id
WHERE b.sequence# IS NULL
  AND a.dest_id = 1
  AND a.standby_dest = 'NO'
ORDER BY a.sequence# DESC
FETCH FIRST 10 ROWS ONLY;

Check Recovery-Related Errors in Alert Log

-- ORA-10567 and related messages in the alert log
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-10567%'
   OR message_text LIKE '%ORA-00283%'
   OR message_text LIKE '%ORA-01110%'
   OR message_text LIKE '%inconsistent%'
   OR message_text LIKE '%resetlogs%'
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Data Guard Standby Diagnostics

-- Check standby apply lag and last applied SCN (on standby)
SELECT
  name,
  value,
  datum_time
FROM v$dataguard_stats
ORDER BY name;

-- Check managed recovery process status (on standby)
SELECT
  process,
  status,
  thread#,
  sequence#,
  block#,
  delay_mins
FROM v$managed_standby
ORDER BY process;

-- Check for SCN mismatch between primary and standby
SELECT
  db_unique_name,
  open_mode,
  current_scn,
  resetlogs_change#,
  protection_mode,
  database_role
FROM v$database;

Step-by-Step Resolution

1. Stop Recovery and Assess the Situation

Never attempt to force open the database until you understand the exact inconsistency.

-- If in RMAN, cancel recovery:
-- RMAN> EXIT

-- From SQL*Plus, confirm the database state:
SELECT name, open_mode, log_mode FROM v$database;

-- Review exact error details in the alert log before proceeding.
-- The error will indicate which data file and which SCN is mismatched.

2. Identify Which Incarnation the Redo Belongs To

-- Check incarnation history
SELECT
  incarnation#,
  resetlogs_change#,
  TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
  status
FROM v$database_incarnation
ORDER BY incarnation#;

-- In RMAN, list all known incarnations
-- RMAN> LIST INCARNATION OF DATABASE;

If redo from a previous incarnation is being applied to a database opened with RESETLOGS, switch RMAN to the correct incarnation:

-- RMAN: reset to the correct incarnation before restoring/recovering
-- RMAN> RESET DATABASE TO INCARNATION <incarnation#>;

3. Restore from a Consistent RMAN Backup

If the current data files or redo cannot be reconciled, restore from the most recent consistent RMAN backup:

-- RMAN recovery to last known good point
-- Step 1: Start RMAN and connect
-- rman target /

-- Step 2: Restore the database
-- RMAN> STARTUP MOUNT;
-- RMAN> RESTORE DATABASE;
-- RMAN> RECOVER DATABASE;
-- RMAN> ALTER DATABASE OPEN RESETLOGS;

-- RMAN recovery to a specific SCN (point-in-time recovery)
-- RMAN> RUN {
--   SET UNTIL SCN = 12345678;
--   RESTORE DATABASE;
--   RECOVER DATABASE;
-- }
-- RMAN> ALTER DATABASE OPEN RESETLOGS;

-- RMAN recovery to a specific time
-- RMAN> RUN {
--   SET UNTIL TIME "TO_DATE('2026-03-23 14:00:00','YYYY-MM-DD HH24:MI:SS')";
--   RESTORE DATABASE;
--   RECOVER DATABASE;
-- }
-- RMAN> ALTER DATABASE OPEN RESETLOGS;

4. Handle Individual Inconsistent Data Files

When only specific data files are inconsistent, restore only those files:

-- RMAN: restore only the inconsistent data file(s)
-- RMAN> RUN {
--   SET UNTIL SCN = <target_scn>;
--   RESTORE DATAFILE 5;
--   RECOVER DATAFILE 5;
-- }

-- Alternatively, offline the problem file and open the rest of the database:
ALTER DATABASE DATAFILE 5 OFFLINE;
ALTER DATABASE OPEN;
-- Then recover the offline file:
-- RMAN> RECOVER DATAFILE 5;
ALTER DATABASE DATAFILE 5 ONLINE;

5. Recreate the Control File if It Is the Source of Inconsistency

-- If the control file is suspected to be from the wrong point in time,
-- recreate it using the backup control file procedure:

-- Step 1: Restore a backup control file
-- RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- Step 2: Mount the database with the restored control file
-- RMAN> STARTUP MOUNT;

-- Step 3: Recover using the backup control file
-- RMAN> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

-- Step 4: Open RESETLOGS (required after using backup controlfile)
-- SQL> ALTER DATABASE OPEN RESETLOGS;

6. Data Guard: Fix Standby After Divergence

-- On the standby: stop apply and assess
-- DGMGRL> EDIT DATABASE standby_db SET STATE='APPLY-OFF';

-- Check MRP (managed recovery process) status
SELECT process, status, sequence#, block#
FROM v$managed_standby
WHERE process LIKE 'MRP%';

-- If standby has diverged (opened in wrong mode), flashback to before divergence
-- (requires Flashback Database to be enabled)
-- RMAN> FLASHBACK DATABASE TO SCN <scn_before_divergence>;
-- SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

-- Re-register logs from primary
-- SQL> ALTER DATABASE REGISTER LOGFILE '/path/to/archive/log.arc';

-- Restart apply
-- DGMGRL> EDIT DATABASE standby_db SET STATE='APPLY-ON';

Prevention Strategies

1. Validate Backups Regularly with RMAN

-- Validate all backups without restoring (checks for corruption)
-- RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

-- Validate a specific backup set
-- RMAN> VALIDATE BACKUPSET <backup_set_key>;

-- Check validation results
SELECT
  session_key,
  object_type,
  status,
  time_taken_display,
  input_bytes_display,
  output_bytes_display
FROM v$rman_status
WHERE operation = 'VALIDATE'
ORDER BY start_time DESC
FETCH FIRST 10 ROWS ONLY;

2. Monitor Redo Log and Archiving Health

-- Monitor for archiving failures or gaps
CREATE OR REPLACE PROCEDURE check_archive_health AS
  v_gap_count  NUMBER;
  v_fail_count NUMBER;
BEGIN
  -- Check for archiving failures in v$archive_dest_status
  SELECT COUNT(*) INTO v_fail_count
  FROM v$archive_dest_status
  WHERE status = 'ERROR';

  IF v_fail_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('ALERT: ' || v_fail_count ||
      ' archive destination(s) in ERROR state.');
  END IF;

  -- Check for gaps in the local archive log sequence
  SELECT COUNT(*) INTO v_gap_count
  FROM v$archive_gap;

  IF v_gap_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('ALERT: Archive log gaps detected. ' ||
      v_gap_count || ' gap(s) found.');
  END IF;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'ARCHIVE_HEALTH_CHECK',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'check_archive_health',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

3. Best Practices for Recovery Operations

• Always use RMAN for all backup and recovery operations — never manually copy data files
• Enable CONTROL_FILE_AUTOBACKUP ON in RMAN so a current control file is available after every backup
• Enable Flashback Database on all databases to allow rapid point-in-time recovery without full restore
• After every ALTER DATABASE OPEN RESETLOGS, take an immediate RMAN full backup — old archived logs from before RESETLOGS cannot be used with the new incarnation
• In Data Guard environments, always use DGMGRL or Oracle’s recommended procedures for switchover and failover; never manually open a standby database read-write without a proper role transition
• Test recovery procedures in a non-production environment at least quarterly using actual production backups
• Maintain at least two independent RMAN backup destinations
• Keep RMAN catalog up to date and backed up separately from the recovery catalog database

Diagnostic Scripts

These Oracle Day by Day scripts support backup and recovery monitoring:

• health.sql — Overall database health including backup and redo log status
• gvsess.sql — Active session analysis useful for identifying recovery-related background processes

Related Errors

• ORA-00283 - Recovery session cancelled due to errors
• ORA-01578 - Oracle data block corrupted
• ORA-16038 - Log sequence number cannot be archived
• ORA-19815 - Flash recovery area full
• ORA-01110 - Data file reference (companion error identifying the affected file)

Emergency Response

Quick Fixes

1. Do not attempt to force-open the database (ALTER DATABASE OPEN RESETLOGS) without understanding the inconsistency. Doing so with inconsistent data files can corrupt the database further.

2. Collect diagnostic information first

   -- From MOUNT mode:
   SELECT file#, name, status, checkpoint_change#, resetlogs_change#
   FROM v$datafile_header
   ORDER BY file#;
   
   SELECT incarnation#, resetlogs_change#, status
   FROM v$database_incarnation ORDER BY incarnation#;

3. In RMAN, list backups available for recovery

   RMAN> LIST BACKUP SUMMARY;
   RMAN> LIST ARCHIVELOG ALL;
   RMAN> LIST INCARNATION OF DATABASE;

4. Engage Oracle Support (SR) if this is a production database and recovery options are unclear. ORA-10567 involves SCN-level inconsistencies that require expert diagnosis.

Post-Resolution Cleanup

-- After successful recovery and OPEN RESETLOGS, immediately take a full backup
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- Confirm all data files are consistent and online
SELECT file#, name, status, checkpoint_change#, fuzzy, recovery
FROM v$datafile_header
ORDER BY file#;

-- Verify no data files are offline unexpectedly
SELECT file#, name, status, online_status
FROM v$datafile
WHERE online_status != 'ONLINE'
ORDER BY file#;

-- Cross-check RMAN catalog to remove references to obsolete logs
-- RMAN> CROSSCHECK ARCHIVELOG ALL;
-- RMAN> DELETE EXPIRED ARCHIVELOG ALL;
-- RMAN> CROSSCHECK BACKUP;
-- RMAN> DELETE EXPIRED BACKUP;