当前位置： 首页 > Oracle, oracle 10g > 正文

为何新增的日志成员删除不掉

Jan042015
作者：dbdream   发布：2015-01-04 13:48   分类：Oracle, oracle 10g     抢沙发  

最近在添加redo member的时候，发现个 有意思的问题，就是新添加的redo member只有当前日志组的可以删，其他日志组新添加的redo member都不可以删，也在网上看了一些帖子，都是一些解决方案，没有说明具体的原因，我研究了下，找到了原因，下面演示并解释下为什么会这样。

我的测试环境OEL5.4，ORACLE 10.2.0.1.0，当前有3组日志组，每组一个成员。

SYS@PROD> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------  ------------- ---------
         1          1          7  104857600          1 NO  INACTIVE           230142 11-AUG-14
         2          1          8  104857600          1 NO  INACTIVE           233406 11-AUG-14
         3          1          9  104857600          1 NO  CURRENT            233416 11-AUG-14

下面先为每组日志组添加一个成员。

SYS@PROD> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk2/redo01_a.log' to group 1;

Database altered.
SYS@PROD> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk2/redo02_a.log' to group 2;

Database altered.
SYS@PROD> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk2/redo03_a.log' to group 3;

Database altered.

现在每组日志有两个成员。

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS      FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------  ------------- ---------
         1          1          7  104857600          2 NO  CURRENT            230142 11-AUG-14
         2          1          5  104857600          2 NO  INACTIVE           168994 10-AUG-14
         3          1          6  104857600          2 NO  INACTIVE           201858 11-AUG-14

SYS@PROD> select group#,member from v$logfile order by 1;

    GROUP#  MEMBER
----------  --------------------------------------------------
         1  /u01/app/oracle/oradata/PROD/disk2/redo01_a.log
         1  /u01/app/oracle/oradata/PROD/disk1/redo01.log
         2  /u01/app/oracle/oradata/PROD/disk1/redo02.log
         2  /u01/app/oracle/oradata/PROD/disk2/redo02_a.log
         3  /u01/app/oracle/oradata/PROD/disk2/redo03_a.log
         3  /u01/app/oracle/oradata/PROD/disk1/redo03.log

下面删除新增的redo member，就会报错。

SYS@PROD> alter database drop logfile member'/u01/app/oracle/oradata/PROD/disk1/redo03_a.log';
alter database drop logfile member'/u01/app/oracle/oradata/PROD/disk1/redo03_a.log'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 3
ORA-01517: log member: '/u01/app/oracle/oradata/PROD/disk1/redo03_a.log'

网上都说切换几次日志就可以删了，可是为什么现在不让删呢？看下日志文件的状态就知道了。

SYS@PROD> select group#,status,member from v$logfile order by 1;

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1         /u01/app/oracle/oradata/PROD/disk1/redo01.log
         2 STALE   /u01/app/oracle/oradata/PROD/disk1/redo02.log
         3 STALE   /u01/app/oracle/oradata/PROD/disk1/redo03.log
         1 INVALID /u01/app/oracle/oradata/PROD/disk1/redo01_a.log
         2 INVALID /u01/app/oracle/oradata/PROD/disk1/redo02_a.log
         3 INVALID /u01/app/oracle/oradata/PROD/disk1/redo03_a.log

可见，除了CURRENT状态的第一组日志的老成员状态正常，第二组和第三组日志的老成员都变成了STALE状态了，所以新增的日志成员状态都是INVALID状态。这里简单说下这些状态的含义，STALE状态说明日志是陈旧的，不完全的，一般如果存在这种状态的日志文件，最好不要关闭数据库，否则也能打不开。INVALID状态说明这些文件是不可用的，在这里的状态应该是还没有被使用。

切换几次日志，在看看状态。

SYS@PROD> alter system switch logfile;

System altered.

SYS@PROD> alter system switch logfile;

System altered.

SYS@PROD> alter system switch logfile;

System altered.

SYS@PROD> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------    ------------- ---------
         1          1          7  104857600          2 NO  CURRENT             230142 11-AUG-14
         2          1          8  104857600          2 NO  ACTIVE              233406 11-AUG-14
         3          1          9  104857600          2 NO  ACTIVE              233416 11-AUG-14

SYS@PROD> alter system checkpoint;

System altered.

SYS@PROD> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------   ------------- ---------
         1          1          7  104857600          2 NO  INACTIVE            233482 11-AUG-14
         2          1          8  104857600          2 NO  INACTIVE            233406 11-AUG-14
         3          1          9  104857600          2 NO  CURRENT             233416 11-AUG-14

