[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

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就明白了.

ITPub博客

[20170407]关于增量检查点的一个疑问.txt

最新文章