Subscribe YouTube Channel For More Live Tutorials

DML Redirection in Oracle Dataguard 19c

DML Redirection in Oracle Data Guard 19c enables DML operations to be executed on Active Data Guard standby databases. This feature is useful when you need to perform write operations on a standby database without redirecting clients back to the primary database.

Here are the steps to configure and use DML Redirection in Oracle 19c:

  1. Active Data Guard: Ensure that the Active Data Guard license is enabled.
  2. Database Version: Confirm that the database is running Oracle Database 19c or later.

Steps to Configure DML Redirection

SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER SYSTEM SET ADG_REDIRECT_DML = TRUE;

Verify Configuration: Check the parameter value to confirm:

SQL> SHOW PARAMETER ADG_REDIRECT_DML;
  • Connect to the standby database and execute DML statements as usual.
  • Oracle redirects the DML operations to the primary database transparently.

Monitor Redirection: Use the following views to monitor the redirection activity:

  • V$SESSION: Identify redirected sessions.

Restrictions:

  • Cannot perform DDL or DCL operations on standby.
  • Certain operations (e.g., sequences and global temporary tables) may not behave identically.

Disable DML Redirection

If you no longer need DML redirection, you can disable it:

SQL> ALTER SYSTEM SET ADG_REDIRECT_DML = FALSE;

This configuration provides a seamless way to enable write capabilities on standby databases while leveraging the Data Guard environment.