Files in Support of Backup and Recovery Operations - Oracle RMAN for Absolute Beginners - page 28

You can drop an inactive log group with the ALTER DATABASE DROP LOGFILE GROUP statement:
SQL> alter database drop logfile group <group #>;
If you attempt to drop the current online log group, Oracle returns an ORA-01623 error, stating that you can't drop
the current group. Use the ALTER SYSTEM SWITCH LOGFILE statement to switch the logs and make the next group the
current group:
SQL> alter system switch logfile;
After a log switch the log group that was previously the current group retains an active status as long as it contains
redo that Oracle requires to perform crash recovery. If you attempt to drop a log group with an active status, Oracle
throws an ORA-01624 error, indicating that the log group is required for crash recovery. Issue an ALTER SYSTEM
CHECKPOINT command to make the log group inactive:
SQL> alter system checkpoint;
Additionally, you can't drop an online redo log group if doing so leaves your database with only one log group. If
you attempt to do this, Oracle throws an ORA-01567 error and informs you that dropping the log group isn't permitted
because it would leave you with fewer than two log groups for your database (as mentioned earlier, Oracle requires at
least two redo log groups in order to function).
Dropping an online redo log group doesn't remove the log files from the OS. You have to use an OS command to
do this (such as the rm Linux/Unix command). Before you remove a file from the OS, ensure that it isn't in use and that
you don't remove a live online redo log file. For every database on the server, issue this query to view which online
redo log files are in use:
SQL> select member from v$logfile;
Before you physically remove a log file, first switch the online redo logs enough times that all online redo log
groups have recently been switched; doing so causes the OS to write to the file and thus give it a new timestamp. For
example, if you have three groups, make sure you perform at least three log switches:
SQL> alter system switch logfile;
SQL> /
SQL> /
Now, verify at the OS prompt that the log file you intend to remove doesn't have a new timestamp. First, go to the
directory containing the online redo log files:
$ cd /u01/oraredo/O12C
Then, list the files to view the latest modification date:
$ ls -altr
When you're absolutely sure the file isn't in use, you can remove it. The danger in removing a file is that if it
happens to be an in-use online redo log, and the only member of a group, you can cause serious damage to your
database. Ensure that you have a good backup of your database and that the file you're removing isn't used by any
databases on the server.