scn是当oracle数据库更新后，由dbms自动维护去累积递增的一个数字。当一笔交易commit 时，lgwr会将log buffer写入redo log file，
上次做了下基于scn恢复delete掉的数据后，觉得应该好好理解下scn的知识，今天在网上找了下相关的介绍，参考了某些文章，，在此我通过实验总结一下。
scn是当oracle数据库更新后，由dbms自动维护去累积递增的一个数字。当一笔交易commit 时，lgwr会将log buffer写入redo log file，同时也会将该笔交易的scn同步写入到redo log file内（wait-until-completed）。因此当你commit transaction时，在交易成功的讯息返回之前，lgwr必须先完整的完成上述行为之后，否则你是看不到提交成功的回应讯息。
那系统是如何产生一个最新的
select dbms_flashback.get_system_change_number, scn_to_timestamp(dbms_flashback
2 .get_system_change_number) from dual;
get_system_change_number
------------------------
scn_to_timestamp(dbms_flashback.get_system_change_number)
---------------------------------------------------------------------------
117947354
24-mar-11 11.32.22.000000000 am
也可以用函数
sql> select timestamp_to_scn(systimestamp) as scn from dual;
control中有三种scn分别为，system scn、datafile scn、last scn，数据文件头中有一种scn start scn
system scn从视图v$database中获得，对应checkpoint_change#字段，datafile scn、last scn分别对应视图v$datafile中的checkpoint_change#，last_change#，而 start scn则从v$datafile_header中checkpoint_change#得到。
数据库在正常启动后下，system scn,datafile scn,start scn会相等，而last scn会被置于无穷大，这里为null。
正常关闭后（immediate,noraml,translate），上面四个scn会应执行full checkpoint 而相等。
当系统在非正常关闭后，如shutdown abort，这个时候last scn依然为无穷大，那么当重新启动实例时，系统首先会比较start scn与system scn，如果一致，那么再比较start scn 与last scan是否一样大，因为是非正常关闭，这里会不一样大，那么就需要例程恢复。
如果打开数据库时发现system scn>datafile scn，那么以为着使用旧的备份数据文件，也就是需要介质恢复
如果是system scn1、正常启动时
sql> select checkpoint_change# from v$database;
checkpoint_change#
------------------
117866282
sql> select file#, checkpoint_change# from v$datafile_header;
file# checkpoint_change#
---------- ------------------
1 117866282
2 117866282
3 117866282
4 117866282
5 117866282
6 117866282
7 117866282
8 117866282
9 117866282
10 117866282
11 117866282
file# checkpoint_change#
---------- ------------------
12 117866282
13 117866282
14 117866282
14 rows selected.
sql> select file#, checkpoint_change#, last_change# from v$datafile;
file# checkpoint_change# last_change#
---------- ------------------ ------------
1 117866282
2 117866282
3 117866282
4 117866282
5 117866282
6 117866282
7 117866282
8 117866282
9 117866282
10 117866282
11 117866282
file# checkpoint_change# last_change#
---------- ------------------ ------------
12 117866282
13 117866282
14 117866282
14 rows selected.
2、正常关闭后，然后在startup mount;
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 835104768 bytes
fixed size 2217952 bytes
variable size 624953376 bytes
database buffers 201326592 bytes
redo buffers 6606848 bytes
database mounted.
sql> select file#, checkpoint_change# from v$datafile_header;
file# checkpoint_change#
---------- ------------------
1 117925094
2 117925094
3 117925094
4 117925094
5 117925094
6 117925094
7 117925094
8 117925094
9 117925094
10 117925094
11 117925094
file# checkpoint_change#
---------- ------------------
12 117925094
13 117925094
14 117925094
14 rows selected.
sql> select checkpoint_change# from v$database;
checkpoint_change#
------------------
117925094
sql> select file#, checkpoint_change#, last_change# from v$datafile;
file# checkpoint_change# last_change#
---------- ------------------ ------------
1 117925094 117925094
2 117925094 117925094
3 117925094 117925094
4 117925094 117925094
5 117925094 117925094
6 117925094 117925094
7 117925094 117925094
8 117925094 117925094
9 117925094 117925094
10 117925094 117925094
11 117925094 117925094
file# checkpoint_change# last_change#
---------- ------------------ ------------
12 117925094 117925094
13 117925094 117925094
14 117925094 117925094
14 rows selected.
--发现start scn=last scn,证明系统是正常关闭
sql> alter database open;
database altered.
3、在正常打开状态下进行事务操作
sql> create table w(a number);
table created.
sql> insert into w values(1);
1 row created.
sql> commit;
commit complete.
sql> insert into w values(2);
1 row created.
4、非正常关闭
sql> shutdown abort;
oracle instance shut down.
5、打开到mount状态下，观看scn
sql> startup mount;
oracle instance started.
total system global area 835104768 bytes
fixed size 2217952 bytes
variable size 624953376 bytes
database buffers 201326592 bytes
redo buffers 6606848 bytes
database mounted.
sql> select file#,checkpoint_change#, last_change# from v$datafile;
file# checkpoint_change# last_change#
---------- ------------------ ------------
1 117925097
2 117925097
3 117925097
4 117925097
5 117925097
6 117925097
7 117925097
8 117925097
9 117925097
10 117925097
11 117925097
file# checkpoint_change# last_change#
---------- ------------------ ------------
12 117925097
13 117925097
14 117925097
14 rows selected.
sql> select checkpoint_change# from v$database;
checkpoint_change#
------------------
117925097
sql> select file#,checkpoint_chnge# from v$datafile_header;
select file#,checkpoint_chnge# from v$datafile_header
*
error at line 1:
ora-00904: checkpoint_chnge#: invalid identifier
sql> select file#, checkpoint_change# from v$datafile_header;
file# checkpoint_change#
---------- ------------------
1 117925097
2 117925097
3 117925097
4 117925097
5 117925097
6 117925097
7 117925097
8 117925097
9 117925097
10 117925097
11 117925097
file# checkpoint_change#
---------- ------------------
12 117925097
13 117925097
14 117925097
14 rows selected.
--这时发现start scn 与last scn不等，last scn为无穷大，需要例程恢复
6、改变数据库状态为open，并查看该阶段运行日志
sql> alter database open;
database altered.
sql> select * from w;
a
----------
1
--发现没有提交的事务丢失。
查看日志如下：
thu mar 24 10:47:24 2011
alter database mount
successful mount of redo thread 1, with mount id 1274403260
database mounted in exclusive mode
lost write protection disabled
completed: alter database mount
thu mar 24 10:50:12 2011
alter database open
beginning crash recovery of 1 threads --会自动判断是否需要恢复，这里开始例程恢复
parallel recovery started with 2 processes
started redo scan
completed redo scan
read 162 kb redo, 112 data blocks need recovery
started redo application at
thread 1: logseq 2635, block 491121
recovery of online redo log: thread 1 group 1 seq 2635 reading mem 0 --恢复用的在线重做日志
mem# 0: /u01/oradata/orcl/redo01a.log
mem# 1: /u01/oradata/orcl/redo01b.log
completed redo application of 0.13mb
completed crash recovery at --恢复完成
thread 1: logseq 2635, block 491446, scn 117945330
112 data blocks read, 112 data blocks written, 162 redo k-bytes read
thu mar 24 10:50:13 2011
lgwr: starting arch processes
thu mar 24 10:50:13 2011
arc0 started with pid=22, os id=31059
arc0: archival started
lgwr: starting arch processes complete
arc0: starting arch processes
thu mar 24 10:50:14 2011
arc1 started with pid=23, os id=31061
thread 1 advanced to log sequence 2636 (thread open)
thu mar 24 10:50:15 2011
arc2 started with pid=24, os id=31063
thread 1 opened at log sequence 2636
current log# 2 seq# 2636 mem# 0: /u01/oradata/orcl/redo02a.log