Breaking the Read-Only Barrier: How to Run DML on Oracle Active Data Guard

                                                   ADG_REDIRECT_DML

Introduction

Historically, Active Data Guard (ADG) was strictly read-only. If an application needed to perform even a tiny "Insert into Audit_Log," it had to connect to the Primary.

The DML Redirection feature marks a significant shift in ADG capabilities. 

ADG_REDIRECT_DML allows the standby to accept DML, transparently redirect it to the Primary, and wait for the apply service to bring the change back. This functionality effectively eliminates the 'look-but-don't-touch' limitation, providing a seamless experience for read-mostly applications that require occasional data persistence.

Prerequisites & Environment

• Oracle Database Version: 19c or higher.

• License: Active Data Guard option.

• Mode: Standby must be in READ ONLY WITH APPLY (Active Data Guard).

Primary Database Server : 

Physical standby Database server: READ ONLY WITH APPLY

                 How it Works (The "Magic" Behind the Scenes)

1. DML is issued on the Standby.

2. The Standby passes the metadata/DML to the Primary.

3. Primary executes the DML and generates redo.

4. Redo is shipped back to the Standby and applied.

5. The Standby session completes the commit.

Step-by-Step Configuration

Setting up the Test Environment on the Primary: To demonstrate DML redirection, we first need to create a test user and a sample table on the Primary database.

 Standby :

If you attempt to perform a DML operation on the standby database without further configuration, Oracle will block the transaction with the following error: ORA-16000: database or pluggable database open for read-only access.

To resolve this and enable transparent redirection, update the following initialization parameter on the Standby Database:

 ALTER SYSTEM SET ADG_REDIRECT_DML=TRUE SCOPE=BOTH;

Validating the End-to-End Flow: After enabling ADG_REDIRECT_DML, the standby no longer throws ORA-16000. Instead, the write is transparently proxied to the Primary database. Once the transaction commits, the changes are propagated back to the standby through the standard redo transport services, ensuring data consistency across the environment.

Validate from Primary Database :

Important Limitation: Administrative User Restrictions

During testing, I observed that DML Redirection does not apply to administrative accounts. If you attempt to perform DML on the standby while connected as SYS or SYSTEM, the database will return ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed. This confirms that the feature is intended for application-level schemas rather than administrative maintenance tasks.

Update :

 Key Limitations 

• It is not intended for high-volume write workloads (due to network latency).

• PL/SQL blocks containing DML are handled differently (need ADG_REDIRECT_PLSQL).

• Parallel DML is not supported for redirection.