Oracle DBA

Overview

Oracle Database 19c introduces the capability to execute Data Manipulation Language (DML) operations directly on an Active Data Guard standby database. This feature allows applications to benefit from using Active Data Guard for standby operations that require occasional writes, seamlessly redirecting DML operations to the primary database.

How DML Redirection Works:

When a DML operation is issued on the Active Data Guard standby database, it is transparently redirected to the primary database, where it is executed. The transaction’s redo data is then sent back to the standby database to keep it synchronized. This feature helps balance the load between primary and standby databases, particularly useful for applications requiring read access with minimal writes. The standby database session waits until changes are applied before returning control to the application, ensuring consistency.

Practical Environment Setup:

Primary and Standby Databases: Running Oracle 19c with Data Guard in Maximum Availability mode.

Configuration Details:

Primary Database

SQL> SELECT status, instance_name, database_role, protection_mode FROM v$database, v$instance;

STATUS    INSTANCE_NAME   DATABASE_ROLE  PROTECTION_MODE
--------- --------------- -------------- -----------------------
OPEN      primary_db      PRIMARY        MAXIMUM AVAILABILITY

Standby Database:

SQL> SELECT status, instance_name, database_role, protection_mode FROM v$database, v$instance;

STATUS    INSTANCE_NAME   DATABASE_ROLE  PROTECTION_MODE
--------- --------------- -------------- -----------------------
OPEN      standby_db      PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE
---------------- ----------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

Process Workflow:

1. DML operation is issued on the standby database.
2. DML is redirected to the primary database.
3. Data changes are applied on the primary database.
4. Updates are sent back to the standby database.
5. Changes become visible to the client application on the standby.

Configuration: Enabling adg_redirect_dml Parameter

Enable the adg_redirect_dml parameter on both the primary and standby databases to allow DML redirection.

Primary Database

[oracle@primary_db admin]$ export ORACLE_SID=primary_db
[oracle@primary_db admin]$ sqlplus / as sysdba
SQL> SHOW PARAMETER adg_redirect_dml;
NAME                TYPE    VALUE
adg_redirect_dml    boolean FALSE

SQL> ALTER SYSTEM SET adg_redirect_dml=TRUE SCOPE=BOTH;

System altered.

Standby Database

[oracle@standby_db admin]$ export ORACLE_SID=standby_db
[oracle@standby_db admin]$ sqlplus / as sysdba
SQL> SHOW PARAMETER adg_redirect_dml;
NAME                TYPE    VALUE
adg_redirect_dml    boolean FALSE

SQL> ALTER SYSTEM SET adg_redirect_dml=TRUE SCOPE=BOTH;

System altered.

Testing DML Redirection

1. On the Primary Database:

SQL> CREATE TABLE demo_table (name VARCHAR2(10), entry_date TIMESTAMP);
Table created.

SQL> INSERT INTO demo_table VALUES ('user1', SYSDATE);
1 row created.

SQL> COMMIT;
Commit complete.

2. On the Standby Database:

SQL> SELECT * FROM demo_table;

NAME   ENTRY_DATE
------ -------------------------------
user1  19-OCT-19 01.51.17.000000 AM

3. DML Operation on Standby:

SQL> DELETE FROM demo_table WHERE name = 'user1';

1 row deleted.

SQL> COMMIT;

Commit complete.

4. Result on Primary Database:

SQL> SELECT * FROM demo_table;
No rows selected

Note: Limit DML operations on the standby to avoid overloading the primary database, as extensive DML activity can impact primary database performance. Also, DML operations in Oracle XA transactions are not supported on Active Data Guard standby databases.