Oracle 11gR2中的STANDBY

170次閱讀

共計 6929 個字符，預計需要花費 18 分鐘才能閱讀完成。

Oracle 11gR2 中的 STANDBY_MAX_DATA_DELAY，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

Active Data Guard 是 Oracle 11g 的亮點特性之一，而在 11G release 2 中對 Active Data Guard 引入了更多誘人的新特性，這些特性將 Active Data Guard 打造成 Oracle 讀寫分離或報表查詢的理想方案之一。

STANDBY_MAX_DATA_DELAY 是 11gr2 中對 Active Data Guard 的最大增強 (buffer) 之一，這是一個可以在會話級別指定的參數(session parameter)，該參數指定了在 Primary Database 已 commit 提交的變化與 standby Database 數據庫上涉及相關變化的查詢之間所允許的時間延遲，單位為 second 秒。

使用該 STANDBY_MAX_DATA_DELAY 參數的語法如下：

ALTER SESSION SET STANDBY_MAX_DATA_DELAY ={ NONE | INTEGER }

注意事項

該參數無法為 SYS 用戶所用，在 SYS 用戶的 SESSION 下設置該參數將被忽略

若沒有指定 STANDBY_MAX_DATA_DELAY，即使用其默認值 NONE，那么無論主備庫之間有多大的延遲，在 Physical Standby 上的查詢都會被執行

若查詢延遲超過 STANDBY_MAX_DATA_DELAY 所指定的值那么，將報 ORA-03172 錯誤：

03172, 00000,  STANDBY_MAX_DATA_DELAY of %s seconds exceeded 
// *Cause: Standby recovery fell behind the STANDBY_MAX_DATA_DELAY
// requirement.
// *Action: Tune recovery and retry the query later, or switch to another
// standby database within the data delay requirement.

在實際運用中 STANDBY_MAX_DATA_DELAY 保證了在 Standby 數據庫上所作的報表查詢不會得到過于陳舊的結果(stale result)，通過該參數我們可以指定一個報表應用所容許的數據時間延遲。

當然也可以指定不容許任何數據延遲，即設置 STANDBY_MAX_DATA_DELAY 為零，以便做到實時數據查詢。

配置 Primary 與 Standby 數據庫之間的實時查詢或者說零延遲查詢有以下注意事項：

只有特定的應用程序才會對數據延遲有零容忍的需求，注意你的應用程序是否有如此苛刻的要求

在 Standby 數據庫上執行的查詢語句必須返回和主庫上查詢的完全一致的結果

必須設置 STANDBY_MAX_DATA_DELAY 為 0

在查詢開始的那一刻，Standby 數據庫必須同步到與 Primary 數據庫一致的 Current Scn

若結果沒有在 200ms 內返回，則查詢會因 ORA-03172 而終止

Primary 數據庫必須采用最大可用 (max availability) 或最大保護 (maximum protection) 模式

redo 傳輸必須使用 SYNC 選項

必須啟用 Real-Time Query 特性

實際使用

以下我們通過演示來了解該 STANDBY_MAX_DATA_DELAY 的效果:

SQL  select * from v$version; 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL  select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com   www.askmaclean.com
Primary Database SQL  conn maclean/maclean
Connected.
Primary Database SQL  select database_role,protection_mode from v$database;
DATABASE_ROLE PROTECTION_MODE
---------------- --------------------
PRIMARY MAXIMUM AVAILABILITY
Primary Database SQL  create table TSMDD tablespace users as select * From dba_objects;
Table created.
Standby Database SQL  conn maclean/maclean
Connected.
Standby Database SQL  select database_role,protection_mode from v$database;
DATABASE_ROLE PROTECTION_MODE
---------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY
注意 STANDBY_MAX_DATA_DELAY 是一個會話參數 session parameter, 而非實例參數 instance parameter
Standby Database SQL  select name from v$system_parameter where name= standby_max_data_delay 
no rows selected
Standby Database SQL  alter session set STANDBY_MAX_DATA_DELAY=0;
Session altered.
Standby Database SQL  select count(*) from TSMDD; 
 COUNT(*)
----------
 13378

實際測試可以發現當 STANDBY_MAX_DATA_DELAY= 0 時，并不是查詢語句執行時間超過 200ms 就返回 ORA-03172 錯誤，而是指從查詢開始的 200ms 內，若備庫沒有追上主庫的 Current SCN 時出現 ORA-03172。

Standby Database SQL  alter session set STANDBY_MAX_DATA_DELAY=0; Session altered.
Standby Database SQL  set timing on;
Standby Database SQL  select count(1) from TSMDD a, TSMDD b;
 COUNT(1)
----------
 178970884
Elapsed: 00:00:05.34
Standby Database SQL  alter session set events  10046 trace name context forever,level 12 
Session altered.
在主庫上執行大數據量的 insert 操作，但是不提交 commit;
Primary Database SQL  insert into /*+ append */ tsmdd select * from tsmdd;
此時在 Standby  數據庫   上執行查詢語句將觸發 ORA-3172 錯誤
Standby Database SQL  select count(*) from tsmdd
 *
