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> 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29357786/viewspace-2085209/