Migrate an Oracle database to Cloud SQL for PostgreSQL using Striim

Last reviewed 2021-06-28 UTC

In this tutorial, you use Striim to migrate Oracle® Database Enterprise Edition 18c or later from either an on-premises environment or a cloud environment to a Cloud SQL for PostgreSQL instance on Google Cloud. The tutorial uses tables in the Oracle HR sample schema.

This tutorial is for enterprise database architects, database engineers, and data owners who plan to use Striim to migrate or replicate Oracle databases to Cloud SQL for PostgreSQL. You should have a basic understanding of how to use Striim to build pipelines. You should also be familiar with the Striim web UI, Striim's key concepts, and how to create an application using Striim's Flow Designer.

Striim is a Google Cloud database migration technology partner. Striim simplifies online migrations by using a drag-and-drop interface to set up continuous data movement between databases. For migrations to Google Cloud, Striim offers a non-intrusive streaming platform for extract, transform, and load (ETL) that's efficient to deploy and straightforward to iterate. To build the migration pipeline, you use Striim's Flow Designer throughout this tutorial.

If database migration is not something you are familiar with, see this tech talk from Cloud Next '19.

Architecture

Database migration using Striim involves two stages of sequential data movement:

  • Stage 1: A one-time, initial replication of the Oracle database.
  • Stage 2: The continuous replication of every change committed on the source database system thereafter by using change data capture (CDC).

The following diagram illustrates a basic deployment architecture:

The deployment architecture connects to an Oracle database
and writes data to a Cloud SQL for PostgreSQL instance on Google Cloud.

This architecture involves running the Striim application on a Compute Engine instance. It connects to an Oracle database that's hosted on-premises or in the cloud, and writes data to a Cloud SQL for PostgreSQL instance on Google Cloud.

To avoid any network or connectivity issues between the Striim and Cloud SQL instances, use the same network for both instances. You can deploy Striim from Google Cloud Marketplace on a Compute Engine instance or, if you need high-availablilty, you can deploy Striim as a cluster.

For this tutorial, deploy from Cloud Marketplace.

The advantage of deploying Striim from Cloud Marketplace is that it lets you connect to various databases and data sources using their built-in adapters. You can connect the adapters by using Flow Designer, Striim's interactive, drag-and-drop interface, to form an acyclic graph. This graph is also known as a Striim pipeline or a Striim application.

The migration use case in this tutorial uses three Striim adapters:

  • Database Reader: Reads data from the Oracle source database during the initial load stage.
  • Oracle Reader: Reads data using LogMiner from the Oracle source database during the continuous data replication stage.
  • Database Writer: Writes data to the Cloud SQL for PostgreSQL database during the initial load and during the continuous data replication.

Objectives

  • Prepare your Oracle database as a source database for migration or replication.

  • Prepare a Cloud SQL for PostgreSQL database as the target database for migration or replication.

  • Fulfill the prerequisites for installing and running Striim.

  • Convert the schema of the Oracle database to the corresponding schema in PostgreSQL.

  • Perform the initial load from your Oracle database to Cloud SQL for PostgreSQL.

  • Set up the continuous replication from your Oracle database to Cloud SQL for PostgreSQL.

Costs

In this document, you use the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

The Striim solution in Cloud Marketplace offers a limited-term free-trial license. When the trial expires, usage charges are billed to your Google Cloud account. You can also obtain Striim licenses directly from Striim for on-premises deployment and in a Compute Engine virtual machine (VM). You also might incur costs that are associated with running an Oracle database outside Google Cloud.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Make sure that billing is enabled for your Google Cloud project.

This tutorial assumes you already have the following:

  • An Oracle Database Enterprise Edition 18c or later for Linux x86-64 that you want to migrate.
  • A Compute Engine running CentOS that has Striim installed. You can deploy Striim through the Google Cloud Marketplace solution.

Preparing the Oracle database

The following sections discuss configuration changes that you might need to connect to your Oracle database and migrate it with Striim. For configuration details, see Basic Oracle Configuration Task.

Choose the source for Oracle CDC

While there are different Oracle CDC sources, this tutorial uses LogMiner. You can read about alternate options in Alternate Oracle CDC sources.

Prepare the Oracle Database Enterprise Edition 18c (or later)

