scn是資料庫對自身變化的一個標記。透過一個序列號能夠反映出資料庫在那個時間點正在進行的操作，scn沒有選用時間來作為基準單位，可能也是因為時間的不確定性，比如當前時間為2014年3月14號晚上八點整，如果修改了系統時間，改為晚上七點鐘，name重啟資料庫以後，那個時間段的操作就都亂套了。這個scn在一般的庫上都看似比較大，根據資料庫的設計角度來說，這個scn能夠使用很長很長的時間。
scn的變化也基本分為四類，例項級別scn，資料檔案scn，資料檔案頭scn,結束scn，為了測試scn的變化情況，準備了下面的場景
1)全域性檢查點更新如alter system checkpoint，資料庫級scn應該會發生變化。
2)resize 資料檔案把資料檔案的大小進行改變
3)切換redo日誌檔案
4)歸檔當前日誌檔案
5)建立表
6)插入資料
7)插入一些資料，頻繁的commit
8)插入一些資料，頻繁的rollback

測試的結果如下,對於發生變化的部分都用黃色進行了標註。可以比對測試場景對比學習一下。有些場景沒有涉及到，目的只是向大家分享一下日常的操作中scn的變化。

場景	database level					datafile&file header (header不包括last_change#列)		redo
	Chk_change#	Ctl_change#	Ctl_time	Curr_scn	Arch_change#	Chk_change#	Last_change#	First_change#	Nxt_change#
開始	3626097	3626320	2014-05-09 02:56:11	3626335	3626097	pool_data 3626318 others 3626097		504377	514666
1	3626336	3626336	2014-05-09 02:56:36	3626337	3626097	3626336		504377	514666
2	3626336	3626338	2014-05-09 02:56:38	3626346	3626097	3626336		504377	514666
3	3626336	3626349	2014-05-09 02:56:38	3626350	3626097	3626336		504377	514666
4	3626336	3626354	2014-05-09 02:56:39	3626355	3626354	3626336		504377	514666
5	3626336	3626354	2014-05-09 02:56:39	3626417	3626354	3626336		504377	514666
6	3626336	3626354	2014-05-09 02:56:39	3626442	3626354	3626336		504377	514666
7	3626336	3626354	2014-05-09 02:56:39	3626459	3626354	3626336		504377	514666
8	3626336	3626354	2014-05-09 02:56:39	3626462	3626354	3626336		504377	514666

生成scn快照的指令碼如下,對於每一個操作，都可以使用下面的指令碼從資料庫級，資料檔案，資料檔案頭，線上日誌等維度進行scn的查驗。
sqlplus -s n1/n1 < set pages 20
prompt ######scn from database level
col checkpoint_change# format 99999999999999999
col RESETLOGS_CHANGE# format 99999999999999999
col PRIOR_RESETLOGS_CHANGE# format 99999999999999999
col CONTROLFILE_CHANGE# format 99999999999999999
col ARCHIVELOG_CHANGE# format 99999999999999999
col CURRENT_SCN format 99999999999999999
col control_time format a20
set linesize 200
select RESETLOGS_CHANGE# ,RESETLOGS_TIME,PRIOR_RESETLOGS_CHANGE#,CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# ,to_char(CONTROLFILE_TIME,'yyyy-mm-dd hh24:mi:ss') control_time,ARCHIVELOG_CHANGE#,CURRENT_SCN from v\$database;

prompt #####scn from datafile

col checkpoint_change# format 99999999999999999
col creation_change# format 99999999999999999
col checkpoint_change# format 99999999999999999
col last_change# format 99999999999999999
col online_change# format 99999999999999999
col online_time format a20
col last_time format a20
col checkpoint_time format a20
col creation_time format a9
col file# format 999
set linesize 200
select file#,creation_change#,creation_time,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,last_change#,to_char(last_time,'yyyy-mm-dd hh24:mi:ss')last_time,offline_change#,online_change#,to_char(online_time,'yyyy-mm-dd hh24:mi:ss')online_time from v\$datafile;

prompt #####scn from datafile header
col tablespace_name format a10
col resetlogs_change# format 99999999999999999
col creation_time format a9
--col undo_opt_current_change# format 99999999999999999
col checkpoint_time format a20
set linesize 200
select file#,creation_change#,creation_time,tablespace_name,resetlogs_change#,resetlogs_time,checkpoint_change#,checkpoint_time,checkpoint_count from v\$datafile_header;

prompt #####scn from redo
col first_change# format 99999999999999999
col next_change# format 99999999999999999
col sequence# format 99999999999999999
select *from (select recid,sequence#,first_change#,next_change# from v\$log_history )
where rownum<20;

EOF
exit

對如上的測試場景中scn的變化進行總結，一共會生成9個快照。
ksh showscn.sh >beginning_snshowscn.shot.lst0
sqlplus -s n1/n1 <

alter system checkpoint; --全域性，資料檔案，資料檔案頭部scn都遞增，保持一致
EOF
ksh showscn.sh > checkpoint_snshowscn.shot.lst1
sqlplus -s n1/n1 < alter database datafile '/u03/ora11g/oradata/TEST01/pool_data02.dbf' resize 160M; --&gt只有資料庫級scn遞增
EOF
ksh showscn.sh > resize_datafile_snshowscn.shot.lst2
sqlplus -s n1/n1 < alter system switch logfile; --&gt日誌檔案scn遞增
EOF
ksh showscn.sh > redo_switch_snshowscn.shot.lst3
sqlplus -s n1/n1 < alter system archive log current;
EOF
ksh showscn.sh >archive_current_snshowscn.shot.lst4

sqlplus -s n1/n1 < create table aaaa as select * from dba_objects;
EOF
ksh showscn.sh >create_tab_snshowscn.shot.lst5

sqlplus -s n1/n1 < insert into aaaa select *from aaaa;
commit;
EOF
ksh showscn.sh >insert_tab_snshowscn.shot.lst6

sqlplus -s n1/n1 < insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
insert into aaaa select *from aaaa where rownum<10;
commit;
EOF
ksh showscn.sh >over_commit_snshowscn.shot.lst7

sqlplus -s n1/n1 < insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
insert into aaaa select *from aaaa where rownum<10;
rollback;
EOF
ksh showscn.sh >rollback_snshowscn.shot.lst8

執行指令碼的日誌如下：
System altered.
Database altered.
System altered.
System altered.
Table created.
13470 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete.
9 rows created.
Commit complete
9 rows created.
9 rows created.
9 rows created.
9 rows created.
9 rows created.
9 rows created.
9 rows created.
9 rows created.
Rollback complete.

生成的快照如下：
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 beginning_snshowscn.shot.lst0
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 checkpoint_snshowscn.shot.lst1
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 resize_datafile_snshowscn.shot.lst2
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 redo_switch_snshowscn.shot.lst3
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 archive_current_snshowscn.shot.lst4
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 create_tab_snshowscn.shot.lst5
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 insert_tab_snshowscn.shot.lst6
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 over_commit_snshowscn.shot.lst7
-rw-r--r-- 1 ora11g dba 3144 May 9 02:56 rollback_snshowscn.shot.lst8

關於SCN的總結測試

相關文章