10 Replies Latest reply: Nov 9, 2011 1:16 AM by Simon M RSS

    ORA-01190: control file or data file 5 is from before the last RESETLOGS

    672680
      originally, EXAMPLE01.DBF is in offline status.
      then, i did, followings:
      backup database;
      change the database status to archivelog mode.
      backup again.
      recover to certain scn and resetlogs.
      and now, when i try to query the table which is included in EXAMPLE01.DBF file, it throws errors, saying file is offline.
      and it is offline, so when i try to get it online, it throws below errors.

      How can I solve it?
      SQL> connect /as sysdba
      Connected.
      SQL> alter DATABASE datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'  online;
      alter DATABASE datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'  online
      *
      ERROR at line 1:
      ORA-01190: control file or data file 5 is from before the last RESETLOGS
      ORA-01110: data file 5: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'
        • 1. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
          CKPT
          ORA-01190: control file or data file string is from before the last RESETLOGS
          Cause: Attempting to use a data file when the log reset information in the file does not match the control file. Either the data file or the control file is a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS.

          Action: Restore file from a more recent backup.
          • 2. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
            672680
            I did below and it is throwing errors:
            RMAN> restore datafile 5;
            
            Starting restore at 16-DEC-10
            using target database control file instead of recovery catalog
            allocated channel: ORA_DISK_1
            channel ORA_DISK_1: sid=154 devtype=DISK
            
            channel ORA_DISK_1: starting datafile backupset restore
            channel ORA_DISK_1: specifying datafile(s) to restore from backup set
            restoring datafile 00005 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
            channel ORA_DISK_1: reading from backup piece E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2010_12_13\O1_
            MF_NNND1_TAG20101213T132300_6JCCFO2X_.BKP
            channel ORA_DISK_1: restored backup piece 1
            piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2010_12_13\O1_MF_NNND1_TAG20101213T132300_6JCCF
            O2X_.BKP tag=TAG20101213T132300
            channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
            Finished restore at 16-DEC-10
            
            RMAN> recover datafile 5;
            
            Starting recover at 16-DEC-10
            using channel ORA_DISK_1
            
            starting media recovery
            
            archive log thread 1 sequence 22 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_13\O1_MF_1_22_6JCNNOSD_.ARC
            archive log thread 1 sequence 23 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_13\O1_MF_1_23_6JD43R00_.ARC
            archive log thread 1 sequence 24 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_14\O1_MF_1_24_6JFG7QK2_.ARC
            archive log thread 1 sequence 1 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_14\O1_MF_1_1_6JG97D9R_.ARC
            archive log thread 1 sequence 2 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_14\O1_MF_1_2_6JGRV1FO_.ARC
            archive log thread 1 sequence 3 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_15\O1_MF_1_3_6JJ3007T_.ARC
            archive log thread 1 sequence 4 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_15\O1_MF_1_4_6JJ9YH0Z_.ARC
            archive log thread 1 sequence 5 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_15\O1_MF_1_5_6JJLYBTV_.ARC
            archive log thread 1 sequence 6 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_15\O1_MF_1_6_6JKNMZ8S_.ARC
            archive log thread 1 sequence 7 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_16\O1_MF_1_7_6JLTTPTC_.ARC
            archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_13\O1_MF_1_22_6JCNNOSD_.ARC th
            read=1 sequence=22
            archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_13\O1_MF_1_23_6JD43R00_.ARC th
            read=1 sequence=23
            archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_12_14\O1_MF_1_24_6JFG7QK2_.ARC th
            read=1 sequence=24
            media recovery complete, elapsed time: 00:00:04
            Finished recover at 16-DEC-10
            
            RMAN>
            after open the database, when i query hr.employees, it throws error below:
            ORA-08103: object no longer exists
            08103. 00000 -  "object no longer exists"
            *Cause:    The object has been deleted by another user since the operation
                       began, or a prior incomplete recovery restored the database to
                       a point in time during the deletion of the object.
            *Action:   Delete the object if this is the result of an incomplete
                       recovery.
            • 3. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
              CKPT
              it seems you have performed Point in time recovery/incomplete recovery...

              thats why the archives are not applied

              please post your complete scenario with commands.

              that would helpful to answer exactly..

              Thanks
              • 4. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                672680
                yes, i did perform incomplete recovery and it was several days ago.
                and datafile 5 was offline then.

                I did something like this:
                startup mount;
                alter database flashback off;
                reset database to incarnation 2;
                run
                {
                     set until time "to_date('2010-12-14 20:00:00','YYYY-MM-DD HH24:MI:SS')";
                     restore database;
                     recover database;
                }
                alter database open resetlogs;
                • 5. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                  Chinar
                  Drop and create again HR schema or import this from other database.
                  http://download.oracle.com/docs/cd/B28359_01/server.111/b28328/installation.htm#I4367
                  http://download.oracle.com/docs/cd/B28359_01/server.111/b28328/installation.htm#I6236
                  • 6. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                    672680
                    this would be my last resort.
                    Plus, what if this datafile is not for example?

                    Edited by: PhoenixBai on Dec 16, 2010 2:12 PM
                    • 7. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                      Chinar
                      PhoenixBai wrote:
                      this would be my last resort.
                      Plus, what if this datafile is not for example?

                      Edited by: PhoenixBai on Dec 16, 2010 2:12 PM
                      I do not understand what do you want doing related this datafile?
                      • 8. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                        672680
                        it is not about a specific datafile.
                        it is about how to handle or fix this kind of situation.
                        • 9. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                          672680
                          Any input would be highly appreciated.
                          • 10. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
                            Simon M
                            I think you may need to first restore Controlfile 1st (from before the Tablespace was made Offline), then perform Database Restore\Recovery. You may need to SET INCARNATION back to before RESETLOGS before this. I'll see if I can reproduce the issue. It is difficult when changes are made to Tablespaces, as these get reflected in the Controlfiles, which aren't included in abnormal Database Restore.