Day 12: Data Guard

December 23, 2020

A large portion of Oracle customers rely on Data Guard as their disaster recovery solution. With Data Guard, customers can achieve near real-time replication of the database, from the primary to the standby. Now, with Oracle Database 19c, even more innovative features have been added to Data Guard.

Active Data Guard DML Redirection

Industry experts claim that Active Data Guard-DML Redirection is the best new feature, introduced in Oracle Database 19c. ADG DML redirection allows data manipulation language (DML), in the form of inserts, updates, and deletes to be executed against the standby database. Incidental DML operations can be run on Active Data Guard standby databases. This allows more applications to benefit from using an Active Data Guard standby database, when some writes are required. On execution the DML operation is passed to the primary database.

DML redirection helps in load balancing between the primary and standby databases. When an incidental DML is issued on an Active Data Guard standby database, the update is passed to the primary database where it is executed. The resulting redo of the transaction updates the standby database, after which control is returned to the application.

DML operations on a standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks. The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby. DML redirection on the standby database requires the Active Directory license; and the COMPATIBLE database initialization parameter must be set to 19.0.0 or higher. The database must be in open mode with archive logs being applied on the standby database, flashback must be enabled as a prerequisite as well. Below are the steps that you can execute to effectively leverage the ADG DML redirection feature:

On the primary database, set the ADG_REDIRECT_DML parameter.

select open_mode, database_role, protection_mode, flashback_on from v$database;
If flashback is not turned on, we need to enable flashback alter database flashback on;
select open_mode, database_role, protection_mode, flashback_on from v$database;
show parameter adg_redirect_dml;
alter system set adg_redirect_dml=true scope=both; show parameter adg_redirect_dml;

On the ADG standby database, set the ADG_REDIRECT_DML parameter.

To receive the full article, please complete the form provided.  A pdf will be automatically emailed to you, so be sure to check your spam/junk mail if you do not see it.

    Full Name (required)

    Your Email (required)

    Company (required)

    Phone number (required)

    Job Title (required)