Best Practices from Oracle Development's A‑Team

Oracle GoldenGate Best Practices: Oracle Migrations and Upgrades 9i and up


Eliminating database downtime poses a significant challenge for IT organizations that need to upgrade or migrate mission-critical database environments running Oracle Database 9i or Oracle Database 10g to Oracle Database 11g. This is particularly true for applications that must provide continuous or near-continuous operations to users who increasingly expect uninterrupted availability of online services. Any outage of an application or website, even if that outage is scheduled or planned, has an impact on the revenue and reputation of the business.

For databases that host the data for these mission-critical applications, availability requirements have become stringent. Unfortunately, there are essential events that require application downtime, including modifying hardware or database software, upgrading applications, applying software patches, and migrating to different computing architectures. Because such events are not considered a system or data failure, they are aptly classified as planned outages.

This document explains how organizations can upgrade or migrate from Oracle Database 9i or Oracle Database 10g to Oracle Database 11g with minimum downtime. Using Oracle GoldenGate’s real-time data integration and replication capabilities, businesses can create a clone database to offload instantiation and conversions, keep transactions in sync across the databases, manage partial or phased migrations and upgrades, conduct post upgrade/ migration data verification and implement a reliable failback strategy.

Main Article

This document covers Oracle GoldenGate’s best practices and guidelines to be followed while using Oracle GoldenGate for Oracle database upgrades and migrations. This document is intended for Oracle Database Administrators (DBAs) and Oracle Developers with some basic knowledge of the Oracle GoldenGate software product. This document is intended to be a supplement to the existing series of documentation available from Oracle.

The following assumptions have been made during the writing of this document:

  • The reader has basic knowledge of Oracle GoldenGate products and concepts
  • Oracle GoldenGate version 10 and above
  • Oracle Database version 9.2 and above
  • All Oracle GoldenGate supported platforms for Oracle

Concepts and Terminology

To help comprehend this use case, key concepts and terms are outlined in the following subsections.

Source and Target

Throughout this document, the production database is referred to as the source and the secondary copy as the target database

Oracle GoldenGate

Oracle GoldenGate is a real-time change data capture application that provides guaranteed data capture, routing, transformation, and delivery across homogeneous as wells as heterogeneous business systems. Oracle GoldenGate uses a low-overhead architecture to capture transactions non-intrusively from a source database by reading online transaction logs, transforming the data when needed, and applying those with guaranteed integrity to a target database in real time. Oracle GoldenGate’s processes run continuously, even bidirectionally, and support high-volume, rapidly changing environments, moving thousands of transactions per second with very low impact. The target database is a transactional replica at a logical level, which can be leveraged for multiple applications.

Database Upgrade/Migration Options

A database upgrade advances the Oracle Database software release number from one version to another. There are two primary ways to perform an upgrade.

In-place upgrade

An in-place upgrade renders the database inaccessible for business applications while the database software is being upgraded. This procedure entails running an upgrade script, recompiling invalid PL/SQL and downtime that is usually not acceptable in most mission-critical environments (This white paper does not address in-place upgrades.)

Rolling upgrade

The term rolling upgrade refers to upgrading different instances of the same database, such as in an Oracle Real Application Clusters (Oracle RAC) environment, one at a time, without stopping the database (This paper does not address Rolling upgrades.).

Database Migration

Moving an Oracle database across different operating systems is a common requirement in many computing environments. A migration enables the underlying operating system or hardware platform to be changed. In Oracle, on-disk file formats are not homogeneous across platforms. Under Oracle 10g compatibility, the on-disk structures for platforms that appear in v$TRANSPORTABLE_PLATFORM are identical, but the endian format could differ.

Zero or Minimum Downtime upgrade or migration

Using Oracle GoldenGate in conjunction with Oracle Database features, an upgrade or migration can be performed without any significant application downtime.  Using the methods outline in this document the application outage can be limited to the time it takes to refocus the application on the new upgrade environment.  Using its real-time heterogeneous data movement technology, Oracle GoldenGate imposes negligible database downtime for upgrades or migrations from Oracle Database 9i or Oracle Database 10g to Oracle Database 11g.

