OracleDBA之路之Redo log files(三)

Dropping Redo Log Members
一个组中至少要包含一个member，如果只剩下一个将无法删除，这种时候你只能删除该组
你不能删除一个工作在 active 或者current 状态的member，如果你要删除工作在active状态的member，那么你必须强制切换该redo log file：alter system switch logfile;

SQL> select group#,status from v$log;
 
    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 ACTIVE
         3 ACTIVE
         4 CURRENT
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         4         ONLINE  /u01/oradata/houzhh/redo04.rdo           NO
         4         ONLINE  /u01/oradata/houzhh/redo04b.rdo          NO
         4         ONLINE  /u01/oradata/houzhh/redo04c.rdo          NO

如果删除一个正在 current状态group下的member
SQL> alter database drop logfile member '/u01/oradata/houzhh/redo04.rdo';
alter database drop logfile member '/u01/oradata/houzhh/redo04.rdo'
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/u01/oradata/houzhh/redo04.rdo'
ORA-00312: online log 4 thread 1: '/u01/oradata/houzhh/redo04b.rdo'
ORA-00312: online log 4 thread 1: '/u01/oradata/houzhh/redo04c.rdo'

active 状态可以删除member
SQL> select group#,status from v$log;
 
    GROUP# STATUS
---------- ----------------
         4 ACTIVE
 
SQL> alter database drop logfile member '/u01/oradata/houzhh/redo04b.rdo';
 
Database altered.
 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         4         ONLINE  /u01/oradata/houzhh/redo04.rdo           NO
 
SQL>

如果该组下只有一个member时无法删除
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE  /u01/oradata/houzhh/redo03.log           NO
         2         ONLINE  /u01/oradata/houzhh/redo02.log           NO
         1         ONLINE  /u01/oradata/houzhh/redo01.log           NO
         4         ONLINE  /u01/oradata/houzhh/redo04.rdo           NO
 
SQL> alter database drop logfile member '/u01/oradata/houzhh/redo04.rdo';
alter database drop logfile member '/u01/oradata/houzhh/redo04.rdo'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /u01/oradata/houzhh/redo04.rdo for
group 4
 
SQL>

Dropping Log Groups
无法删除一个正在使用的group ，即该组状态为 Current 当前(活动)
SQL> select group#,status from v$log;
 
    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 ACTIVE
         3 ACTIVE
         4 CURRENT
 
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance houzhh (thread 1) - cannot drop
ORA-00312: online log 4 thread 1: '/u01/oradata/houzhh/redo04.rdo'

无法删除状态为ACTIVE的group
SQL> select group#,status from v$log;
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT
         4 ACTIVE
 
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01624: log 4 needed for crash recovery of instance houzhh (thread 1)
ORA-00312: online log 4 thread 1: '/u01/oradata/houzhh/redo04.rdo'

正确删除一个group 时 ，他的状态应该为INACTIVE
example
SQL> select group#,status from v$log;
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
         4 INACTIVE
 
SQL> alter database drop logfile group 4;
 
Database altered.
 
SQL> select group#,status from v$log;
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
 
SQL>