ORA-01122: database file 5 failed verification check

datafileoracleoracle-11g-r2recovery

Oracle version: 11g r2
OS: win server 2008 

SQL> alter database open;  
  alter database open  
  *

ERROR at line 1:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '[file_path]'
ORA-01210: data file header is media corrupt 

  SQL> alter database datafile '[file_path]' offline;  
  alter database datafile '[file_path]' offline  
  *

ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled 

  SQL> archive log list;  
  Database log mode              No Archive Mode  
  Automatic archival             Disabled  
  Archive destination            USE_DB_RECOVERY_FILE_DEST  
  Oldest online log sequence     8231  
  Current log sequence           8235  

  SQL> shutdown immediate;  
  ORA-01109: database not open  

  Database dismounted.  
  ORACLE instance shut down.  
  SQL> startup mount;  
  ORACLE instance started.  

  Total System Global Area 2.7392E+10 bytes  
  Fixed Size                  2188768 bytes  
  Variable Size            1.5032E+10 bytes  
  Database Buffers         1.2348E+10 bytes  
  Redo Buffers                9183232 bytes  
  Database mounted.  

  SQL> recover database;

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '[file_path]'
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '[file_path]'
ORA-01210: data file header is media corrupt

Best Answer

You cannot put the datafiles in Offline when your DB is in No Archive Log mode.

So below are the steps to recover the database considering current situation :

  1. Shutdown immediate;

  2. startup nomount;

  3. Recreate the control file using backup trace. :

    alter database backup controlfile to trace as '$ORACLE_HOME/dbs/control_new.sql';

Now make changes in the created control_new.sql:

CREATE CONTROLFILE REUSE DATABASE "db_name" RESETLOGS ARCHIVELOG  
   MAXLOGFILES 16  
   MAXLOGMEMBERS 3  
   MAXDATAFILES 100  
   MAXINSTANCES 8  
   MAXLOGHISTORY 2921  
 LOGFILE  
  GROUP 1 '/u01/app/oracle/oradata/db_name/redo1.log' SIZE 400M,  
  GROUP 2 '/u01/app/oracle/oradata/db_name/redo2.log' SIZE 400M,  
  GROUP 3 '/u01/app/oracle/oradata/db_name/redo3.log' SIZE 400M,  
  GROUP 4 '/u01/app/oracle/oradata/db_name/redo4.log' SIZE 400M,  
  GROUP 5 '/u01/app/oracle/oradata/db_name/redo5.log' SIZE 400M,  
  GROUP 6 '/u01/app/oracle/oradata/db_name/redo6.log' SIZE 400M  
 -- STANDBY LOGFILE  
 DATAFILE  
  '/u01/app/oracle/oradata/db_name/system01.dbf',  
 ...  
  '/u01/app/oracle/oradata/db_name/FCJ_DATA_TS_13.dbf'  
 CHARACTER SET AL32UTF8;

  1. RUn the file in SQL prompt :

    @$ORACLE_HOME/dbs/control_new.sql

5.Mount the database :

alter database mount;

  1. Recover database using control file:

    Recover database using backup control file until cancel;

  2. Now open the database : alter database open;

Related Question