Master Data Mover — Oracle GoldenGate (Part 2 of 2)
In Part 1 of this series, we had a quick introduction of Oracle GoldenGate as a Master of all Trades tool in terms of being a true Enterprise Data Integration software.
In this article, Part 2, I am going to cover one of the most industry relevant use-case of Oracle GoldenGate.
— — — — —
High Availability and Disaster Recovery requirements have been conventionally fulfilled by setting up Cold/Warm Standby configurations of database and application tiers at remote geographic locations. This is as part of Business Continuity Plan or sometimes a regulatory requirement. Any failover or switchover (during a planned drill) leads to a finite amount of time going into getting the overall application operational from the remote site. More or less the same story when switching back to the main hosting site.
While the above, if well-architected, does more or less help to achieve the Recovery Point Objective (RPO), the Recovery Time Objective (RTO) still remains a bone of contention.
Enterprise customers have a wish and aspiration if *something* can be done around reducing or possibly eliminating the RTO component from this failover/switchover which is a non-negotiable part of application lifecycle.
So, here I present the options to build a more RTO friendly Architecture at Database Tier using Oracle GoldenGate.
Active-Active Architecture using Oracle GoldenGate
Two practical choices available:
1] Live Standby- 2-sites (read 2 distinct databases) running wherein Write transactions happen on one of the sites only. Other site remains in Live standby mode catering to the Read transactions. As and when required, the application tier can be simply re-pointed to start sending Write transactions to the Live Standby database. This helps to reduce planned or un-planned downtimes.
Benefits: Conflict Detection and Resolution (CDR) does not need to be factored in and thus simpler to implement.
2] Active-Active- 2 or 3 sites’ databases support this multi-master topology wherein Write transactions can be supported by databases at one or more sites and others support Read transactions. These are designed to eliminate downtimes for planned or unplanned outages. Downtime would simply depend on reconnect time.
Benefits: Higher Availability, Load Distribution, Better Resource Utilization
Challenges: Conflict Detection and Resolution (CDR) needs to be factored in due to multi-site Write transactions.
RPO & RTO Considerations:
Having Oracle GoldenGate (OGG) alone does handle the Recovery Time Objective (RTO) well. When requirement is to support very aggressive Recovery Point Objective (RPO), it is recommended to take the approach of supplementing the OGG based architecture by deploying Oracle (Active) Data Guard.
With Real Application Cluster (RAC) running at each site, there is already local High Availability covering instance/node level failures.
OGG + Active Data Guard are part of Oracle’s Maximum Availability Architecture GOLD & PLATINUM tiers helps achieving availability, scale & performance across the sites. Active Data Guard helps achieving the zero* data loss in case of loss of a database.
*- depending on the mode in which Data Guard replication has been configured
OGG Replication Design Considerations:
Ø Intelligent/logical connection routing:
o Split connections by user-name / location
o Routers / Load Balancers with IP Session Persistence
o Banking applications can route traffic using Even & Odd accounts
o Stock Trading kind of applications can route traffic using name patterns. E.g. A-M & N-Z
o Ordering kind of applications can route traffic using customer first/last name initials
Ø Preventing Duplicate Keys
o Generation of primary keys uniquely across all active databases
o Sequences to be generated with odd/even or N+1 approach
Ø DDL (Alter category) should be applied to one server at a time
Ø Avoid automatic name generation, system object names, interval partitioning
Ø Avoid running batches on multiple sites at the same time
Ø Avoid dependent transactions (Txn#2 depends on the outcome of Txn#1)
Ø Avoid using certain data types like ROWID, BFILE, REF and getting into scenarios where difference in 2 rows is only the column of data type LOB, Nested table, Array etc.
Dealing with Conflicts & Resolutions:
Majorly, there are 2 approaches to handle conflicts manually.
1. Timestamp Based Resolution
o Timestamps should be from a single timezone (UTC)
o Highest precision datatype is best (TIMESTAMP with timezone (UTC) better than DATE)
o Common column name (i.e. LAST_UPDATED_DATE)
2. Trusted Source
o Have some identifier that keeps track of where the record was last updated by a user.
- Auto CDR* Feature of OGG (introduced in 12.2) works with no visible changes to the application. Invisible timestamp columns automatically created and maintained by database kernel. This alleviates lot of developmental headaches in dealing with the transactional conflicts.
There are tremendous benefits to improve database performance, response times, and availability and to achieve significant scalability gains by implementing an active-active replication environment.
Oracle GoldenGate is uniquely designed to support robust conflict detection and resolution processes to optimize data accuracy and integrity in active-active replication configurations.
Important Links:
Oracle GoldenGate Blogs
https://blogs.oracle.com/dataintegration/goldengate-solutions-and-news
https://blogs.oracle.com/dataintegration/goldengate-technical-features/page/3
Oracle GoldenGate Introduction & Getting Started
Oracle GoldenGate Microservices Architecture in 19c
Oracle GoldenGate Auto CDR
Best Practices for Conflict Detection and Resolution in Active-Active Replication Environments Using Oracle GoldenGate
https://www.oracle.com/technetwork/middleware/gg-activeactive-11-2009-159996.pdf
Disclaimer: I am currently an employee of Oracle Corporation. All views expressed in this article are in my personal capacity and do not necessarily reflect my employer’s views.
