Oracle : REDOLOG FILE MANAGEMENT

REDOLOG FILE MANAGEMENT

 

 

• Redolog files are used to store latest transactional information which are used for recovery purpose incase of any instance failure.
• These are mandatory files for the database.
• The latest transactional data will send to redolog buffers then it will send to the redolog files.
• This redolog files are present in a database, in the form of members and groups.
• MEMBER: it is stored at physical level of database it contains the latest transactional information. The size of redolog member depends on group.
• Redolog files are having .log is the extension but it is not mandatory those can create with any extension.
• GROUP: redolog group is the collection of redolog members. It maintains the members information at logical level.
• Members present in a group having the same data and groups are having the different data.
• We are providing the size for group, what ever the members present in a group will get the same size.
• Members having the same size in a group, we can create the different groups with various sizes.
• We can see the members and group information by using the following data dictionary views.

V$LOGFILE and V$LOG

• For database it is necessary to have the following count of groups and members:

Redolog	Minimum	Maximum
Member	1	5
Group	2	16

 

 

 

 

• Redolog information are written to redolog files by logwriter(LGWR) which is database background process.
• LGWR will write the data from RLB to redolog files in various situations.(refer database architecture article)
• LGWR always writes the redo data into single group at a time.
• If one redolog group got filled then log writer(LGWR) will write the latest transaction to the another group. This is called as log switch.

This redlog files having different status, the following diagram shows the status or state of redolog files.

 

 

 

Unused: files which are not used at any   point of time.

Current: Oracle Database uses only one redo log file at a time to store redo information written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Active: Redo log files those are required for instance recovery is called active redo log files.

Inactive: Redo log files that are no longer required for instance recovery are called inactive redo log files.

 

HOW TO ADD A NEW GROUP:

Select the redo group and members present in the database by using this command.

SYS>> select group#, member from v$logfile;

 

 

 

 

Add the new group by using the following syntax.

SYS>> alter database add logfile group 3 (‘/disk2/sscdb/redo3.log’) size 4m;

 

 

SYS>> select group#, member from v$logfile;

 

 

HOW TO ADD A NEW MEMBER TO EXISTING GROUP;

Add the new member redo3a to existing group 3.

SYS>> alter database add logfile member ‘/disk2/sscdb/redo3a.log’ to group 3;

 

 

Check whether the member is added or not by using this command.

SYS>> select group#, member from v$logfile;

 

 

HOW TO DROP A MEMBER IN UNUSED STATE OR INACTIVE STATE:

The redlog files having different status if u want to drop any member or group you need to consider the status.

NOTE: To drop the member or group, the Status should be UNUSED or INACTIVE.

 

Redolog	Unused	Current	Active	Inactive
Group	Yes	No	No	Yes
Member	Yes	No	Yes	Yes

 

 

Check the member or group status by using this command.

 

 

SYS>> Alter database drop logfile member ‘/disk2/sscdb/redo3a.log’;

 

 

Check whether the member is dropped or not.

SYS>> select  group#, member from v$logfile;

 

 

HOW TO DROP A GROUP:

 

SYS>> Alter database drop logfile group 3;

 

 

Check whether the Group is dropped or not.

SYS>> select  group#, member from v$logfile;