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

清風艾艾發表於2016-04-21
Oracle
    一平臺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> 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
操作完畢！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2085209/，如需轉載，請註明出處，否則將追究法律責任。

相關文章