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.
