In this Oracle tutorial, we’ll explain a simple way understanding of redo log group vs file vs member.
Today we are going to learn the concept of redo log buffer in Oracle. In which we will get to know, what is redo log buffer, how it works and what is its role in Oracle architecture.
If you wanted to understand Oracle architecture, it is really important to understand the working of the redo log buffer. The below article will help you to tackle interview questions as well.
Understanding of redo log group vs file vs member
What is a redo log buffer?
It is a tiny memory allocation typically around 14mb by default. It is a cyclic buffer. Once it is full it again starts writing from the top, it overwrites the data once it is full.
What is the redo entry and change Vector?
Oracle database instance records all the DML/DDL transactions into the redo log buffer of SGA in the form of redo entries. The individual statement in a transaction will result in one or more than one redo entries. These all redo entries are collectively called a change vector or redo vector.
Each redo entry consists of redo statement and undo statement for example:
TABLE EMP
| Empno | Name | Deptno |
|---|---|---|
| 1 | Jhon | 10 |
| 2 | Tony | 20 |
| 3 | Laura | 50 |
| 4 | Tiger | 10 |
| 5 | Scott | 10 |
sql>delete empno from emp where deptno=10;
In the above example, the emp table has 3 records for deptno=10. So Oracle will create 3 redo entries for the above statement and these entries will get recorded into redo log buffer. These 3 redo entries are collectively called a change vector or redo vector.
The change vector is nothing but a number of affected rows by a single operation. Redo log buffer captures user’s transactions and also it captures nullifying effect(undo statement) for that transaction.
Working of LGWR process
Since the size of the redo log buffer is small which cannot dynamically grow, these recorded redo entries must be written periodically to the disk into an online redo log file. This is often called flushing of redo log buffer and the job of writing these redo entries to disk is done by the LGWR process.
When LGWR writes redo entries to disk?
LGWR is quite aggressive in writing these entries to disk. Below are the events when LGWR writes redo entries to disk.
- When the buffer size is 1/3 full
- When there are 1 MB of change vectors available in the redo log buffer pending to write
- Every 3 seconds
- Whenever a checkpoint occurs
- When a user commits his/her transactions
A parameter to set the size of redo log buffer
log_buffer= x bytes
Structure of Redo Log Buffer
The redo architecture comprises of 3 things
- Redo Thread (logical)
- Redolog Groups(Logical)
- Redolog member Files (Physical)
1. What is the redo log thread?
- While creating a control file is given by the “MAXINSTANCES” parameter.
- A redo thread is made up of redo groups. The redo thread must be enabled to be operational.
- In the RAC environment, you can bind a redo thread to the particular instance using the init.ora parameter “thread”.
In a non-RAC database, there would mostly be one thread (thread = 1)
2. Understanding the Redo log Group and its status?
-While creating a control file it is given as the “MAXLOGFILES” parameter.
-A redo log group is a collection of identical redo log member files.
-But for better fault tolerance it is common to keep two redo log member files in each redo log group.
-If one is lost, it does not impact. The redo log member files are identical(mirror copies)
LGWR writes to all the members of a given redo log group in parallel
At any given point in time LGWR process writes to only one redo log group. The redo log group is fixed means the redo log member files cannot dynamically grow.
The redo log group in which the LGWR process is currently writing is seen with status “current”
Once the redo log group is full then the LGWR process will switch next redo log group.
The previous group will show status “ACTIVE” and after sometime status would be “INACTIVE”
CURRENT => ACTIVE => INACTIVE => CURRENT => ACTIVE => INACTIVE …………………………………
CURRENT status means oracle currently writing in this buffer
ACTIVE Checkpoint of redo entries in this buffer is pending
INACTIVE status means the redo log has been checkpointed and can be overwritten immediately.
You cannot change the size of the redo log group once created but you always drop and recreated the redo log group with different sizes. You cannot drop the redo log group with status “CURRENT” or “ACTIVE”.
How to switch a redo log group?
Normally the LGWR automatically switches between the redo log groups but you can manually switch from one redo log group to another using command.
sql>alter system switch logfile ;
Every time LGWR picks up a new redo log group and it assigns a monotonously incrementing number to that redo log group called “Log sequence number” or “sequence#”. You can query the redo log groups metadata using view:- v$log Redo log groups are written using physical disk block size (sector of 512 bytes)
How to add a new redo log group
sql>alter database add logfile group 3 ('/path/to/member1.log', '/path/to/member2.log') size 100m reuse;
Herein meaning of reuse says, reuse the files member 1.log or member2.log file if it already exists in the same path.
How to drop redo log group
You can drop redo log groups provided the status is not CURRENT or ACTIVE. Also after applying this command the number of redo log groups available should 2 or more.
sql>alter database drop logfile groups 3;
3. Redo Log member files(Physical)
While creating a control file it is given by the “MAXLOGMEMBERS” parameter. The redo log member files are available physically at the OS level. The size of the file is fixed at the time of redo log group creation.
The file cannot dynamically grow. LGWR writes to all the members of a given redo log group in parallel.
Member files in the redo log group are identical. You can query the status info from the view v$logfile
How to add a new member to an existing group?
sql>alter database add logfile member '/path/to/new/member1.log' to group 3;
You can drop a member from the group provided there remain one member and group status is not current
sql>alter database drop logfile member '/path/to/member_to_be_deleted.log';
To query the redo log switching details
you can query v$log-history
sql>select count(*), trunc(first_time) from v$log_history;
group to overwrite the redo entries
Why I see “checkpoint not complete” in alert.log
When LGWR arrives at a redo log group to overwrite redo entries and by that time if a checkpoint of that group is pending you would see a message in alert log “checkpoint not complete” Because of this LGWR cannot overwrite the redo log group immediately.
Hence Oracle will trigger the “checkpoint” which will flush dirty buffers caused by redo entries to disk. Only after this LGWR can overwrite to redo buffer. It means LGWR has to wait for some time which not good. To overcome either add more redo log groups and/or increase the size of redo log groups.
We hope our above details will help you to clear your understanding of the redo log buffer.
This is the end of the tutorial, understanding of redo log group vs file vs member.
Thanks for reading this article, leave us comment for suggestions or queries.
