Saturday, August 30, 2008

Online Redo log file Recovery --- steps to recover Loss of member

Log file Recovery:
case 1: Loss of member of a online Redolog group.
In this synario,if your log group is multiplexed with one or more members then no need to worry, the other member of that group will take over and allow oracle to keep running with no apparent problem. In this case, JUST DROP AND RECREATE THE MEMBER.
However,alert_.log will show error such as
"ORA-00313:Open failed for members of log group of thread "
and
"ORA-00312: Online log thread :"
Example: SQL> select * from v$log;
SQL> col member for a35
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
1 ONLINE /9i/oradata/demo/log_1_A.log 1
1 ONLINE /9i/oradata/demo/log_1_B.log 2
2 ONLINE /9i/oradata/demo/log_2_A.log 1
2 ONLINE /9i/oradata/demo/log_2_B.log 2---------> corrupt this member
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
After the log switch just open up your alert.log file
$cat /9i/admin/demo/bdump/alert_demo.log TAIL -10
error in file /9i/admin/demo/bdump/demo_lgwr_2676.trc:
ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/9i/oradata/demo/log_2_B.log'ORA-27046: file size is not a multiple of logical block sizeAdditional information: 1
Just remove the physical file of corrupted log member.
$rm -i /9i/oradata/demo/log_2_B.log
If the status is ACTIVE or CURRENT for that group,just SWITCH the logfile
SQL> alter database drop logfile member '/9i/oradata/demo/log_2_B.log';
Database altered.
SQL> alter database add logfile member '/9i/oradata/demo/log_2_B.log' to group 2;
Database altered.

No comments: