[Amit Bansal]

One of our database crashed due to filer crash. When we tried to start the database back, we got following errors

ORA-01122: database file 43 failed verification check
ORA-01110: data file 43: '/u07/oradata/orcl/test_idx_4m001.dbf'
ORA-01207: file is more recent than control file - old control file
ORA-1122 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:43923:2704} */...

Searching for above errors, lead to posts where people were recommending recovery from backup controlfile and opening database with resetlogs.
This is not correct approach as none of archive/redo log files were lost and its issue of missing update in control file.
Correct solution would be to recreate controlfile and recover database. I am documenting steps used for recovering database

1. Mount database and take controlfile backup which will be used to recreate controlfile

sqlplus / as sysdba
startup mount
alter database backup controlfile to trace as /home/oracle/backup_cntrl.sql';

2. Make a copy of this file and edit it so as to keep “create controlfile” with noresetlogs option

3. Shutdown the database and take cold backup (Always take backup of existing db before recovery)

4. Rename/remove the existing control files(can check control_files parameter from init.ora file)

5. If this is RAC, set cluster_database=false and start only one instance with startup nomount

6. Run the script to create controlfile

7. Issue “recover database”

8. Open database now

“Alter database open;”

Hope this helps

[Author]

Posts