Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Reporting users who query the “Active Data Guard” may want that the data be synchronized with Production.
Others may not care whether LAG has occurred for a few seconds. Minor delays may not be very important for someone. But even they often don’t want longer lags . For example, a user can say that I only tolerate a 5 second lag. This parameter is very helpful for this user.
For such cases, ORACLE has created the STANDBY_MAX_DATA_DELAY parameter that will be valid at the session level for Standby side. This parameter takes seconds.
We can set this parameter as session level as follows;
1 | ALTER SESSION SET STANDBY_MAX_DATA_DELAY = {INTEGER|NONE} |
STANDBY_MAX_DATA_DELAY Example
Lest test it;
Step1:
Check if the Data Guard is working healthy on Primary and Standby.
1 2 3 4 5 6 7 | [Primary -1] SQL> select status from gv$instance; STATUS ------------ OPEN OPEN |
1 2 3 4 5 6 7 8 9 10 11 | [Primary -1] SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 107 1 57 2 |
1 2 3 4 5 6 7 8 9 10 11 | [Primary -2] SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 107 1 58 2 |
1 2 3 4 5 6 7 | [Primary -1] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 107 1 58 2 |
1 2 3 4 5 6 7 | [Standby-1] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 107 1 58 2 |
1 2 3 4 5 6 7 | [Standby-2] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; MAX(SEQUENCE#) THREAD# -------------- ---------- 107 1 58 2 |
Step2:
Set the relevant parameter at session level with a user other than SYS. If you try it with SYS you will receive the below error.
ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [Standby -1] SQL> conn test/test Connected. SQL> show user USER is "TEST" SQL> alter session set standby_max_data_delay=5; Session altered. SQL> select * from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED DATUM_TIME ---------------------- ------------------------------ ------------------------------ -------------------- -------------------- transport lag +00 00:00:00 day(2) to second(0) interval 01/04/2017 10:27:03 01/04/2017 10:27:02 apply lag +00 00:00:00 day(2) to second(0) interval 01/04/2017 10:27:03 01/04/2017 10:27:02 apply finish time +00 00:00:00.000 day(2) to second(3) interval 01/04/2017 10:27:03 estimated startup time 31 second 01/04/2017 10:27:03 |
Step3:
Stop RECOVER so that LAG can occur.
1 2 3 4 | [Standby -1] SQL> alter database recover managed standby database cancel; Database altered. |
Step4:
Perform a delete operation on the primary side.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | [Primary-1] SQL> select * from test.countries_yedek; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> delete test.countries_yedek where country_name='Zimbabwe'; 1 row deleted. SQL> commit; Commit complete. |
Step5:
Check if the delete is done in the relevant table on the standby side.
1 2 3 4 5 6 7 | [Standby-1] SQL> select * from test.countries_yedek; select * from test.countries_yedek * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-03172: STANDBY_MAX_DATA_DELAY of 5 seconds exceeded |
Step6:
Check the duration of the LAG to see if the system is working properly.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [Standby-1] SQL> conn sys/Passw0rd4 as sysdba Connected. SQL> show user USER is "SYS" SQL> column name format a22 SQL> column value format a15 SQL> column value format a30 SQL> column datum_time format a20 SQL> column time_computed format a20 SQL> set linesize 9000 SQL> select * from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED DATUM_TIME ---------------------- ------------------------------ ------------------------------ -------------------- -------------------- transport lag +00 00:00:00 day(2) to second(0) interval 01/04/2017 10:32:04 01/04/2017 10:32:04 apply lag +00 00:02:57 day(2) to second(0) interval 01/04/2017 10:32:04 01/04/2017 10:32:04 apply finish time +00 00:00:00.025 day(2) to second(3) interval 01/04/2017 10:32:04 estimated startup time 31 second 01/04/2017 10:32:04 |
Step7:
Reconnect and query with the user.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | [Standby-1] SQL> conn test/test Connected. SQL> select * from test.countries_yedek; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. |
Step8:
Start the RECOVER process, and query the table again.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | [Standby-1] SQL> conn sys/Passw0rd4 as sysdba Connected. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> conn test/test Connected. SQL> select * from test.countries_yedek; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 24 rows selected. |
Now every thins is OK!
Step9:
If we want, we can make this process permanent with AFTER LOGON TRIGGER.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [Primary-1] SQL> conn test/test Connected. SQL> show user USER is "TEST" SQL> CREATE OR REPLACE TRIGGER sla_logon_trigger 2 AFTER LOGON 3 ON TEST.SCHEMA 4 BEGIN 5 IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') 6 IN ('PHYSICAL STANDBY')) 7 THEN execute immediate 8 'alter session set standby_max_data_delay=5'; 9 END IF; 10 END; 11 / Trigger created |
Step10:
Stop RECOVER so that LAG can occur.
1 2 3 4 | [Standby-1] SQL> alter database recover managed standby database cancel; Database altered. |
Step11:
Try to log in with the user.
1 2 3 4 5 | [Standby-1] SQL> conn test/test ERROR: ORA-03172: STANDBY_MAX_DATA_DELAY of 5 seconds exceeded [oracle@standby1 ~]$ |
Step12:
Check whether other users have problems.
1 2 3 | [Standby-1] SQL> conn standby/standby Connected. |
