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参数的语法如下：

ALTERSESSIONSETSTANDBY_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_DELAYof%ssecondsexceeded"//*Cause:StandbyrecoveryfellbehindtheSTANDBY_MAX_DATA_DELAY//requirement.//*Action:Tunerecoveryandretrythequerylater,orswitchtoanother//standbydatabasewithinthedatadelayrequirement.

在实际运用中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*fromv$version;BANNER--------------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.2.0-64bitProductionPL/SQLRelease11.2.0.2.0-ProductionCORE11.2.0.2.0ProductionTNSforLinux:Version11.2.0.2.0-ProductionNLSRTLVersion11.2.0.2.0-ProductionSQL>select*fromglobal_name;GLOBAL_NAME--------------------------------------------------------------------------------www.askmaclean.com&www.askmaclean.comPrimaryDatabaseSQL>connmaclean/macleanConnected.PrimaryDatabaseSQL>selectdatabase_role,protection_modefromv$database;DATABASE_ROLEPROTECTION_MODE------------------------------------PRIMARYMAXIMUMAVAILABILITYPrimaryDatabaseSQL>createtableTSMDDtablespaceusersasselect*Fromdba_objects;Tablecreated.StandbyDatabaseSQL>connmaclean/macleanConnected.StandbyDatabaseSQL>selectdatabase_role,protection_modefromv$database;DATABASE_ROLEPROTECTION_MODE------------------------------------PHYSICALSTANDBYMAXIMUMAVAILABILITY注意STANDBY_MAX_DATA_DELAY是一个会话参数sessionparameter,而非实例参数instanceparameterStandbyDatabaseSQL>selectnamefromv$system_parameterwherename='standby_max_data_delay';norowsselectedStandbyDatabaseSQL>altersessionsetSTANDBY_MAX_DATA_DELAY=0;Sessionaltered.StandbyDatabaseSQL>selectcount(*)fromTSMDD;COUNT(*)----------13378

实际测试可以发现当STANDBY_MAX_DATA_DELAY=0时，并不是查询语句执行时间超过200ms就返回ORA-03172错误，而是指从查询开始的200ms内，若备库没有追上主库的Current SCN时出现ORA-03172。

StandbyDatabaseSQL>altersessionsetSTANDBY_MAX_DATA_DELAY=0;Sessionaltered.StandbyDatabaseSQL>settimingon;StandbyDatabaseSQL>selectcount(1)fromTSMDDa,TSMDDb;COUNT(1)----------178970884Elapsed:00:00:05.34StandbyDatabaseSQL>altersessionsetevents'10046tracenamecontextforever,level12';Sessionaltered.在主库上执行大数据量的insert操作，但是不提交commit;PrimaryDatabaseSQL>insertinto/*+append*/tsmddselect*fromtsmdd;此时在Standby数据库上执行查询语句将触发ORA-3172错误StandbyDatabaseSQL>selectcount(*)fromtsmdd*ERRORatline1:ORA-03172:STANDBY_MAX_DATA_DELAYof0secondsexceededStandbyDatabaseSQL>/selectcount(*)fromtsmdd*ERRORatline1:ORA-03172:STANDBY_MAX_DATA_DELAYof0secondsexceeded

以上查询语句执行过程中的10046 trace如下：

PARSINGINCURSOR#47828795969456len=26dep=0uid=34oct=3lid=34tim=1316692536000853hv=2314050071ad='7115e798'sqlid='3smn48y4yv6hr'selectcount(*)fromtsmddENDOFSTMTPARSE#47828795969456:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692536000852WAIT#47828795969456:nam='standbyqueryscnadvance'ela=201440p1=770798p2=0p3=20obj#=13873tim=1316692536202337WAIT#47828795969456:nam='SQL*Netbreak/resettoclient'ela=25driverid=1650815232break?=1p3=0obj#=13873tim=1316692536202528WAIT#47828795969456:nam='SQL*Netbreak/resettoclient'ela=144driverid=1650815232break?=0p3=0obj#=13873tim=1316692536202694WAIT#47828795969456:nam='SQL*Netmessagetoclient'ela=1driverid=1650815232#bytes=1p3=0obj#=13873tim=1316692536202715***2011-09-2219:55:37.983WAIT#47828795969456:nam='SQL*Netmessagefromclient'ela=1781108driverid=1650815232#bytes=1p3=0obj#=13873tim=1316692537983884CLOSE#47828795969456:c=0,e=24,dep=0,type=0,tim=1316692537984068===============================================================================================PARSINGINCURSOR#47828795969456len=26dep=0uid=34oct=3lid=34tim=1316692537984172hv=2314050071ad='7115e798'sqlid='3smn48y4yv6hr'selectcount(*)fromtsmddENDOFSTMTPARSE#47828795969456:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692537984171WAIT#47828795969456:nam='standbyqueryscnadvance'ela=200546p1=770914p2=0p3=20obj#=13873tim=1316692538184822WAIT#47828795969456:nam='SQL*Netbreak/resettoclient'ela=10driverid=1650815232break?=1p3=0obj#=13873tim=1316692538184998WAIT#47828795969456:nam='SQL*Netbreak/resettoclient'ela=103driverid=1650815232break?=0p3=0obj#=13873tim=1316692538185154WAIT#47828795969456:nam='SQL*Netmessagetoclient'ela=1driverid=1650815232#bytes=1p3=0obj#=13873tim=1316692538185182

注意这里出现的standby query scn advance等待事件，显然该等待事件是为了确认Primary与Standby之间的Scn差距，但这又是一个Internal的undocumented 等待事件。我猜测是P1是Standby数据库的Current Scn，而p3可能是Primary 与 Standby之间的Scn 差距。OBJ#是查询对象的object_id:

SQL>colownerfora20SQL>colobject_namefora20SQL>selectowner,object_namefromdba_objectswhereobject_id=13873;OWNEROBJECT_NAME----------------------------------------MACLEANTSMDD

使用技巧

在实际的使用过程中我们没有必要每次登录会话查询都去指定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参数。这样即避免了修改应用程序的代码，有做到了配置合理的最大数据延迟。

CREATEORREPLACETRIGGERAUTO_SMDDAFTERLOGONONUSER.SCHEMABEGINIF(SYS_CONTEXT('USERENV','DATABASE_ROLE')IN('PHYSICALSTANDBY'))THENexecuteimmediate'altersessionsetstandby_max_data_delay=5';ENDIF;END;

注意以上trigger 只需要在Primary Database上以应用相关用户身份建立即可，会同步到Standby上:

PrimaryDatabaseSQL>connmaclean/macleanConnected.PrimaryDatabaseSQL>CREATEORREPLACETRIGGERAUTO_SMDD2AFTERLOGONONMACLEAN.SCHEMA3BEGIN4IF(SYS_CONTEXT('USERENV','DATABASE_ROLE')IN('PHYSICALSTANDBY'))THEN5executeimmediate'altersessionsetstandby_max_data_delay=0';6ENDIF;7END;8/Triggercreated.

关于Oracle 11gR2中的STANDBY_MAX_DATA_DELAY问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。