I have an oracle database which has problems preventing it from opening.
To overcome the issues, I tried following steps:
First I mounted database:
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1.2560E+10 bytes
Fixed Size 2171344 bytes
Variable Size 6878662192 bytes
Database Buffers 5670699008 bytes
Redo Buffers 8601600 bytes
Database mounted.
After that, I recovered database as below:
SQL> recover database until cancel;
ORA-00279: change 338584095 generated at 11/22/2016 08:41:55 needed for thread 1
ORA-00289: suggestion : /oracle/app/product/11g/db/dbs/arch1_9218_833801667.dbf
ORA-00280: change 338584095 for thread 1 is in sequence #9218
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/app/oradata/ora11g/system01.dbf'
ORA-01112: media recovery not started
After this I tried to alter open database as below:
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: '/oracle/app/oradata/ora11g/system01.dbf'
and finally I tried recovering system01 datafile as below:
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 9218 doesn't match 9215
ORA-00312: online log 2 thread 1: '/oracle/app/oradata/ora11g/redo02.log'
as you can see in the final error "ORA-00314: log 2 of thread 1, expected sequence# 9218 doesn't match 9215" there is a sequence mismatch between the logfile redo02.log and the server.
How can this mismatch occur and what can I do to fix this?
PS: Since database cannot be opened, I cannot switch logfile and since redo02.log is the current logfile, I cannot drop or clean it.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 0 52428800 1 NO UNUSED
338564041 22-NOV-16
3 1 0 52428800 1 NO UNUSED
338544000 22-NOV-16
2 1 9218 52428800 1 NO CURRENT
338584094 22-NOV-16
