首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >关于SCN的总结测试 (68天)

关于SCN的总结测试 (68天)

作者头像
jeanron100
发布2018-03-14 10:50:43
5470
发布2018-03-14 10:50:43
举报

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&header

redo log

chk_change#

ctl_change#

ctl_time

curr_scn

archive_change#

chk_change#

last_change#

first_change#

nxt_change#

beginning

3626097

3626320

2014-05-09 02:56:11

3626335

3626097

pool_data 3626318others 3626097

504377

514666

alter system checkpoint;

3626336

3626336

2014-05-09 02:56:36

3626337

3626097

3626336

504377

514666

resize datafile

3626336

3626338

2014-05-09 02:56:38

3626346

3626097

3626336

504377

514666

alter system switch logfile;

3626336

3626349

2014-05-09 02:56:38

3626350

3626097

3626336

504377

514666

alter system archive log current;

3626336

3626354

2014-05-09 02:56:39

3626355

3626354

3626336

504377

514666

create table aaaa as select * from dba_objects;

3626336

3626354

2014-05-09 02:56:39

3626417

3626354

3626336

504377

514666

insert into aaaa select *from aaaa;commit;

3626336

3626354

2014-05-09 02:56:39

3626442

3626354

3626336

504377

514666

insert,commit;insert,commit;insert,commit;…

3626336

3626354

2014-05-09 02:56:39

3626459

3626354

3626336

504377

514666

insert,insert,insert...rollback

3626336

3626354

2014-05-09 02:56:39

3626462

3626354

3626336

504377

514666

生成scn快照的脚本如下,对于每一个操作,都可以使用下面的脚本从数据库级,数据文件,数据文件头,在线日志等维度进行scn的查验。


sqlplus -s n1/n1 <<EOF
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 <<EOF


alter system checkpoint;  --全局,数据文件,数据文件头部scn都递增,保持一致
EOF
ksh showscn.sh > checkpoint_snshowscn.shot.lst1
sqlplus  -s n1/n1 <<EOF
alter database datafile '/u03/ora11g/oradata/TEST01/pool_data02.dbf' resize 160M;   -->只有数据库级scn递增
EOF
ksh showscn.sh > resize_datafile_snshowscn.shot.lst2
sqlplus  -s n1/n1 <<EOF
alter system switch logfile;  -->日志文件scn递增
EOF
ksh showscn.sh > redo_switch_snshowscn.shot.lst3
sqlplus  -s n1/n1 <<EOF
alter system archive log current;
EOF
ksh showscn.sh >archive_current_snshowscn.shot.lst4


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


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


sqlplus  -s n1/n1 <<EOF
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 <<EOF
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
本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2014-05-10,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档