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

Printable View

04-30-2002, 07:52 PM
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?
05-01-2002, 12:14 AM
sanjay92

What about
alter database open noresetlogs;

Sanjay
05-01-2002, 12:28 AM
vendechukp

tell us whether ur database is started or down
05-01-2002, 02:09 PM
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.
05-01-2002, 02:18 PM
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'
05-01-2002, 02:31 PM
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.
05-01-2002, 02:55 PM
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
05-01-2002, 03:10 PM
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
05-01-2002, 04:39 PM
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>
05-01-2002, 04:48 PM
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..
05-01-2002, 05:02 PM
pando

Are you sure you are recovering a database? You have deleted the control file but that was all you did, you have not lose a single datafile. So basically this is:

Have not backed up the control file using the TO TRACE option, but used the TO filename option of ALTER DATABASE BACKUP CONTROLFILE

you do

Use the control file copy to obtain SQL output. Copy the backup control file and execute STARTUP MOUNT before ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, edit the TO TRACE output to reflect the structural change

This is cut & pasted from Oracle Docs
05-01-2002, 05:17 PM
abongwa

Thank you very much!
You said: "This is cut & pasted from Oracle Docs"

Could you please tell me which chapter? I would like to read the details!

Thanks a lot! So, the conclusion is that you cannot use the binary backup of the controlfile directly to recover the db. You have to use it to create a trace file and then use that trace file to build a new control file, then continue the recovery. (In my scenario, there is no recovery required. It simply is a scenario of lossing all current control files.)

Could you please tell me which chapter? I would like to read the details!
05-01-2002, 05:21 PM
thanigai

Hi abonga can you do this...
......
startup mount
select * from v$recover_file; (note down the file name and #)
alter database recover datafile file#
(will ask for archive files/redofiles)
auto
alter database open;(if you get error user alter database open using backup controlfile)
.....
Hope this work.
can you post the result.
Thanks.
Thanigaivasan.
05-01-2002, 05:22 PM
thanigai

Sorry, I typed your name wrongly Abongwa.
Thanigaivasan
05-01-2002, 05:26 PM
abongwa

Here is the doc that explain ALL:

http://otn.oracle.com/docs/products/...scen.htm#11872

See the topic of Table 6-1 Options for Creating the Control File

I think that's the ONLY right way to use a binary backup of controlfile. All other use is useless. :>
05-01-2002, 05:51 PM
pnrdba

Ur right Narayanan. Get the current logfile from v$log, and then apply that logfile with the exact path.
First , give this command,
Recover database using backup controlfile. It asks for the recovery of the database with a particular file. Just supply the file name which is a current one. Then u get a message like
'Media Recovery Complete'

Now open database with resetlogs.

best of luck

tia
05-01-2002, 05:51 PM
thanigai

I felt like sharing this....site..
http://metalink.oracle.com/metalink/...1&p_showHelp=1

Thanks.
Thanigaivasan
05-01-2002, 05:57 PM
sln81

Hi Abongwa,

When you are trying to recover the database with 'ALTER DATABASE RECOVER UNTIL CANCEL USING BACKUP CONTROLFILE' ... this is an incomplete recovery.
To do this, you should copy all the datafiles from sufficiently recent backup (the files should correspond to the structure of your database when you backed up your control file).
Then perform the recovery with the above command.
When you are prompted for the current log sequence number, then cancel the recovery.
Then use the command 'ALTER DATABASE OPEN RESETLOGS' to open the database.

Hope you'll succeed with this
05-01-2002, 06:04 PM
pando

I dont understand why you peeps are insisting on applying logs when there is nothing to recover heh
05-02-2002, 02:50 AM
oravijay

Hi,

I totally agree with Pando.

He has only deleted the Controlfile. Which means that all the datafiles and the logfiles are intact.
Just recreate the controlfile and open the database normally.

Vijay.
05-02-2002, 10:41 AM
vijay8282

Just a try

Hi

Just a try :

Does anyone observed problem with syntax. there is no gap between datafile and its name ---o---

alter database recover datafile'/export/home/u03/oradata/noya/system01.dbf' using
-----o-----
backup controlfile;

check it out and revert.

Regards
05-02-2002, 10:53 AM
jlakhani

I think the way to go with that is...
if you have archive logging on then the binary ctl file will work but if you dont have archiving then you will have to cp the backup binary ctl file to its respective directories.

svmgrl
startup mount
alter database backup control file to trace
shutdown immediate

delete the control files you just created then run the trace file in as .sql. I just did this scenario at work and I got it right. you can drop me a line if you got a ?

-Jigar
05-02-2002, 02:39 PM
abongwa

Quote:

Originally posted by jlakhani
I think the way to go with that is...
if you have archive logging on then the binary ctl file will work but if you dont have archiving then you will have to cp the backup binary ctl file to its respective directories.

svmgrl
startup mount
alter database backup control file to trace
shutdown immediate

delete the control files you just created then run the trace file in as .sql. I just did this scenario at work and I got it right. you can drop me a line if you got a ?

-Jigar

EVEN IF you are in archivelog mode, you still have to do the same steps you said here. I have NEVER been able to open the database successfully by restoring the binary backup of the controlfile. (even the other stuff are intact)

Please confirm with your thought. Thank you.
05-03-2002, 02:48 AM
sjadhavdba

Quote:

Originally posted by pando

Use the control file copy to obtain SQL output. Copy the backup control file and execute STARTUP MOUNT before ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, edit the TO TRACE output to reflect the structural change

This is cut & pasted from Oracle Docs

I can recover the database without using NORESETLOGS in the above statement.

can u tell me PANDO when exactly this RESETLOGS/ NORESETLOGS come into picture.

i think only if database in ARCHIVE LOG MODE i'm i right. or i'm missing something
05-03-2002, 03:10 AM
oravijay

Hi,

Whenever u do a INCOMPLETE RECOVERY then u should use RESETLOGS. INCOMPLETE RECOVERY
is possible only when the database is running in ARCHIVELOG.

Also when u rename your database you should open the database with RESETLOGS.

Vijay.
05-03-2002, 05:11 PM
abongwa

Oracle Doc said:

"The RESETLOGS option is required after incomplete media recovery OR recovery using a backup control file."

http://otn.oracle.com/docs/products/...ormi.htm#22669

:)
05-06-2002, 12:27 PM
ndisang

Abongwa,
What happened with this experiment. Anxious to find out.