Shulou(Shulou.com)05/31 Report--

STANDBY_MAX_DATA_DELAY in Oracle 11gR2, aiming at this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Active Data Guard is one of the highlights of Oracle 11g, while 11G release 2 introduces more attractive new features for Active Data Guard, which make Active Data Guard an ideal solution for Oracle read-write separation or report queries.

STANDBY_MAX_DATA_DELAY is one of the biggest enhancements to Active Data Guard in 11gr2 (buffer), which is a parameter (session parameter) that can be specified at the session level, which specifies the time delay, in second seconds, allowed between changes that have been submitted by Primary Database commit and queries that involve related changes on the standby Database database.

The syntax for using this STANDBY_MAX_DATA_DELAY parameter is as follows:

ALTER SESSION SET STANDBY_MAX_DATA_DELAY = {NONE | INTEGER}

Matters needing attention

This parameter cannot be used by SYS users. Setting this parameter under the SESSION of SYS users will be ignored

If STANDBY_MAX_DATA_DELAY is not specified, even with its default value of NONE, queries on Physical Standby will be executed regardless of the delay between the primary and secondary libraries.

If the query delay exceeds the value specified by STANDBY_MAX_DATA_DELAY, an ORA-03172 error will be reported:

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.

In practical application, STANDBY_MAX_DATA_DELAY ensures that the report query on Standby database will not get too old results (stale result). Through this parameter, we can specify the data time delay allowed by a report application.

Of course, you can also specify that no data delay is allowed, that is, set STANDBY_MAX_DATA_DELAY to zero in order to achieve real-time data query.

There are the following considerations for configuring real-time queries, or zero-latency queries, between Primary and Standby databases:

Only specific applications will have zero tolerance for data latency. Pay attention to whether your application has such stringent requirements.

The query statement executed on the Standby database must return exactly the same results as the query on the main database

STANDBY_MAX_DATA_DELAY must be set to 0

At the beginning of the query, the Standby database must be synchronized to the Current Scn that is consistent with the Primary database

If the result is not returned in 200ms, the query will be terminated because of ORA-03172

Primary database must use maximum available (max availability) or maximum protection (maximum protection) mode

Redo transport must use the SYNC option

The Real-Time Query feature must be enabled

Practical use

Let's demonstrate the effect of this STANDBY_MAX_DATA_DELAY:

SQL > select * from v$version BANNER----Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit ProductionPL/SQL Release 11.2.0.2.0-ProductionCORE 11.2.0 .2.0 ProductionTNS for Linux: Version 11.2.0.2.0-ProductionNLSRTL Version 11.2.0.2.0-ProductionSQL > select * from global_name GLOBAL_NAME----www.askmaclean.com & www.askmaclean.comPrimary Database SQL > conn maclean/macleanConnected.Primary Database SQL > select database_role,protection_mode from v$database DATABASE_ROLE PROTECTION_MODE--PRIMARY MAXIMUM AVAILABILITYPrimary Database SQL > create table TSMDD tablespace users as select * From dba_objects;Table created.Standby Database SQL > conn maclean/macleanConnected.Standby Database SQL > select database_role,protection_mode from v$database DATABASE_ROLE PROTECTION_MODE--PHYSICAL STANDBY MAXIMUM AVAILABILITY Note STANDBY_MAX_DATA_DELAY is a session parameter session parameter, not an instance parameter instance parameterStandby Database SQL > select name from v$system_parameter where name='standby_max_data_delay';no rows selectedStandby Database SQL > alter session set STANDBY_MAX_DATA_DELAY=0;Session altered.Standby Database SQL > select count (*) from TSMDD COUNT (*)-13378

