oracle online redo log 日志组成员

为提升数据库安全性和性能，要求为数据库的每组在线重做日志（online redo log）至少包含两个日志组，且每个组中至少包含两个日志成员。

本文内容：增加一个日志成员，同时新增一个日志组，要求所有日志成员大小一致。

 

1、视图log和logfile

v$log详细说明了重做日志文件的结构、属性以及动态变化情况。
v$logfile说明了重做日志文件的物理地址和当前在线状态。

 

V$LOG

V$LOG displays log file information from the control file.

Column	Datatype	Description
GROUP#	NUMBER	Log group number
THREAD#	NUMBER	Log thread number
SEQUENCE#	NUMBER	Log sequence number
BYTES	NUMBER	Size of the log (in bytes)
MEMBERS	NUMBER	Number of members in the log group
ARCHIVED	VARCHAR2(3)	Archive status (YES or NO)
STATUS	VARCHAR2(16)	Log status: UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log. CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed. ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED. CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
FIRST_CHANGE#	NUMBER	Lowest system change number (SCN) in the log
FIRST_TIME	DATE	Time of the first SCN in the log

V$LOGFILE

This view contains information about redo log files.

Column	Datatype	Description
GROUP#	NUMBER	Redo log group identifier number
STATUS	VARCHAR2(7)	Status of the log member: INVALID - File is inaccessible STALE - File's contents are incomplete DELETED - File is no longer used null - File is in use
TYPE	VARCHAR2(7)	Type of the logfile: ONLINE STANDBY
MEMBER	VARCHAR2(513)	Redo log member name
IS_RECOVERY_DEST_FILE	VARCHAR2(3)	Indicates whether the file was created in the flash recovery area (YES) or not (NO)

 

 

2、本文实验过程：

1）查询日志组成员

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME

------ ------- --------- ---------- ---------- --- ---------- ------------- ------------

     1       1        46   52428800          1 NO  CURRENT           748944 31-MAR-16

     2       1        44   52428800          1 NO  INACTIVE          712175 30-MAR-16

     3       1        45   52428800          1 NO  INACTIVE          744791 30-MAR-16

 

2）查询日志文件：

SQL>select * from v$logfile;

GROUP# STATUS     TYPE    MEMBER                          IS_RECOVERY_DEST_FILE

------ ---------- ------- ------------------------------ ---------------------------------------

     1            ONLINE  /oradata/oradb/redo01a.log     NO

     2            ONLINE  /oradata/oradb/redo02a.log     NO

     3            ONLINE  /oradata/oradb/redo03a.log     NO

3）分别添加1个日志文件成员到3个日志组中：

SQL> alter database add logfile member '/oradata/oradb/redo01b.log' to group 1;

Database altered.

SQL> alter database add logfile member '/oradata/oradb/redo02b.log' to group 2;

Database altered.

SQL> alter database add logfile member '/oradata/oradb/redo03b.log' to group 3;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS     TYPE        MEMBER                                  IS_RECOVERY_DEST_FILE

------      ----------  ----------  ------------------------------     -----------------------------------

     1                         ONLINE     /oradata/oradb/redo01a.log     NO

     2                         ONLINE     /oradata/oradb/redo02a.log     NO

     3                         ONLINE     /oradata/oradb/redo03a.log     NO

     1      INVALID     ONLINE     /oradata/oradb/redo01b.log     NO

     2      INVALID     ONLINE     /oradata/oradb/redo02b.log     NO

     3     INVALID      ONLINE     /oradata/oradb/redo03b.log     NO

SQL> alter database add logfile group 4 ('/oradata/oradb/redo04a.log','/oradata/oradb/redo04b.log') size 50M;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS     TYPE        MEMBER                                  IS_RECOVERY_DEST_FILE

------      ----------  ----------  ------------------------------     -----------------------------------

     1                         ONLINE     /oradata/oradb/redo01a.log     NO

     2                         ONLINE     /oradata/oradb/redo02a.log     NO

     3                         ONLINE     /oradata/oradb/redo03a.log     NO

     1       INVALID    ONLINE     /oradata/oradb/redo01b.log     NO

     2       INVALID    ONLINE     /oradata/oradb/redo02b.log     NO

     3       INVALID    ONLINE     /oradata/oradb/redo03b.log     NO

     4                         ONLINE     /oradata/oradb/redo04a.log     NO

     4                         ONLINE     /oradata/oradb/redo04b.log     NO

8 rows selected.

 

4）查询日志信息：

SQL> set lines 168 pages 9999

SQL> col member format a30

SQL> col instance_name for a15

SQL> col status for a8

SQL> select i.instance_name,i.thread#,f.group#,f.member,f.type,l.status,l.bytes/1048576 bytes_mb,l.archived

  2  from gv$logfile f,gv$log l,gv$instance i

  3  where f.group# = l.group#

  4  and l.thread#=i.thread#

  5  and i.ins_id=f.inst_id

  6  and f.inst_id=l.inst_id

  7  order by i.instance_name,f.group#,f.member;

SQL> /

INSTANCE_NAME   THREAD# GROUP#    MEMBER                                                TYPE           STATUS        BYTES_MB     ARC

--------------- -------       ------      --------------------                    ---------- --------     --------        ---------- ---

oradb                 1           1            /oradata/oradb/redo01a.log    ONLINE   CURRENT       50       NO

oradb                 1           1            /oradata/oradb/redo01b.log    ONLINE   CURRENT       50       NO

oradb                 1           2            /oradata/oradb/redo02a.log    ONLINE   INACTIVE        50       NO

oradb                 1           2            /oradata/oradb/redo02b.log    ONLINE   INACTIVE        50       NO

oradb                 1           3            /oradata/oradb/redo03a.log    ONLINE   INACTIVE        50       NO

oradb                 1           3            /oradata/oradb/redo03b.log    ONLINE   INACTIVE        50       NO

oradb                 1           4            /oradata/oradb/redo04a.log    ONLINE   UNUSED          50      YES

oradb                 1           4            /oradata/oradb/redo04b.log    ONLINE   UNUSED          50      YES

8 rows selected.