Standby Max Data Delay

Onur ARDAHANLI

3 years ago

In today’s article, we will talk about Standby Max Data Delay.

While some of the reporting users who make inquiries from Active Data Guard expect the retrieved data to be the same as the Production at that moment, others do not care if LAG has been formed for a few seconds.

The time until this LAG in between is not important to them. But they do not want the result of such a query for higher. For such cases, ORACLE has created the STANDBY_MAX_DATA_DELAY parameter, which will be valid at the session level for the Standby side. This parameter takes seconds.

Use of:

ALTER SESSION SET STANDBY_MAX_DATA_DELAY = {INTEGER|NONE}

1	ALTER SESSION SET STANDBY_MAX_DATA_DELAY = {INTEGER\|NONE}

Let’s test it.

1. We check whether the Data Guard is working properly on the Primary and Standby sides.

[Primary -1]

SQL> select status from gv$instance;

STATUS
------------
OPEN
OPEN

SQL> select status from gv$instance;

STATUS

------------

OPEN

[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

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

[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

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

[Primary -1]

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           107          1
            58          2

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#) THREAD#

-------------- ----------

107 1

58 2

[Standby-1]

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           107          1
            58          2

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#) THREAD#

-------------- ----------

107 1

58 2

[Standby-2]

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#)    THREAD#
-------------- ----------
           107          1
            58          2

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

MAX(SEQUENCE#) THREAD#

-------------- ----------

107 1

58 2

2. We set the relevant parameter at session level to a user other than SYS. [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

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

3.We stop the RECOVER process so that LAG can occur. [Standby -1]

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

4.We do a delete operation on the primary side. [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.

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.

5. On the standby side, we make a query to see if the delete operation has taken place on the relevant table. [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

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

6.We learn the duration of the LAG to see that the system is working correctly. [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

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

7. Since the user’s session is broken, we reconnect and query. [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.

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.

8.We initiate the RECOVER operation and query. [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.

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.

9.Everything is Ok.

10. If I want, I can make this process permanent with an AFTER LOGON TRIGGER.

[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

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

11. RECOVER is stopped so that LAG can occur. [Standby-1]

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

12. The user tries to login. [Standby-1]

SQL> conn test/test
ERROR:
ORA-03172: STANDBY_MAX_DATA_DELAY of 5 seconds exceeded
[oracle@standby1 ~]$

SQL> conn test/test

ERROR:

ORA-03172: STANDBY_MAX_DATA_DELAY of 5 seconds exceeded

[oracle@standby1 ~]$

13. We check if other users have problems. [Standby-1]

SQL> conn standby/standby
Connected.

1 2	SQL> conn standby/standby Connected.