Oracle Heartbeat研究

n-lauren發表於2013-02-17

在Oracle中有一個事件叫Heartbeat,這個詞在很多地方被提及,並且有著不同的含義(比如RAC中),我們這裡要討論的是CKPT的Heartbeat機制。

Oracle通過CKPT程式每3秒將Heartbeat寫入控制檔案,以減少故障時的恢復時間(這個我們後面再詳細闡述)。

我們可以通過如下方法驗證這個過程。

1.首先在系統級啟用10046時間跟蹤

並重新啟動資料庫使之生效

[oracle@jumper oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jan 19 09:24:04 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> alter system set event='10046 trace name context forever,level 12' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  114365800 bytes
Fixed Size                   451944 bytes
Variable Size              50331648 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production 

2.檢查bdump目錄下生成的跟蹤檔案

目錄在$ORACLE_HOME/admin/$ORACLE_SID/bdump目錄下,每個後臺程式都會生成一個跟蹤檔案。

[oracle@jumper bdump]$ ls
20050424_alert_conner.log  conner_arc0_2569.trc  conner_dbw0_2559.trc  conner_reco_2567.trc
alert_conner.log           conner_arc1_2571.trc  conner_lgwr_2561.trc  conner_smon_2565.trc
a.sql                      conner_ckpt_2563.trc  conner_pmon_2557.trc

3.檢查CKPT程式的跟蹤檔案

我們可以很容易的發現CKPT程式每3秒都對控制檔案進行一次寫入

[oracle@jumper bdump]$ tail -f conner_ckpt_2563.trc 
WAIT #0: nam='rdbms ipc message' ela= 2994710 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2442 p1=3 p2=3 p3=3
WAIT #0: nam='rdbms ipc message' ela= 2995171 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2586 p1=3 p2=3 p3=3
WAIT #0: nam='rdbms ipc message' ela= 2994962 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2582 p1=3 p2=3 p3=3
WAIT #0: nam='rdbms ipc message' ela= 2995020 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2455 p1=3 p2=3 p3=3
WAIT #0: nam='rdbms ipc message' ela= 2995188 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2412 p1=3 p2=3 p3=3
WAIT #0: nam='rdbms ipc message' ela= 2995187 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2463 p1=3 p2=3 p3=3
WAIT #0: nam='rdbms ipc message' ela= 2995095 p1=300 p2=0 p3=0
WAIT #0: nam='control file parallel write' ela= 2448 p1=3 p2=3 p3=3

4.檢查控制檔案的變更

通過2次dump控制檔案,比較其trace檔案輸出可以比較得到不同之處,我們發現,Oracle僅僅更新了Heartbeat這個數值。

[oracle@jumper udump]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 18 22:44:10 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> alter session set events 'immediate trace name CONTROLF level 10';
Session altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@jumper udump]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 18 22:44:18 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> alter session set events 'immediate trace name CONTROLF level 10' ;
Session altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
[oracle@jumper udump]$ ls
conner_ora_21896.trc conner_ora_21898.trc
[oracle@jumper udump]$ diff conner_ora_21896.trc conner_ora_21898.trc
1c1
< /opt/oracle/admin/conner/udump/conner_ora_21896.trc
---
> /opt/oracle/admin/conner/udump/conner_ora_21898.trc
14c14
< Unix process pid: 21896, image: oracle@jumper.hurray.com.cn (TNS V1-V3)
---
> Unix process pid: 21898, image: oracle@jumper.hurray.com.cn (TNS V1-V3)
16c16
< *** SESSION ID9.813) 2006-01-18 22:44:14.314
---
> *** SESSION ID9.815) 2006-01-18 22:44:21.569
63c63
< heartbeat: 579991793 mount id: 3191936000
---
> heartbeat: 579991796 mount id: 3191936000
[oracle@jumper udump]$ 

Steve曾經這樣描述CKPT Heartbeat:

In 8.0.5 a heartbeat mechanism was included in CKPT's timeout action (every 3 seconds) to update the checkpoint progress record for the thread in the controlfile.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22990797/viewspace-754181/,如需轉載,請註明出處,否則將追究法律責任。

相關文章