You can use theALTER DATABASE command to drop log file groups or individual members from your database. The following
mouseover shows the syntax for both dropping a group and dropping a member:
ALTER DATABASE
The command to use.
DROP LOGFILE
The clause that drops a log file group.
DROP LOGFILE MEMBER
The clause that drops an individual log file.
GROUP group_number
Identifies the log file group to drop.
'existing_filename'
You may optionally identify the log file group to drop by naming its member files. If there is only one member filename, this is how you specify it.
('existing_filename', 'existing_filename'...)
If you are dropping a group with multiple files, you can identify it by listing all the filenames.
'filename'
When dropping individual log files, you identify the files by name. Oracle determines for itself to which group each file belongs.
Caveats
There are a few caveats to remember when dropping redo log files:
Oracle won't allow you to drop the currently active redo log group.
Oracle won't allow you to drop a redo log group that needs to be archived.
Oracle requires that you have at least two groups at all times.
TheDROP LOGFILE MEMBER clause cannot be used to drop all log files in a group. For that, you must useDROP LOGFILE.
Drop Group
If you are trying to drop a group and you suspect that it might be the active group, you can query the v$log view to make certain that's the case. v$log will return one row for each redo log group and will identify the current group by returning a value ofCURRENT in the status column for that group. You can force a log switch using theALTER SYSTEM command, which you will learn to do later in this module. In the next lesson, you will learn a couple of approaches to moving redo log files.