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 秒(Specifies a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary and when those same changes can be queries  on the standby database)。

 

使用該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
--------------------------------------------------------------------------------
 & 

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.