To prepare the Oracle database, follow the instructions on Striim documentation page for the following steps:

  1. Enable Striim's archivelog.
  2. Enable Striim supplemental log data.
  3. Enable Striim primary key logging.
  4. Create an Oracle user with LogMiner privileges for Striim.

    To run these steps, you must be connected to the container database (CDB), regardless of whether you're migrating a CDB or a pluggable database (PDB). We recommend installing and using SQL*Plus to interact with the Oracle database.

  5. Create a Striim quiescemarker table.

    Striim's Oracle Reader adapter for CDC needs a table for storing metadata when it quiesces an application. If you use LogMiner as a source for CDC (as this tutorial does), then you need the quiescemarker table. You must be connected to the CDB when following the steps to create the table.

  6. Establish network connectivity between your Oracle database and the Striim instance.

    By default, the Oracle listener is on port 1521. Ensure that the IP address for the Striim instance is allowed to connect to the Oracle listener port and that no firewall rules block it. The port on which the Oracle listener is configured is in the $ORACLE_HOME/network/admin/tnsnames.ora file.

  7. Note the system change number (SCN) for the Oracle database.

    The SCN is an internal timestamp that's used to reference changes made to a database.

    On your Oracle database, get the oldest SCN:

    SELECT MIN(start_scn) FROM gv$transaction;
    

    Copy this number. You'll need it later in the continuous replication pipeline steps.

Preparing the Striim instance

For information about the operating systems that Striim supports, see system requirements. To use the Oracle Reader with LogMiner, put the Oracle JDBC driver in the Java classpath in your Striim instance. Perform the following steps on each Striim server that runs an Oracle Reader adapter:

  1. Log in to your Oracle account, and then download the ojdbc8.jar file on your local machine.
    • If you don't have an Oracle account, create one.
  2. Click the Download link for the ojdbc8.jar file.

    • Click I reviewed and accept the Oracle License Agreement to download the file, if you accept the license terms.
  3. In Cloud Shell, create a Cloud Storage bucket, and upload the .jar file to it:

    gsutil mb -b on -l REGION gs://BUCKET_NAME
    gsutil cp PATH/ojdbc8.jar gs://BUCKET_NAME
    

    Replace the following:

    • REGION: the region where you want to create the Cloud Storage Bucket
    • BUCKET_NAME: the name of the Cloud Storage bucket where you want to store the ojdbc8.jar file
    • PATH: the path to where you downloaded the ojdbc8.jar file

    After the file is saved on your local machine, we recommend that you upload the .jar file to a Cloud Storage bucket so that you can download it to any instance.

  4. Open an SSH session with your Striim instance, and then download the .jar file onto your Striim instance and place it in the /opt/striim/lib directory:

    sudo su - striim gsutil cp gs://BUCKET_NAME/ojdbc8.jar /opt/striim/lib
    
  5. Verify that the ojdbc8.jar file has the correct file permissions:

     sudo ls -l /opt/striim/lib/ojdbc8.jar
    

    The output should appear as follows:

    -rwxrwx--- striim striim

  6. (Optional) If the .jar file doesn't have the preceding permissions, set the correct permissions:

    sudo chmod 770 /opt/striim/lib/ojdbc8.jar
    sudo chown striim /opt/striim/lib/ojdbc8.jar
    sudo chgrp striim /opt/striim/lib/ojdbc8.jar
    
  7. Stop and restart Striim.

    After making any configuration changes (such as the preceding permission changes), you must restart Striim.

    • If you're using the CentOS 7 Linux distribution, stop Striim:

      sudo systemctl stop striim-node
      sudo systemctl stop striim-dbms
      
    • If you're using the CentOS 7 Linux distribution, start Striim:

      sudo systemctl start striim-dbms
      sudo systemctl start striim-node
      

    If you want to learn more about stopping and restarting Striim for a different operating system, see Starting and stopping Striim

  8. Install the psql client on the Striim instance.

    You use this client to connect to the Cloud SQL instance and create schemas later in this tutorial.

Preparing the Cloud SQL for PostgreSQL schema

When you copy or continuously replicate tabular data from one database to another, Striim typically requires that the target database contains corresponding tables with the correct schema. Google Cloud doesn't have a utility to prepare the schema, but you can use the schema conversion utility from Striim or an open source utility like ora2pg.

Maintain foreign keys during the initial load

During the initial load phase, pay attention to the treatment of foreign keys. Foreign keys establish the relationship between the tables in a relational database. The out-of-order creation or insertion of a foreign key into the target database might destroy the relationship between the two tables. If the integrity between the two databases is compromised, errors might occur. Therefore, it's important to output all foreign key declarations into a separate file during the schema export later in this section.

