AWS Database Blog

Migrate from Oracle to Amazon RDS for MySQL, MariaDB or Amazon Aurora MySQL using Oracle GoldenGate

When thinking about modernizing an Oracle Database to an open-source database on AWS, options include Amazon Relational Database Service (Amazon RDS) for MySQL, Amazon RDS for MariaDB, Amazon RDS for PostgreSQL, or Amazon Aurora. The choice of target database is usually dependent on three major factors:

  • Database conversion or refactoring efforts
  • The enterprise-wide decision on choosing an open-source database engine
  • The desire to reduce operating costs, increase productivity, and improve employee and customer experience

After deciding on the target database engine, the next important step is to identify the database conversion and migration tool that will be best suited for the database migration. The most critical aspects of deciding a migration tool include its security compliance within the organization, its license requirements, and how it interacts with the source database.

AWS recommended tool for database conversion is AWS Schema Conversion Tool (AWS SCT). With this, you can convert tables, views, stored procedures, and functions to your desired target database engine. And AWS Database Migration Service (AWS DMS) is used for relational database migration to the AWS Cloud. The key features of using AWS DMS for database migration are security, minimal downtime, and its simple-to-use architecture.

The following AWS prescriptive guidance gives an overview to convert and migrate an on-premises Oracle database to an RDS for MySQL database using AWS DMS and AWS SCT. Additionally, the following migration playbook details the steps required to migrate from Oracle to an Aurora MySQL database, and can be used to migrate to any MySQL-compatible database, including MariaDB.

There could be various use cases where you would like to use Oracle GoldenGate (OGG) for migration. For example, if the source is a high transaction database, or when you need to do active-active replication between MySQL and Oracle. Another use case would be if OGG has already been approved by the organization’s security team or if you have already procured an OGG license and have expertise with it, or if you aren’t able to use AWS DMS. In such cases, using the AWS SCT for database conversion and OGG for database migration would make more sense.

In this post, we show you how to migrate an Oracle database to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL-Compatible Edition using OGG.

Solution overview

The following diagram shows the communication flow between your existing Oracle database—which could be on premises, on an Amazon Elastic Compute Cloud (Amazon EC2) instance, or Amazon RDS for Oracle), and the target MySQL compatible database.

The following figure provides more details on the individual components of OGG on Amazon EC2 to migrate an on-premises Oracle database to a MySQL-compatible database on Amazon Aurora or RDS.

A benefit of this solution is that you don’t have to move the trail files from one EC2 instance to another, which can improve migration performance. But there could be other ways of configuring this migration, such as using multiple EC2 instances, one with OGG for Oracle Libraries and a second with OGG for MySQL libraries. You could also install OGG for Oracle in an on-premises environment and OGG for MySQL on an EC2 instance in the same VPC as your target MySQL database.

In the following sections, we show how to set up the infrastructure and implement the solution.

Prerequisites

Before you use OGG to migrate the database, you must have the following:

  • A source Oracle database version 11 and above running on an existing on-premises environment, Oracle on Amazon EC2, or Amazon RDS for Oracle
  • Archive logging enabled on the source
  • A target MySQL version 5.7 and above or MariaDB version 10.4 and above database running on Amazon RDS or Aurora
  • An EC2 instance with the following:
  • A database user account on both the source and target databases
  • Secure network connectivity between the EC2 instance with the source and target database server

Infrastructure setup

The following table details the environment and the database instance names used throughout this post.

Database role IP address Instance name Database unique name Database open mode Database port
Source Oracle Database 172.31.xx.xx ORASOURCE orasource Read write 1521
Target RDS for MySQL Database 172.31.xx.xx mysqltest.xxxxx.ap-southeast-2.rds.amazonaws.com testdb Read write 1433
Amazon EC2 with with GoldenGate for Oracle and MySQL 172.31.xx.xx – – – –

Configure the source Oracle database

To configure the source Oracle database, complete the following steps:

  1. Set the ENABLE_GOLDENGATE_REPLICATION parameter to True. This enables control services provided by the RDBMS for OGG.
  2. Next, ensure supplemental logging is enabled at least at the database level: 
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
  3. In addition to minimum supplemental logging, it’s also recommended to put the Oracle database into forced logging mode. This makes sure that row chaining information is updated in the redo log for update operations. 
    ALTER DATABASE FORCE LOGGING;
  4. Make sure that archive log retention is enabled. This is required to ensure OGG Replicat should be able to resume in case of any issues.
  5. It’s always recommended to have a separate user for OGG with minimum privileges. The following command creates a database user with the minimum permissions that are required for OGG to work: 
    CREATE USER oggadm1  IDENTIFIED BY "<YOUR PASSWORD>" DEFAULT TABLESPACE ADMINISTRATOR TEMPORARY TABLESPACE TEMP;
alter user oggadm1 quota unlimited on administrator;
GRANT CREATE SESSION, ALTER SESSION TO oggadm1;
GRANT RESOURCE TO oggadm1;
GRANT SELECT ANY DICTIONARY TO oggadm1;
GRANT FLASHBACK ANY TABLE TO oggadm1;
GRANT SELECT ANY TABLE TO oggadm1;
GRANT SELECT_CATALOG_ROLE TO oggadm1  WITH ADMIN OPTION;
grant SELECT on DBA_CLUSTERS to oggadm1;
GRANT EXECUTE ON DBMS_FLASHBACK TO oggadm1;
GRANT SELECT ON SYS.V_$DATABASE TO oggadm1;
GRANT ALTER ANY TABLE TO oggadm1;
  6. Additional permissions are required to query local metadata to generate the data definition file, which is used to convert database objects from Oracle to the MySQL engine:
BEGIN
dbms_goldengate_auth.grant_admin_privilege
(
grantee =>'OGGADM1',
privilege_type => 'CAPTURE',
grant_select_privileges => TRUE,
do_grants => TRUE,
container => 'ALL'
);
END;

Configure OGG for Oracle for initial load

Make sure OGG for Oracle on Amazon EC2 is able to connect to the source Oracle database. You can verify this by trying to connect from SQL*Plus or you can try connecting from the OGG for Oracle command line interface. Complete the following steps to configure OGG for Oracle for initial load:

  1. Verify the source database connection. 
    GGSCI Oracle 1> dblogin userid oggadm1@orasource, password <password>
Successfully logged into database.
  2. Create subdirectories. The OGG configuration starts with creating the directory location for various files (listed in the following code) using the command Create subdirs: 
    GGSCI Oracle 2> create subdirs
Creating subdirectories under current directory /u01/app/oracle/product/gg_src
Parameter file
Report file
Checkpoint file
Process status files
SQL script files
Database definitions files
Extract data files
Temporary files
Credential store files
Masterkey wallet files
Dump files
/u01/app/oracle/product/gg_src/dirprm: created.
/u01/app/oracle/product/gg_src/dirrpt: created.
/u01/app/oracle/product/gg_src/dirchk: created.
/u01/app/oracle/product/gg_src/dirpcs: created.
/u01/app/oracle/product/gg_src/dirsql: created.
/u01/app/oracle/product/gg_src/dirdef: created.
/u01/app/oracle/product/gg_src/dirdat: created.
/u01/app/oracle/product/gg_src/dirtmp: created.
/u01/app/oracle/product/gg_src/dircrd: created.
/u01/app/oracle/product/gg_src/dirwlt: created.
/u01/app/oracle/product/gg_src/dirdmp: created.
  3. The first and most important component of configuring OGG is the manager, which is required to run Extract and Replicat. This is a mandatory component to manage and run an OGG instance. As highlighted in solution overview, there will be a unique manager for OGG for Oracle and a separate manager process for OGG for MySQL. Use the following command to modify the content of the manager process: 
    GGSCI Oracle 3> edit params mgr
  4. The content of mgr.prm is as follows for Oracle: 
    PORT 7809
  5. After specifying the desired port for the manager for OGG for Oracle, you can start the manager. Make sure that port specified in the preceding file isn’t being used by any other process. 
    GGSCI Oracle 4> start mgr
GGSCI Oracle 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
  6. Optionally, you can create a wallet to store the source Oracle database credentials. With this approach, you just specify the alias and can avoid specifying user credentials in plain text in the Extract and Replicat configuration files. 
    GGSCI Oracle 6> create wallet
Created wallet.
Opened wallet.
  7. After the wallet is created, edit the wallet with existing credentials: 
    GGSCI Oracle 7> add credentialstore
Credential store created.
GGSCI Oracle 8> alter credentialstore add user oggadm1@orasource, password <password>,alias ggadmin_src
Credential store altered.

GGSCI Oracle 9> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ggadmin_src
Userid: oggadm1@orasource
  8. When the wallet is ready, log in to the database from OGG for Oracle using the alias you created. You don’t need to specify database credentials again. You can use the dblogin utility: 
    GGSCI Oracle 10> dblogin useridalias ggadmin_src
Successfully logged into database.
  9. The next step is to run the DEFGEN utility, which generates a file with field descriptions for each column entry of tables or schemas listed in the defgen parameter file. To modify the schema name and file output location, you must modify the parameter file for the defgen utility: 
    GGSCI Oracle 11> edit params defgen

    The content of defgen.prm looks like the following code. It generates a structure of all tables in the DMS_SAMPLE schema. We have used DMS_SAMPLE as the sample schema for the source database. You can modify this based on your schema name or only tables that you are planning to convert and migrate to MySQL.

    defsfile /u01/app/oracle/product/gg_src/dirdef/dmssample.def,purge
UserIdAlias ggadmin_src
table DMS_SAMPLE.*;
  10. After you create the parameter file for defgen with the correct user credentials and a list of tables and schemas to be migrated, you can run it as follows: 
    ./defgen paramfile /u01/app/oracle/product/gg_src/dirprm/defgen.prm

    The defgen utility creates a sample file like the following example, containing information regarding all the tables. This file size could vary based on the number of tables mentioned in the parameter file. The file is used to translate the Oracle data to MySQL data.

    ***********************************************************************

Oracle GoldenGate Table Definition Generator for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 15:32:20
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
Starting at 2023-05-10 04:39:33
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Fri Mar 17 01:52:38 EDT 2023, Release 4.18.0-425.19.2.el8_7.x86_64
Node: ip-172-31-22-75.ap-southeast-2.compute.internal
Machine: x86_64
Address Space Size
Heap Size
File Size
CPU Time
:
:
:
:
soft limit hard limit
unlimited unlimited
unlimited unlimited
unlimited unlimited
unlimited unlimited
Process id: 3408
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile /u01/app/oracle/product/gg_src/dirdef/dmssample.def,purge
UserIdAlias ggadmin_src
table DMS_SAMPLE.*;
Expanding wildcard table specification DMS_SAMPLE.*:
Retrieving definition for DMS_SAMPLE.MLB_DATA.
2023-05-10 04:39:35 WARNING OGG-06439 No unique key is defined for table MLB_DATA. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Retrieving definition for DMS_SAMPLE.NAME_DATA.
Retrieving definition for DMS_SAMPLE.NFL_DATA.
2023-05-10 04:39:35 WARNING OGG-06439 No unique key is defined for table NFL_DATA. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key
Retrieving definition for DMS_SAMPLE.NFL_STADIUM_DATA.
2023-05-10 04:39:35 WARNING OGG-06439 No unique key is defined for table NFL_STADIUM_DATA. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Retrieving definition for DMS_SAMPLE.PERSON.
Retrieving definition for DMS_SAMPLE.PLAYER.
Retrieving definition for DMS_SAMPLE.SEAT.
Retrieving definition for DMS_SAMPLE.SEAT_TYPE.
Retrieving definition for DMS_SAMPLE.SPORTING_EVENT.
Retrieving definition for DMS_SAMPLE.SPORTING_EVENT_INFO.
Definitions generated for 18 tables in /u01/app/oracle/product/gg_src/dirdef/dmssample.def.
  11. After you generate the file, copy it to the corresponding directory on OGG for MySQL. In our solution, we use just one EC2 instance for the individual installation for OGG for Oracle (location: /u01/app/oracle/product/gg_src) and OGG for MySQL (location: /u01/app/oracle/product/gg_trg): 
    cp /u01/app/oracle/product/gg_src/dirdef/dmssample.def
 /u01/app/oracle/product/gg_trg/dirdef/dmssample.def
  12. Enable unconditional logging of the primary key and the conditional supplemental logging of all unique keys and foreign keys of all tables on the source Oracle database: 
    GGSCI Oracle 12> dblogin useridalias ggadmin_src
