This discussion is archived
10 Replies Latest reply: Nov 8, 2011 11:16 PM by 847751 RSS

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

672680 Pro
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Any input would be highly appreciated.
  • 10. Re: ORA-01190: control file or data file 5 is from before the last RESETLOGS
    847751 Newbie
    Currently Being Moderated
    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.

Legend

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