Today we are going to learn about the recovery from a loss of one redo log member of a multiplexed group. Being the Oracle DBA we all know the importance of change in normal life as well as database life. A single change in the database makes a huge difference. We all are well known that all kind of data changes stores is known Redo logs. Multiplexing is a way to have a backup of redo log in Oracle.This scenario describes what happens when you lose one redo log member of a multiplexed group.It could happen for a simple media failure or because someone “needed” to free some space and saw that .log file!!!
It happens to me years ago and that was told to me by a system administrator.

when you have multiplexed your redo log group using several members as Oracle best practices suggest and your disks begin to experience problems your database will continue to function since it’s still able to write to one redo log member.
If your disk is completely out you can opt to some options whether or not you have a new disk!
With a new disk you can drop and create again the “lost” redo log member to the original location, otherwise you have to perform the same two steps (drop and create) choosing a different location.
Even if you have already lost your redo log member Oracle will arise the ORA-01609 error when you try to drop a member of a CURRENT group. Querying the status column of V$LOG view you can have several results and generally they are:

  1. CURRENT when that log file is “currently” written by the log writer process (ps -ef|grep lgwr)
  2. ACTIVE when that log file is required in case of a crash recovery (even if it’s already archived)
  3. INACTIVE when that log file is not required in case of a crash recovery.

So when you try to drop your “failed” redo log member and you receive the ORA-01609 error just force the database to switch to the next redo log group.

Let’s look it with an example. Here is my redo log configuration:

As you can see I have three redo log groups formed by two multiplexed members and currently the redo log group number 2 is the CURRENT one. Now I simulate the lost of the redo log member named ‘redo02b.log‘.

As you cann see from the alert log appears the error

Now have a look at how the status column of V$LOGFILE view change (from NULL to INVALID) for the lost member

I’ve lost a member of the CURRENT redo log group so I cannot drop it

I have to force a redo log switch:

Now my redo log group state is as the following:

And I can successfully drop and recreate my lost redo log member:

What’s happening ? My new redo log member is still INVALID ? What does it mean ?

If you look at the Oracle documentation INVALID could mean: inaccessible (as it was before) or just created (as I did few lines above).

Let’s look what it happens when I force logfiles to switch:


For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.