保证可恢复性
日志文件多个组，组里多个成员
日志文件非常重要，所以通过日志组管理 ，一个组有多个日志文件，只要一个日志文件是好的，整个日志组就可以使用。
查看日志组的个数和成员数 v$log 看日志组的信息 v$logfile 看日志文件信息 SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 || 'M' "size" from v$log; GROUP# MEMBERS STATUS BYTES/1024/1024||'M'
---------- ---------- ---------------- ---------------------------------------
1 2 CURRENT 50M
2 2 INACTIVE 50M3 2 INACTIVE 50M 日志组状态说明： UNUSED 从来没有用过 CURRENT 当前日志组，表示日志缓冲的日志写入该组 ACTIVE 活动，不再向里面写日志信息，但是该组的日志信息要用于实例恢复 INACTIVE 该日志组记录的日志文件的事务完成，脏数据写回硬盘，不需要做实例恢复，则就不再需要该组的日志信息 CLEARING 日志组在重建 ---重建完毕后就为unused
例子：
要求我们数据库有4个日志组，每个组3个成员，一个存在+DATA，一个存在+FRA，一个存在/u01/app/oracle
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 || 'M' "size" from v$log;
GROUP# MEMBERS STATUS BYTES/1024/1024||'M'
---------- ---------- ---------------- -----------------------------------------
1 2 CURRENT 50M
2 2 INACTIVE 50M
3 2 INACTIVE 50M
按要求需要再添加一个组，每个组3个日志文件，文件保存在+DATA、+FRA、 u01/app/oracle
alter database add logfile member '/u01/app/oracle/redo103.log' to group 1 ;
alter database add logfile member '/u01/app/oracle/redo203.log' to group 2 ;
alter database add logfile member '/u01/app/oracle/redo303.log' to group 3 ;
SQL> select GROUP#,MEMBERS, STATUS,BYTES/1024/1024 || 'M' "size" from v$log;
GROUP# MEMBERS STATUS size
---------- ---------- ---------------- -----------------------------------------
1 3 CURRENT 50M
2 3 INACTIVE 50M
3 3 INACTIVE 50M
每个组就有3个日志文件，检查日志文件状态，刚加入的是不可用的。
SQL> select GROUP# ,MEMBER,STATUS from v$logfile ;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
3 +DATA/orcl/onlinelog/group_3.263.853023339
3 +FRA/orcl/onlinelog/group_3.259.853023343
2 +DATA/orcl/onlinelog/group_2.262.853023327
2 +FRA/orcl/onlinelog/group_2.258.853023335
1 +DATA/orcl/onlinelog/group_1.261.853023319
1 +FRA/orcl/onlinelog/group_1.257.853023325
1 u01/app/oracle/redo103.log INVALID
2 u01/app/oracle/redo203.log INVALID
3 u01/app/oracle/redo303.log INVALID
增加完日志文件后，必须保证你加入的日志文件的文件组是当前日志。
切换日志组，变为有效：
alter system switch logfile ;
alter system switch logfile ;
alter system switch logfile ;
SQL> select GROUP# ,MEMBER,STATUS from v$logfile ;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
3 +DATA/orcl/onlinelog/group_3.263.853023339
3 +FRA/orcl/onlinelog/group_3.259.853023343
2 +DATA/orcl/onlinelog/group_2.262.853023327
2 +FRA/orcl/onlinelog/group_2.258.853023335
1 +DATA/orcl/onlinelog/group_1.261.853023319
1 +FRA/orcl/onlinelog/group_1.257.853023325
1 u01/app/oracle/redo103.log
2 u01/app/oracle/redo203.log
3 u01/app/oracle/redo303.log
日志文件的状态为空就是同步的。
SQL> alter database add logfile group 4 ('+data','+fra','/u01/app/oracle/redo403.log') size 50M;
检查：
SQL> select GROUP#,SEQUENCE#,MEMBERS, STATUS,BYTES/1024/1024 || 'M' "size" from v$log;
GROUP# SEQUENCE# MEMBERS STATUS size
---------- ---------- ------- ---------------- ---------------
1 43 3 ACTIVE 50M
2 44 3 ACTIVE 50M
3 45 3 CURRENT 50M
4 0 3 UNUSED 50M
序列最大的是当前日志组,序列最小的是下一个当前日志组.
只需要切换一次
SQL> alter system switch logfile;
SQL> select GROUP#,SEQUENCE#,MEMBERS, STATUS,BYTES/1024/1024 || 'M' "size" from v$log;
GROUP# SEQUENCE# MEMBERS STATUS size
---------- ---------- ------- ---------------- ---------------
1 43 3 INACTIVE 50M
2 44 3 ACTIVE 50M
3 45 3 ACTIVE 50M
4 46 3 CURRENT 50M
检查日志文件状态：
SQL> select GROUP# ,MEMBER,STATUS from v$logfile;
GROUP# MEMBER STATUS
---------- --------------------------------------------- -------
3 +DATA/orcl/onlinelog/group_3.263.853023339
3 +FRA/orcl/onlinelog/group_3.259.853023343
2 +DATA/orcl/onlinelog/group_2.262.853023327
2 +FRA/orcl/onlinelog/group_2.258.853023335
1 +DATA/orcl/onlinelog/group_1.261.853023319
1 +FRA/orcl/onlinelog/group_1.257.853023325
1 u01/app/oracle/redo103.log
2 u01/app/oracle/redo203.log
3 u01/app/oracle/redo303.log
4 +DATA/orcl/onlinelog/group_4.270.855762611
4 +FRA/orcl/onlinelog/group_4.268.855762617
4 u01/app/oracle/redo403.log
增加完一个日志组，每组3个文件，都是可以的。
多路复用的目的
增加可恢复性
任意一个组中的任何成员丢失，删除，数据库正常使用
例：
SQL> select GROUP# ,SEQUENCE# , STATUS , ARCHIVED , MEMBERS from v$log;
GROUP# SEQUENCE# STATUS ARC MEMBERS
---------- ---------- ---------------- --- -------
1 43 INACTIVE YES 3
2 44 INACTIVE YES 3
3 45 ACTIVE YES 3
4 46 CURRENT NO 3
删除当前日志组4中的成员：
SQL> select MEMBER from v$logfile where GROUP#=4;
MEMBER
---------------------------------------------
+DATA/orcl/onlinelog/group_4.270.855762611
+FRA/orcl/onlinelog/group_4.268.855762617
/u01/app/oracle/redo403.log
rm -rf u01/app/oracle/redo403.log
重启数据库，可以启动，只有访问到第四组，会在警告日志文件给你提示：
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17900.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/u01/app/oracle/redo403.log'
ORA-27037: unable to obtain file status
如何修复：（删除 --> 再重新添加）
SQL> alter database drop logfile member '/u01/app/oracle/redo403.log';
SQL> alter database add logfile member '/u01/app/oracle/redo403.log' to group 4 ;
select MEMBER,status from v$logfile where GROUP#=4;
MEMBER STATUS
--------------------------------------------- -------
+DATA/orcl/onlinelog/group_4.270.855762611
+FRA/orcl/onlinelog/group_4.268.855762617
/u01/app/oracle/redo403.log INVALID
SQL> alter system switch logfile ;
SQL> select MEMBER,status from v$logfile where GROUP#=4;
MEMBER STATUS
--------------------------------------------- -------
+DATA/orcl/onlinelog/group_4.270.855762611
+FRA/orcl/onlinelog/group_4.268.855762617
/u01/app/oracle/redo403.log
恢复可用。
