Print Friendly, PDF & Email
How to Add, Remove, or Resize redo log members (redo files) in an Oracle Database.
SCHEMATIC

A simple schematic of what redo groups and redo members are

                   Disk A              Disk B             Disk C 

            ---------------------------------------------------------
            | redofile_1_A.log | redofile_1_B.log | redofile_1_C.log |   -- Group 1
            ---------------------------------------------------------
            | redofile_2_A.log | redofile_2_B.log | redofile_2_C.log |   -- Group 2
            ---------------------------------------------------------
            |                  |                  |                  |   -- Group n-1
            ---------------------------------------------------------
            | redofile_n_A.log | redofile_n_B.log | redofile_n_C.log |   -- Group n
            ---------------------------------------------------------

                 Member 1            Member 2           Member 3

The following data dictionary views provide information on redo logs

The V$LOG displays log file information from the control file.
The V$LOGFILE contains information about redo log files.
The V$LOG_HISTORY displays log history information from the control file.
DEMO

By default, a newly created database has 3 redo log groups with 1 redo log member in each group.

SET LINES 200 
SET PAGES 999
COL LGWR FOR A5 
COL THREAD FOR 99999
COL GROUP FOR 9999
COL STATUS FOR A8
COL ARCHIVED FOR A8 
COL LOGFILE_TYPE FOR A12 
COL MEMBER FOR A10
COL FILE_NAME FOR A50 
COL MB FOR 9999
COL IS_RECOVERY_DEST_FILE FOR A22

