DEMO: MANAGING THE REDO LOG GROUPS AND MEMBERS
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$LOGFILE contains information about redo log files.
The V$LOG_HISTORY displays log history information from the control file.
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
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
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
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)
Tags In
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- Sessions
- SQL Trace
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum