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:

Prerequisites

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.
3. Primary-Standby Configuration: Ensure the primary and standby databases are properly synchronized.

Steps to Configure DML Redirection

1. Enable Active Data Guard: Ensure the standby database is in read-only mode with Active Data Guard enabled:

SQL> ALTER DATABASE OPEN READ ONLY;

Enable DML Redirection: On the standby database, set the ADG_REDIRECT_DML initialization parameter to TRUE:

SQL> ALTER SYSTEM SET ADG_REDIRECT_DML = TRUE;

Verify Configuration: Check the parameter value to confirm:

SQL> SHOW PARAMETER ADG_REDIRECT_DML;

Perform DML on the Standby Database:

• Connect to the standby database and execute DML statements as usual.
• Oracle redirects the DML operations to the primary database transparently.
• Changes are applied on the primary and then propagated to the standby database via redo logs.

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

• V$ACTIVE_SESSION_HISTORY: Look for sessions with the operation type REDIRECTED_DML.
• 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.