ORACLE 删除redo組成員之ORA-00362&ORA-01609

    一平台oracle 9i資料庫在維護過程中，需要删除每個redo組的備援成員，遭遇ORA-00362，報錯資訊如下：

bash-3.00$ sqlplus /nolog   

SQL*Plus: Release 9.2.0.8.0 - Production on 星期四 4月 21 08:49:28 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba

SQL> col MEMBER for a42

SQL> col status for a8

SQL> select log.group#,log.status,logfile.member from v$log log, v$logfile logfile where log.group#=logfile.group# order by group#;

    GROUP# STATUS   MEMBER

---------- -------- ------------------------------------------

         1 INACTIVE /data/oracle/redolog/redo01.dbf

         1 INACTIVE /data/oracle/redolog/redo01_1.dbf

         2 INACTIVE /data/oracle/redolog/redo02.dbf

         2 INACTIVE /data/oracle/redolog/redo02_1.dbf

         3 CURRENT  /data/oracle/redolog/redo03.dbf

         3 CURRENT  /data/oracle/redolog/redo03_1.dbf

SQL> alter database drop logfile member '/data/oracle/redolog/redo01_1.dbf'; --按理來說處于inactive狀态的redo可以删除成員組成員，但是不行

alter database drop logfile member '/data/oracle/redolog/redo01_1.dbf'

*

ERROR 位于第 1 行:

ORA-00362: 組成組 1 中的有效日志檔案要求輸入成員

ORA-01517: 日志成員: '/data/oracle/redolog/redo01_1.dbf'

   --解決方法是，手工切換redo

SQL> alter system switch logfile;

系統已更改。

         1 ACTIVE   /data/oracle/redolog/redo01.dbf

         1 ACTIVE   /data/oracle/redolog/redo01_1.dbf

         2 CURRENT  /data/oracle/redolog/redo02.dbf

         2 CURRENT  /data/oracle/redolog/redo02_1.dbf

         3 INACTIVE /data/oracle/redolog/redo03.dbf

         3 INACTIVE /data/oracle/redolog/redo03_1.dbf

已選擇6行。

   --删除處于inactive狀态的redo組成員中的一個

SQL> alter database drop logfile member '/data/oracle/redolog/redo01_1.dbf';

資料庫已更改。

SQL> alter database drop logfile member '/data/oracle/redolog/redo03_1.dbf';

   --處于current狀态的redo組成員不能删除

SQL> alter database drop logfile member '/data/oracle/redolog/redo02_1.dbf';

alter database drop logfile member '/data/oracle/redolog/redo02_1.dbf'

ORA-01609: 日志2是線程1的目前日志 - 無法删除成員

ORA-00312: 聯機日志 2 線程 1: '/data/oracle/redolog/redo02.dbf'

ORA-00312: 聯機日志 2 線程 1: '/data/oracle/redolog/redo02_1.dbf'

   --手工切換redo，使redo group 2處于inactive狀态

         2 ACTIVE   /data/oracle/redolog/redo02.dbf

操作完畢！