This discussion is archived
8 Replies Latest reply: Nov 16, 2012 7:03 AM by sb92075 RSS

Alter database open

494959 Newbie
Currently Being Moderated
We are doing backup verification.

Now after the restore /receover database/I am trying to open the datbase with alter database open;
Giving the below error.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/ASD/sapdata1/system_1/system.data1'
  • 1. Re: Alter database open
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Error:   ORA 1190 
    Text:    control file or datafile <name> is from before the last RESETLOGS 
    -------------------------------------------------------------------------------
     Cause: An attempt was made to perform media recovery when the redo log reset information in a datafile does not match the control file.
                Either the datafile or the control file must be a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS.
     Action: Restore the file from a more recent backup.
  • 3. Re: Alter database open
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Or you could use this solution
    1-SQL > startup mount;
    2-SQL > alter database datafile <file#> offline drop;
    3-SQL > alter database open;
    But you data will be lost .
  • 4. Re: Alter database open
    Think_dba Journeyer
    Currently Being Moderated
    I think u can't since its a system datafile..
  • 5. Re: Alter database open
    user296828 Expert
    Currently Being Moderated
    Can you share your high level backup, restore and recovery steps?
  • 6. Re: Alter database open
    494959 Newbie
    Currently Being Moderated
    hi , I have taken the backup 9-11-2012,

    I am trying to do time in recovery with until clause.

    I have restored the database and then recover database.
    The below error I got :-

    Oracle Error:
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01190: control file or data file 1 is from before the last RESETLOGS
    ORA-01110: data file 1: '/oracle/PBW/sapdata1/system_1/system.data1'

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 11/15/2012 09:17:21
    RMAN-06053: unable to perform media recovery because of missing log
    RMAN-06025: no backup of log thread 1 seq 186143 lowscn 5870286990828 found to restore
    RMAN-06025: no backup of log thread 1 seq 186142 lowscn 5870286983456 found to restore

    then i am trying recover the control file with time in recovery, that is also failing.

    then I have issued recover until cancel;
    If i recover that control file, I think we can recover.
  • 7. Re: Alter database open
    user296828 Expert
    Currently Being Moderated
    See if this example can help you..

    http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmrecov.htm#BRADV159
  • 8. Re: Alter database open
    sb92075 Guru
    Currently Being Moderated
    Tippu wrote:

    then I have issued recover until cancel;
    If i recover that control file, I think we can recover.
    old control files contain old SCN which need to be brought current

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points