ERROR at line 1: ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded Standby Database SQL  /
select count(*) from tsmdd
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded

以上查詢語句執行過程中的 10046 trace 如下：

PARSING IN CURSOR #47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692536000853
hv=2314050071 ad= 7115e798  sqlid= 3smn48y4yv6hr 
select count(*) from tsmdd
END OF STMT
PARSE #47828795969456:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692536000852 WAIT #47828795969456: nam= standby query scn advance  ela= 201440 p1=770798 p2=0 p3=20 obj#=13873 tim=1316692536202337 WAIT #47828795969456: nam= SQL*Net break/reset to client  ela= 25 driver id=1650815232
break?=1 p3=0 obj#=13873 tim=1316692536202528
WAIT #47828795969456: nam= SQL*Net break/reset to client  ela= 144 driver id=1650815232
break?=0 p3=0 obj#=13873 tim=1316692536202694
WAIT #47828795969456: nam= SQL*Net message to client  ela= 1 driver id=1650815232 #bytes=1
p3=0 obj#=13873 tim=1316692536202715
*** 2011-09-22 19:55:37.983
WAIT #47828795969456: nam= SQL*Net message from client  ela= 1781108 driver
id=1650815232 #bytes=1 p3=0 obj#=13873 tim=1316692537983884
CLOSE #47828795969456:c=0,e=24,dep=0,type=0,tim=1316692537984068
===============================================================================================
PARSING IN CURSOR #47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692537984172
hv=2314050071 ad= 7115e798  sqlid= 3smn48y4yv6hr 
select count(*) from tsmdd
END OF STMT
PARSE #47828795969456:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692537984171 WAIT #47828795969456: nam= standby query scn advance  ela= 200546 p1=770914 p2=0 p3=20 obj#=13873 tim=1316692538184822 WAIT #47828795969456: nam= SQL*Net break/reset to client  ela= 10 driver
id=1650815232 break?=1 p3=0 obj#=13873 tim=1316692538184998
WAIT #47828795969456: nam= SQL*Net break/reset to client  ela= 103 driver
id=1650815232 break?=0 p3=0 obj#=13873 tim=1316692538185154
WAIT #47828795969456: nam= SQL*Net message to client  ela= 1 driver
id=1650815232 #bytes=1 p3=0 obj#=13873 tim=1316692538185182

注意這里出現的 standby query scn advance 等待事件，顯然該等待事件是為了確認 Primary 與 Standby 之間的 Scn 差距，但這又是一個 Internal 的 undocumented 等待事件。我猜測是 P1 是 Standby 數據庫的 Current Scn，而 p3 可能是 Primary 與 Standby 之間的 Scn 差距。OBJ# 是查詢對象的 object_id:

SQL  col owner for a20
SQL  col object_name for a20
SQL  select owner,object_name from dba_objects where object_id=13873;
OWNER OBJECT_NAME
-------------------- --------------------
MACLEAN TSMDD

使用技巧

在實際的使用過程中我們沒有必要每次登錄會話查詢都去指定 STANDBY_MAX_DATA_DELAY 參數，可以通過創建 AFTER LOGON 觸發器來簡化工作。

在 11 g Release 2 中引入了 USERENV Context 的一種新屬性 DATABASE_ROLE，使用該屬性可以便捷地定位用戶所登錄數據庫的角色是 Primary 還是 Standby，11g 的 SQL 和 PL/SQL 客戶端程序均可以通過 SYS_CONTEXT 函數獲取該數據庫角色信息。

通過創建以下登陸后觸發器可以做到當應用程序登錄到啟用實時查詢的 Standby 數據庫上后即自動設置合適的 STANDBY_MAX_DATA_DELAY 參數。這樣即避免了修改應用程序的代碼，有做到了配置合理的最大數據延遲。

CREATE OR REPLACE TRIGGER AUTO_SMDD
 AFTER LOGON ON USER.SCHEMA
BEGIN
 IF (SYS_CONTEXT( USERENV ,  DATABASE_ROLE) IN (PHYSICAL STANDBY)) THEN
 execute immediate  alter session set standby_max_data_delay=5 
 END IF;
END;

注意以上 trigger 只需要在 Primary Database 上以應用相關用戶身份建立即可，會同步到 Standby 上:

Primary Database SQL  conn maclean/maclean
Connected.
Primary Database SQL  CREATE OR REPLACE TRIGGER AUTO_SMDD
 2 AFTER LOGON ON MACLEAN.SCHEMA
 3 BEGIN
 4 IF (SYS_CONTEXT( USERENV ,  DATABASE_ROLE) IN (PHYSICAL STANDBY)) THEN
 5 execute immediate  alter session set standby_max_data_delay=0 
 6 END IF;
 7 END;
 8 /
Trigger created.

關于 Oracle 11gR2 中的 STANDBY_MAX_DATA_DELAY 問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

正文完