media recovery

[Former Member]

Hi,

I am able create a control file successfully. After that when I try to open the database it was asking media recovery.

I am not able to open the database. Please find the steps that I have performed.

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:

'L:\BACKUPPR1-22AUG09\L\ORACLE\TST\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 190686825 generated at 08/22/2009 09:46:35 needed for thread

1

ORA-00289: suggestion :

L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001

ORA-00280: change 190686825 for thread 1 is in sequence #7735

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:

'L:\BACKUPPR1-22AUG09\L\ORACLE\TST\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

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:

'L:\BACKUPPR1-22AUG09\L\ORACLE\TST\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

Our Environment:

SAP: 4.6C

Oracle: 10.2.0.2.0 x-64 bit

OS: windows 2003.

Thanks and Regards

Satya

[stefan_koehler · Accepted solution]

Hello Satya,

i think that this is an online backup, right?

You need to recover the database to a point that is post the online backup.

For example if your online backup runs from 1 a.m. until 2 a.m... then you need to recover your database after 2 a.m.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

The backup was offline but I missed few redo logs and archive logs(all the files).

So, I created the missed redo logs manually and copied the archive logs(copied from source database when database was running).

I have a backup of all archive logs since from the day offline backup.

Is there anyway I can open the database?

Thansk and Regards

Satya

[former_member204746 · Accepted solution]

recover database using backup controlifle;

[Former Member · Accepted solution]

Hi Eric,

Please find the output.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 190686825 generated at 08/22/2009 09:46:35 needed for thread

1

ORA-00289: suggestion :

L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001

ORA-00280: change 190686825 for thread 1 is in sequence #7735

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

cancel

Media recovery cancelled.

SQL> alter database open

2 ;

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:

'L:\BACKUPPR1-22AUG09\L\ORACLE\TST\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

-

---

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 190686825 generated at 08/22/2009 09:46:35 needed for thread

1

ORA-00289: suggestion :

L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001

ORA-00280: change 190686825 for thread 1 is in sequence #7735

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

ORA-00308: cannot open archived log

'L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Thanks and Regards

Satya

[anindya_bose · Accepted solution]

Enter

Recover database using backup controlfile until cancel.

Then select "AUTO"

Then Enter.

[anindya_bose · Accepted solution]

Enter

Recover database using backup controlfile until cancel.

Then select "AUTO"

Then Enter.

Then "Alter database open resetlogs;

Edited by: Anindya Bose on Aug 27, 2009 8:17 PM

[Former Member · Accepted solution]

Hi,

Kindly find the output.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 190686825 generated at 08/22/2009 09:46:35 needed for thread

1

ORA-00289: suggestion :

L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001

ORA-00280: change 190686825 for thread 1 is in sequence #7735

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

AUTO

ORA-00308: cannot open archived log

'L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log

'L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

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:

'L:\BACKUPPR1-22AUG09\L\ORACLE\TST\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

Thanks and Regards

Satya

[anindya_bose · Accepted solution]

Please check note

https://service.sap.com/sap/support/notes/546006 and 491160

Did you try "alter database open resetlogs;" earlier? I mean before successful recovery?

Try this once agian then...

alter database open resetlogs;

[former_member204746 · Accepted solution]

you need file L:\BACKUPPR1-22AUG09\M\ORACLE\TST\SAPARCH\TSTARCH\ARC07735_0668643243.001

without it, no recovery possible.

[Former Member · Accepted solution]

Thank you very much Eric

Thanks and Regards

Satya