Database Migration (DMS): Offline & Online migrations

We’ll explain how to set up and use DMS to migrate an 11g database to an Autonomous Database Shared (ADB-S) instance on OCI online with the applications available for user activity during the migration. We will assume the landing zone has been set up and the VCNs, Compartments, policies, etc. have been done. We will provision and set up the target ADB and the Integrated Goldengate instance necessary for online migrations.

Guide on how to migrate an Oracle Database 11g on VMware On-Premise or Oracle Cloud VMware Service (OCVS) to an Autonomous Database using the Database Migration Service (DMS) with Integrated Goldengate

15/Oct/2024 | 35 min read

How DMS Migration Works

OCI Database Migration migrates Oracle databases from on-premises or cloud deployments to OCI and partner clouds, such as Oracle Database@Azure. The easy-to-use graphical user experience validates and manages migration workflows. Migration for Oracle Databases transparently uses Zero Downtime Migration, Cloud Premigration Advisor Tool, Data Pump, and GoldenGate for secure, fault-tolerant, and incremental migrations.

For this exercise, we will be migrating online an 11g (11.2.0.4) database on Oracle Cloud VMWare Solution (OCVS) Infrastructure to an Autonomous Transaction Processing (ATP-S) database using the Integrated Oracle Goldengate (OGG) and OCI Object Storage.

DMS Architecture Overview

Infrastructure Setup

Source Environment

Target Environment

The Autonomous Database Service

An autonomous database is a cloud database that uses machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by DBAs. Unlike a conventional database, an autonomous database performs all these tasks and more without human intervention.

Database configurations that influence the migration strategy

There are several technology factors to consider when choosing a migration strategy including your on-premises database version, the target database service, database version, your on-premise host operating system and version, and database character sets, etc. Gather the information below:

• Source database version

• Target database version (in this case 19c or 23ai for the ADB. We’ll use 19c)

• Endian format (byte ordering) of your on-premises database’s host platform

• Database character set of both your source and target databases.

• Right-sizing before cloud migration

Connectivity

You will need to connect your on-premise (in this case OCVS) database to your Oracle Cloud Infrastructure tenancy in order to perform the migration. Connectivity options are listed below in order of preference.

• FastConnect provides a secure connection between your existing network and your virtual cloud network (VCN) over a private physical network instead of the internet.

• Site-to-Site VPN provides a secure connection between a dynamic routing gateway (DRG) and customer-premise equipment (CPE), consisting of multiple IPSec tunnels.

• Internet Gateway provides a path for network traffic between your VCN and the internet.

Prerequisite/Assumptions

• Access to an OCI Tenancy

• Access to OCI DMS

• A VCN with an updated security list (Destination Port Range: 1521,22,443)

• Creating an OCI Vault

• Creating an Object Storage Bucket

• SSH key pair for logging into your Target database and the Goldengate environments (The key needs to be in RSA key in PEM format, other formats like OpenSSL are currently not supported.)

• User should have control access to use OCI DMS. An administrator can review Pre-built policy templates, to authorize the user based on the level of access. If they are granted the highest, then they will have all the related resources for their migration.

You can take a look at the following tutorial for reference: OCI Database Migration Service End-To-End Online Migration Tutorial

Required patches for the source 11g Database:

• Apply the latest Patch set updates (PSU).

• Apply Patch 20448066: DBMS_XSTREAM_GG APIS SHOULD BE ALLOWED FOR SCA PROCESSES

• Apply Patch 17030189: LOGMINER GG DICTIONARY SUPPORT: MISSING ATTRIBUTES

Patches 20448066 and 17030189 aren’t mandatory for an offline migration.

Known Issue:

OGG-02912: Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later. (Doc ID 2304095.1)

After applying patch 17030189, in case you still see the OGG-02912, run the “prvtlmpg.plb” on the database. As noted in Doc ID 2304095.1 this script can be found in any existing Goldengate home directory as shown below:

NLS_CHARACTERSET

Both source and target databases should have the same NLS character. You can check by running the below query:

