DB Recovery Issue

[Former Member]

All,

I have an issue while starting the DB. I have Oracle 9.2.0.8,32bit

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
  2  ;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'G:\ORACLE\DEV\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'


SQL> recover datafile 'G:\ORACLE\DEV\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'
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 606018081 generated at 12/07/2009 11:58:11 needed for thread
1
ORA-00289: suggestion : G:\ORACLE\DEV\SAPARCH\DEVARCHARC00511.001
ORA-00280: change 606018081 for thread 1 is in sequence #511


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'G:\ORACLE\DEV\SAPARCH\DEVARCHARC00511.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 'G:\ORACLE\DEV\SAPARCH\DEVARCHARC00511.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.

Now when i try to find the log sequence,

SQL> select * from v$log
  2  ;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
        15          1        508  104857600          2 YES INACTIVE
    605958033 07-DEC-09

        16          1        510  104857600          2 YES INACTIVE
    605998065 07-DEC-09

        17          1        511  104857600          2 NO  CURRENT
    606018081 07-DEC-09


    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
        18          1        509  104857600          2 YES INACTIVE
    605978049 07-DEC-09

The recovery needs the sequence #511 to complete the recover. But its still in current state.

Please suggest how I can recover the database;

[Former Member · Accepted solution]

What has happened, that you need to use a backup controlfile?

Check if your online redologs are available at X:\ORACLE\DEV\ORIGLOG[A|B]

If you lost the online redologs then a "pretended" recovery could work:

- recover database using backup controlfile until cancel

-> CANCEL

- alter database open resetlogs;

But you will loose the latest data, because of the incomplete recovery!

Cheers Michael

[Former Member · Accepted solution]

Yes, Michael. The redo log files are available.

Also while doing,

 
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 606018081 generated at 12/07/2009 11:58:11 needed for thread
1
ORA-00289: suggestion : G:\ORACLE\DEV\SAPARCH\DEVARCHARC00511.001
ORA-00280: change 606018081 for thread 1 is in sequence #511

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: 'G:\ORACLE\DEV\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

ORA-01112: media recovery not started

This actually goes back to my first step. Issue is with the log sequence. I dont know why its asking for the recent and current log sequence file to make the recovery possible.

Also, I am ready to lose the recent data. I just want to open the database

[Former Member · Accepted solution]

Can you try to give the full path to one online redolog with the number 17 at the recovery prompt?

Cheers Michael

[Former Member · Accepted solution]

Thanks a ton michael...

It really helped,' the full path to the redo log location'.

[sunny_pahuja2]

Hi,

when your are giving command recover database using backup controlfile then use cancel. it will cancel the recovery and generates the new log sequence and it will solve your problem.

Thanks

Sunny