ORA-01122: database file 5 failed verification check

Question

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

• ARS: yes, but that's not the solution.

Answer 1

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;

4. RUn the file in SQL prompt :

   @$ORACLE_HOME/dbs/control_new.sql

5.Mount the database :

alter database mount;

6. Recover database using control file:

   Recover database using backup control file until cancel;

7. Now open the database : alter database open;

• I_am_Batman: ulfet.blogspot.in/2014/12/… for reference.
• amyassin: after following the same I got a data dictionary mismatch error: ORA-01173: data dictionary indicates missing data file from system tablespace