在线重做日志及状态说明

1、查询在线重做日志
SQL> select group#,sequence#,status,archived from v$log;

GROUP# SEQUENCE# STATUS ARC
---------- ---------- ---------------- ---
1 169 INACTIVE YES
2 170 INACTIVE YES
3 171 CURRENT NO

SQL> select group#,member from v$logfile;

GROUP# MEMBER
---------- ------------------------------------------------
2 D:ORACLEORADATADB1REDO02.LOG
1 D:ORACLEORADATADB1REDO01.LOG
3 D:ORACLEORADATADB1REDO03.LOG

2、日志状态说明
unused: 在线重做日志从来没有被写过
current:当前重做日志而且是active，可以是open或者colse
active:活跃的，但不是当前的，就是说在例程恢复中需要用到，可以被用在块级恢复，可能归档或者未归档
clearing:这个日志正在被重建，重建后为空的日志，当日志被cleared后，状态变为unused，使用alter data clear logfile 可以进行重建操作
clearing_current:对于一个关闭的thread，重建当前日志，当日志撤换过程中在写新的重做日志时发生i/o错误出现错误的会出现这种状态的重做日志
inactive:指该在线重做日志在例程恢复过程中已经不再需要了，但有可能在媒体恢复中用到，可能归档或者未归档

3、状态过程变化及应用
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