DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01547 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

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

ORA-01547 is a warning message Oracle raises after a recovery operation completes, indicating that while the RECOVER command itself succeeded, executing ALTER DATABASE OPEN RESETLOGS will likely fail. This typically occurs during incomplete recovery scenarios where the database cannot yet transition to a fully consistent, openable state. Understanding the root cause quickly is critical, as this warning signals that additional steps are needed before the database can be brought online.

Top 3 Causes

1. Corrupted or Missing Online Redo Log Files

If the current or active online redo log group is damaged or missing, Oracle cannot complete the RESETLOGS open sequence even after a successful recovery. This is especially common in environments without redo log multiplexing.

-- Check redo log status
SELECT GROUP#, STATUS, ARCHIVED, MEMBERS
FROM V$LOG;

-- Check redo log member paths
SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE;

-- Clear a corrupted unarchived redo log (use with caution)
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

-- Then attempt to open with RESETLOGS
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

2. SCN Mismatch Between Control File and Datafiles

Using a backup control file that is older than the current datafiles causes SCN inconsistencies. Oracle cannot reconcile the checkpoint SCN differences, leading to RESETLOGS failure.

-- Check SCN values across datafiles and control file
SELECT NAME, CHECKPOINT_CHANGE#, LAST_CHANGE#
FROM V$DATAFILE;

SELECT CHECKPOINT_CHANGE#, RESETLOGS_CHANGE#
FROM V$DATABASE;

-- Recover using backup control file
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

-- After applying all available archive logs, open database
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

3. Broken Archive Log Chain During Incomplete Recovery

A missing or out-of-sequence archive log breaks the recovery chain. Oracle cannot apply logs past the gap, leaving the database in a state where RESETLOGS cannot proceed cleanly.

-- Identify archive log sequence gaps
SELECT SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, STATUS, NAME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

-- Perform point-in-time recovery up to last valid log
RECOVER DATABASE UNTIL CHANGE 9876543;

-- Or recover until a specific time
RECOVER DATABASE UNTIL TIME '2024-01-15 13:00:00';

-- Open the database after recovery
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use RMAN for a controlled and reliable recovery workflow:

-- RMAN incomplete recovery example
RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2024-01-15 13:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

-- Verify database status post-recovery
SELECT STATUS, CHECKPOINT_CHANGE#, RESETLOGS_CHANGE#
FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

Always check the alert.log and trace files immediately when ORA-01547 appears, as companion errors like ORA-01194 or ORA-00283 will reveal the specific file or log causing the issue.

Prevention Tips

1. Multiplex Online Redo Logs and Back Up the Control File Regularly

Always configure at least two members per redo log group on separate disks. Back up the control file after every structural change.

-- Add redo log members for multiplexing
ALTER DATABASE ADD LOGFILE MEMBER
  '/disk2/redo/redo01b.log' TO GROUP 1;

-- Backup control file after structural changes
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control.ctl';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/backup/control.sql';
Enter fullscreen mode Exit fullscreen mode

2. Automate RMAN Backups and Validate Archive Log Continuity

Schedule regular RMAN full backups and archive log backups. Always run CROSSCHECK after backup jobs to ensure no gaps exist in the archive log chain.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE OBSOLETE;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01194 – Datafile needs more recovery to be consistent
  • ORA-00283 – Recovery session cancelled due to errors
  • ORA-01152 – File not restored from a sufficiently old backup
  • ORA-00600 – Internal error often accompanying severe recovery failures

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)