During continuous replication in CDC pipelines, the source database events are propagated to the target database in the order they occur. If you correctly maintain foreign keys on your source, foreign key operations replicate from the source to the target database in the same order.

In contrast, the initial load pipeline defaults to loading your tables in alphabetical order. If you don't disable foreign keys before the initial load, foreign key violation errors occur. To replicate data during the initial load from the source database tables to the target tables on Cloud SQL for PostgreSQL, you must disable foreign key constraints on the tables. Otherwise, the constraints might be violated during the replication process.

As of June 2021, Cloud SQL for PostgreSQL doesn't support configuration options to disable foreign key constraints.

To handle foreign key constraints:

  1. Output all foreign key declarations into a separate file during the schema export.
  2. Create table schemas in the Cloud SQL for PostgreSQL database without the foreign key constraints.
  3. Complete the initial data replication.
  4. Apply the foreign key constraints on the tables.
  5. Create the continuous replication pipeline.

This tutorial offers two options for schema conversion, which the following sections explain:

Convert the schema using Striim's schema conversion utility

Use Striim's schema conversion utility to prepare Cloud SQL for PostgreSQL to integrate data with the target schema and create tables that reflect the source Oracle database.

The Striim schema conversion tool converts the following source objects into equivalent target objects:

  • Tables
  • Primary keys
  • Data types
  • Unique constraints
  • NOT NULL constraints
  • Foreign keys

Using Striim's schema conversion utility, you can analyze the source database and generate DDL scripts to create equivalent schemas in the target database.

We recommend that you manually create the schema in the target database by using the generated DDL scripts. It's easiest to select a subset of your tables, export the schema, and then import the schema into your target Cloud SQL for PostgreSQL database.

The following example demonstrates how to get your target Cloud SQL for PostgreSQL database ready for the initial load by importing your schema using Striim's schema conversion utility:

  1. Open an SSH connection to your Striim instance.

  2. Go to the /opt/striim directory:

    cd /opt/striim
    
  3. List all arguments:

    bin/schemaConversionUtility.sh --help
    
  4. Run the schema conversion utility and include the flags that are appropriate for your use case:

    bin/schemaConversionUtility.sh \
    -s=oracle \
    -d=SOURCE_DATABASE_CONNECTION_URL \
    -u=SOURCE_DATABASE_USERNAME \
    -p=SOURCE_DATABASE_PASSWORD \
    -b=SOURCE_TABLES_TO_CONVERT \
    -t=postgres \
    -f=false
    

    Replace the following:

    • SOURCE_DATABASE_CONNECTION_URL: connection URL for Oracle database—for example, "jdbc:oracle:thin:@12.123.123.12:1521/APPSPDB.WORLD" or "jdbc:oracle:thin:@12.123.123.12:1521:XE"
    • SOURCE_DATABASE_USERNAME: Oracle username to use to connect to the Oracle database
    • SOURCE_DATABASE_PASSWORD: Oracle password to use to connect to the Oracle database
    • SOURCE_TABLES_TO_CONVERT: table names from the source database that are used to convert schemas

    Make sure that you use the -f=false argument. This argument exports the foreign key declarations into a separate file.

    The output folder might contain some or all of the following files. For more details on these files, see Striim's schema conversion utility documentation.

    Output filename Description
    converted_tables.sql Contains all the converted tables that don't require any coercion
    converted_tables_with_striim_intelligence.sql Contains all the converted tables that have been converted with some coercion
    conversion_failed_tables.sql Contains tables where conversion was attempted, but a mapping was not obtained
    converted_foreignkey.sql Contains all foreign key constraint declarations
    conversion_failed_foreignkey.sql Contains all failed foreign key conversions
    conversion_report.txt Contains a verbose report of the schema conversion

    In this tutorial, you use the converted_tables.sql file to create equivalent tables in the Cloud SQL for PostgreSQL database without any foreign key constraints. After the initial replication, you use the converted_foreignkey.sql file to apply the foreign key constraints.

Convert the schema using Ora2Pg

Another option for converting Oracle table schemas to equivalent PostgreSQL schemas is the Ora2Pg utility. You can install this utility on a separate Google Cloud VM.

The Ora2Pg utility converts the Oracle schema and exports the DDL statements that