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

By -

 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

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

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.

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.

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

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


Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 21c — Setting Compression Attribute for Indexes During Import

By -
  In Oracle 12cR1, a new feature was introduced that allowed us to enable or disable the   compression   property for tables during a dump import. This was done using the   TRANSFORM   parameter in the   impdp   command: SQL * Plus: Release 12.2 .0 .1 .0 Production SQL > select compression from dba_tables where table_name = 'TBL1' ; COMPRESS -------- DISABLED SQL > drop table usef.tbl1; Table dropped. [oracle @oshost ~ ]$ impdp usef / a directory = drm dumpfile = test.dmp tables = usef.tbl1 TRANSFORM = TABLE_COMPRESSION_CLAUSE:\"ROW STORE COMPRESS ADVANCED\" SQL > select compression,COMPRESS_FOR from dba_tables where table_name = 'TBL1' ; COMPRESS COMPRESS_FOR -------- ------------------------------ ENABLED ADVANCED This feature was only available at the  table level , and there was no way to control index compression during import (in Oracle 12c). However, in  Oracle 21c , as a new feature, you can now enable (...
Read more

Automatic Service Failback to Preferred Instance in Oracle RAC 19c

By -
  As you know, in a RAC environment when creating a service, you can define   preferred instances   and   available instances . In this case, the service will by default run on the preferred instance. If for any reason the preferred instance fails, or the entire node becomes unavailable, the service will be moved to the available instance. Even after restarting the preferred instance, the service will not automatically fail back; it will continue running on the available instance. To move it back to the preferred instance, you need to manually perform a  relocate  operation. Example: In this example, we create a service named  mysrv  in Oracle 11g: [ grid@node1 ~ ]$ crsctl query crs releaseversion Oracle High Availability Services release version on the local node is [ 11.2 .0 .4 .0 ] [ oracle@node1 ~ ]$ srvctl add service -d mydb -s mysrv -r instance1 -a instance2 After creating the service, start it. You will see that the service is runnin...
Read more