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

原创 eygle 2012-03-10

813

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

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

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

日志记录之。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

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

评论