当前位置: 萬仟网 > IT编程>数据库>Oracle > [20190312]关于增量检查点的疑问(补充).txt

[20190312]关于增量检查点的疑问(补充).txt

15  人参与 | 时间:2019-03-15 | 我要评论

[20190312]关于增量检查点的疑问(补充).txt

--//有人问我以前写一个帖子的问题,关于增量检查点的问题,链接如下:http://blog.itpub.net/267265/viewspace-2136817/
--//实际上我自己看以前写的帖子一下子有点蒙,主要出现low_rba16=0xffffffff.ffffffff.ffff,为什么恢复的起点是on_disk_rba16.
--//先简单说明一下:
--//oracle现在写脏块基本采用增量检查点,即使日志切换,实际上执行也是增量检查点,除非执行alter system checkpoint,或者
--//shutdown immediate(normal)正常关闭数据库,如果异常关闭数据库,启动时执行崩溃恢复(crash recovery),恢复起点从low_rba.
--//先验证这样的情况:

1.环境:
scott@book> @ ver1
port_string         version        banner
------------------- -------------- ----------------------------------------------------------------------------
x86_64/linux 2.4.xx 11.2.0.4.0     oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

--//写一个脚本check.sql,以前写的太复杂,简单一点:
--// x$kccrt 记录全检查点
--// x$kcccp 记录增量检查点

$ cat check.sql
column "full checkpoint_rba" format a21
column low_rba format a20
column low_rba16 format a20
column on_disk_rba format a20
column on_disk_rba16 format a20
column rtckp_rba format a20
column diff_date format 9999999.99
rem column cposd_ono_disk_rba_scn format 99999999999999999999999999999999
column cpdrt heading "检查点队列|脏块数量|cpdrt"
column cpodt_on_disk_rba heading "检查点队列|on disk rba|时间戳|cpodt"
column cpods heading "检查点队列|on disk rba scn|cpods"
column cphbt heading "检查点心跳|cphbt"
column current_sysdate heading "当前时间|sysdate"
set num 12

select b.cplrba_seq || '.' || b.cplrba_bno || '.' || b.cplrba_bof "low_rba"
      ,b.cpodr_seq || '.' || b.cpodr_bno || '.' || b.cpodr_bof "on_disk_rba"
      ,b.cpods "on_disk_rba_scn(cpods)"
      ,to_date (b.cpodt, 'mm-dd-yyyy hh24:mi:ss') "on_disk_rba_time(cpodt)"
      ,a.rtckp_rba_seq || '.' || a.rtckp_rba_bno || '.' || a.rtckp_rba_bof
          "full checkpoint_rba"
      ,a.rtckp_scn "full_checkpoint(rtckp_scn)"
      ,to_date (a.rtckp_tim, 'mm-dd-yyyy hh24:mi:ss')
          "full_checkpoint_time_rtckp_tim"
      ,b.cpods - a.rtckp_scn "diff_scn(on_disk_rdb-ch_scn)"
      ,a.rtcln "current_group"
      ,sysdate current_sysdate
      ,cpdrt
  from x$kccrt a, x$kcccp b
 where a.rtnum = b.cptno and a.inst_id = b.inst_id;

2.测试:
sys@book> shutdown  abort ;
oracle instance shut down.

sys@book> startup mount
oracle instance started.
total system global area    643084288 bytes
fixed size                    2255872 bytes
variable size               205521920 bytes
database buffers            427819008 bytes
redo buffers                  7487488 bytes
database mounted.

sys@book> archive log list
database log mode              archive mode
automatic archival             enabled
archive destination            /u01/app/oracle/archivelog/book/
oldest online log sequence     787
next log sequence to archive   789
current log sequence           789

sys@book> @ check
                                                                                                                                                                                         检查点队列
                                                                                                                                                                   当前时间                脏块数量
low_rba     on_disk_rba on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
----------- ----------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
789.5775.0  789.5955.0  13278979623      2019-03-12 11:20:53 789.1890.16           13278977341      2019-03-12 10:52:50                         2282             2 2019-03-12 11:21:42           12

--//看看日志应用的起点是否从low_rba开始.

sys@book> alter database open ;
database altered.

--//查看alert.log日志:
beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
started redo scan
completed redo scan
 read 90 kb redo, 12 data blocks need recovery
