recover database using backup controlfile until cancel;

[Former Member]

Hello,

after a restore of a database with hp dataprotectore the DB was in inconsistential condition and I´ve started recovering every .data file which has to be revovered: (redolog logs from the allonline backup session where restored already)

SQL> startup

ORACLE instance started.

Total System Global Area 2365587456 bytes

Fixed Size 2067528 bytes

Variable Size 1090519992 bytes

Database Buffers 1258291200 bytes

Redo Buffers 14708736 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'F:ORACLEC12SAPDATA1SYSTEM_1SYSTEM.DATA1'

SQL> recover datafile 'F:ORACLEC12SAPDATA1SYSTEM_1SYSTEM.DATA1'

ORA-00279: change 79104569 generated at 04/14/2010 00:30:13 needed for thread 1

ORA-00289: suggestion : E:ORACLEC12ORAARCHC12ARCH1_1617_704132954.DBF

ORA-00280: change 79104569 for thread 1 is in sequence #1617

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

ORA-00279: change 79107667 generated at 04/14/2010 01:19:08 needed for thread 1

ORA-00289: suggestion : E:ORACLEC12ORAARCHC12ARCH1_1618_704132954.DBF

ORA-00280: change 79107667 for thread 1 is in sequence #1618

ORA-00278: log file 'E:ORACLEC12ORAARCHC12ARCH1_1617_704132954.DBF' no

longer needed for this recovery

Log applied.

Media recovery complete.

Edited by: Martin Haidvogel on Apr 21, 2010 4:45 AM

[Former Member]

I canceled revovering furhter datafiles and give the following command:

SQL> alter database backup controlfile to trace noresetlogs;

Database altered.

for now on it´s no longer possible to start the DB, alert log states the following:

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 42: 'F:ORACLEC12SAPDATA2SR3_24SR3.DATA24'

ORA-01157: cannot identify/lock data file 42 - see DBWR trace file

ORA-01110: data file 42: 'F:ORACLEC12SAPDATA2SR3_24SR3.DATA24'

Opening the database with this command is either not successfull:

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Does any one know how get the database open again and continuin recovery like in the beginning:

SQL> startup

ORACLE instance started.

Total System Global Area 2365587456 bytes

Fixed Size 2067528 bytes

Variable Size 1090519992 bytes

Database Buffers 1258291200 bytes

Redo Buffers 14708736 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'F:ORACLEC12SAPDATA1SYSTEM_1SYSTEM.DATA1'

SQL> recover datafile 'F:ORACLEC12SAPDATA1SYSTEM_1SYSTEM.DATA1'

ORA-00279: change 79104569 generated at 04/14/2010 00:30:13 needed for thread 1

ORA-00289: suggestion : E:ORACLEC12ORAARCHC12ARCH1_1617_704132954.DBF

ORA-00280: change 79104569 for thread 1 is in sequence #1617

Sincerely

[Former Member]

Hi,

Once recovery is complete you must use the below command to open the database.

SQL>alter database open resetlogs;

Otherwise it will keep asking for recovery...

Steps would be:

1.

SQL> recover database using backup controlfile until cancel;

Once all datafile recovered, then

SQL>alter datbase open resetlogs;

It will reset the logs as per the current state of recovered database and open the database.

Regards.

Rajesh Narkhede

[Former Member]

Hi,

thank you very much for your answer, I´ve tried this command and it gives the alert that datafile is from a sufficiently old backup:

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'F:ORACLEC12SAPDATA1SYSTEM_1SYSTEM.DATA1'

Asking google for this error a revoer database has to be done

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Do you know how the command for this operation has to be done?

Sincerely

[Former Member]

When I try to recover database using backup controlfile until cancel, it cannot identify sapdata file 42.

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 42: 'F:\ORACLE\C12\SAPDATA2\SR3_24\SR3.DATA24'

ORA-01157: cannot identify/lock data file 42 - see DBWR trace file

ORA-01110: data file 42: 'F:\ORACLE\C12\SAPDATA2\SR3_24\SR3.DATA24'

Sincerly

[Former Member]

Hi,

I thing the SCN no. of restored files got changed when u attempt to start the database..

Please do the recovery of all datafiles which it ask for, and then open the database with RESETLOGS option.

(I would suggest, restore complete database from the BACKUP and then attempt recovery for greater success...)

It will work...

ORA-01110: data file 42: 'F:\ORACLE\C12\SAPDATA2\SR3_24\SR3.DATA24'
ORA-01157: cannot identify/lock data file 42 - see DBWR trace file
ORA-01110: data file 42: 'F:\ORACLE\C12\SAPDATA2\SR3_24\SR3.DATA24'

For above error check that the datafile is exist at given location..

Regards.

Rajesh Narkhede

Edited by: Rajesh Narkhede on Apr 21, 2010 9:09 AM

[Former Member]

Hi,

thank you very much!

Yes i agree the fastest soloution would be to restore the backup again and just recover the database (recover database;) after startup mount; command.

Sincerely

[Former Member]

Hi Martin,

Go ahead !...

Revert in case of any problem.

Regards.

Rajesh Narkhede

[Former Member]

Hi,

the mentioned datafile: 'F:ORACLEC12SAPDATA2SR3_24SR3.DATA24' doens´t exits on filesystem.

There is a datafile with a `X` in the name beginning: 'F:ORACLEC12SAPDATA2SR3_24XSR3.DATA24'

Should i try to rename this datafile to: 'F:ORACLEC12SAPDATA2SR3_24SR3.DATA24' ?

Sincerely

[Former Member]

Yes,

It look like the same file...

Try to rename that file and see the result...

Regards.

Rajesh Narkhede

[kishore_soma]

Dear Martin,

Good to hear that you were able to restore using data protector.

The funda is u have not applied all the archve logs generated during the backup. I.e., from begin backup mode to end baup mode. Which is very much essential. Thats why u are getting error, system.data1 needs mopre recover.

Also for this above problem. Try renaming the file, else restore that particular file from the same backup (you have that option of restoring only 1 file with DP). Then start recovery again using backup controlfile. Also restore some more archive logs and recover the database.

Hope this will solve your problem

Regards,

Kishore Soma