MANAGING THE REDO LOG GROUPS AND MEMBERS
REQUEST
Add a redo member to a group.
Remove a redo member from a group.
Resize a redo file.
Recreate a redo 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
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0