An upgrade/migration consists of the following high-level steps:

  1. The application points to the source or production database running software version V-OLD.
  2. A target or secondary logical database copy is constructed running software version V-OLD.
  3. The target database copy is upgraded to the next database version V-NEW.
  4. The source and target databases are synchronized.
  5. All access to source database is stopped except for GoldenGate user (needed for failback replication )
  6. The application is pointed to the target database.
  7. The source database is kept in the same version V-OLD for failback reasons.

Standby Database

A standby database is a copy of the main production database that is maintained for high availability or disaster tolerance purposes. The standby database can be physical or logical.

In the physical standby copy, the database is kept in recovery mode. More specifically, the redo logs of the production database are applied to a mounted copy of the production database. There are some hardware and operating system limitations because redo across Oracle platforms is not always compatible.

A logical standby is a copy of the production database that contains the same objects, but doesn’t physically match the structure of the production database copy. It is maintained by instantiating a logical equivalent of the production database and replaying the SQL that modifies the production database at the standby site.

Transportable Tablespace

Transportable tablespace is a feature introduced in Oracle Database 8i that allows nonsystem tablespaces to be moved from one database to another by physically grafting the tablespace datafiles into the control files on the target database, and then importing object metadata into the target database’s dictionary. Transportable tablespace has three main phases:

  • Exporting the metadata (dictionary data for the objects)
  • Transferring the datafiles from one database to another
  • Importing the metadata and datafiles

Transportable tablespace sets can be created from an Oracle Recovery Manager (RMAN) backup to avoid downtime on the primary database.

Cross-Platform Transportable Tablespace

Cross-platform transportable tablespace is an extension of the transportable tablespace feature that enables tablespaces to be transported across database platforms. This feature can only be used after the database compatibility has been advanced to Oracle Database version or later.

Clone Database

A clone database is a database constructed using a restored backup of an existing database recovered to a point in time and opened.

Oracle Recovery Manager

Oracle RMAN is a database tool that manages the process of making backups and managing the process of restoring and recovering from them. It is also used for the conversion of endian systems during a cross-platform CONVERT.


Overview of Oracle GoldenGate Architecture

As shown in Figure 1, Oracle GoldenGate leverages a decoupled architecture comprising independent application modules to capture and replicate data in real time, with low impact on the source database


Figure1. High-level architecture of Oracle GoldenGate, running in a bidirectional configuration


The Oracle GoldenGate Capture/Extract module resides on the source database system and is multithreaded in an Oracle RAC environment. It mines transactions from the Oracle redo log and propagates transactions to an on-disk queue. Only committed transactions are written to the queues.

The Capture module can be used either to initialize the target database directly from tables (mostly for heterogeneous replications) or to do real time change data capture of both DML and DDL changes from transaction or redo logs.

Trail Files

The Oracle GoldenGate trail files can be conceptualized as a persistent ordered set of committed transactions generated by the Oracle GoldenGate Capture process. Trail files describe DML operations (inserts, updates, and deletes) along with transactional context as captured from the source database.


The Pump is similar to a captue/extract process which is used to move the data in the trail files from a source to a target server.


The Oracle GoldenGate Delivery/Replicat module is a process that runs on the target system. It reads the trail files written by Capture/Pump process and applies the captured transactions to the target database using dynamic SQL. To maintain synchronization between the source and target databases, Oracle GoldenGate applies data changes to the target tables using native database calls, statement caches and local database access. To ensure data and referential integrity, Oracle GoldenGate applies data changes in the transaction commit order that occurred on the source database.

Detailed Steps Involved in Zero Downtime Upgrade / Migration

The steps involved in achieving zero downtime using Oracle GoldenGate are given below:

Starting Capture/Extract process on Source Database

  1. Install Oracle GoldenGate software on the source and target database servers.
  2. It is a best practice to write the extract trails locally and use a pump to transfer them to the target database server. While creating the trail files directory, if possible, locate them on different disk controllers to improve the performance on both the source and target servers
  3. Address change management by restricting DDL changes during the upgrade / migration process or execute the additional steps required to capture DDL replication on the source server
  4. Turn on minimum supplemental logging at the database level on the source server.
  5. Turn on supplemental logging at the table level using the Oracle GoldenGate command “ADD TRANDATA” or “ADD SCHEMATRANDATA” on the source server.
  6. Define and start the manager process on the source server.
  7. Define and start the capture process on the source server. Note the time of starting the capture process.
  8. Define and start the manager process on the target server so that the pump process can transfer trail files to target server.
  9. Define and start the pump process which will send the trail file data to the target server.

