Delete online redo log group members

Note the following when deleting an online redo log file group:
① When the log group is active or current, it cannot be deleted.
② After a log group is deleted at the database level, files on the operating system will not be deleted by the level chain.
③ An Oracle database instance must contain at least two online redo log groups.

1. When the log group is active or current, it cannot be deleted.
1) query the status of the log group in the current system
Sys @ ora10g> select group #, status from v $ log;

GROUP # STATUS
--------------------------
1 CURRENT
2 INACTIVE
3 INACTIVE

2) Try to delete the online redo log group in the "CURRENT" status
The log group shown as "CURRENT" is the first group.
Sys @ ora10g> alter database drop logfile group 1;
Alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance ora10g (thread 1)-cannot drop
ORA-00312: online log 1 thread 1: '/oracle/ora10gR2/oradata/ora10g/redo01.log'

The prompt information is clearly described. Because the first group of logs to be deleted is the current log group of the ora10g instance, deletion is not allowed.

2. After a log group is deleted at the database level, files in the operating system will not be deleted by the level chain.
1) Obtain group member information
Sys @ ora10g> col MEMBER for a50
Sys @ ora10g> select group #, member from v $ logfile;

GROUP # MEMBER
------------------------------------------------------------
1/oracle/ora10gR2/oradata/ora10g/redo01.log
2/oracle/ora10gR2/oradata/ora10g/redo02.log
3/oracle/ora10gR2/oradata/ora10g/redo03.log

2) Delete the third log Group
Sys @ ora10g> alter database drop logfile group 3;

Database altered.

Sys @ ora10g> select group #, member from v $ logfile;

GROUP # MEMBER
------------------------------------------------------------
1/oracle/ora10gR2/oradata/ora10g/redo01.log
2/oracle/ora10gR2/oradata/ora10g/redo02.log

Deleted successfully.

3) check whether the operating system file is deleted.
Sys @ ora10g>! Ls-l/oracle/ora10gR2/oradata/ora10g/redo03.log
-Rw-r ----- 1 oracle oinstall 52429312 Jul 12/oracle/ora10gR2/oradata/ora10g/redo03.log

It can be seen that, although the log group has been successfully deleted at the database level, the corresponding files remain in the operating system.
For this reason, if the operating system is not cleaned up, the ORA-27038 error is reported when you create a log group using the same file, prompting that the file already exists, as shown below.
Sys @ ora10g> alter database add logfile group 3 ('/oracle/ora10gR2/oradata/ora10g/redo03.log') size 50 m;
Alter database add logfile group 3 ('/oracle/ora10gR2/oradata/ora10g/redo03.log') size 50 m
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/ora10gR2/oradata/ora10g/redo03.log'-file cannot be created
ORA-27038: created file already exists
Additional information: 1

4) manually delete residual files on the Operating System
Sys @ ora10g>! Rm-f/oracle/ora10gR2/oradata/ora10g/redo03.log

In this way, the log file group deletion task is complete.

3. An Oracle database instance must contain at least two online redo log groups.
Purpose: To switch to archive mode.
1) confirm the current system day group information
Sys @ ora10g> select group #, member from v $ logfile;

GROUP # MEMBER
------------------------------------------------------------
1/oracle/ora10gR2/oradata/ora10g/redo01.log
2/oracle/ora10gR2/oradata/ora10g/redo02.log

2) Try to delete a group of logs again
Currently, there are only two groups of log groups in the system. In this case, we try to delete another group of logs to see how the results are.
Sys @ ora10g> alter database drop logfile group 2;
Alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 wocould leave less than 2 log files for instance ora10g (thread 1)
ORA-00312: online log 2 thread 1: '/oracle/ora10gR2/oradata/ora10g/redo02.log'

The "ORA-01567" error message clearly states everything: dropping log 2 wocould leave less than 2 log files for instance ora10g (thread 1)

4. Summary
This article tests three aspects that need to be paid attention to during online redo log file group deletion. pay more attention to this content during routine maintenance.
We recommend that you create multiple log groups based on specific application characteristics and ensure that each group of logs contains multiple log members to prevent system faults caused by damage to individual log files.

Author: "FJXSUNMIT"