GGSCI Oracle 13>add schematrandata DMS_SAMPLE

    This isn’t required if you just have to load data one time (you won’t be using OGG to replicate ongoing changes).

    Schematrandata adds the supplemental logging and records the SCN (Oracle System Change Number). This allows for a second Replicat process (for CDC only) to start applying the changes from the point where the initial load extract stopped. Therefore, it’s mandatory to enable this before starting the first extract to load initial data.

  13. If you want supplemental logging to be enabled independent to OGG, you have to add supplemental logging using the following commands on each table: 
    ALTER TABLE SCHEMANAME.TABLENAME ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE SCHEMANAME.TABLENAME ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER TABLE SCHEMANAME.TABLENAME DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    Adding schematrandata is still required to keep track of the SCN for ongoing replication changes. Another option would be to keep track of the SCN and start the second Replicat process (for CDC) using the SCN that was recorded before the start of first extract process (for initial load).

    The output of adding schematrandata on all tables in the schema looks like the following code:

    2023-05-15 06:42:08 WARNING OGG-06439 No unique key is defined for table MLB_DATA. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2023-05-15 01:14:27 INFO OGG-01788 SCHEMATRANDATA has been added on schema "DMS_SAMPLE".
2023-05-15 01:14:27 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "DMS_SAMPLE".
2023-05-15 01:14:27 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "DMS_SAMPLE".
2023-05-15 01:14:29 INFO OGG-10471 ***** Oracle Goldengate support information on table DMS_SAMPLE.ABCME *****
Oracle Goldengate support native capture on table DMS_SAMPLE.ABCME.
Oracle Goldengate marked following column as key columns on table DMS_SAMPLE.ABCME: A.
2023-09-19 01:14:29 INFO OGG-10471 ***** Oracle Goldengate support information on table DMS_SAMPLE.MLB_DATA *****
Oracle Goldengate support native capture on table DMS_SAMPLE.MLB_DATA.
Oracle Goldengate marked following column as key columns on table DMS_SAMPLE.MLB_DATA: MLB_ID, MLB_NAME, MLB_POS, MLB_TEAM, MLB_TEAM_LONG, BATS, THROWS, BIRTH_YEAR, BP_ID, BREF_ID, BREF_NAME, CBS_ID, CBS_NAME, CBS_POS, ESPN_ID, ESPN_NAME, ESPN_POS, FG_ID, FG_NAME, LAHMAN_ID, NFBC_ID, NFBC_NAME, NFBC_POS, RETRO_ID, RETRO_NAME, DEBUT, YAHOO_ID, YAHOO_NAME, YAHOO_POS, MLB_DEPTH
No unique key is defined for table DMS_SAMPLE.MLB_DATA.
  14. Configuring Extract is the next step, which runs against the source Oracle database and is used for the initial data load. The parameter SOURCEISTABLE defines that Extract pulls a static dataset from the source database without using any checkpoint information. Information regarding this Extract isn’t visible with the info all command, but you can use the following command to identify the current status: 
    GGSCI Oracle 14> info extract *, tasks

    This is followed by another Extract (as explain in the solution overview), which is used separately to replicate ongoing changes.

  15. You can add the initial Extract using the following command: 
    GGSCI Oracle 15> ADD EXTRACT oraext, TRANLOG , SOURCEISTABLE
EXTRACT added.
GGSCI Oracle 16> edit param oraext

The content of the initial load Extract looks like the following code. In this extract, we are planning to migrate all tables under the schema DMS_SAMPLE. You can replace the schema names with your desired schema to be migrated or just list of tables that will be involved in migration.

EXTRACT oraext
UserIdAlias ggadmin_src
TABLE DMS_SAMPLE.*;
rmthost <Host IP where Replicat is running>, mgrport <Manager Port for Oralce GoldenGate for MySQL>
rmttask replicat, group <MySQL replicat name>

Configure OGG for MySQL for initial load

This process is similar to creating subdirectories for OGG for Oracle. Because OGG for MySQL is on the same instance, we need to make sure the directory selected to create subdirectories is different from OGG for Oracle.

  1. Create subdirectories with the following code: 
    GGSCI MySQL 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/product/gg_trg
