OraFAQ Forum: Backup & Recovery » Recover database using backup controlfile

Home » RDBMS Server » Backup & Recovery » Recover database using backup controlfile (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Recover database using backup controlfile [message #135193]

Tue, 30 August 2005 07:12

paudhya
Messages: 3
Registered: August 2005

Junior Member

Please check out the following backup/recovery scenario.

1. initially, there were only 5 rows in SCOTT.TEST Table.
2. i shutdown the database and took the cold backup.
3. the database is in archivelog mode.
4. i started up the database, and added 3 more rows to the SCOTT.TEST table.
Committed it.
5. switched the logfiles a few number of times.
6. shutdown the database
7. deleted ALL DATAFILES, CONTROLFILES, online REDOLOGFILES
8. i already have the CREATE CONTROLFILE SCRIPT for this database.
9. startup nomount the database. the parameter file is the same as before.
10. Restored the datafiles, created the controlfile using the CREATE CONTROLFILE
SCRIPT.
11. Recovered the database, by applying the archivelogs.
12. i am not able to open the database, so:
13. shutdown immediate the database
14. startup nomount the database
15. create control file again
16. open the database resetlogs..
i get all the data which was in the archives.

My question is why didnt it open the database after i had done the recovery? why do i have to shutdown the database, and then recreate the control file to do the same?
Please note that there were no recovery steps between 13 and 16.

The spoolfile if required is attached:

Attachment: spoolfile.txt
(Size: 7.61KB, Downloaded 3887 times)

Report message to a moderator

Re: Recover database using backup controlfile [message #135198 is a reply to message #135193]

Tue, 30 August 2005 07:22

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

Control file is created with norestelogs option only when the current version of all online logs are available.

Which option did you chose: resetlogs or noresetlogs while recreating the controlfile.
If resetlogs option then you need to open database with alter database open resetlogs command only.

Regds
Girish

Report message to a moderator

Re: Recover database using backup controlfile [message #135319 is a reply to message #135198]

Wed, 31 August 2005 00:04

paudhya
Messages: 3
Registered: August 2005

Junior Member

The control file is created with RESETLOGS option. I had lost my online redologfiles, so anyway, the RESETLOGS is the only option available to me. If you check the spoolfile attached, you would notice that I did the recovery by applying all the archivelog files. But since I didnt have any online redologfile, i couldnt get it applied. After the recovery, i tried to open the database in RESETLOGS Mode, which failed. So, I shutdown the database, STARTUP NOMOUNT, then created the controlfile again, using the same syntax as before. Now I didnt give any RECOVER command, since i have already applied the archives. So, i simply say ALTER DATABASE OPEN RESETLOGS; This works, and i get the data which was in archives.

My question is that why do i have to shutdown and then restart the database by recreating the controlfiles?

Report message to a moderator

Re: Recover database using backup controlfile [message #135328 is a reply to message #135198]

Wed, 31 August 2005 01:40

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

Why you gave "recover database using backup controlfile;" after creating the controlfile?

Instead "recover database;" should have been given.

Regds
Girish

Report message to a moderator

Re: Recover database using backup controlfile [message #135331 is a reply to message #135328]

Wed, 31 August 2005 01:50

paudhya
Messages: 3
Registered: August 2005

Junior Member

initially I gave RECOVER DATABASE, but it failed saying that i should be using USING BACKUP CONTROLFILE clause

Report message to a moderator

Re: Recover database using backup controlfile [message #135532 is a reply to message #135331]

Thu, 01 September 2005 00:59

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

HI

I tried emulating the scenario on my system. I too faced same issue i.e. needed to bounce the database whenever i tried recovery using backup controlfile with resetlog option. But when I tried recovery with noresetlogs option in controlfile creation, didn't faced any such issue.

Working on same, if I do get any hint, will let you know.

neverthless, question is really good one, allowed me to refresh the recovery concepts.

Regds
Girish

[Updated on: Thu, 01 September 2005 00:59]

Report message to a moderator

Re: Recover database using backup controlfile [message #155389 is a reply to message #135532]

Sun, 15 January 2006 21:27

quytc
Messages: 81
Registered: November 2005
Location: Viet Nam

Member

Hi,
I have got spoolfile.txt. But I don't understand Why you don't copy Logfile, control file from colbackup directory to database directory

Report message to a moderator

Re: Recover database using backup controlfile [message #210771 is a reply to message #135193]

Fri, 22 December 2006 04:49

talek
Messages: 3
Registered: December 2006
Location: Romania, Iasi

Junior Member

Hi,

I've just took a look at your spool file. You have problems because you didn't specified CANCEL when your are asked for the last archive. The ALTER DATABASE OPEN RESETLOGS will do its job only if the previous RECOVER command will successfully finish. Below is the recover scenario:

SQL> recover database using backup controlfile;
ORA-00279: change 793731 generated at 12/22/2006 12:00:32 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf
ORA-00280: change 793731 for thread 1 is in sequence #1


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

ORA-00279: change 794266 generated at 12/22/2006 12:09:00 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf
ORA-00280: change 794266 for thread 1 is in sequence #2
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_1_609804799.dbf' no longer
needed for this recovery


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

ORA-00279: change 794268 generated at 12/22/2006 12:09:03 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf
ORA-00280: change 794268 for thread 1 is in sequence #3
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_2_609804799.dbf' no longer
needed for this recovery


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

ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4
ORA-00278: log file
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_3_609804799.dbf' no longer
needed for this recovery


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

ORA-00308: cannot open archived log
'/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


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:
'/opt/oracle/oradata/DDB/datafile/o1_mf_system_2rlg719d_.dbf'

So, i reproduce the same situation as yours! But, as I said, it is important to specify CANCEL when you are asked for the last log.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

HTH.
Cheers!

Report message to a moderator

Re: Recover database using backup controlfile [message #385274 is a reply to message #210771]

Sun, 08 February 2009 22:14

saenal
Messages: 1
Registered: February 2009

Junior Member

I have the same problem, but the result is like this one:

Specify log: {<RET>=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: '/xxxx/xxxxxxx/XXXX/system01.dbf'

Report message to a moderator

Re: Recover database using backup controlfile [message #385275 is a reply to message #135193]

Sun, 08 February 2009 22:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Did you read & follow the bouncing RED ball advice as stated in URL below?
http://www.orafaq.com/forum/f/5/136107/

Report message to a moderator

Re: Recover database using backup controlfile [message #385319 is a reply to message #385274]

Mon, 09 February 2009 01:40

talek
Messages: 3
Registered: December 2006
Location: Romania, Iasi

Junior Member

Saenal,

it seems that you need additional redolog to be applied. How about applying the redolog file from the current redolog group too? Does this make any difference?

Report message to a moderator

Re: Recover database using backup controlfile [message #462659 is a reply to message #135319]

Fri, 25 June 2010 08:37

akvverma2002
Messages: 1
Registered: June 2010

Junior Member

dear paudhya,
I have no answer to your question. Even I had the same scenario. I was able to recover the database by following your post of creating the control file again and opening the database without using the recover database again. It worked. Thanks a lot.
Anil (Western Railway, Mumbai).

Report message to a moderator

Re: Recover database using backup controlfile [message #462689 is a reply to message #462659]

Fri, 25 June 2010 12:35

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

@akvverma2002; Thanks for your suggesstion. Before post please review/check last update date.

Report message to a moderator

Previous Topic:	How to schedule rman hot backup daily,,,
Next Topic:	Rman restore

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 19:01:53 CST 2024