The actual test can find that when the STANDBY_MAX_DATA_DELAY=0 is executed, the ORA-03172 error is not returned when the execution time of the query statement exceeds 200ms, but the ORA-03172 occurs in the 200ms from the beginning of the query if the standby database does not catch up with the Current SCN of the main database.

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)-178970884Elapsed: 00:00:05.34Standby Database SQL > alter session set events' 10046 trace name context forever,level 12 session altered. Perform insert operations with a large amount of data on the main database, but do not commit commit;Primary Database SQL > insert into / * + append * / tsmdd select * from tsmdd Executing the query statement on the Standby database at this time will trigger the ORA-3172 error 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

The 10046 trace during the execution of the above query is as follows:

PARSING IN CURSOR # 47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692536000853hv=2314050071 ad='7115e798' sqlid='3smn48y4yv6hr'select count (*) from tsmddEND OF STMTPARSE # 47828795969456 from tsmddEND OF STMTPARSE # 47828795969456 Tim=1316692536000852 WAIT # 47828795969456: nam='standby query scn advance' ela= 201440 p1 "770798 p2" 0p3 "20 obj#=13873 tim=1316692536202337 WAIT # 47828795969456: nam='SQL*Net break/reset to client' ela= 25 driver id=1650815232break?=1 p3room0 obj#=13873 tim=1316692536202528WAIT # 47828795969456: nam='SQL*Net break/reset to client' ela= 144driver id=1650815232break?=0 p3room0 obj#=13873 tim=1316692536202694WAIT # 47828795969456: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1p3=0 obj#=13873 tim=1316692536202715*** 2011-09-22 19:55:37.983WAIT # 47828795969456: nam='SQL*Net message from client 'ela= 1781108 driverid=1650815232 # bytes=1 p3 47828795969456:c=0 0 obj#=13873 tim=1316692537983884CLOSE # 47828795969456:c=0 Epimel 24 depended 0title type0memoir 1316692537984068 parsing IN CURSOR # 47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692537984172hv=2314050071 ad='7115e798' sqlid='3smn48y4yv6hr'select count (*) from tsmddEND OF STMTPARSE # 47828795969456 Francis 53meme 0MICOLING 1739041831 Tim=1316692537984171 WAIT # 47828795969456: nam='standby query scn advance' ela= 200546 p1 "770914 p2" 0p3 "20 obj#=13873 tim=1316692538184822 WAIT # 47828795969456: nam='SQL*Net break/reset to client' ela= 10 driverid=1650815232 break?=1 p3room0 obj#=13873 tim=1316692538184998WAIT # 47828795969456: nam='SQL*Net break/reset to client' ela= 103 driverid=1650815232 break?=0 p3room0 obj#=13873 tim=1316692538185154WAIT # 47828795969456: nam='SQL*Net message to client' ela= 1 driverid=1650815232 # bytes=1 p3room0 obj#=13873 tim=1316692538185182

Notice the standby query scn advance wait event here, which is obviously intended to confirm the Scn gap between Primary and Standby, but this is another Internal undocumented wait event. My guess is that P1 is the Current Scn of Standby database, while p3 may be the Scn gap between Primary and Standby. OBJ# is the object_id of the query object:

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

Use skills

In the actual use process, we do not need to specify STANDBY_MAX_DATA_DELAY parameters every login session query, we can create AFTER LOGON triggers to simplify the work.

A new attribute DATABASE_ROLE of USERENV Context is introduced in 11g Release 2, which can be used to easily locate whether the role of the database logged in by the user is Primary or Standby,11g 's SQL and PL/SQL client programs can obtain the database role information through the SYS_CONTEXT function.

By creating the following post-login trigger, you can automatically set the appropriate STANDBY_MAX_DATA_DELAY parameters when the application logs in to the Standby database that enables real-time query. In this way, the modification of the application code is avoided, and the most big data delay with reasonable configuration is achieved.

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

Note that the above trigger only needs to be established on Primary Database as the relevant user of the application, and will be synchronized to the Standby:

Primary Database SQL > conn maclean/macleanConnected.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. So much for the answer to the STANDBY_MAX_DATA_DELAY question in Oracle 11gR2. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.