Parameter file
Report file
Checkpoint file
Process status files
SQL script files
Database definitions files
Extract data files
Temporary files
Credential store files
Masterkey wallet files
Dump files
/u01/app/oracle/product/gg_trg/dirprm: created.
/u01/app/oracle/product/gg_trg/dirrpt: created.
/u01/app/oracle/product/gg_trg/dirchk: created.
/u01/app/oracle/product/gg_trg/dirpcs: created.
/u01/app/oracle/product/gg_trg/dirsql: created.
/u01/app/oracle/product/gg_trg/dirdef: created.
/u01/app/oracle/product/gg_trg/dirdat: created.
/u01/app/oracle/product/gg_trg/dirtmp: created.
/u01/app/oracle/product/gg_trg/dircrd: created.
/u01/app/oracle/product/gg_trg/dirwlt: created.
/u01/app/oracle/product/gg_trg/dirdmp: created.
  2. A separate manager process for OGG for MySQL is required. This needs to be configured to be run on a different port to avoid conflict with the port in use for OGG for Oracle: 
    GGSCI MySQL 2> edit params mgr
  3. The content of mgr.prm for MySQL is as follows: 
    PORT 7810
  4. Start the manager for OGG for MySQL: 
    GGSCI MySQL 3> start mgr

    At this stage, there are two OGG managers running on the same machine.

    Make sure the database is available on the target MySQL database. The target database name could be anything and doesn’t necessarily need to be the same as the source database. Also, tables that you are planning to migrate should already exist. If you are unsure how to convert Oracle tables into a MySQL-compatible database, you can use AWS SCT. For instructions, refer to Use the AWS Schema Conversion Tool to Convert the Oracle Schema to Aurora MySQL.

  5. Create database on the target MySQL instance: 
    mysql> create database dms_sample;
Query OK, 1 row affected (0.00 sec)

    You can verify the connection to the target MySQL database is successful by using the DBLOGIN utility and specifying the endpoint of your target MySQL database. This could be Amazon RDS for MySQL, Amazon RDS for MariaDB, or Aurora MySQL-Compatible. If Aurora MySQL-Compatible is the target, make sure to use the cluster endpoint to connect to the Aurora MySQL writer instance.

  6. Verify the connection to the target MySQL from OGG for MySQL: 
    GGSCI MySQL 4> DBLOGIN SOURCEDB dms_sample@mysqltest.xxxxxxxx.ap-southeast-2.rds.amazonaws.com:3306, USERID viqash, PASSWORD <YOUR PASSWORD>
  7. Configure the Replicat process in OGG for MySQL. The Replicat process is responsible for migrating data to the target database. The parameter SPECIALRUN highlights that this Replicat is used only for the initial load process: 
    GGSCI MySQL 5> add replicat mysqlrep, SPECIALRUN
REPLICAT added.
  8. Because this Replicat is only for the initial load, its status won’t be visible in the standard info all command from OGG for MySQL. Instead, use the following code: 
    GGSCI MySQL 6> info replicat *, tasks
  9. You can modify the contents of Replicat using the following command from OGG for MySQL: 
    GGSCI MySQL 7> edit params mysqlrep
  10. The contents of the Replicat process look like the following code. You need to specify the SOURCEDEFS file that was generated earlier using the DEFGEN utility: 
    REPLICAT mysqlrep
TARGETDB ggdatabase@mysqltest.xxxxxx.ap-southeast-2.rds.amazonaws.com, UserID viqash,Password <YOUR PASSWORD>
SOURCEDEFS /u01/app/oracle/product/gg_src/dirdef/dmssample.def
MAP DMS_SAMPLE.*, TARGET dms_sample.*;

It’s important to understand that default tables in Oracle are created in uppercase, whereas tables in MySQL by default are created in lowercase. In the preceding scenario, while specifying * for target tables, you need to make sure all tables in the target MySQL database are also created in uppercase (the same as Oracle). However, the right approach would be to create tables in the target MySQL database in lowercase and specify table mapping separately for all tables. The Replicat configuration file looks like the following code:

