Every company has (hopefully!) a disaster recovery plan for their production data. The most common protection for production databases, whether business critical or not, is to set up real-time data replicas to provide complete, usable copies that can take over the production workload in case of local or regional failure.

However, disaster recovery and business continuity investments increase IT expenditure, so optimizing the assets to provide as much value as possible becomes strategic. In other words, it becomes compelling to use the standby databases not just for “passive” data protection but as an active component of the application architecture.

To answer this requirement, most database vendors provide read-only access to the standby databases. If the applications are architected right, they can offload the read-only workload to the standby database. And, should the need arise, scale out to a farm of standby databases.

Despite that, scaling out read-only workloads is not an easy task. It requires proper architecture, dedicated data sources, or applications that are read-only by nature (e.g., reporting applications). 

In this context, Oracle Active Data Guard has stood out from the competition for many reasons. It provides extraordinarily scalable and performant read-only replicas, and applications can profit from causal consistency (also known as external consistency), sequence and temporary table support, and in-memory columnar store, to mention a few.

Oracle DML redirection lets applications manipulate data from the standby database.

Since Oracle Database 19c, Oracle Active Data Guard further crushes the competitors by letting the applications manipulate data (DML statements) while connected to the standby database. Under the hood, Oracle DML redirection redirects the DMLs to the primary database. The transactions appear to be executed on the standby database but are executed on the primary database. DML redirection guarantees ACID compliance: while the transaction is active, only the session on the standby database can see the modified data until it commits the transaction.

As shown in the image below:

1. A session connected to the standby database issues the DML statement
2. The DML is redirected to the primary database using a database link transparently created by DML redirection
3. The DML is applied to the primary database
4. The change is streamed back to the standby database with the normal Data Guard replication
5. The result of the DML is visible in the privacy of the transaction started by the session
6. Once the session commits the transaction, the data will be visible to the other sessions on the primary and standby databases.  

DML redirection is not limited to DML statements. It can also redirect PL/SQL calls while keeping the advantages of using global temporary tables and sequences on the standby database for read-only transactions.

DML redirection increases the standby database usage and, ultimately, the return on investment.

Why is DML redirection a game-changer?

In the past, applications running significant read workloads but requiring to write some data had to connect to the primary service. Today, the same applications can connect exclusively to the standby database service, offload all the reads, and effectively run only the writing part of a transaction to the primary database. That implicitly removes the complexity required to architect applications with multiple data sources.

How much of the write workload can be redirected from the standby to the primary database?

• In 19c, Oracle recommends keeping the redirected DMLs lower than 10% of the workload (the lower, the better).
• Further optimizations have been made in 21c to improve the transaction elapsed time perceived by the standby session by an order of magnitude. This allows running workloads with a higher write ratio (80% reads, 20% writes) on the standby database. As always, the final application performance and benefits for the primary database vary with the application workload and architecture, so the application must be adequately tested to evaluate the impact of offloading it to the standby database.

In general, whatever application (or application module or microservice) predominantly reads is an excellent candidate to connect directly to the standby database and leverage DML redirection for the occasional writes.

Many customers, including leading financial institutions, successfully leverage DML redirection to run reporting applications, microservices, and other applications against the standby databases. Try it on-premises or on Oracle Cloud Infrastructure (Exadata Database or Base Database Services) today!