丢失一个logfile member会怎么样?

当logfile的一个group里面有多个member的时候,如果丢失一个member,oracle的文档说数据库可以正常工作:

If a disk that contains an online redo log file fails, other copies
are still intact and available to Oracle. System operation is not
interrupted and the lost online redo log files can be easily
recovered.

我们来做一个实验,先看看当前的数据库:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/dingjia/guangxi/redo01.log
/home/oracle/dingjia/guangxi/redo02.log
/home/oracle/dingjia/guangxi/redo03.log
SQL>  select group#,members,status,sum(bytes/1024/1024) from v$log
          group by group#,members,status;  2  

    GROUP#    MEMBERS STATUS	       SUM(BYTES/1024/1024)
---------- ---------- ---------------- --------------------
	 1	    1 INACTIVE			       2048
	 3	    1 INACTIVE			       2048
	 2	    1 CURRENT			       2048

有三个group的logfile,我们向第一个group里面增加一个member

SQL> alter database add logfile member '/home/oracle/dingjia/guangxi/redo11.log' to group 1;
Database altered.
SQL> select GROUP# ,STATUS , MEMBER from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- ----------------------------------------
	 1	   /home/oracle/dingjia/guangxi/redo01.log
	 2	   /home/oracle/dingjia/guangxi/redo02.log
	 3	   /home/oracle/dingjia/guangxi/redo03.log
	 1	   /home/oracle/dingjia/guangxi/redo11.log

然后我们通过 alter system switch logfile 将第一组logfile变成current,再删除其中一个logfile。

       SQL> select group#,members,status,sum(bytes/1024/1024) from v$log
          group by group#,members,status  2  ;

    GROUP#    MEMBERS STATUS	       SUM(BYTES/1024/1024)
---------- ---------- ---------------- --------------------
	 3	    1 INACTIVE			       2048
	 2	    1 INACTIVE			       2048
	 1	    2 CURRENT			       2048
	 
SQL> host rm  /home/oracle/dingjia/guangxi/redo11.log

在oracle的警告日志里有提示 $ tail -n 50 /u01/app/oracle/diag/rdbms/guangxi/guangxi/trace/alert_guangxi.log

Errors in file /u01/app/oracle/diag/rdbms/guangxi/guangxi/trace/guangxi_arc0_10972.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/dingjia/guangxi/redo11.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

数据库仍然在正常运转,运行各种测试都不影响。
解决的办法就把有故障的log member删除

SQL> alter database drop logfile member '/home/oracle/dingjia/guangxi/redo11.log';

Database altered.
姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页