【REDO】删除联机重做日志文件组成员的注意事项

2020-02-05 20:06:57

    关于联机重做日志文件组成员的删除需要注意以下几点：
    ①无法删除状态为“CURRENT”的日志组成员
    ②无法删除日志组中的后一个日志成员
    ③无法删除归档模式下未归档的日志组成员

1.无法删除状态为“CURRENT”的日志组成员
1）查询获取数据库中日志组和日志文件的状态
sys@ora10g> col MEMBER for a42
sys@ora10g> col status for a8
sys@ora10g> col MEMBER for a42
sys@ora10g> 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 CURRENT /oracle/ora10gR2/oradata/ora10g/redo01_b.log
         1 CURRENT /oracle/ora10gR2/oradata/ora10g/redo01.log
         2 INACTIVE /oracle/ora10gR2/oradata/ora10g/redo02_b.log
         2 INACTIVE /oracle/ora10gR2/oradata/ora10g/redo02.log

2）尝试删除状态为“CURRENT”的日志组成员
sys@ora10g> alter database drop logfile member '/oracle/ora10gR2/oradata/ora10g/redo01_b.log';
alter database drop logfile member '/oracle/ora10gR2/oradata/ora10g/redo01_b.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/oracle/ora10gR2/oradata/ora10g/redo01.log'
ORA-00312: online log 1 thread 1: '/oracle/ora10gR2/oradata/ora10g/redo01_b.log'

提示信息中给出所有状态为“CURRENT”的日志组成员信息，同时给出提示，这些成员是无法删除的。

2.无法删除日志组中的后一个日志成员
1）尝试删除第二组日志中的个日志成员
sys@ora10g> select log.group#,log.status,logfile.member from v$log log, v$logfile logfile where log.group#=logfile.group# and log.group#=2;

    GROUP# STATUS   MEMBER
---------- -------- ---------------------------------------------
         2 INACTIVE /oracle/ora10gR2/oradata/ora10g/redo02_b.log
         2 INACTIVE /oracle/ora10gR2/oradata/ora10g/redo02.log

sys@ora10g> alter database drop logfile member '/oracle/ora10gR2/oradata/ora10g/redo02_b.log';

Database altered.

sys@ora10g> select log.group#,log.status,logfile.member from v$log log, v$logfile logfile where log.group#=logfile.group# and log.group#=2;

    GROUP# STATUS   MEMBER
---------- -------- ---------------------------------------------
         2 INACTIVE /oracle/ora10gR2/oradata/ora10g/redo02.log

删除成功。

2）尝试删除第二组日志中的后一个日志成员
sys@ora10g> alter database drop logfile member '/oracle/ora10gR2/oradata/ora10g/redo02.log';
alter database drop logfile member '/oracle/ora10gR2/oradata/ora10g/redo02.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /oracle/ora10gR2/oradata/ora10g/redo02.log for group 2

结论得以验证，ORA-00361错误提示表明我们无法删除重做日志组中的后一个日志成员。

3.无法删除归档模式下未归档的日志组成员
这一条结论是显然的，否则Oracle将无法保证事务的可恢复性。

4.小结
    本文就联机重做日志文件组成员删除过程中需要注意的三个方面进行了测试，在日常维护过程中就此内容需要多加注意。
    建议根据具体的应用特点创建多个日志组，并且保证每组日志中包含多个日志成员，防止因个别日志文件损坏导致系统故障。

Good luck.

secooler
11.07.13

-- The End --

分享好友

分享这个小栈给你的朋友们，一起进步吧。