How to multiplex Redo Logs in Oracle

How to multiplex Redo Logs in Oracle

Redo Log files records every change made by users. This guarantees that we will not lose any change, even if it was not written in Data Files due to instance failure. Oracle organizes Redo Logs in Redo Log Groups. Oracle requires at least two Redo Log Groups, each with at least one Redo Log Files to operate. Redo Logs are the most crucial component for recovery operations in case of instance failure, which means that Database Administrators should protect them well. All Redo Log Files in one Redo Log Group are identical. Having this in mind leads to that the best method of protecting Redo Log Files is to create multiple Redo Log Files in each Redo Log Group and put each Redo Log file from the same group on different physical disk. Multiplexing prevents from losing just one copy of the Redo Log File. When Redo Log Files are multiplexed the Log Writer Process (LGWR) writes simultaneously to all members of Redo Log Group.

Oracle fills Redo Log Files with multiple redo entries. Redo Entry is a group change vectors, each of which is a description of a change made to a single block in the database. It is worth to remember that Redo Entry also stores information about undo segments. Redo Entries are stored in Redo Log Buffer, which is a component of SGA, before Log Writer writes them to Redo Log Files. Only when all Redo Entries associated with particular transaction are on disk Oracle notifies that transaction has been committed.

Log Writer (LGWR) process writes the logs in circular fashion, when current Redo Log File is filled, Log Writer moves to the next group, when the Redo Log file from the last group is filled it starts writing to the first Redo Log File. The file/s to which LGWR writes redo entries are named current Redo Log File/s. Redo Log Files that are required for instance recovery are called active redo log files. Redo Log Files that are no longer required for instance recovery are called inactive redo log files. Each change of Redo Log Group to which Redo Log File writes is called a Log Switch. After Log Switch occurred Oracle assign new log sequence number to all Redo Log Files in new Redo Log Group. Log sequence number is incremented by one for example 1,2,3,…10.

Oracle Redo Log Architecture

Before we start multiplexing Redo Log Files, we need to identify current setup.
To show the configuration of Redo Log Groups we will use V$LOG view.

SQL> set linesize 160
SQL> column archived format a10
SQL> select group#, thread#, sequence#, bytes / 1024 / 1024 "Size in MB",
2 members, archived, status
3 from v$log
4 /
GROUP# THREAD# SEQUENCE# Size in MB MEMBERS ARCHIVED STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
1 1 76 50 1 NO CURRENT
2 1 74 50 1 NO INACTIVE
3 1 75 50 1 NO INACTIVE


GROUP#
THREAD#
SEQUENCE#
Size in MB
MEMBERS
ARCHIVED
STATUS
1
1
76
50
1
NO
CURRENT
2
1
74
50
1
NO
INACTIVE
3
1
75
50
1
NO
INACTIVE

As we can see we have three Redo Log Groups each with only one member. Now we will check location of Redo Log Files, that information is stored in V$LOGFILE view.

SQL> set linesize 160
SQL> column member format a60
SQL> column is_recovery_dest_file format a25
SQL> select group#, status, type, member,
2 is_recovery_dest_file
3 from v$logfile
4 /


GROUP#
STATUS
TYPE
MEMBER
IS_RECOVERY_DEST_FILE
1
ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
2
ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
3
ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

Now we are going to multiplex Redo Log Files, by adding 2 new members to each Redo Log Group. We will place the new Redo Log Files on separate disks in previously prepared directories.

[oracle@localhost ~]$ ls -l /u02/app/oracle/oradata/orcl/ 
total 9536
-rw-r----- 1 oracle oinstall 9748480 Jul 18 18:01 control02.ctl
[oracle@localhost ~]$ ls -l /u03/app/oracle/oradata/orcl/
total 9536
-rw-r----- 1 oracle oinstall 9748480 Jul 18 18:01 control03.ctl

To add new Redo Log File to existing Redo Log Group, we use ALTER DATABASE ADD LOGFILE MEMBER sql statement.

SQL> alter database add logfile member
2 '/u02/app/oracle/oradata/orcl/redo01.log','/u03/app/oracle/oradata/orcl/redo01.log'
3 to group 1
4 /

Database altered.

SQL> select group#, status, type, member,
2 is_recovery_dest_file
3 from v$logfile
4 /