SQL> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET';

Database preparation utility

Prepare your databases using either of the following methods:

• Prepare your database by running scripts generated by the database preparation utility (Recommended option).

• Manually configure your databases by following the documentation and running the SQL commands

Both options work fine, however, in this guide, we use the database preparation utility. It accepts inputs that are specific to your migration and generates a SQL script that you run on your source and target databases. After analyzing your databases for any missing or required configurations as well as privileges, it generates a final script that performs the required operations on your databases to prepare them for migration. The below image displays how to run this script:

Note:

When setting up your migration through the OCI Console:

• Use the system user as the Initial Load Database Username when creating a database connection for the source database on DMS

• Use the ggadmin user as the Goldengate Database User on the source database when setting up the migration using DMS

The dms_prep_db.sql script is generated by preparation utility. Please review this script, modify it as necessary, and run it on your databases. Your source database will be ready for migration after the execution of these operations.

SQL> @dms_prep_db.sql

Sample Schemas and Data on the Source Database

<<Include MEDREC data to prove a successful migration on the target>>

See *Annex

Disable Oracle Database Vault before the migration

You can re-enable Oracle Database Vault after you complete the migration tasks.

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault’; (Value-> should show False)

Reference

• Disabling and Enabling Oracle Database Vault

• How To Uninstall or Reinstall Database Vault in 11g (Doc ID 803948.1)

Create the Data Pump directory.

On the source database host, create a directory that can be used by Data Pump for exported dump files:

sudo su – oracle

mkdir -p /u01/app/oracle/dumpdir

SQL> Create or Replace directory dumpdir AS '/u01/app/oracle/dumpdir';

SQL> grant read, write on directory dumpdir to system;

Managing Connections

After completing the database preparation phase, we setup the source and target database connections on DMS using the OCI Console next.

For secure connections, you can use any encryption method like SSL or SSH. Both these technologies help to encrypt and authenticate the data. SSL uses port 443 to create encrypted connections.
To safely access HTTP URIs and Object Stores, a wallet is required with appropriate certificates for the object stores. All Object Storage access is done through https and requires the database to have the certifications of all trusted locations. You need to create a security wallet containing the certificates.
To achieve HTTPS connectivity, use SSL (Recommended) and perform the following steps in the source database host. (Refer How to setup ACL Doc ID 2357072.1)

• Create SSL Wallet with Certificates

• Set up Network ACL

Note:

• The wallet has to be created with auto-login capabilities.

• On RAC installations, the wallet must either be accessible for all nodes centrally or create the wallet on all nodes for local wallet storage.

You can either download a pre-created wallet or manually create a wallet. Please refer SSL and Network ACL. In this guide, we will download a pre-created wallet, however below are the steps to manually create a wallet.

Manual creation of Wallet:

Download the necessary certificates.

• Extract the files: $tar xvf dbc_certs.tar

• Create the wallet directory by running the following command:

• mkdir -p /u01/app/oracle/dumpdir/wallet

• mkdir -p /u01/app/oracle/dumpdir/cert

• Extracted the files: $tar xvf dbc_certs.tar in dir "/u01/app/oracle/dumpdir/cert"

Create an auto-login wallet

Run the following command:
orapki wallet create -wallet /u01/app/oracle/dumpdir/wallet -pwd WalletPasswd123 -auto_login

Import SSL certificates to a new auto-login wallet created under this new directory:

