[20170407]關于增量檢查點的一個疑問.txt

[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;

--//這樣設定出現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 ):

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

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;

@ &r/checkpoint

update scott.t1 set object_name=object_name where rownum<100 ;

commit;

host sleep 3

--//很奇怪,必須加入sleep 3,然後看不到變化.

--//結果如下:

           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

--//執行一些事務後,出現髒塊:

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