RMAN Backup and Recovering From Loss of Current Redo Log Group
Scenario 3: Recovering From Loss of Redo Log Member of Current
Group
The following scenario will demonstrate the recovery steps from
the loss of the redo log member of the Current redo log group.
This is the most dangerous situation where the DBA will lose
everything that is written to the redo log file which is not
multiplexed.
- First of all, take a backup of the database (shutdown the
database and copy all .dbf and .ctl files to another directory).
Then get the member name of the Current redo log group and
delete it. Then restart the database:
SQL>
shutdown
immediate;
[oracle@localhost ~]$ cd $ORACLE_HOME/oradata/new/
[oracle@localhost new]$ mkdir backup
[oracle@localhost new]$ cp *.dbf backup/
[oracle@localhost new]$ cp *.ctl backup/
[oracle@localhost new]$ exit
SQL>
startup
<.....output trimmed .....>
Database opened.
SQL>
select
a.group#, a.status, b.member
from
v$log a, v$logfile b
where
a.group#=b.group# and a.status='current';
GROUP# STATUS MEMBER
---------- ----------------
----------------------------------------
3 CURRENT /u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log
SQL>
host
[oracle@localhost ~]$ rm -rf
/u01/oracle/product/10.2.0/db_1/oradata/new/redo03.log
[oracle@localhost ~]$ exit
exit
SQL>
startup
force
<.....output trimmed .....>
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
- It is not possible to clear the current redo log file as you
have done for the loss of the inactive redo log member. It is
also not possible to drop and add a nonmultiplexed current redo
log member as has been done for the loss of the multiplexed
member of any group. If you try it, you will fail:
SQL>
alter
database clear unarchived logfile
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log';
alter
database clear unarchived logfile
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log'
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance new
(thread 1)
ORA-00312: online log 3 thread 1:
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log'
SQL>
alter
database drop logfile
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log';
alter
database drop logfile
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log'
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance new (thread 1) -
cannot
drop
ORA-00312: online log 3 thread 1:
'/u01/ORACLE/product/10.2.0/db_1/oradata/new/redo03.log'
SQL>
- In this situation, you need to recover the database to the
lost redo logs first sequence number. To get this scn value,
use the following query:
SQL>
select
a.first_change#, a.status, b.member
from
v$log a, v$logfile b
where
a.group#=b.group# and a.status='current';
FIRST_CHANGE# STATUS MEMBER
------------- ----------------
-------------------------------------
446985 CURRENT /u01/oracle/product/10.2.0/db_1/oradata/new/redo03.log
- Shut down the database, restore all files from the backup
directory and bring the database to mount mode. Then use the
recover database until change command to recover the database
until the first_change# value of the missing redo log file and
open the database with the resetlogs option:
SQL>
shutdown
immediate;
[oracle@localhost ~]$ cd $ORACLE_HOME/oradata/new
[oracle@localhost new]$ rm -rf *.dbf
[oracle@localhost new]$ rm -rf *.ctl
[oracle@localhost new]$ cd ./backup
[oracle@localhost new]$ mv ./backup/* .
[oracle@localhost new]$ exit
SQL>
startup
mount
<.....output trimmed .....>
Database mounted.
SQL>
recover
database until change 446985;
Media recovery complete.
SQL>
alter
database open resetlogs;
Database altered.
SQL>
Scenario 4: Recovering From Loss of Redo Log Member of Active
Group
The active group member is needed for instance recovery, so you
may need to perform an incomplete recovery if you lost its members
as you have done for the group members with current status.
Remember, the first action which should be performed when the
active member of the redo log file is lost is running the alter
system checkpoint command. If it does not fail, then go on and
clear the redo log file as you have done in the inactive member
loss.
If it fails, then you have only one option which is performing
an incomplete recovery. All steps are the same as in the current
group loss scenario.
