Migrate from Oracle Database to Amazon RDS for PostgreSQL by using Oracle GoldenGate - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

Migrate from Oracle Database to Amazon RDS for PostgreSQL by using Oracle GoldenGate

Created by Dhairya Jindani (AWS), Rajeshkumar Sabankar (AWS), and Sindhusha Paturu (AWS)

Environment: PoC or pilot

Source: Databases: Relational

Target: Amazon RDS for PostgreSQL

R Type: Re-architect

Workload: Oracle

Technologies: Migration; Databases

AWS services: Amazon RDS

Summary

This pattern shows how to migrate an Oracle database to Amazon Relational Database Service (Amazon RDS) for PostgreSQL by using Oracle Cloud Infrastructure (OCI) GoldenGate.

By using Oracle GoldenGate, you can replicate data between your source database and one or more destination databases with minimal downtime.

Note: The source Oracle database can be either on-premises or on an Amazon Elastic Compute Cloud (Amazon EC2) instance. You can use a similar procedure when using on-premises replication tools.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • An Oracle GoldenGate license

  • Java Database Connectivity (JDBC) driver to connect to the PostgreSQL database

  • Schema and tables created with the AWS Schema Conversion Tool (AWS SCT) on the target Amazon RDS for PostgreSQL database

Limitations

  • Oracle GoldenGate can replicate existing table data (initial load) and ongoing changes (change data capture) only

Product versions

  • Oracle Database Enterprise Edition 10g or newer versions 

  • Oracle GoldenGate 12.2.0.1.1 for Oracle or newer versions

  • Oracle GoldenGate 12.2.0.1.1 for PostgreSQL or newer versions

Architecture

The following diagram shows an example workflow for migrating an Oracle database to Amazon RDS for PostgreSQL by using Oracle GoldenGate:

Migration workflow from on-premises Oracle database to Amazon RDS for PostgreSQL.

The diagram shows the following workflow:

  1. The Oracle GoldenGate Extract process runs against the source database to extract data.

  2. The Oracle GoldenGate Replicat process delivers the extracted data to the target Amazon RDS for PostgreSQL database.

Tools

Epics

TaskDescriptionSkills required

Download Oracle GoldenGate.

Download the following versions of Oracle GoldenGate:

  • Oracle GoldenGate 12.2.0.1.1 for Oracle or a newer version

  • Oracle GoldenGate 12.2.0.1.1 for PostgreSQL or a newer version

To download the software, see Oracle GoldenGate Downloads on the Oracle website.

DBA

Install Oracle GoldenGate for Oracle on the source Oracle Database server.

For instructions, see the Oracle GoldenGate documentation.

DBA

Install Oracle GoldenGate for PostgreSQL database on the Amazon EC2 instance.

For instructions, see the Oracle GoldenGate documentation.

DBA
TaskDescriptionSkills required

Set up Oracle GoldenGate for Oracle Database on the source database.

For instructions, see the Oracle GoldenGate documentation.

Make sure that you configure the following:

  • Supplemental logging

  • Oracle GoldenGate users

  • Any required grants and permissions

  • Parameter files

  • Manager process

  • Directory

  • GLOBALS files

  • Oracle Wallet

DBA

Set up Oracle GoldenGate for PostgreSQL on the target database.

For instructions, see Part VI Using Oracle GoldenGate for PostgreSQL on the Oracle website.

Make sure that you configure the following:

  • Manager process

  • GLOBALS files

  • Oracle Wallet

DBA
TaskDescriptionSkills required

Set up the Extract process in the source database.

In the source Oracle Database, create an extract file to extract data.

For instructions, see ADD EXTRACT in the Oracle documentation.

Note: The extract file includes the creation of the extract parameter file and trail file directory.

DBA

Set up a data pump to transfer the trail file from the source to the target database.

Create an EXTRACT parameter file and trail file directory by following the instructions in PARFILE in Database Utilities on the Oracle website.

For more information, see What is a Trail? in Fusion Middleware Understanding Oracle GoldenGate on the Oracle website.

DBA

Set up replication on the Amazon EC2 instance.

Create a replication parameter file and trail file directory.

For more information about creating replication parameter files, see section 3.5 Validating a parameter file in the Oracle Database documentation.

For more information about creating a trail file directory, see Creating a trail in the Oracle Cloud documentation.

Important: Make sure that you add a checkpoint table entry in the GLOBALS file at the target.

For more information, see What is a Replicat? in Fusion Middleware Understanding Oracle GoldenGate on the Oracle website.

DBA
TaskDescriptionSkills required

In the source database, create a parameter file to extract data for the initial load.

Follow the instructions in Creating a parameter file in GGSCI in the Oracle Cloud documentation.

Important: Make sure that the Manager is running on the target.

DBA

In the target database, create a parameter file to replicate data for the initial load.

Follow the instructions in Creating a parameter file in GGSCI in the Oracle Cloud documentation.

Important: Make sure that you add and start the Replicat process.

DBA
TaskDescriptionSkills required

Stop the Replicat process and make sure that the source and target databases are in sync.

Compare row counts between the source and target databases to make sure that the data replication was successful.

DBA

Configure data definition language (DDL) support.

Run the DDL script for creating triggers, sequence, synonyms, and referential keys on PostgreSQL.

Note: You can use any standard SQL client application to connect to a database in your DB cluster. For example, you can use pgAdmin to connect to your DB instance.

DBA

Related resources