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.
