ORA-01547: RECOVER Succeeded but OPEN RESETLOGS Error - Complete Recovery

ORA-01547: Warning: RECOVER Succeeded but OPEN RESETLOGS Would Get Error

Error Overview

Error Text: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

The ORA-01547 error is a warning rather than a terminal failure. Oracle is reporting that the recovery commands it executed appeared to complete without error, but attempting to open the database with OPEN RESETLOGS would fail because one or more datafiles are not at a consistent state compatible with the recovery point. It is nearly always followed by a secondary error (such as ORA-01194 or ORA-01152) that names the specific datafile that is out of sync.

This situation most commonly arises during incomplete (point-in-time) recovery, when a subset of datafiles has not been recovered to the same SCN as the rest of the database. Attempting to open before resolving the inconsistency would corrupt the database.

Common Causes

1. Incomplete Recovery with Missing Archive Logs

• A point-in-time recovery was performed but one or more archive logs required to bring all datafiles to the target SCN are missing
• Some datafiles were backed up at different points in time and the available archivelogs cannot bridge all of them to the target SCN

2. Datafile Not Included in Recovery

• A datafile was not restored from backup before the RECOVER command was run
• A newly added datafile was not included in the restore set, leaving it at a different SCN than the rest of the database
• A tablespace was taken offline before the backup and not included in the restore

3. Archive Log Gap in a Data Guard Environment

• A Data Guard standby was being recovered and an archive log gap exists on the standby that cannot be resolved from the primary or archive log destination
• Redo was applied up to a certain SCN but a sequence gap prevents full synchronization

4. Inconsistent Backup Set

• Different datafiles were restored from backups taken at different points in time (e.g., incremental restore path mixed different backup levels)
• A control file backup is from a different point in time than the datafile backups being used, creating an SCN mismatch

5. RESETLOGS SCN Boundary Issues

• Attempting to recover across a previous OPEN RESETLOGS event without having backups from after that resetlogs operation
• The target SCN requested falls before the RESETLOGS SCN embedded in some datafile headers

Diagnostic Queries

Identify the Inconsistent Datafiles

-- The secondary error (ORA-01194 or ORA-01152) names the file; also check:
SELECT
  df.file#,
  df.name,
  df.status,
  df.checkpoint_change#            AS file_scn,
  db.checkpoint_change#            AS database_scn,
  df.checkpoint_change# - db.checkpoint_change# AS scn_delta,
  df.recover,
  df.fuzzy
