168大数据
标题: ORA-00362 ORA-01517为何新增的日志成员删除不掉 [打印本页]
作者: dbdream 时间: 2014-8-18 17:42
标题: ORA-00362 ORA-01517为何新增的日志成员删除不掉
最近在添加redo member的时候，发现个有意思的问题，就是新添加的redo member只有当前日志组的可以删，其他日志组新添加的redo member都不可以删，也在网上看了一些帖子，都是一些解决方案，没有说明具体的原因，我研究了下，找到了原因，下面演示并解释下为什么会这样。
我的测试环境OEL5.4，ORACLE 10.2.0.1.0，当前有3组日志组，每组一个成员。
[AppleScript] 纯文本查看 复制代码
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
下面先为每组日志组添加一个成员。
[AppleScript] 纯文本查看 复制代码
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.
现在每组日志有两个成员。
[AppleScript] 纯文本查看 复制代码
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
下面删除新增的redomember，就会报错。
[AppleScript] 纯文本查看 复制代码
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'
网上都说切换几次日志就可以删了，可是为什么现在不让删呢？看下日志文件的状态就知道了。
[AppleScript] 纯文本查看 复制代码
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状态说明这些文件是不可用的，在这里的状态应该是还没有被使用。
切换几次日志，在看看状态。
[AppleScript] 纯文本查看 复制代码
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状态的日志组外的日志成员是可以删的。
[AppleScript] 纯文本查看 复制代码
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.
那么新增加的日志组，是不是也是这样的呢？下面来测试一下，先为这三组日志添加成员，每组还是两个成员。
[AppleScript] 纯文本查看 复制代码
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.
在新创建两个日志组，每组也是两个成员。
[AppleScript] 纯文本查看 复制代码
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.
看看新增和日志组和新增成员是不是一样的状态。
[AppleScript] 纯文本查看 复制代码
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状态。
既然新增的日志组的成员状态正常，那么自然新增的日志组的成员是可以删除的。
[AppleScript] 纯文本查看 复制代码
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.
这个案例也告诉我们，在新增日志成员的时候，最好切换几次日志，使新增的成员和原有成员信息一致。
[groupid=393]Oracle DBA交流[/groupid]
| 欢迎光临 168大数据 (http://www.bi168.cn/) |
Powered by Discuz! X3.2 |