This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

HowTo: Recover Oracle DB with ORA-01547 after RESETLOGS Failed

Scenario: You have restored a database from a backup but part way through recovery, you didn’t have all the archive redo logs.
Your recovery is an automated script that then progressed on to OPEN RESTLOGS, which failed.

Now you have all the archive logs and you want to re-run recovery, but you get ORA-01547 plus ORA-01194.

If you are certain that you now have all the archive logs (maybe you forgot to copy all of them), you need to restart recovery:

SQL> shutdown abort;
SQL> startup mount exclusive;
SQL> recover automatic database using backup controlfile until cancel;

You now get the error:

ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

The database is looking for redo information generated during the previous recovery session.
During a recovery, redo information is generated because the database is replaying the undo records of transactions to be rolled back, but these records may need to be rolled back. So you get redo records.

Unfortunately, because your database is in a precarious state, it doesn’t quite know where to look for the redo records.
So, all you need to do is tell it where the redo logs are located (your database probably won’t have performed a log switch yet).

First, cancel the recovery:

SQL> CANCEL

For checking purposes, it’s good to confirm that only SYSTEM tablespace is needing recovery by querying the current SCNs across all datafiles:

SQL> select distinct fhscn SCN from x$kcvfh;

SCN
----------------
5996813573990

Again, for checking purposes, you can see that one of the datafiles has a status of != 4. (4= consistent):

SQL> select distinct fhsta STATUS from x$kcvfh;

STATUS
----------
4
8196

You can see that this is the SYSTEM datafile (filenumber =1):

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh where fhsta = 8196;

FILENUMBER STATUS SCN SEQUENCE
---------- ---------- ---------------- ----------
1 8196 5996813573990 1

To be able to provide the first redo log to the recovery process, you should query your log members.
I have found that usually, the recovery only needs the very first log member listed when you run the following query (I guess this is the “active” log member at the time the RESETLOGS was called):

SQL> select member from v$logfile;

MEMBER
—————————————————-
/oradata/PROD/log_a/log4a_PROD.log            <<– This one.
/oradata/PROD/log_b/log4b_PROD.log
/oradata/PROD/log_a/log3a_PROD.log
/oradata/PROD/log_b/log3b_PROD.log
/oradata/PROD/log_a/log2a_PROD.log
/oradata/PROD/log_b/log2b_PROD.log
/oradata/PROD/log_a/log1a_PROD.log
/oradata/PROD/log_b/log1b_PROD.log

8 rows selected.

Now you can call the recovery process again.

This time, when you are prompted for the archive log name, enter the redo log member name & path you got from the previous SQL:
SQL> recover automatic database using backup controlfile until cancel;
 
ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/PROD/log_a/log4a_PROD.log

Log applied.
Media recovery complete.

Now open your database:

SQL> alter database open resetlogs;
 

Database altered.

SQL> exit

You should now run an immediate FULL backup using your preferred method.


4 thoughts on HowTo: Recover Oracle DB with ORA-01547 after RESETLOGS Failed

  1. Many, many thanks, you made my day! Had Oracle 11gR2 database in NOARCHIVELOG mode, until I read your post just didn't think of using REDOxx.log in RECOVER UNTIL CANCEL. Used it and all is perfect.

  2. This has solved a long running issue for me. I used to get it while setting up a test DB and would always give up and use another backup that hopefully did not have that problem. Thank you!

Add Your Comment

* Indicates Required Field

Your email address will not be published.

*