Print Friendly, PDF & Email

 
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 groups and 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 next views provide information regarding to redolog files:
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

So to start, let’s get details about current logfile groups and their members. By default a database has 3 groups with 1 member in each.

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

For 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

Since I want to create new groups with the default group numbers (1,2,3) I need temporary ones. For temporary groups all redo members are small in size, just in several MiB

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 creating new groups execute a manually logfile switch to start redo writing to the newly created logfile groups

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

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 groups (1,2,3) can be dropped

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

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 required groups with desired number of members in each

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;

Execute the manual logfile switch and drop temporary groups (101,102,103)

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


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.

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

Confirm current groups

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 operating system files are not deleted from a disk automatically after dropping a group you must remove it manually. Leave only those that are part of a database

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

oracle@dbpilot.net: rm redo01.log redo02.log redo03.log redo_101.log redo_102.log redo_103.log
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

If needed you can add an additional member into existing groups. You should remember if you have more than 1 member in group then you should place them on different physical disks. Otherwise, it’s highly useless to have all redolog members on the same disk. If disk fails, you will lose all members simultaneously.

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; 

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

Also, if needed you can remove one or more members from a 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';


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

A member can be deleted 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 arises if a member is active

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'

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 group the following error occurs

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 details about redo members and groups refer to the official documentation.

 
RELATED

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