16、重做日志文件的状态及重做日志组的状态说明

重做日志文件和重做日志组的状态[@more@]

一、日志文件的状态包括：

1、NULL -File is in use

2、STALE-contents of the file are incomplete

3、INVALID-File is inaccessible

4、DELETED-File is no longer used

二、重做日志组的状态包括：

1、CURRENT-Current redo log.This implies that the redo log is active.The redo log could be open or closed.

2、UNUSED-Online redo log has never been written to.This is the status of a redo log that was just added.or just after a RESETLOGS.when it is not the current redo log

3、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

4、INACTIVE-Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

5、CLEARING-这个日志正在被重建，重建后为空的日志，当日志被cleared后，状态变为unused，使用alter data clear logfile 可以进行重建操作

6、CALERING_CURRENT-对于一个关闭的thread，重建当前日志，当日志撤换过程中在写新的重做日志时发生i/o错误出现错误的会出现这种状态的重做日志

三、状态变化过程及应用

SQL> alter database clear logfile group 1;

数据库已更改。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 0 UNUSED YES --运行clear命令后，状态为unused
2 173 CURRENT NO
3 171 INACTIVE YES

SQL> drop table t;

表已丢弃。

SQL> create table t as select * from dba_objects;

表已创建。

SQL> insert into t select * from t;

已创建25745行。

SQL> insert into t select * from t;

已创建51490行。

SQL> insert into t select * from t;

已创建102980行。

SQL> insert into t select * from t;

已创建205960行。

SQL> insert into t select * from t;

已创建411920行。

SQL> insert into t select * from t;

已创建823840行。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 174 INACTIVE YES
2 176 CURRENT NO --活动且当前
3 175 ACTIVE NO --活动

SQL> commit;

提交完成。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 174 INACTIVE YES
2 176 CURRENT NO
3 175 INACTIVE YES --提交后发现已经归档

SQL> insert into t select * from t;

已创建1647680行。

SQL> alter system switch logfile;

系统已更改。

SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 177 ACTIVE NO
2 176 ACTIVE YES
3 178 CURRENT NO

--几秒之后
SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 177 INACTIVE YES --发现归档了，并且状态变为inactive
2 179 CURRENT NO
3 178 ACTIVE YES

--此时非正常关闭
--那么需要例程恢复，看看应用到那些在线重做日志
SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 319888364 bytes
Fixed Size 453612 bytes
Variable Size 192937984 bytes
Database Buffers 125829120 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 177 INACTIVE YES
2 179 CURRENT NO
3 178 ACTIVE YES

--打开数据库
SQL> alter database open;

数据库已更改。

--查看alert日志
ALTER DATABASE MOUNT
Sun May 21 09:33:29 2006
Successful mount of redo thread 1, with mount id 1184869413.
Sun May 21 09:33:29 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Sun May 21 09:35:47 2006
alter database open
Sun May 21 09:35:47 2006
Beginning crash recovery of 1 threads
Sun May 21 09:35:47 2006
Started first pass scan
Sun May 21 09:35:49 2006
Completed first pass scan
112893 redo blocks read, 7011 data blocks need recovery
Sun May 21 09:35:50 2006
Started recovery at
Thread 1: logseq 178, block 159334, scn 0.0 --发现会用到这个在序号为178，因为是active
Recovery of Online Redo Log: Thread 1 Group 3 Seq 178 Reading mem 0
Mem# 0 errs 0: D:ORACLEORADATADB1REDO03.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 179 Reading mem 0
Mem# 0 errs 0: D:ORACLEORADATADB1REDO02.LOG
Sun May 21 09:35:53 2006
Ended recovery at
Thread 1: logseq 179, block 67445, scn 0.5728855 --179也会用到，因为当前肯定是active的
7011 data blocks read, 7011 data blocks written, 112893 redo blocks read
Crash recovery completed successfully
Sun May 21 09:35:53 2006
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 180
Thread 1 opened at log sequence 180
Current log# 1 seq# 180 mem# 0: D:ORACLEORADATADB1REDO01.LOG
Successful open of redo thread 1.
Sun May 21 09:35:53 2006
SMON: enabling cache recovery
Sun May 21 09:35:53 2006
ARC0: Evaluating archive log 2 thread 1 sequence 179
ARC0: Beginning to archive log 2 thread 1 sequence 179
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:DBBKARC00179.001'
Sun May 21 09:35:55 2006
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Sun May 21 09:35:55 2006
SMON: enabling tx recovery
Sun May 21 09:35:56 2006
Database Characterset is ZHS16GBK
Sun May 21 09:35:58 2006
ARC0: Completed archiving log 2 thread 1 sequence 179
Sun May 21 09:36:01 2006
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

