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. |