SELECT DECODE(LG.STATUS,'CURRENT', THREAD# || ' -->',NULL) AS LGWR,
       LG.THREAD# "THREAD",
       LG.GROUP# "GROUP",
       LF.TYPE LOGFILE_TYPE,
       LG.STATUS,
       LG.SEQUENCE# AS LOG_SEQUENCE,
       LG.ARCHIVED,
       LG.MEMBERS AS GROUP_MEMBER,
       ROUND(LG.BYTES/1024/1024) MB,
       LF.MEMBER FILE_NAME,
       LF.IS_RECOVERY_DEST_FILE
FROM
       V$LOG LG,
       V$LOGFILE LF
WHERE
       LG.GROUP#=LF.GROUP#
ORDER BY LG.GROUP#;
LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                    IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------- ----------------------
1 -->      1     1 ONLINE       CURRENT            19 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo01.log    NO
           1     2 ONLINE       INACTIVE           17 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo02.log    NO
           1     3 ONLINE       INACTIVE           18 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo03.log    NO

How to create a new redo log group?
In this demo, as I’m going to create new redo log groups with the same log group numbers as the current ones – 1,2,3, I need temporary ones. For temporary redo log groups, all redo log members are small in size, just in several MiB

-- New temporary redo log groups

ALTER DATABASE ADD LOGFILE GROUP 101 ('/u01/app/oracle/oradata/TST19C/redo_temp_1.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE GROUP 102 ('/u01/app/oracle/oradata/TST19C/redo_temp_2.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE GROUP 103 ('/u01/app/oracle/oradata/TST19C/redo_temp_3.log') SIZE 10M;

After temporary redo log groups are in place execute the ALTER SYSTEM SWITCH LOGFILE command to start the redo writing to a newly created redo log groups

-- Perform manual LOGFILE switching

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
-- Confirm that one of the temporary redo log groups is active

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----------------------
           1     1 ONLINE       INACTIVE           19 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo01.log          NO
           1     2 ONLINE       INACTIVE           17 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo02.log          NO
           1     3 ONLINE       INACTIVE           18 NO                  1   200 /u01/app/oracle/oradata/TST19C/redo03.log          NO
           1   101 ONLINE       INACTIVE           20 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_1.log     NO
           1   102 ONLINE       INACTIVE           21 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_2.log     NO
1 -->      1   103 ONLINE       CURRENT            22 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_3.log     NO

6 rows selected.

Drop default redo log groups numbered as 1, 2, 3

-- Dropping redo log groups 1,2,3

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
-- Confirm that only temporary redo log groups exist

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----------------------
           1   101 ONLINE       INACTIVE           20 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_1.log     NO
           1   102 ONLINE       INACTIVE           21 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_2.log     NO
1 -->      1   103 ONLINE       CURRENT            22 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_3.log     NO

In a next step I create new redo log groups numbered as 1, 2, 3

-- Each new redo log member of 500 MiB 

ALTER DATABASE ADD LOGFILE GROUP  1 ('/u01/app/oracle/oradata/TST19C/redo_01a.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP  2 ('/u01/app/oracle/oradata/TST19C/redo_02a.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP  3 ('/u01/app/oracle/oradata/TST19C/redo_03a.log') SIZE 500M;
-- Confirm that new groups 1,2,3 successfully added

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----------------------
           1     1 ONLINE       UNUSED              0 YES                 1   500 /u01/app/oracle/oradata/TST19C/redo_01a.log        NO
           1     2 ONLINE       UNUSED              0 YES                 1   500 /u01/app/oracle/oradata/TST19C/redo_02a.log        NO
           1     3 ONLINE       UNUSED              0 YES                 1   500 /u01/app/oracle/oradata/TST19C/redo_03a.log        NO
           1   101 ONLINE       INACTIVE           20 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_1.log     NO
           1   102 ONLINE       INACTIVE           21 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_2.log     NO
1 -->      1   103 ONLINE       CURRENT            22 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_3.log     NO

6 rows selected.

When new redo log groups are in place, execute again the ALTER SYSTEM SWITCH LOGFILE command and then drop temporary redo log groups 101, 102, 103

-- Manual LOGFILE switch 

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- Temporary redo logs are not active 

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_R
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----
           1     1 ONLINE       INACTIVE           23 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_01a.log        NO
           1     2 ONLINE       INACTIVE           24 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_02a.log        NO
1 -->      1     3 ONLINE       CURRENT            25 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_03a.log        NO
           1   101 ONLINE       INACTIVE           20 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_1.log     NO
           1   102 ONLINE       INACTIVE           21 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_2.log     NO
           1   103 ONLINE       INACTIVE           22 NO                  1    10 /u01/app/oracle/oradata/TST19C/redo_temp_3.log     NO

-- Dropping Temporary redo log groups 101, 102, 103

ALTER DATABASE DROP LOGFILE GROUP 101;
ALTER DATABASE DROP LOGFILE GROUP 102;
ALTER DATABASE DROP LOGFILE GROUP 103;
ALTER SYSTEM CHECKPOINT;

That’s it. Redo log groups have been re-created with desired group number and desired redo log member size

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_R
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----
           1     1 ONLINE       INACTIVE           23 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_01a.log        NO
           1     2 ONLINE       INACTIVE           24 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_02a.log        NO
1 -->      1     3 ONLINE       CURRENT            25 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_03a.log        NO
NOTE

As REDO FILES are not deleted from a disk automatically after dropping redo groups, you must remove them manually. Leave only those that are part of a database

## Dropped redo log groups are still on a disk

oracle@19c > cd /u01/app/oracle/oradata/TST19C/
oracle@19c > ls -gGht --time-style='+' *redo*
-rw-r-----. 1 501M  redo_03a.log
-rw-r-----. 1 501M  redo_02a.log
-rw-r-----. 1 501M  redo_01a.log
-rw-r-----. 1  11M  redo_temp_3.log
-rw-r-----. 1  11M  redo_temp_2.log
-rw-r-----. 1  11M  redo_temp_1.log
-rw-r-----. 1 201M  redo01.log
-rw-r-----. 1 201M  redo02.log
-rw-r-----. 1 201M  redo03.log

## Removing dropped redo log members manually from a disk 

oracle@19c > rm redo_temp_3.log redo_temp_2.log redo_temp_1.log redo01.log redo02.log redo03.log

## Only actual redo log members are left

oracle@19c > ls -gGht --time-style='+' *redo*
-rw-r-----. 1 501M  redo_03a.log
-rw-r-----. 1 501M  redo_02a.log
-rw-r-----. 1 501M  redo_01a.log

How to add an additional member into exsitings groups?

If you have more than 1 redo log member in a group, place them on different physical disks. Don't keep all redo members on the same disk, as if a disk fails, you will lose all members simultaneously.

-- Adding new redo log member into each group - 1,2,3

ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_01b.log' TO GROUP 1; 
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_02b.log' TO GROUP 2; 
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_03b.log' TO GROUP 3;

-- Perform LOGFILE SWITCH to start writing to both members of a group

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- Confirm that new members are succesfully added to redo log groups

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----------------------
           1     1 ONLINE       INACTIVE           23 NO                  2   500 /u01/app/oracle/oradata/TST19C/redo_01a.log        NO
           1     1 ONLINE       INACTIVE           23 NO                  2   500 /u01/app/oracle/oradata/TST19C/redo_01b.log        NO
           1     2 ONLINE       INACTIVE           24 NO                  2   500 /u01/app/oracle/oradata/TST19C/redo_02b.log        NO
           1     2 ONLINE       INACTIVE           24 NO                  2   500 /u01/app/oracle/oradata/TST19C/redo_02a.log        NO
1 -->      1     3 ONLINE       CURRENT            25 NO                  2   500 /u01/app/oracle/oradata/TST19C/redo_03b.log        NO
1 -->      1     3 ONLINE       CURRENT            25 NO                  2   500 /u01/app/oracle/oradata/TST19C/redo_03a.log        NO

6 rows selected.
oracle@19c >ls -gGht --time-style='+' *redo*
-rw-r-----. 1 501M  redo_03a.log
-rw-r-----. 1 501M  redo_03b.log
-rw-r-----. 1 501M  redo_02b.log
-rw-r-----. 1 501M  redo_01b.log
-rw-r-----. 1 501M  redo_02a.log
-rw-r-----. 1 501M  redo_01a.log

How to remove one ore more redo log members from redo log groups?

-- Removing one member from each group

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_01a.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_02a.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_03a.log';
-- Confirm that members are succesfully dropped

LGWR  THREAD GROUP LOGFILE_TYPE STATUS   LOG_SEQUENCE ARCHIVED GROUP_MEMBER    MB FILE_NAME                                          IS_RECOVERY_DEST_FILE
----- ------ ----- ------------ -------- ------------ -------- ------------ ----- -------------------------------------------------- ----------------------
1 -->      1     1 ONLINE       CURRENT            29 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_01b.log        NO
           1     2 ONLINE       INACTIVE           27 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_02b.log        NO
           1     3 ONLINE       INACTIVE           28 NO                  1   500 /u01/app/oracle/oradata/TST19C/redo_03b.log        NO
NOTE

You can drop a redo log member only if it’s not ACTIVE, it’s not being ARCHIVED, and it’s not the LAST one in a group. For instance, the following error occurs if a redo log member is the ACTIVE one.

-- Dropping an ACTIVE redo log group

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance tst19c (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TST19C/redo_03a.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TST19C/redo_03b.log'


-- Dropping a redo log member of an ACTIVE group 

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_03a.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_03a.log'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TST19C/redo_03a.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TST19C/redo_03b.log'

and if a member is the LAST in a redo log group, the following error occurs

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_03b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/TST19C/redo_03b.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /u01/app/oracle/oradata/TST19C/redo_03b.log for group 3
REFERENCES

Managing the Redo Log
Redolog Group In ACTIVE State After Redo Log Switch Even If Database Is Idle (Doc ID 1928361.1)
Primary Note: Overview of Redo Logs and Archiving (Doc ID 1503091.1)

Written At
21 NOV 202119:00
OEL Release
7.9 x64
Database Release
19.12.0.0.0