Thursday 26 November 2015

How to Multiplex Redo Log files? Why Redo Multiplexing is required?

How to Multiplex Redo Log files?

What is Redo Multiplexing? Why do we need to multiplex redo log files? What if one of our redo log file gets corrupted? 

There are a lot of questions that are related to this topic which is - Redo Multiplexing. Let's get to know them one by one.

What is Redo Multiplexing?

Redo Multiplexing or Redo log multiplexing is simply making multiple copies of our redo log files so that in case if one redo log gets corrupted, then we could get that log info from the copied one. When we multiplex a redo log file, what is written on one log file is copy over the other (this happens in the same group only).

Why do we need multiplexed redo log files?

I think the answer is quite clear from the definition of Redo Multiplexing but for the beginners who need some more light on this topic, let me explain it in a better way. 

Redo logs contain all the transaction info. That means every single query that has been made in the database to make some change is stored in the redo log file. Now in case of our normal personal desktop server, we don't care much about multiplexing redo log files but think about a large organization where billions of transactions happen in a single day. How much log would it be generating?

And if you have learnt about RMAN then you should know the importance of redo log files in terms of archive logs that how archive logs help in the database restore and recovery scenarios. Now our database is in archive log mode and we haven't done the multiplexing. What if some redo log file gets lost or corrupted? Now from where will we get that data? Also the archive log will not be generated as we have lost the source log file. So that will be a big issue because we will not be able move back the database prior to that time because we'll be having the missing log info. (I think you'll get my point here that why it is necessary)

How do we multiplex Redo Log Files?

Multiplexing Redo Log Files is very simple. All you have to do is add some more log members in the groups and that's it. But it would be wiser to add equal number of members at each group. And make sure we always have same number of members in each group.

So here we go,

1. Check out the groups and members in the group.

2. Check out the status and location of the members. 
Note :- Empty status means Valid. In case if any member is Invalid, the status will display "INVALID".

By default, you'll be having 1 member in each group but I have 2. This is because I already have multiplexed my redo log files. But I'll do it again and create one more member in each group.

3. Creating more members.

4. Now again checking out the members in the group.

As you can see that I have 3 members in all the 3 groups now.

5. Also checking the status of all the new log members.

The status of the newly created log members is INVALID for now. This is because if you have checked the status in the V$LOG, it is showing that the members of Group 1 are in CURRENT status and the others are in INACTIVE state. We have to manually do the log switch to let the 2nd group come into current state and to let the newly created member join the family. But one log switch will only activate the member of the 2nd group only. That's why we have to switch the log 3 times so that all the new members start serving their purpose.

6. Manually Switching Log File.

9. Checking the status of new members.

As you can see that all of our redo log members are online and working fine. So this is how to multiplex your redo log files. I hope this article helps !!

No comments:

Post a Comment