Recovery for Global Consistency in an Oracle Distributed Database Environment
(Doc ID 1096993.1)
Last updated on MARCH 03, 2020
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 18.104.22.168 [Release 10.2 to 11.2]
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious
data from the Oracle sample schema(s),Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example
to describe the procedure:
Procedure = dist_qry_hb
Job = distrib_heartbeat
The goal of this document is to describe a methodology for recovering multiple Oracle databases involved to a consistent state with regards to local and distributed database transactions. The participating databases may be involved in distributed or remote transactions or can be completely independent but are required to be “synchronized” for application consistency. Siebel, Peoplesoft, SAP, and other custom applications that include multiple databases are real world examples that may require this global consistency across multiple databases.
The requirements are the following:
- All participating database servers need archivelog mode enabled, flashback database enabled and have valid database backups or standby databases
- A heartbeat query or update needs to be deployed to synchronize the database clocks or System Change Number (SCN). In the example below, we create a simple table in all the participating databases and the table is accessed via a database link.
- All participating databases are Oracle databases (e.g. not mySQL, TimesTen, Hyperion)
The mechanism outlined will allow distributed databases to maintain greater global consistency by using a “heartbeat transaction” to keep database System Change Numbers (SCN) of all the databases in sync. If the need for point-in-time recovery arises, all databases involved in the distributed transactions can be recovered (e.g. “RMAN recover until scn”, “flashback database to scn” or “recover database until change”).
The common use cases are:
- Disaster recovery or point-in-time recovery where one or multiple of databases have lost data
- Environment cloning for testing
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|Methodology of Heartbeat Transaction|
|Point-in-time Recovery in this environment|
|Example 1: One database incurred data loss|
|Example 2: Subset of the databases incurred data loss|
|Example 3: All database incurred data loss or all databases were restored from backup|