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;
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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26521853/viewspace-1083882/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26521853/viewspace-1083882/