一平臺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

SQL> conn / as sysdba
SQL> col MEMBER for a42

SQL> col status for a8

SQL> col MEMBER for a42

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;

系統已更改。

SQL> alter system switch logfile;

系統已更改。

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 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'

*

ERROR 位於第 1 行:

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狀態

SQL> alter system switch logfile;

系統已更改。

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

資料庫已更改。

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

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

3 CURRENT /data/oracle/redolog/redo03.dbf
操作完畢！

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

相關文章