You are correct that multiplexing and archiving both apply to redo log files. But they serve different purposes. (Multiplexing can also apply to other files such as the control file.)
Muliplexing is when you maintain an exact copy of a file on a different disk - it is important that the two copies are on separate disks. As transactions are processed within Oracle, LGWR will write REDO information to the current, active REDO log. 'If' the REDO log is multiplexed (ie. there are two or more copies of the REDO log file on different disks) then LGWR will write the 'same' redo information to 'each' of the redo log files - hence maintaining the fact that each copy is identical.
Consider that we only had one redo log file, and no multiplexed copies. So LGWR only writes REDO information to that one REDO log file. What happens if there is fault on that disk, or that REDO log file becomes corrupt. The LGWR will be unable to write REDO log information to it. And Oracle will stop! It cannot proceed if LGWR cannot write REDO log data.
BUT.....consider the case with a multiplexed copy. As described above, in normal activities, LGWR will write the same REDO log information to each multiplexed copy. But now, if a disk fails containing one of the REDO log copies, then no problem! LGWR will still be able to write to the other multiplexed copy, and Oracle will be allowed to continue. (Giving you time to fix the faulty disk and create a new multiplexed copy.)
So.....the purpose of multiplexing a file, such as the REDO log file, is to minimise the risk of disruption due to some problem in accessing one of the files eg. a disk fault. Oracle recommends that you maintain at least 2 multiplexed copies of each REDO log on separate disks.
Now we come on to 'archiving'.
A set of multiplexed REDO log files is called a log GROUP. ie. all log files within a log group are identical and multiplexed ie. stored, on different disks. LGWR will continue writing to the log GROUP (ie. simulatneously to each log file within the group, as described earlier) until the log files are full. Now, Oracle requires you to have at least two REDO log Groups. Hence, when one group is full, LGWR can 'switch' to the second group, and start writing REDO log information to that group. (NB. Log switches are the process of 'switching' the recording of REDO information from one log group to the next).
The obvious next question is, what happens when this second group is full? Well....if 'archiving' is not active ie. if Oracle is being operated in NOARCHIVELOG mode, then LGWR will switch back to the first log group, 'over-writing' information that was previously saved there. And that older REDO information that is over-written, is LOST! If the database were to fail, we would not have that REDO information with which to recover the database. Generally not a good situation to be in.....!
One solution might seem to be to have more REDO log groups. Hence, when the second REDO log group is full, LGWR can write to the third group. However, whatever the number of log groups, when LGWR fills the last one, it will return to the first one and start over-writing the original information. Plus, each log group will take up valuable disk resource, so it doesn't make sense to have too many.
So....we use 'archiving' of the redo log files. ie. we operate in ARCHIVELOG mode.
In this case, when a log group is full, and LGWR switches to the second log group, an Oracle process 'ARCn' will start archiving ie. taking a copy, of the original full REDO log file and store it to disk somewhere else. Then, when the second group is full, LGWR can return to the first REDO log group and over-write the original REDO information. But in this case, we are safe, because we have archived ie. backed up, that old REDO log information already.
So if the database WERE to fail at any time, we have a complete history of REDO log information with which to recover the database. Recovery of the database would involve applying REDO information from both the ARCHIVED REDO logs, and the on-line REDO logs.
One final point. Oracle recommends you maintain at least 2 REDO log groups each containing at least 2 multiplexed log files. However, you might consider having at least THREE log groups.
If you only had 2 REDO log groups, LGWR may fill up the second group, before ARCn has finished archiving the first group to a separate disk. Oracle would then have to halt, until ARCn has completed, and LGWR could switch to the first log group again. By having 3 log groups, LGWR coupld switch to group 3 when group 2 is full, giving ARCn plenty of time to archive REDO information from group 1, before LGWR switches back to that group 1.
Originally posted by quester I am really confused between MULTIPLEXING redo log files and ARCHIVING redo log files. I know the process is different, but isn't the application same.
I mean when we can have multiple copies by MULTIPLEXING, why do we then need to ARCHIVE also? Kindly clarify my confusion.
"It just ain't possible to explain some things. It's interesting to wonder on them and do some speculation, but the main thing is you have to accept it-take it for what it is, and get on with your growing."
-- Jim Dodge (b. 1945), American writer, actor