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.
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:
To help comprehend this use case, key concepts and terms are outlined in the following subsections.
Throughout this document, the production database is referred to as the source and the secondary copy as the target database
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.
A database upgrade advances the Oracle Database software release number from one version to another. There are two primary ways to perform an 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.)
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.).
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.
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:
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 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:
Transportable tablespace sets can be created from an Oracle Recovery Manager (RMAN) backup to avoid downtime on the primary database.
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 10.0.0.0 or later.
A clone database is a database constructed using a restored backup of an existing database recovered to a point in time and opened.
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.
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.
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.
The steps involved in achieving zero downtime using Oracle GoldenGate are given below:
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.
Once the upgrade process on the target database has completed, follow the steps below to synchronize the target server with the source server
Start replicat <group_name>, AFTERCSN <SCN number>
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.
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).
In order to facilitate fallback the following steps are needed so that changes made on the target server are replicated to 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
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.