Read replicas in Oracle on premise — Oracle ADG with DML re-direct
With the introduction of distributed databases came the concept of replicas. Cloud popularized it more and more …. this further lead to some request for replicas on premise systems as well (called as Oracle Data Replication). Objective was to offload the reporting and optional applications from the primary database server. We went through a few possible options —
https://hevodata.com/learn/oracle-database-replication/
https://www.striim.com/blog/oracle-database-replication-methods/
https://bryteflow.com/oracle-replication-in-real-time-step-by-step/
http://www.dba-oracle.com/oracle_tips_multi_master_replication_streams.htm
and in this post we’ll talk about Oracle Active Data Guard with DML re-direct (wherein you can have replicas which support ad-hoc write requests as well).
What is Oracle data guard?
Oracle Data guard is an extension of Oracle RDBMS which aids in maintaining secondary standby databases as supplementary repositories to primary datastores. Data Guard supports two types of standbys — physical standby (redo apply) and logical standby (sql apply) sites.
Limitations of Oracle Data guard (Why Oracle active data guard)
Data guard cannot be used as a read replica because if you want it open read-only, you must pause the sync process (debatable).
To overcome this limitation Oracle came up with Active Data Guard. With ADG we could open the Standby Database in read-only mode while Oracle replicated the redo from the master. This helped the architecture in -
> Reducing the load on the primary
> Ensuring that the load on the primary is consistent
> Enhanced utilization of the standby resources
What is Oracle active data guard?
Oracle Active Data Guard is an extension to Data guard. Though it’s main purpose is to prevent data loss and avoid downtime it can also be used as a physical replica to offload the traffic from the primary for purposes such as reporting.
Note that Oracle ADG has an additional license cost to it. It allows read-only access on the physical standby node at the same time as applying archived transactions from the primary node. It prevents data loss and downtime in the simplest and most economical manner by maintaining a synchronized physical replica of a production database at a remote location. If the production database is unavailable for any reason, client connections can quickly, and in some configurations transparently, failover to the synchronized replica to restore service.
Limitations of Active Data guard
The biggest problem with the read replica is that sometime read heavy applications still need to write or update a small table. Say Reporting tools often need to log if a report has failed or how much time it took. And the problem was not obvious- For example, even simple SQL such as <sequence>.nextval is not supported as this requires a dictionary update.
Prior to Oracle 19c we would create a DB links and that would solve the issue. Though that is also not without it’s own set of problems (say db links require production password).
Refer for more details — Using Active Data Guard Reporting with Oracle E-Business Suite Release 12.2 and an Oracle 11g or 12c Database (Doc ID 1944539.1)
What is ADG DML re-direction?
With Oracle 19c ADG comes with a DML re-direct feature making life happier. DML enables the Data Manipulation Language (DML) operations on the standby server to be redirected and run on the production (Primary) database.
The DML Redirection process can be broken down into 5 steps:
> Client issues a DML on the Standby
> Standby captures the DML and pushes it to the primary database using an internal db-link
> Primary executes the DML (which generates redo)
> This redo is a normal redo stream and together with the normal redo stream this is sent to the standby database
> Standby database applies the received redo stream and releases the lock on the session so the session can see the result.
Voila you have limited write replica available (keep in mind that writes through standby are slower)
What is the cost of Active Data Guard?
Active Data Guard carries a list price of $11,500 USD, and both the primary and standby databases must be licensed if they reside on different physical servers.
Pending (Performance statistics for sharing load between Primary and Secondary)
