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.

multiplex redo log files

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.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)