[20170407]关于增量检查点的一个疑问.txt
--//oracle现在写脏块基本采用增量检查点,除非执行alter system checkpoint,或者shutdown immediate(normal)正常关闭数据库.
--//别人的疑问,如果如果写增量检查点时,current log tail at RBA=Incremental checkpoint up to RBA时,如下情况
1.环境:
SYS@book> @ &r/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
SYS@book> alter system set log_checkpoints_to_alert=true scope=memory;
System altered.
SYS@book> alter system set log_checkpoint_timeout=120 scope=memory;
System altered.
--//这样设置出现current log tail at RBA=Incremental checkpoint up to RBA概率大一些.
--//检查alert.
Incremental checkpoint up to RBA [0x31a.c823.0], current log tail at RBA [0x31a.c823.0]
Fri Apr 07 15:38:00 2017
--//这时查看视图x$kcccp:
column on_disk_rba16 format a20
column rtckp_rba format a20
column diff_date format 999999.99
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"
PROMPT
PROMPT REDO ( Hexadecimal ):
PROMPT
SELECT cpdrt ,
'0x'||to_char(cplrba_seq,'FMxxxxxxxx') || '.' || to_char(cplrba_bno,'FMxxxxxxxx')|| '.' || to_char(cplrba_bof,'FMxxxx') "low_rba16",
'0x'||to_char(cpodr_seq,'FMxxxxxxxx') || '.' || to_char(cpodr_bno,'FMxxxxxxxx') || '.' || to_char(cpodr_bof,'FMxxxx') "on_disk_rba16",
TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS') cpodt_on_disk_rba,
SYSDATE current_sysdate,
ROUND ( (SYSDATE - TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS')) * 86400,
2)
diff_date,
CPODS ,
CPHBT,
current_scn,
current_scn - cpods diff_scn,
indx
FROM x$kcccp, v$database
WHERE CPLRBA_SEQ <> 0;
SYS@book> @ &r/checkpoint
REDO ( Hexadecimal ):
检查点队列
检查点队列 on disk rba 检查点队列
脏块数量 时间戳 当前时间 on disk rba scn 检查点心跳
CPDRT low_rba16 on_disk_rba16 CPODT SYSDATE DIFF_DATE CPODS CPHBT CURRENT_SCN DIFF_SCN INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
0 0xffffffff.ffffffff. 0x31a.c838.0 2017-04-07 15:37:59 2017-04-07 15:38:46 47.00 13277192191 940697982 0 -13277192191 0
ffff
--//low_rba = 0xffffffff.ffffffff.ffff.如果这时异常关闭数据库会出现什么情况呢?oracle如何确定恢复的起点呢low_rba ? (这也
--//是别人问的问题)
--//执行shutdown abort,再重新启动数据库,观察alrt文件:
Fri Apr 07 15:39:24 2017
alter database open
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 794, block 51256, scn 13277192191
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Recovery of Online Redo Log: Thread 1 Group 1 Seq 794 Reading mem 0
Mem# 0: /mnt/ramdisk/book/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 794, block 51257, scn 13277212192
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Fri Apr 07 15:39:25 2017
LGWR: STARTING ARCH PROCESSES
Fri Apr 07 15:39:25 2017
ARC0 started with pid=45, OS id=21810
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 advanced to log sequence 795 (thread open)
Thread 1 opened at log sequence 795
Current log# 2 seq# 795 mem# 0: /mnt/ramdisk/book/redo02.log
Successful open of redo thread 1
--//0x31a = 794
--//0xc838 = 51256
--//正好是on_disk_rba地址.注意看下划线内容.
--//噢明白了.如果出现这样的情况,不需要恢复.
--//实际上如果出现脏块,仅仅将low_rba16设置为当前的on_disk_rba16.以这个作为异常恢复的起点.
--//简单写一个例子:
]$ cat a.sql
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
@ &r/checkpoint
update scott.t1 set object_name=object_name where rownum<100 ;
commit;
host sleep 3
@ &r/checkpoint
--//很奇怪,必须加入sleep 3,然后看不到变化.
--//结果如下:
REDO ( Hexadecimal ):
检查点队列
检查点队列 on disk rba 检查点队列
脏块数量 时间戳 当前时间 on disk rba scn 检查点心跳
CPDRT low_rba16 on_disk_rba16 CPODT SYSDATE DIFF_DATE CPODS CPHBT CURRENT_SCN DIFF_SCN INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
0 0xffffffff.ffffffff. 0x31c.14f4.0 2017-04-07 16:16:42 2017-04-07 16:16:44 2.00 13277216046 940700230 13277216052 6 0
ffff
--//执行一些事务后,出现脏块:
REDO ( Hexadecimal ):
检查点队列
检查点队列 on disk rba 检查点队列
脏块数量 时间戳 当前时间 on disk rba scn 检查点心跳
CPDRT low_rba16 on_disk_rba16 CPODT SYSDATE DIFF_DATE CPODS CPHBT CURRENT_SCN DIFF_SCN INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
4 0x31c.14f5.0 0x31c.150c.0 2017-04-07 16:16:45 2017-04-07 16:16:47 2.00 13277216057 940700233 13277216060 3 0
--//注意看low_rba16 ,on_disk_rba16就明白了.