Skip to content

real life oracle dba scenarios – redo log file

below are some real life oracle dba scenarios of redo log files usage and problems.

scenario 1

we have a redo log group with only one redolog file member and we lost the “current” redo log file..

we have tried few possibilities by restore database and recover it @ the end of recovery i got below error

ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘/export/home/oracle/ora_tst/ora_tst/redo03.lo
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


whenever a redo log file in CURRENT status  is gone and if you dont have multiple copies of this redo log then you should do incomplete recovery. This means you have to first restore from your backup and then recover and open the database with resetlogs option.This is the keeping this in mind i performed the following steps.

1)opened rman and restored from my latest backup.

2)then recovered it and opened the database with resetlogs

The resetlogs option creates new emptyredo logs. Go through the rman documentation to find out the exact syntax as i dont want to mix redolog and rman concepts here. I will also be adding very soon real life scenarios on using rman.

scenario 2

we have 2 redo log groups in our database. group 1 and group 2.

group1 has 2 members and group 2 has 2 members.

saw some error messages in the alert log that in group2 one of the member is corrupt. so i have to fix this issue. below are the steps i did to solve this issue.

1)first thing is if the group has 2 members then i can drop 1 member without any risk.

i have to find the exact filename and path

so executed the below command

select group#, member,status from v$logfile;



2     C:\ORACLE\ORADATA\O8I\REDO01A.LOG  invalid

2     D:\ORACLE\ORADATA\O8I\REDO01B.LOG  inactive

i can see that the third file has invalid status and this is the file that has a problem.

so i issued the command

alter database drop logfile member 'c:\oracle\oradata\o8i\redo01a.log';

in case you recieve any errors saying that you cant drop then execute the below command and try again

alter system switch logfile;

alter database drop logfile member ‘c:\oracle\oradata\o8i\redo01a.log’;

database altered

now i want to add a new member to this group as it is good practice to have 2 log file members in each log group.Read the oracle online documentation if you dont know the difference log groups and log members.

ALTER DATABASE ADD LOGFILE MEMBER ‘c:\oracle\oradata\o8i\redo03a.log’  to group 2;

database altered.

scenario 3

Recently saw some error messages in our alert log file

Thread 1 cannot allocate new log, sequence 2474

Checkpoint not complete

Current log# 3 seq# 2473 mem# 0: C:\ORACLE\ORADATA\O8I\REDO05.LOG

below are the tasks that were done to fix this error

1)the above error means that either the redo log files are too small or there are very few redo log files.

2)so how can i change the size of redo log files? only way is to drop them and create new redo log files.

3)below are the different code statements that were executed.

– See what you have at present

select group#, member from v$logfile;



— add two new files

alter database add logfile ‘c:\oracle\oradata\o8i\redo03.log’ size 4M ;

Database altered.

alter database add logfile ‘c:\oracle\oradata\o8i\redo04.log’ size 4M ;

Database altered.

— Make sure that the current log file is one of the new ones

alter system switch logfile;

System altered.

alter system switch logfile;

System altered.

— Now drop the old log files

alter database drop logfile ‘c:\oracle\oradata\o8i\redo02.log’;

Database altered.

alter database drop logfile ‘c:\oracle\oradata\o8i\redo01.log’;

Database altered.

scenario 4

below is a link where you can find some real discussion about redo logs. follow the discussion closely and your knowledge about the redo log groups will improve a lot.

redo log ask tomredo log oracle forums

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.