started redo application at
 thread 1: logseq 789, block 5775
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>起点对应low_rba=789.5775.0
recovery of online redo log: thread 1 group 2 seq 789 reading mem 0
  mem# 0: /mnt/ramdisk/book/redo02.log
completed redo application of 0.00mb
completed crash recovery at
 thread 1: logseq 789, block 5956, scn 13278999624
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>结束对应on_disk_rba=789.5955.0加1个块(512字节redo),scn号对应on_disk_rba_scn+1.
 12 data blocks read, 12 data blocks written, 90 redo k-bytes read
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//5955-5775 = 180,应用日志180块,日志文件每块512字节.
--//180*512/1024 = 90k,这些正好对上.
tue mar 12 11:23:26 2019
lgwr: starting arch processes
tue mar 12 11:23:26 2019
arc0 started with pid=45, os id=56804
arc0: archival started
lgwr: starting arch processes complete
arc0: starting arch processes
thread 1 advanced to log sequence 790 (thread open)
thread 1 opened at log sequence 790
--//日志切换使用新日志.
  current log# 3 seq# 790 mem# 0: /mnt/ramdisk/book/redo03.log
successful open of redo thread 1
mttr advisory is disabled because fast_start_mttr_target is not set
tue mar 12 11:23:27 2019
smon: enabling cache recovery

--//也就是异常关闭后,crash recovery的起点从low_rba到on_disk_rba,完成后scn号+1,日志块号加1.日志切换使用新日志.

3.如果low_rba16=0xffffffff.ffffffff.ffff呢?

sys@book> alter system checkpoint ;
system altered.

sys@book> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
4294967295.429496729 790.659.0            13279000486      2019-03-12 11:32:34 790.658.16            13279000485      2019-03-12 11:32:33                            1             3 2019-03-12 11:32:35            0
5.65535

--//等一会执行:
sys@book> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
4294967295.429496729 790.678.0            13279000505      2019-03-12 11:32:53 790.658.16            13279000485      2019-03-12 11:32:33                           20             3 2019-03-12 11:32:54            0
5.65535


--//你可以发现alter system checkpoint 后,如果没有事务low_rba16=0xffffffff.ffffffff.ffff,而on_disk_rba一直在增加.而cpdrt=0.
--//似乎11g不知道为什么在"空转"(没有事务产生的情况下)的情况,日志也在不断增加,不知道为什么?

sys@book> shutdown  abort ;
oracle instance shut down.

sys@book> startup mount
oracle instance started.
total system global area    643084288 bytes
fixed size                    2255872 bytes
variable size               205521920 bytes
database buffers            427819008 bytes
redo buffers                  7487488 bytes
database mounted.

sys@book> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
4294967295.429496729 790.705.0            13279000532      2019-03-12 11:33:20 790.658.16            13279000485      2019-03-12 11:32:33                           47             3 2019-03-12 11:36:09            0
5.65535

--//可以发现这个时候low_rba16=0xffffffff.ffffffff.ffff,这个时候恢复的起点从那里开始,实际上从on_disk_rba开始,或者讲根本没
--//有恢复,cpdrt=0也是佐证,虽然当时on_disk_rba还在不断增加.

sys@book> alter database open ;
database altered.

--//查看alert.log:
beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
started redo scan
completed redo scan
 read 0 kb redo, 0 data blocks need recovery
started redo application at
 thread 1: logseq 790, block 705, scn 13279000532
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>起点对应on_disk_rba=790.705.0
recovery of online redo log: thread 1 group 3 seq 790 reading mem 0
  mem# 0: /mnt/ramdisk/book/redo03.log
completed redo application of 0.00mb
completed crash recovery at
 thread 1: logseq 790, block 706, scn 13279020533
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>结束对应on_disk_rba=790.705.0加1个块(512字节redo),scn号对应on_disk_rba_scn+1.
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=> 日志应用0k.
tue mar 12 11:39:33 2019
lgwr: starting arch processes
tue mar 12 11:39:33 2019
arc0 started with pid=45, os id=56980
arc0: archival started
lgwr: starting arch processes complete
arc0: starting arch processes
thread 1 advanced to log sequence 791 (thread open)
thread 1 opened at log sequence 791
--//日志切换使用新日志.
  current log# 1 seq# 791 mem# 0: /mnt/ramdisk/book/redo01.log
successful open of redo thread 1
mttr advisory is disabled because fast_start_mttr_target is not set
tue mar 12 11:39:34 2019
smon: enabling cache recovery

