Print Friendly, PDF & Email
How to Add, Remove, or Resize redo log members (redo files) in an Oracle Database? How to create or drop a redo log group?

REQUEST
Add a redo member to a group.
Remove a redo member from a group.
Resize a redo file.
Recreate a redo group.

DETAILS
A simple schema about 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 views provide information about all REDO LOG MEMBERS and all REDO LOG GROUPS in an Oracle database:

The V$LOG displays the redo log file information from the control file.
The V$LOGFILE identifies redo log groups and members and member status.
The V$LOG_HISTORY contains log history information.

TESTCASE
By default, when a database is created it has 3redo log groups with 1 redo log member in each group.

-- A query for a standalone database 

SET LINES 200 
SET PAGES 999
COL LGWR FOR A4 
COL GROUP FOR 99999
COL STATUS FOR A8 
COL TYPE FOR A10 
COL MEMBER FOR A10
COL FILENAME FOR A50 

SELECT DECODE(B.STATUS,'CURRENT','-->',NULL) AS LGWR, B.GROUP# AS "GROUP", A.TYPE, B.STATUS, B.ARCHIVED, 
B.SEQUENCE# AS "SEQUENCE", B.BYTES/1024/1024 "MB", B.MEMBERS "GROUPMEMBER", A.MEMBER "FILENAME"
FROM V$LOGFILE A, V$LOG B WHERE A.GROUP#=B.GROUP# order by "GROUP";
LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- -----------------------------------
          1 ONLINE     INACTIVE NO                39         50           1 /oracle/oradata/DBPILOT/redo01.log
          2 ONLINE     INACTIVE NO                40         50           1 /oracle/oradata/DBPILOT/redo02.log
-->       3 ONLINE     CURRENT  NO                41         50           1 /oracle/oradata/DBPILOT/redo03.log
-- A query for a cluster(RAC) database 

SET LINES 200 
SET PAGES 999
COL LGWR FOR A6 
COL GROUP FOR 99999
COL STATUS FOR A8 
COL TYPE FOR A10 
COL MEMBER FOR A10
COL FILENAME FOR A50 