REPLICAT mysqlrep
TARGETDB ggdatabase@mysqltest.xxxxxx.ap-southeast-2.rds.amazonaws.com, UserID viqash,Password <password>
SOURCEDEFS /u01/app/oracle/product/gg_trg/dirdef/dmssample.def
MAP DMS_SAMPLE.NAME_DATA, target dms_sample.name_data;
MAP DMS_SAMPLE.MLB_DATA, target dms_sample.mlb_data;
MAP DMS_SAMPLE.NFL_DATA, target dms_sample.nfl_data;
MAP DMS_SAMPLE.NFL_STADIUM_DATA, target dms_sample.nfl_stadium_data;
MAP DMS_SAMPLE.SEAT_TYPE, target dms_sample.seat_type;
MAP DMS_SAMPLE.SPORT_TYPE, target dms_sample.sport_type;
MAP DMS_SAMPLE.SPORT_LEAGUE, target dms_sample.sport_league;
MAP DMS_SAMPLE.SPORT_LOCATION, target dms_sample.sport_location;
MAP DMS_SAMPLE.SPORT_DIVISION, target dms_sample.sport_division;
MAP DMS_SAMPLE.SPORT_TEAM, target dms_sample.sport_team;
MAP DMS_SAMPLE.SEAT, target dms_sample.seat;
MAP DMS_SAMPLE.PLAYER, target dms_sample.player;
MAP DMS_SAMPLE.PERSON, target dms_sample.person;
MAP DMS_SAMPLE.SPORTING_EVENT, target dms_sample.sporting_event;
MAP DMS_SAMPLE.SPORTING_EVENT_TICKET, target dms_sample.sporting_event_ticket;
MAP DMS_SAMPLE.TICKET_PURCHASE_HIST, target dms_sample.ticket_purchase_hist;

Disable foreign key constraints before initial load

Foreign key constraints on the target MySQL must be disabled before starting the initial load. This can be done by modifying the RDS parameter init_connect as ‘SET FOREIGN_KEY_CHECKS=0’. It’s a dynamic parameter and will be applied without database restart. Setting this parameter disables foreign key checks for all DML commands. When the initial load is complete, you must enable it again before the extract for ongoing replication is started.

Run the initial data load

Complete the following steps to run the initial data load:

  1. Connect to OGG for Oracle GGSCI and check the status of the manager and Extract using info all. This won’t give you the information for the SPECIALRUN Extract and will only show the status of the manager: 
    GGSCI Oracle 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
  2. When all the configuration of the initial load Extract and Replicat is complete, start the Extract to perform the initial database migration: 
    GGSCI Oracle 18> start oraext
Sending START request to MANAGER ...
EXTRACT ORAEXT starting
  3. Verify the status of Extract with the following command from OGG for Oracle: 
    GGSCI Oracle 19> info replicat *, tasks

The output looks like the following for the Extract process (on OGG for Oracle):

EXTRACT ORAEXT Last Started 2023-05-18 06:15 Status RUNNING
Checkpoint Lag Not Available
Process ID 12318
Log Read Checkpoint Table DMS_SAMPLE.SPORTING_EVENT_TICKET
2023-05-18 06:18:34 Record 38607805
Task SOURCEISTABLE

Similarly, the status of the Replicat process can be identified as follows (on OGG for MySQL):

GGSCI MySQL 8> info replicat *, tasks

The output looks like the following code:

REPLICAT MYSQLREP Initialized 2023-05-18 06:15 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:43:55 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

If you are not able to see the data on target table, check ggserr.log under /u01/app/oracle/product/gg_src/ for Extract errors and /u01/app/oracle/product/gg_trg/ for Replicat error messages. Similarly, if Extract or Replicat is in an abended state, look for ggserr.log in the respective folder to identify the root cause.

Configure OGG for Oracle for ongoing replication changes

This section covers settings to configure ongoing replication changes. To modify parameters that relate to the OGG for Oracle instance as a whole, you have to update the GLOBALS file. If there any parameters that you want to apply to all extracts, you can add them here. Complete the following steps:

  1. Edit the GLOBALS parameter in OGG for Oracle: 
    GGSCI Oracle 20> edit params ./GLOBALS
  2. Update the parameter ALLOWOUTPUTDIR to specify the allowed output trail directory (which must already exist): 
    ALLOWOUTPUTDIR /u01/app/oracle/product/gg_src/dirdat
  3. It’s required to register the primary extract group with an Oracle database for enabling integrated capture mode: 
    GGSCI Oracle 21> dblogin UserIdAlias ggadmin_src