4.我在原链接写一个脚本:
scott@book> create table t1 as select * from all_objects ;
table created.

$ cat a.sql
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
@ check
update scott.t1 set object_name=object_name where rownum=1 ;
commit;
host sleep 3
@ check


sys@book> @ a.sql
system altered.
system altered.
system altered.
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
4294967295.429496729 791.21362.0          13279021797      2019-03-12 11:52:59 791.21362.16          13279021800      2019-03-12 11:53:00                           -3             1 2019-03-12 11:53:01            0
5.65535
1 row updated.
commit complete.
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
791.21363.0          791.21366.0          13279021805      2019-03-12 11:53:02 791.21362.16          13279021800      2019-03-12 11:53:00                            5             1 2019-03-12 11:53:04            3

--//注意看发生事务前后的low_rba,on_disk_rba.不好描述,自己看.^_^.
--//一旦有事务产生,你可以发现low_rba不再是4294967295.4294967295.65535.
--//很奇怪不知道为什么11g下在没有事务的情况下会"空转",这样11g的日志即使是很空闲的数据库日志增加也会比10g大.

5.看看10g的情况:
sys@192.168.100.33:1521/test> @ ver1

port_string                    version        banner
------------------------------ -------------- ----------------------------------------------------------------
x86_64/linux 2.4.xx            10.2.0.4.0     oracle database 10g enterprise edition release 10.2.0.4.0 - 64bi

sys@192.168.100.33:1521/test> alter system checkpoint ;
system altered.

sys@192.168.100.33:1521/test> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
4294967295.429496729 1497.42866.0         14987614992      2019-03-12 11:55:37 1497.42866.16         14987615031      2019-03-12 11:57:34                          -39             3 2019-03-12 11:57:35            0
5.65535


sys@192.168.100.33:1521/test> @ check
                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
4294967295.429496729 1497.42866.0         14987614992      2019-03-12 11:55:37 1497.42866.16         14987615031      2019-03-12 11:57:34                          -39             3 2019-03-12 11:58:29            0
5.65535

--//注意看执行时间2019-03-12 11:57:35 -2019-03-12 11:58:29 之间,没有任何事务产生,on_disk_rba根本不变化.这样10g日志产生量
--//明显比11g小.

6.我改上面的脚本check.sql:
--//最后加入host sleep 1.执行如下:

$ rlsql -s -l sys/oracle as sysdba <<eof
> $(seq 100| xargs -i{} cat /home/oracle/sqllaji/check.sql)
> eof

                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
791.24582.0          791.24711.0          13279023352      2019-03-12 12:11:39 791.21362.16          13279021800      2019-03-12 11:53:00                         1552             1 2019-03-12 12:11:41            8


                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
791.24582.0          791.24712.0          13279023353      2019-03-12 12:11:40 791.21362.16          13279021800      2019-03-12 11:53:00                         1553             1 2019-03-12 12:11:42            8


                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
791.24582.0          791.24713.0          13279023354      2019-03-12 12:11:41 791.21362.16          13279021800      2019-03-12 11:53:00                         1554             1 2019-03-12 12:11:43            8

--//在没有事务的情况下.每秒scn增加1,日志块增加1,是否更我访问这些内存"表"有关,换1个方式测试,取消check.sql后面的host sleep 1,建立脚本b.sql:
$ cat b.sql
@ check.sql
host sleep 30
@ check.sql


sys@book> @ b.sql

                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
791.24582.0          791.24852.0          13279023481      2019-03-12 12:13:41 791.21362.16          13279021800      2019-03-12 11:53:00                         1681             1 2019-03-12 12:13:43           19


                                                                                                                                                                                                           检查点队列
                                                                                                                                                                                     当前时间                脏块数量
low_rba              on_disk_rba          on_disk_rba_scn( on_disk_rba_time(cp full checkpoint_rba   full_checkpoint( full_checkpoint_tim diff_scn(on_disk_rdb-ch_scn) current_group sysdate                    cpdrt
-------------------- -------------------- ---------------- ------------------- --------------------- ---------------- ------------------- ---------------------------- ------------- ------------------- ------------
791.24582.0          791.24882.0          13279023511      2019-03-12 12:14:11 791.21362.16          13279021800      2019-03-12 11:53:00                         1711             1 2019-03-12 12:14:13           19

--//确实每秒scn增加1,on_disk_rba也是增加每秒1块.

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com