马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
最近在添加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.
这个案例也告诉我们,在新增日志成员的时候,最好切换几次日志,使新增的成员和原有成员信息一致。
来自群组: Oracle DBA交流 |