Pages

Wednesday, April 23, 2014

Database crashed, controlfiles deleted and missing archive logs

Current situation: tonight a database crashed and the controlfiles have been destroyed/deleted. When I tried to open the database then I got:

$ export ORACLE_SID=KARDB
$ sqlplus "sys/password as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 22 18:03:00 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup

ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size                  2232840 bytes
Variable Size             524291576 bytes
Database Buffers          536870912 bytes
Redo Buffers                5541888 bytes
ORA-00205: error in identifying control file, check alert log for more info


So I tried to restore the controlfiles via rman. First I started rman and connected to the target database and the catalog (note that the database it self must be in mount state):

$ rman target sys/password@kardb catalog rcat/rcat@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 22 18:02:31 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: KARDB (not mounted)
connected to recovery catalog database


Then I restored the controlfiles:

RMAN> restore controlfile;
Starting restore at 22-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /backup/oracle/ora_cfc-2733729462-20140422-02
channel ORA_DISK_1: piece handle=/backup/oracle/ora_cfc-2733729462-20140422-02 tag=TAG20140422T175943
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u02/oradata/kardb/cntrl/cntrl1KARDB.ctl
output file name=/u03/oradata/kardb/cntrl/cntrl2KARDB.ctl
output file name=/u04/oradata/kardb/cntrl/cntrl3KARDB.ctl
Finished restore at 22-APR-14


After the restore has finished I tried to open the database again:

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


As the output above mentioned I tried to open the database with resetlogs:

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: '/u04/oradata/kardb/data/systKARDB01.dbf'


Tried to recover the database:

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


Tried to recover the database using backup controlfile:

SQL> recover database using backup controlfile;
ORA-00279: change 435430 generated at 04/22/2014 17:58:01 needed for thread 1
ORA-00289: suggestion : /u02/oradata/kardb/archive/logarcKARDB.819285046.26.1
ORA-00280: change 435430 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
archive log file does not exist:


Note that the requested archive log file does not exist:

$ ls -la /u02/oradata/kardb/archive/logarcKARDB.819285046.26.1
/u02/oradata/kardb/archive/logarcKARDB.819285046.26.1: No such file or directory


Second attempt to open the database with resetlogs:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u04/oradata/kardb/data/systKARDB01.dbf'


Tried to recover the database using backup controlfile until cancel:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 435430 generated at 04/22/2014 17:58:01 needed for thread 1
ORA-00289: suggestion : /u02/oradata/kardb/archive/logarcKARDB.819285046.26.1
ORA-00280: change 435430 for thread 1 is in sequence #26
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: '/u04/oradata/kardb/data/systKARDB01.dbf'
ORA-01112: media recovery not started


Third attempt to open the database with resetlogs:

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: '/u04/oradata/kardb/data/systKARDB01.dbf'


List all backuped archive log files in rman:

RMAN> list archivelog all;
released channel: ORA_DISK_1
List of Archived Log Copies for database with db_unique_name KARDB
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
...
365     1    25      A 17-APR-14
        Name: /u02/oradata/kardb/archive/logarcKARDB.819285046.25.1


Still no file with sequence 26 available. The last one has sequence 25.
Then I listed the redo log files:

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/oradata/kardb/redoa/log1aKARDB.log
/u02/oradata/kardb/redob/log1bKARDB.log
/u02/oradata/kardb/redoa/log2aKARDB.log
/u02/oradata/kardb/redob/log2bKARDB.log
/u02/oradata/kardb/redoa/log3aKARDB.log
/u02/oradata/kardb/redob/log3bKARDB.log
/u02/oradata/kardb/redoa/log4aKARDB.log
/u02/oradata/kardb/redob/log4bKARDB.log
/u02/oradata/kardb/redoa/log5aKARDB.log
/u02/oradata/kardb/redob/log5bKARDB.log
/u02/oradata/kardb/redoa/log6aKARDB.log
/u02/oradata/kardb/redob/log6bKARDB.log


Now I tried to recover the database and used all of the above redo log files until I found sequence 26. A FAIL looks like the following (note the the last two lines of the output - output truncated):

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 435430 generated at 04/22/2014 17:58:01 needed for thread 1
ORA-00289: suggestion : /u02/oradata/kardb/archive/logarcKARDB.819285046.26.1
ORA-00280: change 435430 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/oradata/kardb/redoa/log1aKARDB.log
ORA-00310: archived log contains sequence 25; sequence 26 required
ORA-00334: archived log: '/u02/oradata/kardb/redoa/log1aKARDB.log'
...


A WIN looks like:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 435430 generated at 04/22/2014 17:58:01 needed for thread 1
ORA-00289: suggestion : /u02/oradata/kardb/archive/logarcKARDB.819285046.26.1
ORA-00280: change 435430 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/oradata/kardb/redoa/log2aKARDB.log
Log applied.
Media recovery complete.


Unfortunatly I still couldn't open the database normal:

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


But at least with resetlogs:

SQL> alter database open resetlogs;
Database altered.


And the database was back online again.