--此过程会应用到日志序号为178，179的两个重做日志序号，并且把日志序号加1置为新的current状态，把

--之前current状态的序号进行归档，然后才打开数据库。
--查询后看到序号最新的已经是180了，并且之前的日志都已经归档。
SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 180 CURRENT NO
2 179 INACTIVE YES
3 178 INACTIVE YES

--查看未提交的记录，发现已经回滚
SQL> select count(*) from t;

COUNT(*)
----------
1647680

四、关于stale状态的说明

A stale redo log file is one that Oracle believes might be incomplete for some reason. This typically happens when a temporary error prevents the LGWR background process from writing to a redo log group member.

If Oracle cannot write to a redo log file at any one time, that log file can no longer be trusted, and Oracle marks it as "STALE". This indicates that the log file cannot be relied upon to provide all the data written to the log.

Here is the recommended procedure to deal with a stale redo log:

1. Issue the following query:

SELECT V2.GROUP#, MEMBER, V2.STATUS MEMBER_STATUS,
V1.STATUS GROUP_STATUS
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# AND V2.STATUS = 'STALE';

This will show you the group status for all stale log files.

2. For each stale log file,

2.a) If the GROUP_STATUS is 'INACTIVE', do nothing.

That implies Oracle has already checkpointed past that redo group, and thus its contents are no longer needed for instance recovery.
Once the redo group becomes current again, the stale status of
the log file will go away by itself.

2.b) If the GROUP_STATUS is 'ACTIVE', go back to Step 1 and repeat
the query a few more times.

This status usually means that the log group is no longer the current one, but the corresponding checkpoint has not completed yet. Unless there is a problem, the log group should become inactive shortly.

2.c) If the GROUP_STATUS is 'CURRENT', force a log switch now.

ALTER SYSTEM SWITCH LOGFILE;

This will also force a checkpoint. If the checkpoint completes successfully, the contents of the redo group are no longer needed for instance recovery. Go back to Step 1 and repeat the query a few more times until the log group becomes inactive.

IMPORTANT: If the stale logfile belongs to an active group or the current group (cases 2.b and 2.c above), DO NOT ISSUE A SHUTDOWN ABORT UNTIL THE GROUP BECOMES INACTIVE.

3. Investigate the extent of the problem.

Examine the alert.log file for this instance and the LGWR
trace file, if one can be found in your background_dump_dest.
See if there is any pattern to the problem. Do you see any
recent errors, such as ORA-312, referencing that particular log
file? If so, there may be some corruption problem with the file
or a problem with the I/O subsystem (disk, controllers, etc.) .
If you are running any other Oracle version on any other platform

If there is no pattern to the problem, it is more likely an
isolated incident.

4. If you are archiving, make sure the log has been correctly archived.

Before archiving a redo log group, the ARCH process actually
verifies that its contents are valid. If that is not the case,
it issues an error such as ORA-255 ("error archiving log %s
of thread %s, sequence # %s"). Therefore, if the log group to
which the stale member belongs has been successfully archived,
it means the redo contents of the group are good, and that
archived log can be safely used for recovery. ARCH errors, if
any, will be reported in your alert.log file and in an ARCH
trace file.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/248644/viewspace-931077/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/248644/viewspace-931077/