SELECT DECODE(B.STATUS,'CURRENT', THREAD# || ' -->',NULL) AS LGWR, B.THREAD#, B.GROUP# AS "GROUP", A.TYPE, B.STATUS, 
B.ARCHIVED, B.SEQUENCE# AS "SEQUENCE", B.BYTES/1024/1024 "MB", B.MEMBERS "GROUPMEMBER", A.MEMBER "FILENAME"
FROM V$LOGFILE A, V$LOG B WHERE A.GROUP#=B.GROUP# order by "GROUP";
LGWR   THREAD#  GROUP TYPE       STATUS   ARC   SEQUENCE    MB GROUPMEMBER FILENAME
------ ------- ------ ---------- -------- --- ---------- ----- ----------- --------------------------------------------------
1 -->        1      5 ONLINE     CURRENT  NO      162355   500           1 +DATA001/DBPILOT/ONLINELOG/group_5.894.997617557
             1      6 ONLINE     ACTIVE   YES     162354   500           1 +DATA001/DBPILOT/ONLINELOG/group_6.2502.997617557
             2      7 ONLINE     ACTIVE   YES     166809   500           1 +DATA001/DBPILOT/ONLINELOG/group_7.4946.997617559
2 -->        2      8 ONLINE     CURRENT  NO      166810   500           1 +DATA001/DBPILOT/ONLINELOG/group_8.8269.997617559

 
How to create new redo log group?

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 ('/oracle/oradata/DBPILOT/redo_101.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE GROUP  102 ('/oracle/oradata/DBPILOT/redo_102.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE GROUP  103 ('/oracle/oradata/DBPILOT/redo_103.log') SIZE 10M;

After temporary redo log groups were created, manually execute LOGFILE SWITH command to start the redo writing to a newly created redo log groups

-- Perform manual LOGFILE switch

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
-- One of the temporary redo log group must be selected

LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- -------------------------------------
          1 ONLINE     INACTIVE NO                39         50           1 /oracle/oradata/DBPILOT/redo01.log
          2 ONLINE     INACTIVE NO                40         50           1 /oracle/oradata/DBPILOT/redo02.log
          3 ONLINE     INACTIVE NO                41         50           1 /oracle/oradata/DBPILOT/redo03.log
        101 ONLINE     INACTIVE NO                42         10           1 /oracle/oradata/DBPILOT/redo_101.log
        102 ONLINE     INACTIVE NO                43         10           1 /oracle/oradata/DBPILOT/redo_102.log
-->     103 ONLINE     CURRENT  NO                44         10           1 /oracle/oradata/DBPILOT/redo_103.log

Now default redo log groups numbered as 1,2,3 can be dropped

-- Dropping redo log groups with numbers 1,2,3 

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
-- Only temporary redo log groups exist

LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- ------------------------------------
        101 ONLINE     INACTIVE NO                42         10           1 /oracle/oradata/DBPILOT/redo_101.log
        102 ONLINE     INACTIVE NO                43         10           1 /oracle/oradata/DBPILOT/redo_102.log
-->     103 ONLINE     CURRENT  NO                44         10           1 /oracle/oradata/DBPILOT/redo_103.log

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

-- Each redo log member of 500 MiB 

ALTER DATABASE ADD LOGFILE GROUP  1 ('/oracle/oradata/DBPILOT/redo_01a.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP  2 ('/oracle/oradata/DBPILOT/redo_02a.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP  3 ('/oracle/oradata/DBPILOT/redo_03a.log') SIZE 500M;

When new redo log groups are created execute again manual LOGFILE SWITH 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;
-- Current redo log group is not the part of the temporary redo log groups - 101, 102, 103

LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- --------------------------------------------------
          1 ONLINE     INACTIVE NO                45        500           1 /oracle/oradata/DBPILOT/redo_01a.log
          2 ONLINE     INACTIVE NO                46        500           1 /oracle/oradata/DBPILOT/redo_02a.log
-->       3 ONLINE     CURRENT  NO                47        500           1 /oracle/oradata/DBPILOT/redo_03a.log
        101 ONLINE     INACTIVE NO                42         10           1 /oracle/oradata/DBPILOT/redo_101.log
        102 ONLINE     INACTIVE NO                43         10           1 /oracle/oradata/DBPILOT/redo_102.log
        103 ONLINE     INACTIVE NO                44         10           1 /oracle/oradata/DBPILOT/redo_103.log

6 rows selected.
-- Drop temporary redo log groups

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

That’s it. I’ve recreated redo log groups with desired group number and desired redo log member size

LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- ------------------------------------
          1 ONLINE     INACTIVE NO                45        500           1 /oracle/oradata/DBPILOT/redo_01a.log
          2 ONLINE     INACTIVE NO                46        500           1 /oracle/oradata/DBPILOT/redo_02a.log
-->       3 ONLINE     CURRENT  NO                47        500           1 /oracle/oradata/DBPILOT/redo_03a.log

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

## Dropped redo log members are still on a disk

oracle@dbpilot.net: ls -gGht --time-style='+'
total 1.7G
-rw-r----- 1 501M  redo_03a.log
-rw-r----- 1 501M  redo_01a.log
-rw-r----- 1 501M  redo_02a.log
-rw-r----- 1  11M  redo_103.log
-rw-r----- 1  51M  redo03.log
-rw-r----- 1  11M  redo_101.log
-rw-r----- 1  11M  redo_102.log
-rw-r----- 1  51M  redo01.log
-rw-r----- 1  51M  redo02.log

## Removing dropped redo log members manually from a disk 

oracle@dbpilot.net: rm redo01.log redo02.log redo03.log redo_101.log redo_102.log redo_103.log

## Only actual redo log members left

oracle@dbpilot.net: ls -gGht --time-style='+'
total 1.5G
-rw-r----- 1 501M  redo_03a.log
-rw-r----- 1 501M  redo_01a.log
-rw-r----- 1 501M  redo_02a.log

 
How to add an additional member into exsitings groups?

You should follow the rule of thumb, if you have more than 1 redo log member in a group then you should place them on different physical disks. Otherwise, it’s highly useless to have all redo log members on the same disk. If a disk fails, you will lose all members simultaneously.

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

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

-- Query list of redo logs

SET LINES 200 
SET PAGES 999
COL LGWR FOR A4 
COL GROUP FOR 99999
COL STATUS FOR A8 
COL TYPE FOR A10 
COL MEMBER FOR A10
COL FILENAME FOR A50 

SELECT DECODE(B.STATUS,'CURRENT','-->',NULL) AS LGWR, B.GROUP# AS "GROUP", A.TYPE, B.STATUS, B.ARCHIVED, 
B.SEQUENCE# AS "SEQUENCE", B.BYTES/1024/1024 "MB", B.MEMBERS "GROUPMEMBER", A.MEMBER "FILENAME"
FROM V$LOGFILE A, V$LOG B WHERE A.GROUP#=B.GROUP# order by "GROUP";


LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- ------------------------------------
          1 ONLINE     INACTIVE NO                45        500           2 /oracle/oradata/DBPILOT/redo_01a.log
          1 ONLINE     INACTIVE NO                45        500           2 /oracle/oradata/DBPILOT/redo_01b.log
          2 ONLINE     INACTIVE NO                46        500           2 /oracle/oradata/DBPILOT/redo_02a.log
          2 ONLINE     INACTIVE NO                46        500           2 /oracle/oradata/DBPILOT/redo_02b.log
-->       3 ONLINE     CURRENT  NO                47        500           2 /oracle/oradata/DBPILOT/redo_03a.log
-->       3 ONLINE     CURRENT  NO                47        500           2 /oracle/oradata/DBPILOT/redo_03b.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 '/oracle/oradata/DBPILOT/redo_01a.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/DBPILOT/redo_02a.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/DBPILOT/redo_03a.log';


-- Query list of redo log groups

LGWR  GROUP TYPE       STATUS   ARCHIVED    SEQUENCE         MB GROUPMEMBER FILENAME
---- ------ ---------- -------- --------- ---------- ---------- ----------- ------------------------------------
-->       1 ONLINE     CURRENT  NO                54        500           1 /oracle/oradata/DBPILOT/redo_01b.log
          2 ONLINE     INACTIVE NO                52        500           1 /oracle/oradata/DBPILOT/redo_02b.log
          3 ONLINE     INACTIVE NO                53        500           1 /oracle/oradata/DBPILOT/redo_03b.log
NOTE

A redo log member can be dropped only if it’s not ACTIVE, it’s not being ARCHIVED and it’s not the LASTone in a group.
For instance, the following error occurs if a redo log member is ACTIVE one

-- Dropping 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 DBPILOT (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/oracle/oradata/DBPILOT/redo_03a.log'
ORA-00312: online log 3 thread 1: '/oracle/oradata/DBPILOT/redo_03b.log'

-- Dropping a redo log member of an ACTIVE redo log group 

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/DBPILOT/redo_03a.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/DBPILOT/redo_03a.log'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 3
ORA-01517: log member: '/oracle/oradata/DBPILOT/redo_03a.log'

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

-- Dropping the only redo log member of a redo log group

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/DBPILOT/redo_01b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/DBPILOT/redo_01b.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /oracle/oradata/DBPILOT/redo_01b.log for group 1

For more infomation about REDO LOG MEMBERS and REDO LOG GROUPS refer to the ORACLE DOC.

REFERENCES
Redolog Group In ACTIVE State After Redo Log Switch Even If Database Is Idle (Doc ID 1928361.1)

 
 

Version  : 16:38 17.01.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0