Successfully logged into database.
GGSCI Oracle 22> register extract oracdc database
2023-05-19 01:29:59 INFO OGG-02003 Extract ORACDC successfully registered with database at SCN 15898276

    The benefit of integrated capture mode is that the Extract process interacts directly with the database log mining server, whereas in the case of classic capture mode, changes need to read from Oracle redo or archive log files. Another benefit of having integrated capture mode is it supports more data and storage types compared to classic capture mode.

  4. Create an extract group in integrated mode: 
    GGSCI Oracle 23> add extract oracdc, integrated tranlog, begin now
EXTRACT (Integrated) added.

    The Extract parameter file needs to be updated with the host details and port number where the OGG for MySQL libraries are configured. In our case, both (Oracle and MySQL) are configured on one single host but different ports, therefore we specify 7810, which is for OGG for MySQL. Tables that you would like OGG to replicate must be specified here. You have the option to specify * to replicate all tables or you could only specify tables that are continuously updating and leave the static tables to be migrated using the initial extract.

  5. Use the following code: 
    GGSCI Oracle 24> edit params oracdc
EXTRACT oracdc
UserIdAlias ggadmin_src
rmthost 172.31.22.75, mgrport 7810
RMTTRAIL /u01/app/oracle/product/gg_src/dirdat/or
TABLE DMS_SAMPLE.MLB_DATA;

    When Oracle Automatic Storage Management (ASM) is in use, the TRANLOGOPTIONS ASMUSER and ASMPASSWORD must be set in the Extract parameter file.

    At this stage, the process on OGG for Oracle looks like the following code:

    GGSCI Oracle 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ORACDC 00:00:00 00:00:09
  6. The next step is to add RMTTRAIL and specify the exact same location for the remote trail while creating the extract to replicate ongoing changes: 
    GGSCI Oracle 26> add rmttrail /u01/app/oracle/product/gg_src/dirdat/or, extract oracdc
RMTTRAIL added.

Configure OGG for MySQL for ongoing replication changes

To configure OGG for MySQL to replicate ongoing changes to the MySQL database, complete the following steps:

  1. Log in to the target MySQL from OGG for MySQL: 
    GGSCI MySQL 9> dblogin sourcedb dms_sample@mysqltest.xxxxxx.ap-southeast-2.rds.amazonaws.com, UserID viqash,password <password>
  2. You need to update the parameter ALLOWOUTPUTDIR in the GLOBALS file, similar to what we did for OGG for Oracle, to specify the allowed output trail directory (which must already exist): 
    GGSCI MySQL 10> edit params ./GLOBALS
  3. The GLOBALS file looks like the following code: 
    ALLOWOUTPUTDIR /u01/app/oracle/product/gg_src/dirdat
CHECKPOINTTABLE dms_sample.ggschkpt

    Make sure there is no table with the same name in target database that is mentioned in GLOBALS parameter file.

  4. The next step is to configure the Replicat at OGG for MySQL. Because we specified the manager port of OGG for MySQL in the extract created in OGG for Oracle, this integrates with the extract that was created in OGG for Oracle: 
    GGSCI MySQL 11> edit params mysqlcdc

    You must specify the SOURCEDEFS file, which was previously generated and used for the Extract for the initial load. The discard file holds information regarding the operations that failed while applying changes to the target database. Additionally, mapping (as explained earlier) needs to take care of Oracle uppercase and MySQL lowercase. If you specify * to replicate all tables, MySQL must have all tables created in uppercase as well. HANDLECOLLISIONS is an important parameter that takes care of duplicates and ensures no records are missed from migration.

  5. Use the following code: 
    REPLICAT mysqlcdc
TARGETDB ggdatabase@mysqltest.xxxxxx.ap-southeast-2.rds.amazonaws.com, UserID viqash,Password password
SOURCEDEFS /u01/app/oracle/product/gg_trg/dirdef/dmssample.def
MAP DMS_SAMPLE.MLB_DATA, target dms_sample.mlb_data;
DISCARDFILE /u01/app/oracle/product/gg_trg/dirrpt/mysqlcdc.dsc, PURGE
HANDLECOLLISIONS
  6. Use CHECKPOINTTABLE to create a checkpoint table with the name that was specified in the GLOBALS parameter file. It’s important to specify the checkpoint table, which records the read position and is helpful to resume the Replicat from the point where it was last stopped or failed. 
    GGSCI MySQL 12> add CHECKPOINTTABLE
