How to Multiplex Redo Log Files in Oracle

As a DBA, it’s your responsibility to multiplex redo log files just like multiplexing control files to protect your organization data loss due to media failure or redo log file corruption.

Follow below steps to multiplex redo log files.

Step-1: Connect to a database

Connect to SQL*Plus as the system/manager@yourdb user.

SQL> CONNECT system/manager@testdb AS SYSDBA

Step-2: List all the redo log file

Query V$LOGFILE view to list all the redo log files in the database.

SQL> Select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                        
---------- ------- ------- --------------------------------------
         3 STALE   ONLINE  C:\ORACLE\ORADATA\TESTDB\REDO03.LOG 
         2         ONLINE  C:\ORACLE\ORADATA\TESTDB\REDO02.LOG 
         1 STALE   ONLINE  C:\ORACLE\ORADATA\TESTDB\REDO01.LOG

Step-3 Shutdown the database and start it in mount stage

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  135338868 bytes 
Fixed Size                   453492 bytes 
Variable Size             109051904 bytes 
Database Buffers           25165824 bytes 
Redo Buffers                 667648 bytes 
Database mounted.
SQL>

Step-4: Add redo log file members

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'F:\Multiplexing_REDO_Log_Files\REDO01.RDO' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'F:\Multiplexing_REDO_Log_Files\REDO02.RDO' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'F:\Multiplexing_REDO_Log_Files\REDO03.RDO' TO GROUP 3;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'E:\Multiplexing_REDO_Log_Files\REDO01.RDO' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'E:\Multiplexing_REDO_Log_Files\REDO02.RDO' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'E:\Multiplexing_REDO_Log_Files\REDO03.RDO' TO GROUP 3;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\Multiplexing_REDO_Log_Files\REDO01.RDO' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\Multiplexing_REDO_Log_Files\REDO02.RDO' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\Multiplexing_REDO_Log_Files\REDO03.RDO' TO GROUP 3;

Database altered.

SQL>

Step-5: open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

Step-6: Now check the redo log files

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# 
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
         1          1         44  104857600          4 NO  INACTIVE               1144787 
         2          1         45  104857600          4 NO  CURRENT                1168470 
         3          1         43  104857600          4 NO  INACTIVE               1111078 

SQL> SELECT * FROM V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                        
---------- ------- ------- ---------------------------------------------
         3 STALE   ONLINE  C:\ORACLE\ORADATA\TESTDB\REDO03.LOG          
         2         ONLINE  C:\ORACLE\ORADATA\TESTDB\REDO02.LOG          
         1 STALE   ONLINE  C:\ORACLE\ORADATA\TESTDB\REDO01.LOG          
         1 INVALID ONLINE  F:\MULTIPLEXING_REDO_LOG_FILES\REDO01.RDO    
         2 INVALID ONLINE  F:\MULTIPLEXING_REDO_LOG_FILES\REDO02.RDO    
         3 INVALID ONLINE  F:\MULTIPLEXING_REDO_LOG_FILES\REDO03.RDO    
         1 INVALID ONLINE  E:\MULTIPLEXING_REDO_LOG_FILES\REDO01.RDO    
         2 INVALID ONLINE  E:\MULTIPLEXING_REDO_LOG_FILES\REDO02.RDO    
         3 INVALID ONLINE  E:\MULTIPLEXING_REDO_LOG_FILES\REDO03.RDO    
         1 INVALID ONLINE  D:\MULTIPLEXING_REDO_LOG_FILES\REDO01.RDO    
         2 INVALID ONLINE  D:\MULTIPLEXING_REDO_LOG_FILES\REDO02.RDO    
         3 INVALID ONLINE  D:\MULTIPLEXING_REDO_LOG_FILES\REDO03.RDO    

12 rows selected.

SQL>

All the redo log files are added.