Golden Gate Zero Downtime Database Migration

Detailed step using a platform Migration using Golden Gate with Zero down time.

This is a case study of moving an Oracle Database 9i on IBM AIX to Oracle Database 11g Release 2 on Linux and the detailed implementation steps. These steps can be simplified and adopted to accomplish a rolling upgrade.
Two scenarios are described next: a migration without the addition of a failback solution and the same migration with the addition of a failback.

1. Migration Without Failback:

Overview of a cross-platform database migration without failback

1. Address change management by restricting the creation of newer packages and tablespaces during the migration process (not shown). Using DDL migration, these activities can be allowed to some extent.
2. Start the Oracle GoldenGate Capture process at the production database Dprod.
3. Do a point-in-time recovery of an existing backup of Dprod until some SCN Qscn in a separate staging area. Call this database Dpitr. The Capture process in Step 2 must capture all transactions that have a commit time stamp higher than this Qscn. It must have been positioned at or before the beginning of the longest-running transaction when the source database was at Qscn. You can query gv$transaction and gv$database at the source database to identify the longest-running transaction and the current SCN at any point in time to identify where Capture should be positioned.
4. Upgrade Dpitr to Oracle Database 11g Release 2 on IBM AIX. Advance compatibility to Oracle Database version 10.0.0.0 or later.
5. Set up a vanilla Oracle Database 11g Release 2 database on Linux. Call this database Dtarget.
6. Take a full export without rows at Dpitr. Call the generated export exp_norows.dmp. This will pick up any objects that are not picked up as part of the transportable tablespace export.
7. Unplug the user tablespaces from Dpitr with the Oracle Database cross-platform transportable tablespace feature, using source-side endian conversion. (Note that the conversion would not be required if the endian systems were the same.) This is the step that avoids any performance degradation and does not require any quiescing at Dprod. This step will create a small export dump file. Call this exp_xtts.dmp.
8. Plug the set of tablespaces from Dpitr into Dtarget using the cross-platform transportable tablespace feature. Use the exp_xtts.dmp file created from Step 7. (Note that the plugged in tablespaces are in read-only mode.)
9. Make the set of user tablespaces in Dtarget Read Write (not shown).
10. Do a NOROWS import with IGNORE=Y option at Dtarget using the exp_norows.dmp dump file.
11. Start the Oracle GoldenGate Delivery process at Dtarget and synchronize up to the changes generated since Qscn. 
12. If any datatypes are not supported by transportable tablespace or Oracle GoldenGate, then do a special export/import of these objects from Dprod to Dtarget. 
13. Use Oracle GoldenGate Veridata to verify that the data at Dprod and Dtarget is synchronized. 
14. Swiitch the application from Dprod to Dtarget (not shown). 

The above procedure offloads any quiescing, conversion work to a clone database, and takes advantage of Oracle GoldenGate’s incremental real-time data capture and delivery to eliminate the downtime to zero, excluding the application switchover time. 

As an alternative to using the cross-platform transportable tablespace feature in Step 6, you can use a full export with data and import into a vanilla database, in which case steps 7, 8, and 9 do not apply. Additionally, in Step 10, you can import the data as well as all database objects. 

For upgrading from Oracle Database 10g to Oracle Database 11g, you can use Data Pump Export in parallel to improve performance.

2. Migration with Failback

A cross-platform database migration with failback

1. Address change management by restricting the creation of newer packages and tablespaces during the migration process.

2. Start the Oracle GoldenGate Capture process at the production database Dprod.

3. Do a point-in-time recovery of an existing backup of Dprod until some SCN Qscn in a separate staging area. Call this database Dpitr. The Capture process in Step 2 must capture all transactions that have a commit time stamp higher than this Qscn. It must have been positioned at or before the beginning of the longest-running transaction when the source database was at Qscn. You can query gv$transaction and gv$database at the source database to identify the longest-running transaction and the current SCN at any point in time to identify where the Capture process should be positioned.

4. Upgrade Dpitr to Oracle Database 11g Release 2 on IBM AIX. Advance compatibility to Oracle Database version 10.0.0.0 or later.

5. Set up a vanilla Oracle Database 11g Release 2 database on Linux. Call this database Dtarget.

6. Take a full export without rows at Dpitr. Call the generated export exp_norows.dmp. This will pick up any objects that are not picked up as part of the transportable tablespace export.

7. Unplug the user tablespaces from Dpitr with the Oracle Database cross-platform transportable tablespace feature, using source-side endian conversion. (Note that the conversion would not be required if the endian systems were the same.) This is the step that avoids any performance degradation and does not require any quiescing at Dprod. This step will create a small export dump file. Call this exp_xtts.dmp.

8. Plug the set of tablespaces from Dpitr into Dtarget using the cross-platform transportable tablespace feature. Use the exp_xtts.dmp file created from Step 7. (Note that the plugged in tablespaces are in read-only mode.)

9. Make the set of user tablespaces in Dtarget Read Write.

10. Do a NOROWS import with IGNORE=Y option at Dtarget using the exp_norows.dmp dump file.

11. Start the Oracle GoldenGate Delivery process at Dtarget and synchronize up to the changes generated since Qscn.

12. If any datatypes are not supported by transportable tablespace or Oracle GoldenGate, then do a special export/import of these objects from Dprod to Dtarget.

13. After Oracle GoldenGate eliminates the lag between Dprod and Dtarget, use Oracle GoldenGate Veridata to verify that the data at Dprod and Dtarget is synchronized.

14. Start the Oracle GoldenGate Capture process on Dtarget.

15. Switch the application from Dprod to Dtarget (not shown).

16. Start the Oracle GoldenGate Delivery process on Dprod.

Failback Steps

During the real-time bidirectional data movement, Oracle GoldenGate allows two databases to support transaction processing. This makes failback a trivial process, if the new database is not stable:

1. Stop the application at Dtarget (the new primary) running Oracle Database 11g Release 2.

2. Once Oracle GoldenGate has applied all transactions from Dtarget to Dprod, switch the application to Dprod.

3. Declare Dprod as the new primary.

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 after the rolling upgrade, once the source and target 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.