When Database is Not Consistent

  • by
There are several different kinds of situation when you recover a database with inconsistent states among files. In the following, I have to assume that you can mount the database without question.
  1. Data files are consistent, but controlfile is advanced.
    • Do you have all required logs?
    • If you have both archived logs and redo logs, then you can just recover database completely, and open the database normally.
    • SQL> recover database;
      SQL> alter database open;

    • If you have only archived logs, then you can recover the database as far as it can and open the database with resetlogs.
    • SQL> recover database until cancel;
      SQL> alter database open resetlogs;
  2. Data files are consistent, but controlfile is lagged.
  3. You can only open the database with resetlogs. No any recovery action that you can do. But the question is: How are you so sure that all the data files are consistent?

  4. Data files are inconsistent, but controlfile is advanced.
  5. In my opinion, this case is almost as same as the situation 1 above. It’s irrelevant with inconsistent data files if and only if you have all required archived logs to fulfill the consistency of data files. If you have no any archived logs, then you can’t open the database. Unless, you accept an incomplete open of database.

  6. Data files are inconsistent, but controlfile is lagged.
  7. Oh, you can do nothing about it except that the database is a standby. This database can not be recoverable and therefore not openable.
    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01122: database file 1 failed verification check
    ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
    ORA-01207: file is more recent than control file - old control file

    If you re-catalog the absent archived logs, the control file will accept it, but they are useless when you try to RECOVER DATABASE UNTIL CANCEL.
    SQL> recover database until cancel;
    ORA-00283: recovery session canceled due to errors
    ORA-01122: database file 1 failed verification check
    ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
    ORA-01207: file is more recent than control file - old control file

    You should either try to find a newer controlfile to mount or restore a full consistent datafiles by all means, then go back to case 3 above.

    On the other side, if your database is a standby, you can ALTER DATABASE REGISTER LOGFILE ‘/PATH/TO/ARCHIVED_LOG’ to catalog the absent archived logs and then perform a manual recovery by issuing RECOVER STANDBY DATABASE UNTIL CANCEL.

Leave a Reply

Your email address will not be published. Required fields are marked *