检查点SCN变化实验

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:32

insert into test.t3 values(111);
select checkpoint_change#,current_scn from v$database;
commit;
select checkpoint_change#,current_scn from v$database;
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
alter system archive log current;
select checkpoint_change#,current_scn from v$database;
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
select * from v$log;
--由于设备IO不繁忙,因此要等一会儿才会更新三个检查点SCN
下面是实验证明:
SYS@PROD>alter system archive log current;

System altered.

SYS@PROD>select checkpoint_change#,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
747855 753850

SYS@PROD>select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
747855
747855
747855
747855
747855
747855
747855
747855
747855

9 rows selected.

SYS@PROD>select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
747855
747855
747855
747855
747855
747855
747855
747855
747855

9 rows selected.

SYS@PROD>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV
---------- ---------- ---------- ---------- ---------- ------
STATUS FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------
1 1 31 104857600 4 YES
ACTIVE 747853 31-JAN-14

2 1 32 104857600 4 NO
CURRENT 753845 31-JAN-14

3 1 30 104857600 4 YES
INACTIVE 747801 31-JAN-14

SYS@PROD>select checkpoint_change#,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
747855 753892

SYS@PROD>select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
747855
747855
747855
747855
747855
747855
747855
747855
747855

9 rows selected.

SYS@PROD>select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
747855
747855
747855
747855
747855
747855
747855
747855
747855

9 rows selected.

--上面是立即查询三个检查点SCN还没变化
--下面是过了几分钟才发生了变化,同步成为753845,与current的group2日志组的low scn也就是上一个current group1的high scn相同,见log_history的最后一行的next_change#
SYS@PROD>select recid,sequence#,first_change#,next_change# from v$log_history;

RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 36 597766 598183
2 1 598449 638186
3 2 638186 638842
4 3 638842 638854
5 4 638854 638937
6 5 638937 638948
7 6 638948 639692
8 7 639692 639694
9 8 639694 639697
10 9 639697 639699
11 10 639699 639702

RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
12 11 639702 639970
13 12 639970 639972
14 13 639972 639975
15 14 639975 639977
16 15 639977 639979
17 16 639979 641997
18 17 641997 644653
19 18 644653 644655
20 19 644655 644658
21 1 644659 648896
22 2 648896 648898

RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
23 3 648898 648902
24 4 648902 648904
25 1 648905 669544
26 2 669544 670151
27 3 670151 670186
28 4 670186 673454
29 5 673454 673459
30 6 673459 711108
31 7 711108 714855
32 8 714855 714857
33 9 714857 717474

RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
34 10 717474 717476
35 11 717476 717846
36 12 717846 717848
37 13 717848 739376
38 14 739376 739642
39 15 739642 739718
40 16 739718 739793
41 17 739793 739803
42 18 739803 739820
43 19 739820 740496
44 20 740496 740517

RECID SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
45 21 740517 740562
46 22 740562 740682
47 23 740682 747402
48 24 747402 747546
49 25 747546 747593
50 26 747593 747623
51 27 747623 747645
52 28 747645 747763
53 29 747763 747801
54 30 747801 747853
55 31 747853 753845

55 rows selected.

SYS@PROD>select checkpoint_change#,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
753845 754238

SYS@PROD>select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
753845
753845
753845
753845
753845
753845
753845
753845
753845

9 rows selected.

SYS@PROD>select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
753845
753845
753845
753845
753845
753845
753845
753845
753845

9 rows selected.

alter system checkpoint;
--如果手工触发检查点,发生完全检查点,将会写出所有的脏块,完全检查点发生时,将不能有新的脏块产生,直到完全检查点完成,完全检查点也将会在数据文件头,控制文件中数据库信息节,数据文件节中写入当前系统SCN,而且是立即更新虽然比current_scn小一点,但是原理是正确的,因为oracle随时都有后台进程在发生,查询是有时间差的
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
select checkpoint_change#,current_scn from v$database;
select * from v$log;

本文标签:
本文标题:检查点SCN变化实验
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】