如何删除日志组成员

如何删除日志组成员

Oracle使用多个重做日志组来管理重做日志，这些重做日志组循环使用；每个日志组至少包含一个日志成员，保险起见，建议每个日志组包含两个以上成员，每个日志成员位于不同的磁盘上，写入日志时Oracle会将相同的内容同时写入同一个日志组的多个成员；
日志文件与日志成员是一回事。

第一个步骤，转移日志文件位置：
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
日志记录之。