Oracle STANDBY_MAX_DATA_DELAY Parameter

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;

STANDBY_MAX_DATA_DELAY Example

Lest test it;

Step1:

Check if the Data Guard is working healthy on Primary and Standby.

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.

Step3:

Stop RECOVER so that LAG can occur.

Step4:

Perform a delete operation on the primary side.

Step5:

Check if the delete is done in the relevant table on the standby side.

Step6:

Check the duration of the LAG to see if the system is working properly.

Step7:

Reconnect and query with the user.

Step8:

Start the RECOVER process, and query the table again.

Now every thins is OK!

Step9:

If we want, we can make this process permanent with AFTER LOGON TRIGGER.

Step10:

Stop RECOVER so that LAG can occur.

Step11:

Try to log in with the user.

Step12:

Check whether other users have problems.