共计 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 行业资讯频道了解更多相关知识。
