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.
No comments:
Post a Comment