GROUP#
STATUS
TYPE
MEMBER
IS_RECOVERY_DEST_FILE
1
ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
2
ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
3
ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO
1
INVALID
ONLINE
/u02/app/oracle/oradata/orcl/redo01.log
NO
1
INVALID
ONLINE
/u03/app/oracle/oradata/orcl/redo01.log
NO

As we can see new Redo Log Files have status INVALID, this is completely normal behavior. INVALID status will be cleared when those Redo Log Files will be initialized, which means when Log Writer will write to them for the first time. To initialize them right now we must perform required number of Log Switches. To perform manual Log Switch we use sql statement ALTER SYSTEM SWITCH LOGFILE.

SQL> select group#, status
2 from v$log
3 /

GROUP#
STATUS
1
CURRENT
2
INACTIVE
3
INACTIVE

As we can see Redo Log Group 1 in the CURRENT group, which means that we must perform three log switches to initialize our newly added Redo Log Files. The first switch will be from Group 1 to Group 2, the second from Group 2 to Group 3 and the last one from Group 3 to Group 1.

SQL> alter system switch logfile
2 /

System altered.

SQL> select group#, status
2 from v$log
3 /
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE

SQL> select * from v$logfile
2 /

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------------------------------------ -------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 INVALID ONLINE /u02/app/oracle/oradata/orcl/redo01.log NO
1 INVALID ONLINE /u03/app/oracle/oradata/orcl/redo01.log NO

SQL> alter system switch logfile
2 /

System altered.

SQL> select group#, status from v$log
2 /

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT

SQL> select * from v$logfile
2 /

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------------------------------------ -------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 INVALID ONLINE /u02/app/oracle/oradata/orcl/redo01.log NO
1 INVALID ONLINE /u03/app/oracle/oradata/orcl/redo01.log NO

SQL> alter system switch logfile
2 /

System altered.

SQL> select group#, status from v$log
2 /

GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE

SQL> select * from v$logfile
2 /

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------------------------------------ -------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
1 ONLINE /u02/app/oracle/oradata/orcl/redo01.log NO
1 ONLINE /u03/app/oracle/oradata/orcl/redo01.log NO

Now we will two Redo Log Files to each of the remaining Redo Log Groups.

SQL> alter database add logfile member
2 '/u02/app/oracle/oradata/orcl/redo02.log', '/u03/app/oracle/oradata/orcl/redo02.log'
3 to group 2
4 /

Database altered.

SQL> alter database add logfile member
2 '/u02/app/oracle/oradata/orcl/redo03.log'
3 to group 3
4 /

Database altered.

SQL> alter database add logfile member
2 '/u03/app/oracle/oradata/orcl/redo03.log'
3 to group 3
4 /

Database altered.

SQL> alter system switch logfile
2 /

System altered.

SQL> alter system switch logfile
2 /

System altered.

SQL> select group#, thread#, sequence#, bytes / 1024 / 1024 "Size in MB",
2 members, archived, status
3 from v$log
4 /

GROUP# THREAD# SEQUENCE# Size in MB MEMBERS ARCHIVED 
STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
1 1 79 50 3 NO ACTIVE
2 1 80 50 3 NO ACTIVE
3 1 81 50 3 NO CURRENT




GROUP#
THREAD#
SEQUENCE#
Size in MB
MEMBERS
ARCHIVED
STATUS
1
1
79
50
3
NO
ACTIVE
2
1
80
50
3
NO
ACTIVE
3
1
81
50
3
NO
CURRENT




SQL> select group#, status, type, member,
2 is_recovery_dest_file
3 from v$logfile
4 /




GROUP#
STATUS
TYPE
MEMBER
IS_RECOVERY_DEST_FILE
1
ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
2
ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
3
ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO
1
ONLINE
/u02/app/oracle/oradata/orcl/redo01.log
NO
1
ONLINE
/u03/app/oracle/oradata/orcl/redo01.log
NO
2
ONLINE
/u02/app/oracle/oradata/orcl/redo02.log
NO
2
ONLINE
/u03/app/oracle/oradata/orcl/redo02.log
NO
3
ONLINE
/u02/app/oracle/oradata/orcl/redo03.log
NO
3
ONLINE
/u03/app/oracle/oradata/orcl/redo03.log
NO




9 rows selected.