Basics of Redo Log Files in Oracle 12c
Redo log files store the information from the log buffer in the Oracle 12c database. They’re written to by the Log Writer (LGWR). Again, you can’t read these binary files without the help of the database software.
Typically, redo log files are named with the extension .LOG or .RDO. It can be anything you want, but best practice indicates one of those two extensions. Also, redo log files are organized into groups and members. Every database must have at least two redo log groups.
Redo log files contain all the information necessary to recover lost data in your database. Every SQL statement that you issue changing data can be reconstructed by the information saved in these files.
Redo log files don’t record select statements. If you forget what you selected, you’re just going to have to remember that on your own!
The optimal size for your redo log files depends on how many changes you make to your database. The size is chosen by you when you set up the database and can be adjusted later. When the LGWR is writing to a redo log file, it does so sequentially.
It starts at the beginning of the file and once it is filled up, it moves on to the next one. This is where the concept of groups comes in. Oracle fills each group and moves to the next. Once it has filled all the groups, it goes back to the first.
You could say they are written to in a circular fashion. If you have three groups, it would go something like 1,2,3,1,2,3, . . . and so on.
Each time a group fills and the writing switches, it’s called a log switch operation. These things happen during a log switch operation:
The LGWR finishes writing to the current group.
The LGWR starts writing to the next group.
A database check point occurs.
The DBWR writes dirty blocks out of the buffer cascade.
How fast each group fills up is how you determine its size. By looking at all the things that occur when a log switch happens, you might agree that it is a fairly involved operation. For this reason, you don’t want frequent log switches.
The general rule is that you don’t want to switch log files more often than every 15–30 minutes. If you find that happening, consider increasing the size of each group.
Because these redo log files may be involved in recovery operations, don’t lose them. Similar to control files, redo log files should be configured with mirrored copies of one another. And, as with control files, each member should be on a separate disk device. That way, if a disk fails and the database goes down, you still have recovery information available. You should not lose any data.
Each copy within a group is called a member. A common configuration might be three groups with two members apiece, for a total of six redo log files. The group members are written to simultaneously by the log writer.
How many groups are appropriate? The most common configuration you’ll come across is three. You want enough that the first group in the list can be copied off and saved before the LGWR comes back around to use it. If it hasn’t been copied off, the LGWR has to wait until that operation is complete. This can severely impact your system. Thankfully, you’ll rarely see this happen.
How many members are appropriate? It depends on how paranoid you are. Two members on two disks seems to be pretty common. However, it isn’t uncommon to see three members on three disks. More than that and you’re just plain crazy. Well, not really.
It’s just that the more members you have, the more work the LGWR has to do. It can impact system performance while at the same time offering very little return.
We commonly get this question: If my disks are mirrored at the hardware level, do I need more than one member on each group? After all, if a disk fails, I have another one right there to pick up the slack.
Unfortunately, you get different answers depending on who you ask. Ask us, and we’ll recommend at least two members for each group:
Oracle still recommends two members for each group as a best practice.
Depending on how your hardware is set up, you may have the same disk controller writing to your disk mirrors. What if that controller writes corrupt gibberish? Now both your copies are corrupted. Separating your members across two different disks with different controllers is the safest bet.