Can you use a binary backup of controlfile to recover a db

[abongwa]

This is only for the proof of concept: Using a binary backup of controlfile to recover the database.

Here is what I have done:

1. The database is healthy, up and running.
2. alter database backup controlfile to '/export/home/oracle/bkp.ctl';
3. shutdown abort
4. rm /u01/oradata/control01.ctl (This is the only ctl file.)
5. cp /export/home/oracle/bkp.ctl /u01/oradata/control01.ctl
6. Startup mount;

Then:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 478967119 generated at 04/30/2002 16:32:36 needed for thread
1
ORA-00289: suggestion : /export/home/u03/arch/noya/arch_1_635.arc
ORA-00280: change 478967119 for thread 1 is in sequence #635


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/export/home/u01/oradata/noya/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/export/home/u01/oradata/noya/system01.dbf'

Could you please advise which step go wrong?

[sanjay92]

What about
alter database open noresetlogs;

Sanjay

[vendechukp]

tell us whether ur database is started or down

[abongwa]

For sanjay92:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/export/home/u01/oradata/noya/system01.dbf'

For vendechukp:

I intentionally shutdown abort the db so that I can test if the binary backup can recover the db. So, the db is currently down.

[abongwa]

For sanjay92:

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 478885897 generated at 04/30/2002 17:52:04 needed for thread
1
ORA-00289: suggestion : /export/home/u03/oradata/noya/arch/arch_1_470.arc
ORA-00280: change 478885897 for thread 1 is in sequence #470


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/export/home/u03/oradata/noya/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/export/home/u03/oradata/noya/system01.dbf'

[alenz]

have you tried leaving off the "until cancel" part of the recover command. this will do real recovery and not "fake" recovery. Or let Oracle try to apply the log.

[abongwa]

None of them is working:

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using BACKUP CONTROLFILE
ORA-00279: change 478885897 generated at 04/30/2002 17:52:04 needed for thread
1
ORA-00289: suggestion : /export/home/u03/oradata/noya/arch/arch_1_470.arc
ORA-00280: change 478885897 for thread 1 is in sequence #470


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/export/home/u03/oradata/noya/arch/arch_1_470.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

*****************
Note: arch_1_470.arc has not been archived.


SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-01649: operation not allowed with a backup controlfile

[Knarayan]

probably this is because the datafile 1 is in recover status, try running recover datafile/alter datafile recover, i don't remember the command exactly, and then alter database open resetlogs, i think that should do the trick..

HOpe this helps

[abongwa]

I am sorry. That does not work either. Please, if somebody has actually done this before, please help. In the past, I always assume this will work. Now, when I try it actually, it's not that simple.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/export/home/u03/oradata/noya/system01.dbf'


SQL> alter database recover datafile /export/home/u03/oradata/noya/system01.dbf;
alter database recover datafile /export/home/u03/oradata/noya/system01.dbf
*
ERROR at line 1:
ORA-02236: invalid file name


SQL> alter database recover datafile'/export/home/u03/oradata/noya/system01.dbf';
alter database recover datafile'/export/home/u03/oradata/noya/system01.dbf'
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> alter database recover datafile'/export/home/u03/oradata/noya/system01.dbf' using backup controlfile;
alter database recover datafile'/export/home/u03/oradata/noya/system01.dbf' using backup controlfile
*
ERROR at line 1:
ORA-00274: illegal recovery option USING


SQL> recover datafile'/export/home/u03/oradata/noya/system01.dbf' using backup controlfile
ORA-00274: illegal recovery option USING


SQL>

[Knarayan]

I think this is the way to go:
go uptill startup mount,
do select * from v$log to find out which is the current/active log,
then do recover database using backup controlfile
when it prompts for the arc file just give the current redolog file instead, i think this should complete the recovery
and then do a
alter database open resetlogs;

Hope this works, do let us know..