SYS@PROD> select group#,status,member from v$logfile;

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1         /u01/app/oracle/oradata/PROD/disk1/redo01.log
         2         /u01/app/oracle/oradata/PROD/disk1/redo02.log
         3         /u01/app/oracle/oradata/PROD/disk1/redo03.log
         1         /u01/app/oracle/oradata/PROD/disk1/redo01_a.log
         2         /u01/app/oracle/oradata/PROD/disk1/redo02_a.log
         3         /u01/app/oracle/oradata/PROD/disk1/redo03_a.log

6 rows selected.

日志文件在切换后，都被重新格式化了，此时除CURRENT状态的日志组外的日志成员是可以删的。

 

SYS@PROD> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo02_a.log';

Database altered.

SYS@PROD> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo03_a.log';

Database altered.

SYS@PROD> alter system switch logfile;

System altered.

SYS@PROD> alter system checkpoint;

System altered.

SYS@PROD> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo01_a.log';

Database altered.

那么新增加的日志组，是不是也是这样的呢？下面来测试一下，先为这三组日志添加成员，每组还是两个成员。

SYS@PROD> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk2/redo01_a.log' to group 1;

Database altered.

SYS@PROD> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk2/redo02_a.log' to group 2

Database altered.

SYS@PROD> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk2/redo03_a.log' to group 3

Database altered.

在新创建两个日志组，每组也是两个成员。

SYS@PROD> alter database add logfile group 4 ('/u01/app/oracle/oradata/PROD/disk1/redo04.log','/u01/app/oracle/oradata/PROD/disk2/redo04_a.log') size 100M;

Database altered.

SYS@PROD> alter database add logfile group 5 ('/u01/app/oracle/oradata/PROD/disk1/redo05.log','/u01/app/oracle/oradata/PROD/disk2/redo05_a.log') size 100M;

Database altered.

看看新增和日志组和新增成员是不是一样的状态。

SYS@PROD> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS        FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ----------    ------------- ---------
         1          1         10  104857600          2 NO  CURRENT              233482 11-AUG-14
         2          1          8  104857600          2 NO  INACTIVE             233406 11-AUG-14
         3          1          9  104857600          2 NO  INACTIVE             233416 11-AUG-14
         4          1          0  104857600          2 YES UNUSED                    0
         5          1          0  104857600          2 YES UNUSED                    0

SYS@PROD> select group#,status,member from v$logfile order by 1;

    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         1 INVALID /u01/app/oracle/oradata/PROD/disk2/redo01_a.log
         1         /u01/app/oracle/oradata/PROD/disk1/redo01.log
         2         /u01/app/oracle/oradata/PROD/disk1/redo02.log
         2 INVALID /u01/app/oracle/oradata/PROD/disk2/redo02_a.log
         3 INVALID /u01/app/oracle/oradata/PROD/disk2/redo03_a.log
         3         /u01/app/oracle/oradata/PROD/disk1/redo03.log
         4         /u01/app/oracle/oradata/PROD/disk1/redo04.log
         4         /u01/app/oracle/oradata/PROD/disk2/redo04_a.log
         5         /u01/app/oracle/oradata/PROD/disk2/redo05_a.log
         5         /u01/app/oracle/oradata/PROD/disk1/redo05.log

10 rows selected.

新增日志组的成员状态是正常的，因为都是空的，而在原有日志组中添加的成员，因为原有日志文件中是有信息的，而新增的文件还没有信息，新增的文件和原因的文件并不一致，所以新增的日志成员的状态是INVALID状态。

既然新增的日志组的成员状态正常，那么自然新增的日志组的成员是可以删除的。

SYS@PROD> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk2/redo04_a.log';

Database altered.

SYS@PROD> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk2/redo05_a.log';

Database altered.

这个案例也告诉我们，在新增日志成员的时候，最好切换几次日志，使新增的成员和原有成员信息一致。

本文固定链接: http://www.dbdream.com.cn/2015/01/%e4%b8%ba%e4%bd%95%e6%96%b0%e5%a2%9e%e7%9a%84%e6%97%a5%e5%bf%97%e6%88%90%e5%91%98%e5%88%a0%e9%99%a4%e4%b8%8d%e6%8e%89/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2015年01月04日发表在 Oracle, oracle 10g 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 为何新增的日志成员删除不掉 | 信春哥，系统稳，闭眼上线不回滚！
关键字: , , , ,
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！

为何新增的日志成员删除不掉：等您坐沙发呢！

发表评论

快捷键：Ctrl+Enter