Oracle Database Discussions

Hi All,

Recently we had a database corruption issue, and we did the below things for bringing up the database.

SQL> ORACLE instance started.
Total System Global Area 3847049488 bytes
Fixed Size 741648 bytes
Variable Size 2214592512 bytes
Database Buffers 1610612736 bytes
Redo Buffers 21102592 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/data/oradata/DEVDB/system/system01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

We have tried recreating the control file and opening the database, but it doesn’t work. still getting the below errors.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3847049488 bytes
Fixed Size 741648 bytes
Variable Size 2214592512 bytes
Database Buffers 1610612736 bytes
Redo Buffers 21102592 bytes
SQL> @DEVDB_ora_17975_ctl.sql
Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/oradata/DEVDB/system/system01.dbf'

As the next step, I have taken the backup controlfiles and online redo logfiles to backup folders on the disk.

After that started the db with nomount and recreated the controlfile.
But still got the below error when tried to open the db with resetlogs option.

SQL> @controlfile_DEVDB.sql;
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/oradata/DEVDB/system/system01.dbf'


But when verified the SCN timestamp on some of the dbf files are different.
It shows that all the Index datafiles are having the different timestamp compared with the other dbf files.
So I have made those index datafiles offline and recreated the controlfile and recovered the database
with backup controlfile until cancel. And applied the online redolog which is required for recovery and
opened the database with resetlogs. It opened successfully.

SQL> select status from v$instance;
STATUS
------------
MOUNTED

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 11665987902059 generated at 12/02/2010 17:15:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/admin/DEVDB/arch/arch_62234.arc
ORA-00280: change 11665987902059 for thread 1 is in sequence #62234
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/admin/DEVDB/arch/arch_62234.arch
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

$ cp -p /data/oradata/DEVDB/rdo2/rdo2_bkp/onl_1_2.log /home/oracle/admin/DEVDB/arch/arch_62234.arch
$ ls -ltr /home/oracle/admin/DEVDB/arch/arch_62234.arch
-rw-r----- 1 oracle dba 52429824 Dec 2 18:31 /home/oracle/admin/DEVDB/arch/arch_62234.arch

Now the issue is we need to recover the Index dbf files which i have made as offline during the recovery process.

Can anyone suggest how can i recover my Index dbf files ?

Thanks & Regards
Vedavathi