日志组是好的，只是组中的某个日志文件坏（日志组至少有一个日志文件时好的）
问题分析：
如果一个组中至少有一个成员是好的，丢失了日志文件，不会影响数据实例运行，但是在警告日志会体现。
修复办法：
1. 删除坏的日志成员，重建
2. 如果日志组已经归档了，则可以重建整个日志组（alter database clear logfile group n）
为了保护日志，至少4个组，每个组2个成员
SQL> select GROUP# , SEQUENCE# , STATUS , ARCHIVED ,MEMBERS from v$log;
GROUP# SEQUENCE# STATUS ARC MEMBERS
---------- ---------- ---------------- --- ----------
1 1 CURRENT NO 2
2 0 UNUSED YES 2
3 0 UNUSED YES 2
添加一个日志组：
SQL> alter database add logfile group 4 ('/u01/app/oracle/redo401.log' ,'/u01/app/oracle/redo402.log') size 50M ;
保证有效：切换一次日志组
SQL> alter system switch logfile ;
SQL> select GROUP# , SEQUENCE# , STATUS , ARCHIVED ,MEMBERS
from v$log; 2
GROUP# SEQUENCE# STATUS ARC MEMBERS
---------- ---------- ---------------- --- ----------
1 5 CURRENT NO 2
2 2 INACTIVE YES 2
3 3 INACTIVE YES 2
4 4 INACTIVE YES 2 #新增的日志组
查看日志组成员之间镜像：
SQL> select member ,status from v$logfile;
MEMBER STATUS
--------------------------------------------- -------
+DATA/orcl/onlinelog/group_3.263.853023339
+FRA/orcl/onlinelog/group_3.259.853023343
+DATA/orcl/onlinelog/group_2.262.853023327
+FRA/orcl/onlinelog/group_2.258.853023335
+DATA/orcl/onlinelog/group_1.261.853023319
+FRA/orcl/onlinelog/group_1.257.853023325
/u01/app/oracle/redo401.log
/u01/app/oracle/redo402.log
#status 为空，表示日志组成员正常使用的
模拟故障：删除其中任何一个成员，没有问题
查出第1个组的日志文件在哪里
SQL> select member ,status from v$logfile where group#=1;
MEMBER STATUS
--------------------------------------------- -------
+DATA/orcl/onlinelog/group_1.261.853023319
+FRA/orcl/onlinelog/group_1.257.853023325
SQL> shutdown immediate;
$ su - grid
$ asmcmd
ASMCMD> rm +DATA/orcl/onlinelog/group_1.261.853023319
SQL> startup #数据库能正常启动，看日志，有警告信息。
cd u01/app/oracle/diag/rdms/orcl/orcl/trace
vi alert_orcl.log
发现：
Errors in file u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_29396.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/orcl/onlinelog/group_1.261.853023319'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/onlinelog/group_1.261.853023319
ORA-15012: ASM file '+DATA/orcl/onlinelog/group_1.261.853023319' does not exist
Checker run found 1 new persistent data failures
有日志文件不可用，提示第1组的日志文件+DATA/orcl/onlinelog/group_1.261.853023319没有了
看日志组的状态
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 5 2 NO CURRENT #第一个组当前日志
2 2 2 YES INACTIVE
3 3 2 YES INACTIVE
4 4 2 YES INACTIVE
第一个组有2个日志文件，坏了一个，并且是当前日志组
删除坏的重建
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_1.261.853023319';
RROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
注意：不能对当前日志组删除的
先要切换日志组
SQL> alter system switch logfile ;
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 5 2 YES ACTIVE
2 6 2 NO CURRENT
3 3 2 YES INACTIVE
4 4 2 YES INACTIVE
SQL> alter system checkpoint ;
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 5 2 YES INACTIVE #非当期组，而且是归档了
2 6 2 NO CURRENT
3 3 2 YES INACTIVE
4 4 2 YES INACTIVE
再次删除，再添加
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_1.261.853023319';
SQL> alter database add logfile member '+DATA' to group 1 ;
SQL> alter system switch logfile ;
最后检查
SQL> select member ,status from v$logfile;
MEMBER STATUS
--------------------------------------------- -------
+DATA/orcl/onlinelog/group_3.263.853023339
+FRA/orcl/onlinelog/group_3.259.853023343
+DATA/orcl/onlinelog/group_2.262.853023327
+FRA/orcl/onlinelog/group_2.258.853023335
+DATA/orcl/onlinelog/group_3.261.853122229
+FRA/orcl/onlinelog/group_1.257.853023325
/u01/app/oracle/redo401.log
/u01/app/oracle/redo402.log
+DATA/orcl/onlinelog/group_1.273.853122243
适合场景：原来路径不可修复，直接删除，在新的地方再建立一个
2. 如果日志组是非当前状态，并且已经归档了，直接把整个日志原地重建
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 9 2 NO CURRENT
2 6 2 YES ACTIVE
3 7 3 YES ACTIVE
4 8 2 YES ACTIVE
把第四组变为current状态
SQL> alter system switch logfile ;
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 9 2 YES ACTIVE
2 10 2 YES ACTIVE
3 11 3 YES ACTIVE
4 12 2 NO CURRENT
SQL> select MEMBER from v$LOGFILE where GROUP# =4;
MEMBER
---------------------------------------------
/u01/app/oracle/redo401.log
/u01/app/oracle/redo402.log
模拟故障：删除一个日志成员
rm u01/app/oracle/redo401.log
alter system switch logfile ;
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log; 2
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 2 NO CURRENT
2 10 2 YES INACTIVE
3 11 3 YES ACTIVE
4 12 2 YES ACTIVE
alter system checkpoint ;
SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS
from v$log; 2
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 2 NO CURRENT
2 10 2 YES INACTIVE
3 11 3 YES INACTIVE
4 12 2 YES INACTIVE #非当前日志组且已归档
重建日志组：
SQL> alter database clear logfile group 4;
日志组损坏：一个组中的所有日志文件都损坏了
SQL> select member from v$logfile where group#=1;
MEMBER
---------------------------------------------
+FRA/orcl/onlinelog/group_1.257.853023325
+DATA/orcl/onlinelog/group_1.273.853122243
模拟故障：损坏日志组1 方法：删除该组的所有成员
关闭数据库 shutdown immediate;
删除所有日志组1的日志文件
su - grid
asmcmd
rm +FRA/orcl/onlinelog/group_1.257.853023325
rm +DATA/orcl/onlinelog/group_1.273.853122243
SQL> startup #启动数据库失败
startup mount ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2593
Session ID: 1 Serial number: 5
看一下警告日志
Errors in file u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_2503.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DATA/orcl/onlinelog/group_1.273.853122243'
ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/onlinelog/group_1.273.853122243
ORA-15012: ASM file '+DATA/orcl/onlinelog/group_1.273.853122243' does not exist
ORA-00312: online log 1 thread 1: '+FRA/orcl/onlinelog/group_1.257.853023325'
ORA-17503: ksfdopn:2 Failed to open file +FRA/orcl/onlinelog/group_1.257.853023325
ORA-15012: ASM file '+FRA/orcl/onlinelog/group_1.257.853023325' does not exist
发现提示日志文件不可用：
+DATA/orcl/onlinelog/group_1.273.853122243
+FRA/orcl/onlinelog/group_1.257.853023325
都属于第一日志组
startup mount;
查看日志组的状态
SQL> select GROUP# ,SEQUENCE# ,MEMBERS, ARCHIVED , STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 13 2 YES INACTIVE
4 14 2 NO CURRENT
3 11 3 YES INACTIVE
2 10 2 YES INACTIVE
看到第一个日志组 STATUS为inactive 并且归档为yes
修复方法：
SQL> alter database clear logfile group 1 ;
启动数据库：
SQL> alter database open ;
登录后检查日志组和日志文件的状态
SQL> select GROUP# ,SEQUENCE# ,MEMBERS, ARCHIVED , STATUS
from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 0 2 YES UNUSED #刚加入的
2 10 2 YES INACTIVE
3 11 3 YES INACTIVE
4 14 2 NO CURRENT
切换一次日志组，使新加入组生效
alter system switch logfile ;
SQL> select GROUP# ,SEQUENCE# ,MEMBERS, ARCHIVED , STATUS
from v$log; 2
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 15 2 NO CURRENT
2 10 2 YES INACTIVE
3 11 3 YES INACTIVE
4 14 2 YES ACTIVE
检查日志文件的状态
SQL> col member for a45
SQL> select member ,status from v$logfile ;
MEMBER STATUS
--------------------------------------------- -------
+DATA/orcl/onlinelog/group_3.263.853023339
+FRA/orcl/onlinelog/group_3.259.853023343
+DATA/orcl/onlinelog/group_2.262.853023327
+FRA/orcl/onlinelog/group_2.258.853023335
+DATA/orcl/onlinelog/group_3.261.853122229
+DATA/orcl/onlinelog/group_1.273.853124941
/u01/app/oracle/redo401.log
/u01/app/oracle/redo402.log
+FRA/orcl/onlinelog/group_1.257.853124945
TIPS:如果日志组是inactive 修复方法：整个日志组重建 alter database clear logfile group n ;
