MANAGING THE REDO LOG GROUPS AND MEMBERS
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
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0