DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle AI Database 26ai(23.26.2)— DML Redirection for Data Guard per Pluggable Database

#oracle #ai #devops #linux

**Oracle AI Database 26ai **introduces DML Redirection for Data Guard per Pluggable Database, allowing write operations to be transparently redirected from a standby PDB to its corresponding primary PDB.

With this enhancement, a standby PDB is no longer limited to serving read-only workloads. Applications can perform both read and write operations while connected to the standby PDB, with write requests automatically executed on the primary PDB. This makes the standby environment a more valuable resource by offloading additional application activity and increasing the flexibility of disaster recovery environments.

Let’s verify this feature with a simple test.

Step 1 — Verify the Standby PDB

Connect to the standby database (CDB2) and verify that the target PDB (AMOL) is opened in READ ONLY mode.

[oracle@OEL9-DB2 ~]$ sqlplus /@cdb2 as sysdba

SQL*Plus: Release 23.26.2.0.0 - Production on Tue Jul 14 13:29:04 2026
Version 23.26.2.0.0

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.0.0 - Production
Version 23.26.2.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 BABOL                          READ WRITE NO
         4 AMOL                           READ ONLY  NO
Enter fullscreen mode Exit fullscreen mode

Also verify that the database is operating as a Physical Standby.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
Enter fullscreen mode Exit fullscreen mode

At this point, the standby database is open for read access while redo apply remains active.

Step 2 — Enable DML Redirection

Enable DML redirection on the standby database.

SQL> alter system set adg_redirect_dml=true;

System altered.
Enter fullscreen mode Exit fullscreen mode

This parameter enables Oracle to transparently redirect write operations issued against the standby PDB to its corresponding primary PDB.

Step 3 — Execute a DML Statement on the Standby

Insert a row while connected to the standby PDB.

SQL> alter session set container=AMOL;
Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 AMOL                           READ ONLY  NO
SQL> insert into tbl values(1,'Vahid Yousefzadeh');

1 row created.

SQL> commit;

Commit complete.
Enter fullscreen mode Exit fullscreen mode

Notice that the INSERT and COMMIT complete successfully despite the standby PDB being opened in READ ONLY mode. The write operation is transparently redirected to the primary PDB.

Step 4 — Verify the Result on the Primary

Connect to the primary database (CDB1) and switch to the AMOL PDB.

--primary(source) CDB1

SQL> alter session set container=amol;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 AMOL                           READ WRITE NO

        ID FULLNAME
---------- --------------------
         1 Vahid Yousefzadeh
Enter fullscreen mode Exit fullscreen mode

The inserted row is present in the primary PDB, confirming that the DML statement executed from the standby PDB was successfully redirected.

Top comments (0)