No checkpoint table specified. Using GLOBALS specification dms_sample.ggschkpt...
Successfully created checkpoint table dms_sample.ggschkpt.
  7. The last step is to add the Replicat with the EXTTRAIL, which is the same as the RMTTRAIL created for the earlier Extract process: 
    GGSCI MySQL 13> ADD REPLICAT MYSQLCDC, EXTTRAIL /u01/app/oracle/product/gg_src/dirdat/or

At this stage, the process on OGG for MySQL looks like the following code:

GGSCI MySQL 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED MYSQLCDC 00:00:00 00:00:06

Enable foreign key constraints before ongoing replication changes

The same parameter init_connect is modified to the value ‘SET FOREIGN_KEY_CHECKS=0’. This makes sure that data replicated from the source is consistent on the target and doesn’t encounter any integrity constraint violations.

Start ongoing replication changes

The final step is to start the extract first to start capturing changes from the source database.

  1. Run the following code in OGG for Oracle GGSCI: 
    GGSCI Oracle 27> start oracdc
  2. Then you start the Replicat process, which starts applying changes to the target database: 
    GGSCI MySQL 15> start mysqlcdc

    At this stage, OGG starts replicating any changes happening in your source Oracle database to the target database. You can verify by performing some DMLs (inserts, updates, and deletes) to see if they are being replicated correctly to the target database.

  3. Verify the status of rmttrail by looking at the info rmttrail command: 
    GGSCI Oracle 28> info rmttrail *
Extract Trail: /u01/app/oracle/product/gg_src/or
Seqno Length: 9
Flip Seqno Length: no
Extract: ORACDC
Seqno: 12
RBA: 2891
File Size: 500M
  4. To verify the status of Extract (for ongoing replication changes), you can look at the info command on OGG for Oracle. This command also provides the checkpoint information and the process run history: 
    GGSCI Oracle 29> info extract oracdc
EXTRACT ORACDC Initialized 2023-05-19 00:00 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 3243
Log Read Checkpoint Oracle Redo Logs
2023-05-19 00:00:32 Seqno 0, RBA 0
SCN 0.15926625 (15926625)
  5. The view report command provides more information about the discard file generated by Extract: 
    GGSCI Oracle 30> view report oracdc
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:58
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
Starting at 2023-05-19 01:11:59
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Apr 5 13:35:01 EDT 2023, Release 4.18.0-477.10.1.el8_8.x86_64
Node: ip-172-31-22-75.ap-southeast-2.compute.internal
Machine: x86_64
Address Space Size
Heap Size
File Size
CPU Time
:
:
:
:
soft limit hard limit
unlimited unlimited
unlimited unlimited
unlimited unlimited
unlimited unlimited
  6. Similarly, you can view the status and information for Replicat on OGG for MySQL: 
    GGSCI MySQL 16> info mysqlcdc
REPLICAT MYSQLCD3 Last Started 2023-05-19 00:05 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 3249
Log Read Checkpoint File /u01/app/oracle/product/gg_src/dirdat/or000000012
2023-05-19 00:10:00.000000 RBA 2891

Clean up

To avoid incurring future charges, delete the EC2 instance on which OGG for Oracle and OGG for MySQL were installed.

On the Amazon EC2 console, navigate to the Instances page, select the instance, and then choose Terminate instance on the Instance state menu.

Summary

In this post, we discussed how to migrate between an Oracle database to a MySQL-compatible database using OGG. We also looked into the configuration steps to continuously replicate ongoing changes from an Oracle database to a MySQL-compatible database.

Try these settings in your next Oracle database migration to a MySQL-compatible database. If you have any comments or questions, leave them in the comments section.

About the Authors

Viqash Adwani is a Sr. Database Specialty Architect with Amazon Web Services. He works with internal and external Amazon customers to build secure, scalable, and resilient architectures in the AWS Cloud and help customers perform migrations from on-premises databases to Amazon RDS and Amazon Aurora databases.

Subhash Raut is a Database Migration Specialist Solutions Architect at AWS. He works on challenges related to database migrations and works closely with customers to help them realize the true potential of AWS DB migration tools.

Dilip Sasikumar is a Technical Account Manager with Amazon Web Services and is based in Melbourne, Australia. He works with enterprise support customers providing assistance on operational excellence, cost optimization and security. Dilip is also a database enthusiast with keen interest in relational databases and database migrations.