Dropping Redo Log Groups and Members

Post by **jimb** » Sat Nov 03, 2012 6:52 pm

In some cases, you may want to drop an entire group of redo log members. For example, you want to reduce the number of groups in an instance redo log. In a different case, you may want to drop one or more specific redo log members. For example, if a disk failure occurs, you may need to drop all the redo log files on the failed disk so that the database does not try to write to the inaccessible files. In other situations, particular redo log files become unnecessary. For example, a file might be stored in an inappropriate location.

Dropping Log Groups

To drop a redo log group, you must have the ALTER DATABASE system privilege. Before dropping a redo log group, consider the following restrictions and precautions:
- An instance requires at least two groups of redo log files, regardless of the number of members in the groups. (A group comprises one or more members.)
- You can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur.
- Make sure a redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the V$LOG view.
SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;

SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;

GROUP# ARC STATUS BYTES
---------- --- ---------------- ----------
1 YES INACTIVE 52428800
2 NO CURRENT 52428800
3 YES INACTIVE 52428800

Drop a redo log group with the SQL statement ALTER DATABASE with the DROP LOGFILE clause.

The following statement drops redo log group number 1:
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;

GROUP# ARC STATUS BYTES
---------- --- ---------------- ----------
2 NO CURRENT 52428800
3 YES INACTIVE 52428800

When a redo log group is dropped from the database, and you are not using the Oracle-managed files feature, the operating system files are not deleted from disk. Rather, the control files of the associated database are updated to drop the members of the group from the database structure. After dropping a redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log files.

[TEST1011@ORCL11G /home/oracle] rm -rf /u01/app/oracle/oradata/ORCL11G/redo01.log

When using Oracle-managed files, the cleanup of operating systems files is done automatically for you.

Dropping Redo Log Members

To drop a redo log member, you must have the ALTER DATABASE system privilege. Consider the following restrictions and precautions before dropping individual redo log members:

- It is permissible to drop redo log files so that a multiplexed redo log becomes temporarily asymmetric. For example, if you use duplexed groups of redo log files, you can drop one member of one group, even though all other groups have two members each. However, you should rectify this situation immediately so that all groups have at least two members, and thereby eliminate the single point of failure possible for the redo log.

- An instance always requires at least two valid groups of redo log files, regardless of the number of members in the groups. (A group comprises one or more members.) If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid. To see a redo log file status, use the V$LOGFILE view. A redo log file becomes INVALID if the database cannot access it. It becomes STALE if the database suspects that it is not complete or correct. A stale log file becomes valid again the next time its group is made the active group.

- You can drop a redo log member only if it is not part of an active or current group. If you want to drop a member of an active group, first force a log switch to occur.

- Make sure the group to which a redo log member belongs is archived (if archiving is enabled) before dropping the member. To see whether this has happened, use the V$LOG view.

To drop specific inactive redo log members, use the ALTER DATABASE statement with the DROP LOGFILE MEMBER clause.

Query the redo log member to be dropped:
SQL> select GROUP#, MEMBER from v$logfile order by GROUP#;

SQL> select GROUP#, MEMBER from v$logfile order by GROUP#;

GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/ORCL11G/redo01.log
1 /u01/app/oracle/oradata/ORCL11G/redo01b.log
2 /u01/app/oracle/oradata/ORCL11G/redo02b.log
2 /u01/app/oracle/oradata/ORCL11G/redo02.log
3 /u01/app/oracle/oradata/ORCL11G/redo03b.log
3 /u01/app/oracle/oradata/ORCL11G/redo03.log

6 rows selected.

The following statement drops the redo log /u01/app/oracle/oradata/ORCL11G/redo03b.log:

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL11G/redo03b.log';

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL11G/redo03b.log';

Database altered.

When a redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping a redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log file.
[TEST1011@ORCL11G /home/oracle] rm -rf /u01/app/oracle/oradata/ORCL11G/redo03b.log

To drop a member of an active group, you must first force a log switch.

Reference: http://docs.oracle.com/cd/B28359_01/ser ... edo005.htm