FROM v$datafile df
CROSS JOIN v$database db
ORDER BY ABS(df.checkpoint_change# - db.checkpoint_change#) DESC;

Check the Database’s Current SCN and Incarnation

-- Current database incarnation and SCN information
SELECT
  dbid,
  name,
  db_unique_name,
  resetlogs_change#,
  resetlogs_time,
  prior_resetlogs_change#,
  checkpoint_change#,
  status,
  open_mode,
  log_mode
FROM v$database;

-- Check all database incarnations
SELECT
  incarnation#,
  resetlogs_change#,
  resetlogs_time,
  prior_incarnation#,
  prior_resetlogs_change#,
  status
FROM v$database_incarnation
ORDER BY incarnation#;

Check Available Archive Logs

-- Review what archive log sequences are available and their SCN ranges
SELECT
  sequence#,
  first_change#,
  next_change#,
  first_time,
  next_time,
  archived,
  applied,
  deleted,
  status,
  name
FROM v$archived_log
WHERE standby_dest = 'NO'
  AND deleted = 'NO'
ORDER BY sequence#;

-- Identify gaps in the archive log sequence
SELECT
  a.sequence# + 1                  AS missing_from,
  b.sequence# - 1                  AS missing_to,
  b.sequence# - a.sequence# - 1   AS gap_count
FROM v$archived_log a
JOIN v$archived_log b ON b.sequence# = (
  SELECT MIN(c.sequence#) FROM v$archived_log c WHERE c.sequence# > a.sequence#
)
WHERE b.sequence# - a.sequence# > 1
  AND a.standby_dest = 'NO'
  AND b.standby_dest = 'NO'
ORDER BY a.sequence#;

Check RMAN Recovery Catalog for Backup Coverage

-- From RMAN: list all backups and their SCN coverage
-- Run in RMAN, not SQL*Plus
LIST BACKUP SUMMARY;

-- Check which SCN range each backup set covers
LIST BACKUP OF DATABASE;

-- Check for datafile copies
LIST COPY OF DATABASE;

-- Report what archivelogs RMAN knows about
LIST ARCHIVELOG ALL;

Examine the Alert Log for Recovery Details

-- Review alert log entries during the recovery window
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01547%'
   OR message_text LIKE '%ORA-01194%'
   OR message_text LIKE '%ORA-01152%'
   OR message_text LIKE '%RECOVER%'
   OR message_text LIKE '%RESETLOGS%'
ORDER BY originating_timestamp DESC
FETCH FIRST 100 ROWS ONLY;

Step-by-Step Resolution

1. Identify Which File Is Preventing RESETLOGS

The secondary error accompanying ORA-01547 names the file. Before doing anything else, run the diagnostic query above to understand which files have SCN discrepancies and by how much.

-- Identify all files needing recovery
SELECT file#, name, recover, fuzzy, checkpoint_change#
FROM v$datafile
WHERE recover = 'YES' OR fuzzy = 'YES';

2. Recover the Identified Problem Datafile

If the file simply needs additional archivelog application:

-- From SQL*Plus (RECOVER will prompt for archivelogs)
RECOVER DATAFILE &file_number;

-- Or recover using automatic archivelog application
RECOVER AUTOMATIC DATAFILE &file_number;

-- After recovery, attempt to open
ALTER DATABASE OPEN RESETLOGS;

Using RMAN for the same operation:

-- From RMAN: recover the specific datafile
RECOVER DATAFILE &file_number;

-- Then open from SQL*Plus
ALTER DATABASE OPEN RESETLOGS;

3. Perform a Full Database Incomplete Recovery to a Consistent Point

If some archivelogs are missing and a consistent point-in-time must be chosen that all datafiles can reach:

-- From RMAN: determine the latest consistent SCN all files can reach
-- First, identify the minimum SCN across all datafiles requiring recovery
SELECT MIN(checkpoint_change#) AS minimum_consistent_scn FROM v$datafile;

-- Run incomplete recovery to that SCN (or a time/log sequence)
-- Option A: Recover to a specific SCN
RUN {
  SET UNTIL SCN &minimum_consistent_scn;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

-- Option B: Recover to a specific time
RUN {
  SET UNTIL TIME "TO_DATE('2026-03-20 14:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

-- Option C: Recover to a specific archive log sequence
RUN {
  SET UNTIL SEQUENCE &sequence_number THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

4. Handle a Missing Datafile That Cannot Be Recovered

If a non-critical tablespace’s datafile cannot be recovered to the required SCN, it can be dropped before opening:

-- Offline the problem datafile permanently (data in it will be lost)
ALTER DATABASE DATAFILE &file_number OFFLINE DROP;

-- Now attempt OPEN RESETLOGS
ALTER DATABASE OPEN RESETLOGS;

-- After opening, drop the tablespace to clean up (data in it is gone)
DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES;

This approach is only appropriate for non-critical or expendable tablespaces. Never use it for the SYSTEM, SYSAUX, UNDO, or any tablespace containing essential application data.

5. Resolve a Data Guard Archive Log Gap

If this error occurs on a Data Guard standby during managed recovery:

-- On the standby: check for gaps
SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;

-- On the primary: manually copy the missing archivelogs to the standby
-- Then register them in the standby's control file
ALTER DATABASE REGISTER PHYSICAL LOGFILE '/path/to/archivelog';

-- Resume managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6. Recovering Across a RESETLOGS Boundary

If recovery requires crossing a previous RESETLOGS event, ensure the catalog or control file knows about all incarnations and that post-RESETLOGS backups are available:

-- In RMAN: reset the database to the target incarnation before recovering
-- List incarnations
LIST INCARNATION OF DATABASE;

-- Reset to the prior incarnation to recover to a point before the last RESETLOGS
RESET DATABASE TO INCARNATION &incarnation_number;

-- Then restore and recover
RESTORE DATABASE UNTIL SCN &target_scn;
RECOVER DATABASE UNTIL SCN &target_scn;
ALTER DATABASE OPEN RESETLOGS;

Prevention Strategies

1. Validate Backup Consistency Before Recovery

-- Always validate backups before using them for recovery (run in RMAN)
VALIDATE DATABASE;
VALIDATE BACKUPSET ALL;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;

-- Report files needing backup to avoid coverage gaps
REPORT NEED BACKUP DAYS 1;
REPORT UNRECOVERABLE;

2. Maintain Complete Archive Log Retention

-- Ensure archive log retention is sufficient for the recovery window
-- Configure RMAN retention policy
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

-- Or keep a minimum number of full backups
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

-- Check current archivelog deletion policy
SHOW ALL;

3. Test Recovery Procedures Regularly

-- Periodically test RESTORE and RECOVER in a non-production environment
-- This validates both backups and the recovery procedure itself
-- At minimum, run a restore validation
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

4. Configuration Best Practices

• Always run in ARCHIVELOG mode — without it, only full offline restores are possible
• Configure at least two archivelog destinations to guard against log loss
• Use a Recovery Catalog for RMAN in production — it retains historical backup and incarnation information beyond what the control file can store
• After any OPEN RESETLOGS, take a full database backup immediately — this establishes a clean recovery baseline for the new incarnation
• In Data Guard environments, monitor v$archive_gap proactively and configure FAL_SERVER/FAL_CLIENT for automatic gap resolution

5. Monitoring Recovery Window Coverage

-- Check that all datafiles have a backup within the required window
SELECT
  df.name,
  MAX(bp.completion_time)          AS last_backup_time,
  ROUND(SYSDATE - MAX(bp.completion_time), 1) AS days_since_backup
FROM v$datafile df
LEFT JOIN (
  SELECT file# AS file_number, MAX(completion_time) AS completion_time
  FROM rc_backup_datafile
  GROUP BY file#
) bp ON df.file# = bp.file_number
GROUP BY df.name
ORDER BY days_since_backup DESC NULLS FIRST;

Diagnostic Scripts

These Oracle Day by Day scripts can assist with backup and recovery analysis:

• gvsess.sql — Identify active recovery sessions and their progress

Related Errors

• ORA-01578 - Oracle data block corrupted
• ORA-00376 - File cannot be read at this time
• ORA-16038 - Log sequence cannot be archived
• ORA-19815 - Flash recovery area full

Emergency Response

Immediate Assessment

-- Determine which files are blocking OPEN RESETLOGS
SELECT file#, name, status, recover, fuzzy, checkpoint_change#
FROM v$datafile
WHERE recover = 'YES' OR fuzzy = 'YES' OR status NOT IN ('ONLINE','SYSTEM');

-- Confirm the target recovery SCN
SELECT checkpoint_change#, resetlogs_change#, current_scn FROM v$database;

Immediate Actions

1. Do not issue ALTER DATABASE OPEN RESETLOGS until all datafiles are confirmed consistent — doing so with inconsistent files will corrupt the database
2. Read the secondary error carefully — ORA-01194 or ORA-01152 will name the specific file that is the problem
3. Check archive log availability for the sequences between the problem file’s SCN and the target SCN
4. Engage RMAN for the recovery — manual recovery via SQL*Plus is error-prone for complex scenarios

Post-Resolution Verification

-- After successful OPEN RESETLOGS, verify all files are online and consistent
SELECT file#, name, status, checkpoint_change# FROM v$datafile ORDER BY file#;

-- Confirm the database opened cleanly
SELECT name, open_mode, resetlogs_change#, resetlogs_time FROM v$database;

-- Immediately take a full backup after RESETLOGS
-- (Run in RMAN)
BACKUP DATABASE PLUS ARCHIVELOG;