-bash-4.2$ for i in s /u01/app/oracle/dumpdir/cert/*.cer
> do
> orapki wallet add -wallet . -trusted_cert -cert $i -pwd WalletPasswd123
> done

On the source database, the user can setup ACL by following steps:

SQL> exec dbms_network_acl_admin.create_acl(acl => 'www.xml',description => 'WWW ACL', principal => 'SYSTEM',is_grant => true,privilege => 'connect');

SQL> exec dbms_network_acl_admin.assign_acl(acl => 'www.xml',host => '*',lower_port => 443, upper_port =>443);

SQL> exec dbms_network_acl_admin.add_privilege(acl => 'www.xml',principal => 'SYSTEM',is_grant => true,privilege => 'connect');

Once the connect privilege is granted, connect as the relevant user such as, SYSTEM, and verify if the privilege is granted using the following query:

OCI Database Migration Service Console

Navigate to Migration & Disaster Recovery --> Database Migrations --> Database Connections

Creating a private endpoint to access this database indicates whether the database is publicly accessible or if you want to create a private endpoint. Check this box if you plan to connect your database over a private IP address. Do not check it if the database has a public IP address. Check the box and select the private subnet.

Under ‘Show Advanced option’, you can provide the SSH information for your database hosts if you wish to provide SSH access. In this guide we use SSL.

Click the Create button and test your connection.

Target DB Preparation

Connect to the target database and unlock the default ggadmin user if it hasn’t been done yet.

SQL> alter user ggadmin identified by <<password>> account unlock;

When setting up your migration on the OCI Console:

• Use the ADMIN user as the Initial Load Database Username when creating a database connection for the source database on DMS

• Use the ggadmin user as the Goldengate Database User on the source database when setting up the migration using DMS

Create Target Database Connections

Create Migration

Navigate to the database migration page, and select Create Migration:

The source database is single instance 11g database, so we won’t the pluggable database option

Select data pump via object storage and enter the directory name and path created earlier. Also enter the source database SSL wallet path:

Check the ‘Use online replication’ box.

For Online Replication, the user has two Goldengate options:

• The managed option (recommended) is simpler and faster (DMS integration with Goldengate). This is what we use in this guide and …

• Manually deployed and managed a Goldengate marketplace instance in their tenancy. Please see this blog on how to deploy the marketplace instance.

For reference, please follow Managing Migrations (oracle.com)

Click the ‘Replication’ Tab

You can edit the Goldengate parameters and scroll to the bottom. Do not check the ‘Use Marketplace Goldengate Instance’ box. You can make changes across the ‘Initial Load’ tab i.e. Full or Schema loads or how CPAT runs in the ‘Validation’ tab. When done, click on the Create button.

Migration Settings Overview

This shows a summary of your choices in the migration creation wizard. You can then click on the ‘Validate’ button to check if all’s fine before the actual validation.

Migration Validation

The validation job runs through the phases as shown below. If you deem fit, you can edit out CPAT runs (if it passes) for every subsequent validation job.

Succeeded Job

You can download the validation log as highlighted below, review and respond if any actions are needed.

Start the Migration

Click the ‘Start’ button to begin the migration.

Wait for each phase to execute

When all phases are marked complete, your migration is successful.

You can also, Press Start to begin the migration. The ‘Start migration’ dialog is shown. Select the default phase: Monitor replication lag. This will cause the replication to run continuously until the Migration is resumed:

Navigate to Jobs in the left-hand Resources list:

• Click on the most recent Migration Job

• Click on Phases in the left-hand Resources list.

• Job phases are updated as the migration progresses.

• Wait till the Monitor replication lag phase completes.

You can see Job phases have been completed up to Monitor Replication lag.

Note: During DMS migration job workflow replication phase, all data and metadata operations in transactions committed after the initial load are replicated until you resume the migration job after the Monitor Replication Lag phase.

At this point, both Source and Target are in sync

**MEDREC Sample Schema

You can now press Resume on the job to complete replication. In the Resume Job dialog, choose the Switchover phase and press Resume. The Switchover phase will gracefully stop replication and signal the target application to initiate transactions to the target DB:

Switchover is in progress. You can also monitor the progress in work requests:

After the Switchover phase is completed, then the job will wait for the final step. Resume again for the last Cleanup phase to complete:

The migration runs and shows as Succeeded when finished:

Annex

*MEDREC App

• MEDREC Application. A sample medical schema with patient, physician, medical records and drug data. This is the schema migrated from the source DB to the ADB instance. Tests on ADB should reflect the source data.

**MEDREC

• MEDREC Test Schema details