8 Replies Latest reply: Nov 16, 2012 9:03 AM by sb92075 RSS

    Alter database open

    494959
      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
          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
            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
              I think u can't since its a system datafile..
              • 5. Re: Alter database open
                12cdb
                Can you share your high level backup, restore and recovery steps?
                • 6. Re: Alter database open
                  494959
                  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
                    12cdb
                    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
                      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