1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Database Cannot Open

Discussion in 'Security, Backup and Recovery' started by tutawala, Apr 12, 2010.

  1. tutawala

    tutawala Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have Oracle Real Application Cluster (RAC) Database 10g installed on Windows 2003 Server

    Database can be in mount state using RMAN
    However, Alter database open; gives an error

    RMAN-03002: failure of alter db command at 04/07/2010 10:06:53
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: 'N:\ORACLE\DEV\DB\APPS_ST\DATA\SYSTEM01.DBF'

    Then, tried restoring data from previous backup taken using RMAN.
    RESTORE DATABASE;
    The RMAN freezes, however after few hours the Alert log file shows that RMAN has restored all the 44 datafiles.

    Then tried recovery of data using RMAN
    RECOVER DATABASE; which too failed with the following error:
    ORA-00283: recovery session canceled due to errors
    RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed datafile 1
    ORA-00283: recovery session canceled due to errors
    ORA-00314: log 2 of thread 1, expected sequence# 289 doesn't match 0
    ORA-00312: online log 2 thread 1: 'N:\ORACLE\DEV\DB\APPS_ST\DATA\LOG02B.DBF'
    ORA-00314: log 2 of thread 1, expected sequence# 289 doesn't match 0
    ORA-00312: online log 2 thread 1: 'N:\ORACLE\DEV\DB\APPS_ST\DATA\LOG02A.DBF'

    Could anyone please help me out with this Issue
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How did you shutdown this database prior to RMAN backup? The mesage states you need to recover the database prior to opening it; have you even tried that? At the mount state do this:

    Code (SQL):
    SET linesize 132
    COLUMN member format a60

    SELECT lf.GROUP#, lf.member, l.SEQUENCE# FROM v$logfile lf, v$log l WHERE lf.GROUP# = l.GROUP# ORDER BY 3;
    Unfortunately an atempt was made to open the database resetlogs (I believe) which set all of your sequence numbers to 0 (which should be evident from the query output). You can still recover the database using:

    recover database using backup controlfile until cancel

    and by using the redo log list generated by the above query be able to recover and then open the database.
     
    tutawala likes this.
  3. tutawala

    tutawala Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Database was not shutdown as Online backup was taken.
    I have tried restore and recover of the database, but failed.

    I have even tried with the following command
    SQL> CONN / AS SYSDBA
    SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

    I was first asked about the following archive log file. So, i gave its location as:
    N:\oracle\DEV\db\apps_st\data\Archives\DEVDFA6E9F5_00289_0673919223.001
    Then, in a few seconds i was asked about the following archive log file:
    N:\oracle\DEV\db\apps_st\data\Archives\DEVDFA6E9F5_00105_0673919223.002
    After entering this file location, SQLPLUS got hanged. I waited for an hour but no response. Then i had to close the SQLPLUS. I even checked the alert log file, but there was no error reported.

    This happened with RMAN also earlier. The RMAN gets hanged while restore and recovery.

    I am thinking of restoring the database with image copy (cold backup) that i had taken before this problem raised.

    What could be the steps that i can follow to restore the coldbackup keeping in mind that i have Oracle RAC 10 g with 2 instances on Windows 2003 server and running with Oracle Apps R12?

    Detailed steps would be appreciated.

    As it is test system, I dont care if i loose data, i just need to open the database so that i can carry on with testing.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Recovery takes time; the first archive log most likely had nothing to recover and Oracle proceeded to ask for and process the next log in the sequence. Such operations take time; let the process run. If the archive log is somehow corrupted you'll be notified and it will then be necessary to find another copy of it (you are archiving to more than one location?) or, if that's not possible recreate the test database.