如何删除日志组成员( DROP LOGFILE MEMBER )

整理自己测试数据库的日志.

第一个步骤，转移日志文件位置：

SQL> alter database mount;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1         16   52428800          2 NO  INACTIVE
       209996 04-MAR-12

         3          1         15   52428800          2 NO  INACTIVE
       209220 04-MAR-12

         2          1         17   52428800          2 NO  CURRENT
       230802 05-MAR-12


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG
C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_1_6Y993YBB_.LOG
C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG
C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG
C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG
C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_3_6Y993YX3_.LOG

6 rows selected.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG' to 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LO
G';

Database altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG' to 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LO
G';

Database altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG' to 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LO
G';

Database altered.

缺省的每组日志有两个成员：

SQL> alter database open;

Database altered.
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                        IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG                         NO
         1         ONLINE  C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_1_6Y993YBB_.LOG             YES
         2         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG                         NO
         2         ONLINE  C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG             YES
         3         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG                         NO
         3         ONLINE  C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_3_6Y993YX3_.LOG             YES

6 rows selected.

清除其中的一个日志组成员：

SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_1_6Y993YBB_.LOG';

Database altered.

SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG';
alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG'
ORA-00312: online log 2 thread 1: 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG'


SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_3_6Y993YX3_.LOG';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member 'C:\ORACLE\FLASH_RECOVERY_AREA\ORA10G\ONLINELOG\O1_MF_2_6Y993YLR_.LOG';

Database altered.

最后的日志情况：

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                        IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_1_6Y993Y41_.LOG                         NO
         2         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_2_6Y993YGP_.LOG                         NO

         3         ONLINE  D:\ORACLE\ORADATA\ORA10G\ONLINELOG\O1_MF_3_6Y993YRD_.LOG                         NO