Initial Instantiation of Secondary or Target Database

  • In order to not miss any data, before starting any instantiation method, make sure that all the open transactions that existed when the capture process was started are completed.
  • The best source for this information is the V$TRANSACTION performance view (GV$TRANSACTION in a RAC database). After starting capture, look at the transactions that exist in this view to see how many of them have a START_TIME less than the time noted when starting the capture process.
  • If a particular transaction is running for longer than expected, you will need to investigate who is running that transaction and what that transaction is doing. Ultimately, you may discover that you will need to kill the session that owns the long running transaction in order to begin the instantiation of your target system.
  • There are a few methods available to instantiate the target database. There is an Oracle document titled "Oracle GoldenGate Best Practice: Instantiation from a Oracle Source Database" available on the support site which talks about initial instantiation methods in detail. The complete document can be found on the Oracle Support site under the document ID: 1276058.1

Upgrade Target Database

Run the upgrade process on the target database to bring it up to the required level. There are Oracle documents available which explains the upgrade process in detail and is outside the purview of this document.

Start Replicat / Delivery process on Target Database

Once the upgrade process on the target database has completed, follow the steps below to synchronize the target server with the source server

  • Define the delivery/replicat process after adding a checkpoint table in the target database
  • Start the delivery/replicat process with the command “AFTERCSN”. Make sure to use the correct SCN number noted during restoration of target DB. The full command is

Start replicat <group_name>, AFTERCSN <SCN number>

  • This will start applying the changes to the target database and synchronize the source and target databases.
  • When the lag of the delivery/replicat process becomes zero, the source and target databases are in sync.

Oracle GoldenGate Veridata

Oracle GoldenGate Veridata is a standalone high-speed data comparison solution that identifies and reports data discrepancies between two databases without interrupting ongoing business processes. It allows data discrepancies to be isolated for testing and troubleshooting. Oracle GoldenGate Veridata is ideal for conducting data validation once the source and target databases are fully operational and running different versions of Oracle Database. It can also help to determine if a failback is needed, in case of any risky data anomalies.

Target Server Cutover

Once the data is compared and verified between the source and target database, you can plan the cut over to the target server (pointing all the applications to the target server).

  1. Stop all applications pointing to the source server and disconnect all the users
  2. Stop the extract process and pump process after making sure all trail files have been processed by the pump process
  3. Stop the replicat process after making sure all trail files have been processed by the replicat process
  4. Start all the applications after pointing them to the target server

Fallback Steps

In order to facilitate fallback the following steps are needed so that changes made on the target server are replicated to the source server

  1. Run the additional steps to capture DDL on the target server (if DDL replication needs to be enabled)
  2. Turn on minimum supplemental logging at the database level on the target server.
  3. Turn on supplemental logging at the table level using the Oracle GoldenGate command “ADD TRANDATA” or “ADD SCHEMATRANDATA” on the target server
  4. Before starting the applications define and start the capture process on the target server
  5. Start the pump process on the target server
  6. Define and start the apply process on the source server


Users expect mission-critical applications to be continuously available. Even planned outages can have a negative impact on user satisfaction. Using Oracle GoldenGate, an upgrade or migration can be completed with zero-database downtime and only very minimal application switchover downtime. Key technical advantages of this solution include:

Upgrades or migrations using two databases

  • Reduce the load on primary database during instantiation by offloading to a clone database
  • Conversions offloaded to a clone staging database
  • Transaction synchronization across databases
  • Data replication and transactional integrity verification using Oracle GoldenGate Veridata

Oracle GoldenGate enables the world’s largest enterprises to improve the availability, performance, and accessibility of the transactional data that drives mission-critical business processes. Oracle GoldenGate’s wide variety of use cases includes real-time business intelligence, query offloading, zero-downtime upgrades and migrations, disaster recovery, and active-active databases for data distribution, data synchronization, and high availability.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content