14 Migrate New Data in Real Time from Oracle Database Classic to Oracle Cloud Infrastructure

You can migrate a single-instance database, created on Oracle Cloud Infrastructure Classic, to an Oracle Cloud Infrastructure virtual machine database system, by using Data Integration Platform Cloud. You can use this procedure as part of an overall migration of your Oracle Cloud environment to Oracle Cloud Infrastructure.

About Using Data Integration Platform Cloud to Migrate Oracle Database Cloud Deployment to Oracle Cloud Infrastructure

Using Data Integration Platform Cloud (DIPC), you can perform real-time data migration. The DIPC migration process watches the source database and updates the target with every change that happens on the source.

• When to Use DIPC for Migrating an Oracle Database Cloud Deployment to OCI
• Architecture
• What You Need to Perform the Migration
• How to Migrate an Oracle Database Cloud Deployment to OCI Using DIPC

When to Use Data Integration Platform for Migrating an Oracle Database Cloud Deployment to Oracle Cloud Infrastructure

Use Data Integration Platform Cloud to migrate new data in real time from Oracle Database Classic to Oracle Cloud Infrastructure.

To use Data Integration Platform Cloud for migration, your source database should be an Oracle Database Classic instance and the target should be an Oracle database system on a virtual machine on OCI. This migration is application for Oracle database 11g to 11g, 11g to 12c, and 12c to 12c.

This migration method watches the source database and updates the target with every change that happens in the source. Every new insert, delete, or update that happens on the source will be reflected in the target database in real-time. Before you start this real-time migration, you must create the target database with the same schemas and tables that you want to replicate.

Use DIPC for migration in the following scenarios:

• When you don’t want to shut down your source production database.
• When you want to perform real-time migration of Oracle databases 11g to 12c, which is more complicated with other migration methods.
• When your new database only requires a copy of your source database from a certain point of time that you choose. This point of time has to be after you start the migration. The target database can be used for data analytics, to take the query load off your source database. It can also be used as a more current database, which doesn't include historical data from the source database.

Limitations:

• Replicate Data does not perform an initial load. For a complete migration of the source database, including the historical data, use Oracle Data Pump or Oracle Recovery Manager (RMAN) to copy the initial load from source to the target first and perform this real-time migration after that.
• The change in the source database isn't available for processing until after the Data Manipulation Language (DML) commands such as insert, update and delete have been committed. Therefore, only transactions are replicated in Replicate Data.
• Data Definition Language (DDL) operations such as drop or create are not part of the replication process.
• Replicate Data is one to one and uni-directional, which means that it accepts one source and one target for each task. If you want to include several sources to deliver data to one target, you can create a Replicate data task for each source and have them all deliver to the same target.

Architecture

You can migrate your data from an Oracle Database Classic instance hosted on Oracle Cloud Infrastructure Classic to a virtual machine database system on Oracle Cloud Infrastructure (OCI). Oracle Database versions 11.2.0.4, 12.1.02 and 12.2.0.1 are all supported.

Here is how data flows when Data Integration Platform Cloud replicates your data:

Your source database is on OCI Classic. You create a target database on OCI. An application on OCI called Oracle Data Integration Platform Cloud (DIPC) orchestrates data replication from the source database to the target database. To perform the replication, you set up two DIPC remote agents on two compute instances: one for source and one for target. The agents have some binaries called Oracle GoldenGate (OGG) that can send data information from source to target.

The data flows from source database directly to the source agent and from there to the target agent and finally to the target database. It doesn't go to the DIPC host. Only monitoring information is exchanged between the agents and the DIPC host.

The data that flows from source agent to the target agent has to pass through a firewall. You open up some ports on the target compute instance, so that the data can come in. You don't need to open any ports on the source side, because it's a one-way replication and data only goes out.

The connections between each database and its agent is through a private IP address. The compute instance on OCI Classic should be on the same network as the Database Classic instance. Similarly, the Compute instance on the OCI system should be on the same VCN as the virtual machine database system. With this setup, you don't need VPN to have the agents access the databases.

The connection between the agents and their DIPC host is through an outgoing https connection. Information only goes out to the DIPC host for monitoring the data flow.

The following diagram shows the migration process:

What You Need to Perform the Migration

Before you begin the database migration, in addition to knowledge of Oracle Cloud Infrastructure, you must have knowledge in several areas of Oracle Database tools.

You will need the following components or tools:

• An Oracle Cloud Infrastructure account with a role to create compartments, subnets, target database, and Data Integration Platform instances.
• An account on Oracle Support to create a service request.
• Tools to create SSH keys, such as PuTTY or SSH
• Tool to connect to VMs, such as PuTTY
• Tool to connect to databases, such as SQL developer or you can SSH to the database directly

You should be familiar with the following tasks:

• Setting up compartments, subnets, and databases in Oracle Cloud Infrastructure (OCI)
• Creating SSH keys using either PuTTY or SSH
• Using SQL*Plus
• Performing Oracle database management tasks, such as creating schemas, users, and tables.

Note:

You must create a service request through Oracle Support. Ask them to reserve a Data Integration Platform Cloud instance for you. Otherwise, you will get an error message that you don't have enough resources.

How to Migrate an Oracle Database Cloud Deployment to Oracle Cloud Infrastructure by Using Data Integration Platform

You perform the following tasks to migrate an Oracle Database Classic cloud deployment to Oracle Cloud Infrastructure by using Data Integration Platform Cloud.

• Prepare for Migration
  • Create a Data Integration Platform Cloud instance
  • Set up the agents
  • Prepare source database
  • Prepare target database
• Migrate the Database
  • Create Connections
  • Create a Replicate Data Task
  • Run the Replicate Data Task

Prepare for Migration

To prepare for migration with Data Integration Platform Cloud perform the following tasks:

1. Create a Data Integration Platform Cloud Instance
2. Set Up the Agents
3. Prepare Source Database
4. Prepare Target Database

Create a Data Integration Platform Cloud Instance

Data Integration Platform Cloud (DIPC) is an all-in-one platform with graphical menu options for data preparation, integration, replication and quality management.

Here are the steps to create a Data Integration Platform Cloud instance:

1. Log in to Oracle Cloud Infrastructure home page.
2. Click the menu option for Oracle Cloud.
3. Click My Services Dashboard.
4. If you land on the Guided Journey page, then click Dashboard.
5. Click Customize Dashboard.
6. Under Integration, click Show for Data Integration Platform.
7. Close the Customize Dashboard window.
8. Confirm that Data Integration Platform appears as a tile on your dashboard.
9. In the Data Integration Platform tile, click Open Service Console from the Action Menu.
10. If you encounter a welcome page, click Go to Console.
11. Don't click the prerequisite link on the page, because it is an old link and gives an error message. Currently the prerequisites are created for your when you create the DIPC instance. So there are no prerequisites, if you can already create an instance.
12. Click Create Instance.
13. Complete the Instance details. For example:
    • Instance Name: ABCMigration
    • Description: Data Migration from the ABC database to OCI
    • Email: an email address to receive a notification when your instance is ready
    • Region: No Preference
    • Tags: Click the Click to Create a Tag button and create a tag called Migration and then click OK. (optional)
    • License Type: Subscribe to a new Data Integration Platform Cloud software license and the Data Integration Platform Cloud.
14. Click Next.
15. Complete the Service Details. For example:
    • Service Edition: Enterprise Edition

      Note:

      You must either choose Enterprise or Governance Edition. Enterprise Edition offers choices for selective copy or real time replication of your data sources. This edition also offers extract, load and transform (ELT) options for your data entities. Governance Edition includes Enterprise features plus additional data quality management options. Standard Edition is for ELT only and does not offer you a choice to perform a migration.
    • Data Volume - GB: Select an option to be billed based upon. You're charged a monthly flat rate based on the volume of data processed per hour for data movement from/to Data Integration Platform Cloud. For more information, see Pricing.
    • Instance Reserved: Enable this checkbox if an instance has already been reserved for you. If you have not reserved an item, you can't create an instance. Submit a request to reserve a DIPC instance for you through Oracle Support.
16. Click Next.
17. If you need to make changes, use the Previous button to navigate back to the Instance or Details pages, and then come back to this page and confirm and create the instance.
18. Confirm that the name of your instance is in the list of Instances and the field Created On displays the creation date.

Set Up the Agents

Agents are applications that you allow to securely connect to data sources and orchestrate data integration activities. An agent's components depends on the activities that it orchestrates. For example, data replication and adding data to data lake have different components.

For data migration between two Oracle databases, you set up two agents:

• A source agent to capture data activities from the source and send it to the target agent
• A target agent to receive information from the source agent and apply the data changes to the target database

The best location for the source agent is on OCI-Classic where the Oracle Database Classic is. Similarly, the best place for the target agent is on OCI, in the same region as the target database system. A Compute instance is a physical location that you can create on OCI or OCI classic and then copy your agent binaries there.

Here are the steps to set up your agents:

1. Download Agent Binaries
2. Create a Location for Agents
3. Register the Agents
4. Run the Agents

Download Agent Binaries

To set up an agent, you must first download the proper agent binaries from the Data Integration Platform console to your machine.

If both source and target databases have the same version, then you can download the binaries once and use it twice.

Here is how you can get your agent binaries:

1. On My Services Dashboard, click the Action Menu for the Data Integration Platform tile and then click Open Service Console.
2. In the Instances page, locate your Data Integration Platform Cloud instance. For example, ABCMigration
3. From the instance's Manage this instance menu, select Data Integration Platform Console.
4. Click Download in the Agents tile.
5. Select the operating system and components:
   • Operating System: This option is not your database's operating system. It is the operating system of the location that the agent will be installed. Select Linux 64-bit, because both the OCI and OCI Classic Compute instances have Linux operating systems.
   • Connectors/Components: For this option, either choose Oracle 11g (OGG) or Oracle 12c (OGG). This option provides the replication binaries that match your Oracle database version.
6. Click Download.
7. Save the binaries so that later you can transfer it to a Compute instance.

Create a Location for Agents

Each Data Integration Platform Cloud agent must have access to a data source to orchestrate its data integration activities. Compute instances are ideal locations for agents that manage cloud database activities.

For an Oracle Database Classic, the best place to put the agent binaries is on a Compute Classic instance. Similarly, for a database on OCI, the best place for the agent is on a Compute instance on OCI. This way, you don't need to open any ports between each database and its agent and all that communication is done through private IP addresses. The only port you open, is on the target Compute instance to allow the source agent to send information to the target agent.

Here are the steps to create a location for your source and target agents.

1. Create a Compute Instance for Source Agent
2. Add Java to Your Compute Instance
3. Create a Compute Instance for the Target Agent
4. Open Ports on Target Compute Instance

Create a Compute Instance for Source Agent

If you have an Oracle Database Classic, then, place its agent binaries on a Compute Classic instance located in the same region as the database.

Here are the steps to create a Compute instance for your source agent:

1. Go to My Services Dashboard.
2. Click Customize Dashboard.
3. Click Show for Compute Classic.
4. Close the Customize Dashboard window.
5. In the Compute Classic tile, click Open Service Console from the Action Menu.
6. Click Create Instance.
7. Click Customize.
8. For Image, select the latest one. For example, OL_7.2_UEKR4_x86_64 which is an Oracle Linux 7.2 UEKR4 and proceed to the Shape page.
9. For Shape, select oc5 which gives you 4 OCPUs and 30GB of memory.
10. Proceed to the Instance page.
11. For Placement, select the same domain or location as the source database.
12. Add your public Key to SSH Keys.
13. Proceed to the Network page.
14. For Network options, select IP Network.
15. Click the Action Menu for the listed default network and then select Update.
16. Set the Public IP Address to Auto Generated and then click Update.
17. Name your DNS Hostname Prefix and then proceed to the next page.
18. Click the Action Menu for the listed default storage and then select Update.
19. Update the storage Size to 120 GB.
20. Confirm that the new storage size is 120 GB and then proceed to the Review page.
21. Review all your options and either go back to modify the fields or if you agree with your options, then click Create.

Add Java to Your Compute Instance

Data Integration Platform agents refer to $JAVA_HOME to perform their activities. You must set up $JAVA_HOME to point to JDK with a Java version of 1.8 or higher, before you set up your agents.

Compute instances don't come with JDK, so you must install JDK on your Compute instances and then have $JAVA_HOME point to JDK.

1. Download JDK with Java version 1.8 or higher from Java SE Downloads
2. Use an application that has Secure Copy Protocol (SCP) such as WinSCP or SSH to transfer the JDK execution file to your Compute instance. Provide the IP address and the private key that matches the public key that you gave to your Compute instance to connect to it. The username is opc.
3. Use PuTTY or SSH to connect to the Compute instance. For instructions on how to connect to instances with PuTTY, see Connect to an Oracle Cloud Instance From Windows Using PuTTY.
4. Install JDK on the Compute instance.
5. Ensure that your environment is pointing to the correct Java.
6. Ensure that JAVA_HOME points to your JDK. If not, then set it to point to the right location.

Example:

[xxx]$ java - version
java version "1.8.0_91"  
  
[xxx]$ which java
~/Public/JDK/bin/java

[xxx]$ echo $JAVA_HOME
/home/oracle/Public/JDK

Create a Compute Instance for the Target Agent

The Compute instance that you create for the target agent is similar to the source Compute, except that it's on Oracle Cloud Infrastructure and it must be much larger than the source Compute. Ensure that the storage size that you allocate for the target is 2TB or larger.

The target agent receives and stores all the information about the changes in the source database in the target Compute storage. Because these files are constantly growing, you must have a large storage to ensure a great performance. Here are some guidelines to create a Compute instance on OCI.

1. Log in to Oracle Cloud Infrastructure.
2. Select a region on the top navigation bar that matches your target database region.
3. From the Action Menu, go to Compute and then Instances.
4. Click Create Instance.
5. Select Virtual Machine for instance type.
6. For Shape select VM.Standard2.1
7. Select Custom boot volume size.
8. Enter 2000, because the size you enter is in GBs and 2TB is equivalent to 2000 GB.
9. Enter an SSH public key with the .pub extension. Ensure that you have the matching private key on your machine.
10. Refer to Launching Your First Linux Instance for additional guidance.
11. Click Create.
12. Install JDK for Java 1.8 or higher on your Compute instance.
13. Have $JAVA_HOME point to your JDK.

Open Ports on Target Compute Instance

Data Integration Platform Cloud source agents send data replication information to target agents. The only way for the target agents to receive this information is to open ports on the Oracle Cloud Infrastructure (OCI) Compute instance where they are located.

You must open port 7809 for the agent application that manages the replication and 7819-7829 for the data collector application. Here are steps on opening ports for a Compute instance on OCI:

1. Log in to OCI.
2. Go to the region that your target database is located. For example, us-phoenix-1.
3. From the Actions Menu, go to Compute and then Instances.
4. Click the Compute instance hat you have allocated for your agent.
5. Click the link in the Subnet field. For example, Public_Subnet ilMx:PHX-AD-3
6. Click Security Lists from the list of Resources.
7. Click the default security list for your VCN. For example, Default Security List for DIPC-VCN.
8. In the Resources section, go to Ingress Rules.
9. Click Add Ingress Rules.
10. Enter the following information for your ingress rule:
    • SOURCE TYPE: CIDR
    • SOURCE CIDR: 0.0.0.0/0
    • IP PROTOCOL: TCP
    • SOURCE PORT RANGE: All
    • DESTINATION PORT RANGE: 7809
11. Click + Additional Ingress Rule.
12. Enter the following information for your ingress rule:
    • SOURCE TYPE: CIDR
    • SOURCE CIDR: 0.0.0.0/0
    • IP PROTOCOL: TCP
    • SOURCE PORT RANGE: All
    • DESTINATION PORT RANGE: 7819-7829
13. Click Add Ingress Rules.

Register the Agents

After you download your Data Integration Platform agent components, you perform certain steps and send special parameters to the Data Integration Platform host, so that it can discover the agent. This procedure is called registering the agent.

Here are steps to register an agent. Perform these steps twice. Once for the source agent and once for the target agent.

1. Use an application that has Secure Copy Protocol (SCP) such as WinSCP or SSH to transfer the agent zip file to the Compute instance you have allocated for the agent.
   Provide the IP address and the private key that matches the public key that you gave to your Compute instance to connect to it. The username is opc.
2. Use PuTTY or SSH to connect to the Compute instance.
   For PuTTY instructions, see Connect to an Oracle Cloud Instance From Windows Using PuTTY.
3. Unzip the agent package that's called something like DIPCAgent-18.4.3_Linux-x64.zip, into <agent_unzip_loc>.
4. Create a file under <agent_unzip_loc>/dicloud and call it agent_register.sh.
5. Copy the following code into the agent_register.sh file and then save it.

   ./dicloudConfigureAgent.sh <agent_name> -recreate -authType=OAUTH2 -idcsServerUrl=https://idcs-xxx.identity.oraclecloud.com -agentIdcsScope=https://xxx.adipc.opc.oraclecloud.com:443urn:opc:resource:consumer::all -agentClientId=xxx -agentClientSecret=xxx -dipchost=xxx -dipcport=443 -user=<your_Oracle_Cloud_user_name> -password=<your_Oracle_Cloud_password>

   The dicloudConfigureAgent.sh command sends several parameters to the Data Integration Platform host. With these parameters, the Data Integration Platform host can recognize the details of the agent location and display it in the Agents page.
   The -recreate parameter tells the dicloudConfigureAgent command to create a directory called <agent_name> and place all the agent configuration files, such as agent.properties in this directory. If an agent with the name <agent_name> already exists in the dicloud directory, then the existing agent directory is renamed and kept, before an <agent_name> directory is recreated.
6. Find the values for the following required parameters and update the agent_register.sh file one by one, with the correct values.
   • idcsServerUrl:
     1. On My Services Dashboard, click Customize Dashboard.
     2. Click Show for Identity Cloud and then close the window.
     3. On the Identity Cloud tile, click the Action Menu.
     4. Click View Details.
     5. From the identity section, copy the value displayed for Service Instance URL.
     6. Remove the /ui/v1/adminconsole section from the <idcsServerUrl>/ui/v1/adminconsole and copy the <idcsServerUrl> section for the value of the idcsServerUrl parameter.
   • agentIdcsScope, agentClientId, agentClientSecret: Create a confidential application and get the values from its detail page. See Create a Confidential Application for Agents.
   • dipchost:
     1. In the Data Integration Platform Console, go to the Agents page.
     2. Find the URL of the Agents page in the browser which has a format of https://<dipchost>/dicloud/app/agents
     3. For the value of the dipchost parameter, copy the <dipchost> section of the URL.
   • dipcport: 443
   • username: username for your Oracle Cloud user account
   • password: password for your Oracle Cloud user account
   Before you execute agent_register.sh, ensure that $JAVA_HOME is set correctly and you are using JDK for Java 1.8 or higher. See Add Java to Your Compute Instance
7. To register the agent, execute the agent_register.sh file.

Create a Confidential Application for Agents

A confidential application is a server-side application that uses OAUTH 2.0 to keep the confidentiality of the client secret. You can create a confidential application for your Data Integration Platform Cloud host. Then you can provide the client secret for the application to your agent so it can securely communicate with the DIPC host.

Here are the steps to make create a confidential application for your agent:

1. On My Services Dashboard, click Customize Dashboard.
2. Click Show for Identity Cloud and then close the window.
3. On the Identity Cloud tile, click the Action Menu.
4. Click View Details.
5. Click Open Service Console.
6. Click Applications.
7. Click Add.
8. Click Confidential Application.
9. In the App Details, enter a name for your application such as DIPC_Agent and then click Next.
10. In the Client page, click Configure this application as a client now.
11. For Allowed Grant Types, select Resource Owner, Client Credentials, JWT Assertion and Refresh Token.
12. Click Add for Scope.
13. Find the item with the format of DIPCINST_<Your_DIPC_Instance_Name> .See Create a Data Integration Platform Cloud Instance if you don't remember your Data Integration Platform instance name.
14. Click the arrow that indicates Select scopes for this resource.
15. Select the checkbox on the page for the resource and then click Back.
16. Select the checkbox for DIPCINST_<Your_DIPC_Instance_Name>.
17. Click Add.
18. Copy the link displayed for Allowed Scope to replace the value of the agentIdcsScope parameter in the agent_register.sh file. See Register the Agents
19. Click Next.
20. In the Resources page, click Skip for Later and then click Next.
21. In the Web Tier Policy page, click Skip for Later and then click Next.
22. Click Finish.
23. In the Application Added window, copy Client ID to replace the value of the agentClientId parameter in the agent_register.sh file. See Register the Agents.
24. In the same Application Added window, copy Client Secret to replace the value of the agentClientSecret parameter in the agent_register.sh file. See Register the Agents.
25. Click Close.
26. Click Activate.
27. Click Activate Application.

    Note:

    You must activate the application. Otherwise, your agent can't communicate with the Data Integration Platform host.

Run the Agents

After you register your agents with their Data Integration Platform host, you must start them, so you can use them for your data integration tasks.

Perform the following steps to start your agent:

1. Access the Compute instance that has a registered source agent, by using PuTTYor SSH.
2. Navigate to the <agent_unzip_loc>/dicloud/agent/<your_agent_name>/bin directory.

   If you didn't name your agent, then the default for <your_agent_name> is something like dipcagent001.

3. Run the following command:
   ./startAgentInstance.sh
4. Go to the Agents page in the DIPC console.
5. Find your instance name in the list of agents.
6. Ensure that its status is Running.
7. Repeat the same procedure for the target agent.

Prepare Source Database

To prepare a source database for migration with Data Integration Platform Cloud, you must either create a user on the source that is specific to the migration process or give an existing user specific data integration rights.

Perform the following steps to create a user at the CDB level:

1. Connect to the VM of the source database through SSH, PuTTY or SQL Developer.

   For guidance, see Find the IP Address of an Oracle Cloud Service VM, Change Private Key Format to Use with PuTTY and Connect to a Cloud VM on Windows with PuTTY. To connect to the VM with PuTTY use the opc username.

2. sudo to the oracle user with the sudo -su oracle command.
3. Ensure that the source database is running.

   For guidance, see Ensure That the Database to be Migrated Is Running

4. Connect to the database on the VM, at the CDB level, as the admin user.
5. Run the following commands to create a user called C##GGSRC:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATE=TRUE SCOPE=BOTH;
   CREATE USER C##GGSRC IDENTIFIED BY <PASSWORD> DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
   GRANT RESOURCE, CONNECT, DBA to C##GGSRC CONTAINER=ALL;
   EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE =>'C##GGSRC, CONTAINER => 'ALL');

6. Ensure that you can log in to the source database as C##GGSRC.

Prepare Target Database

Before you migrate your data, you must create a database system with a virtual machine shape on Oracle Cloud Infrastructure. Then you must set up the target database with the same schema and structure as the source database.

Note:

Data Integration Platform Cloud's Replicate Data task doesn't perform an initial copy of the source, so you'll get all the changes from the point of time that you start your job. If you want the target database to be exactly like the source database, then you must perform an initial load first. For initial load, you can use the opcmigrate tool to create a copy of the source database. This method creates the database for you. See Migrate Databases Using the Migration Tools.

If you don't want an initial load, then create a database system with a virtual machine shape type. Then create the the schema that you want to replicate from the source database. Then create the tables and the structure for this schema. Replicate Data task doesn't perform any DDL commands.

After you define the schema and structure for the target database, then perform the following steps:

1. Connect to the VM of the target database.

   You can find the IP address of the database in the database detail page. For guidance with PuTTY, see Change Private Key Format to Use with PuTTY and Connect to a Cloud VM on Windows with PuTTY. To connect to the VM with PuTTY use the opc username.

2. sudo to the oracle user with the sudo -su oracle command.
3. Ensure that the target database is running.
4. Connect to the database at the PDB level.

   For example for a PDB with name PDB1:

   CONNECT SYS/<PASSWORD>#@PDB1 AS SYSDBA
   

5. Run the following commands to create a user called DIPC_TRG:

   CREATE USER DIPC_TGT IDENTIFIED BY PASSWORD;
   GRANT CONNECT, RESOURCE, CREATE SESSION, SELECT ANY TABLE, CREATE ANY TABLE, CREATE ANY VIEW, CREATE ANY PROCEDURE, CREATE DATABASE LINK, SELECT ANY DICTIONARY, EXP_FULL_DATABASE, IMP_FULL_DATABASE TO DIPC_TGT;
   GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO DIPC_TGT;
   GRANT UNLIMITED TABLESPACE TO DIPC_TGT;
   EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('DIPC_TGT');
   

6. Ensure that you can log in to the target database as DIPC_TRG.

Migrate the Database

To perform a real-time migration you can use the Replicate Data Task of Oracle Data Integration Platform Cloud. The Replicate Data Task captures changes in your data source and updates the target in real time with that change.

Here is a summary of steps to use the Replicate Data Task:

1. Create Connections
2. Create a Replicate Data Task
3. Run the Replicate Data Task

Create Connections

A Connection in Data Integration Platform Cloud is a set up to establish a connection to a data source. The first step for a migration or replication is to define the source and target Connections.

After you create your Connections, they are ready to be used in your data integration tasks including the Replicate Data Task.

If a source database is a PDB database, the Replicate Data Task captures the data from the source at the Container Database (CDB) level. Even though you select a schema within a PDB to be replicated, the task happens at the CDB level. Therefore, if your source is an Oracle 12c with PDB, you must create an additional CDB Connection for the Replicate Data Task. Replicate Data Task doesn't use a CDB connection for data delivery, so you don't need to set it up for the target connection.

Here are the type of Connections that you must create for your real-time migration:

Table 14-1 Source Connection Types

Source Database	Connection Type
Oracle 12c	Oracle CDB and then Oracle
Oracle 11g	Oracle

Table 14-2 Target Connection Types

Target Database	Connection Type
Oracle 12c	Oracle
Oracle 11g	Oracle

1. Find Source Database Information
2. Find Target Database Information
3. Create a CDB Connection
4. Create a Source Connection
5. Create a Target Connection

Find Source Database Information

When you create an Oracle Connection in Data Integration Platform Cloud, you must provide host name, service name and the database port number.

Here are steps to find Oracle Database Classic connection information:

1. Go to My Services Dashboard.
2. Open Service Console for Database Classic.
3. Click the name of the source Database Classic instance.
4. Copy the value from the Public IP field.
5. Use SSH or PuTTY to connect to the VM that hosts the source database by using its IP address.
   • PuTTY example: Connect to a Cloud VM on Windows with PuTTY
   • SSH example: xxxx$ ssh opc@<source database IP address>
6. Use sudo to become the oracle super user:

   [opc@<database> ~] sudo su - oracle
   [oracle@<database> ~]

7. Enter the following command:

   [oracle@<database> ~ lsnrctl status
8. Find the host name, port and service name from the output.

   Table 14-3 Source Database Connection Information

   Field Name for Connection	Relevant String	Section to Copy
   Hostname	Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.compute-xxx.oraxxx)(PORT = <port number>))	hostname
   Port	Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.compute-xxx.oraxxx)(PORT = <port number>))	<port number> default value: 1521
   Service Name for CDB Connection	Instance "ORCL", status ready, has 1 handler(s) for this service.... Service "ORCL.xxx.oraclecloud.xxx" has 1 instance(s).	ORCL.xxx.oraclecloud.xxx
   Service Name for PDB Connection	Instance "ORCL", status ready, has 1 handler(s) for this service.... Service "<PDB name>.xxx.oraclecloud.xxx" has 1 instance(s).	<PDB name>.xxx.oraclecloud.xxx default value: pdb1.xxx.oraclecloud.xxx

   Note:

   You can use the same procedure to find service name for Oracle 11g databases that don't have a PDB or CDB.

Find Target Database Information

When you create an Oracle Connection in Data Integration Platform Cloud, you must provide host name, service name and the database port number.

Here are steps to find connection information for an Oracle database system on Oracle Cloud Infrastructure:

1. Go to Oracle Cloud Infrastructure Home page.
2. Click the menu option for Oracle Cloud.
3. In the Database category, click Bare Metal, VM, and Exadata.
4. Copy the Public IP address for the target database.
5. Use SSH or PuTTY to connect to the VM that hosts the target database by using its IP address.
   • PuTTY example: Connect to a Cloud VM on Windows with PuTTY
   • SSH example: xxxx$ ssh opc@<source database IP address>
6. Use sudo to become the oracle super user:

   [opc@<database> ~] sudo su - oracle
   [oracle@<database> ~]

7. Enter the following command:

   [oracle@<database> ~ lsnrctl status

8. Find the host name, port and service name from the output.

   Table 14-4 Target Database Connection Information

   Field Name for Connection	Relevant String	Section to Copy
   Hostname	Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = <private_ip_address>)(PORT = <port number>))	<private_ip_address> default value: 10.0.0.2 or 10.0.0.3
   Port	Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = <private_ip_address>)(PORT = <port number>))	<port number> default value: 1521
   Service Name for PDB Connection	Instance "ORCL", status ready, has 1 handler(s) for this service.... Service "<PDB name>.xxx.oraclevcn.com" has 1 instance(s).	<PDB name>.xxx.oraclecloud.xxx default value: pdb1.xxx.oraclecloud.xxx

   Note:

   You can use the same procedure to find service name for Oracle 11g databases that don't have a PDB or CDB.

Create a CDB Connection

For PDB database migration, you must first create an Oracle CDB Connection. Then you create a PDB Connection for your source database and associate this CDB Connection within your PDB connection.

Note:

A CDB Connection for a Replicate Data Task only works if the source agent is set up with Oracle 12c (OGG) binaries. You can download these binaries from the Agents page. See Download Agent Binaries

To create a CDB Connection:

1. Go to the Agents page of the Data Integration Platform Console.
2. Ensure that the source agent is listed and running.
3. Go to the Home page of the Data Integration Platform Console.
4. Click Create in the Connection tile.
5. Complete the fields in the General Information section.
   The Identifier is to identify this connection through a string with no spaces. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.
   • Agent: <source agent>
   • Type: Oracle Database
   • SubType: Oracle CDB
6. In the Connection Settings enter the Hostname, Port and Service Name. See Find Source Database Information .
7. Enter the rest of the fields for the connection:
   • Username: Use the username starting with C## that you created for capturing data such as C##GGSRC. See Prepare Source Database.
   • Password: Enter the password associated with the username.
8. Click Test Connection.
9. If the test succeeds, then click Save.

Create a Source Connection

Before you can perform a migration or replication in Data Integration Platform Cloud, you must create a Connection to the source database.

Note:

An Oracle 12c Connection for a Replicate Data task only works if its agent is set up with the Oracle 12c (OGG) binaries. Similarly, an Oracle 11g Connection only works for a Replicate Data Task if its agent is set up with the Oracle 11g (OGG) binaries. You can download these binaries from the Agents page. See Download Agent Binaries

To create a source Connection:

1. Go to the Agents page of the Data Integration Platform Console.
2. Ensure that the source agent is listed and running.
3. Go to the Home page of the Data Integration Platform Console.
4. Click Create in the Connection tile.
5. Complete the fields in the General Information section.
   The Identifier is to identify this connection through a string with no spaces. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.
   • Agent: <source agent>
   • Type: Oracle Database
   • SubType: Oracle
6. In the Connection Settings enter the Hostname, Port and Service Name. See Find Source Database Information .
7. Enter the rest of the fields for the connection:
   • Username: Use the username starting with C## that you created for capturing data such as C##GGSRC. See Prepare Source Database.
   • Password: Enter the password associated with the username.
   • Schema Name: You can use the default or any schema that you wish to replicate. You can only use one schema for the Replicate Data Task.
   • CDB Connection: The CDB connection you created for the source connection, if your source is a PDB.
8. Click Test Connection.
9. If the test succeeds, then click Save.

Create a Target Connection

Before you can perform a migration or replication in Data Integration Platform Cloud, you must create a Connection to the target database.

Note:

An Oracle 12c Connection for a Replicate Data task only works if its agent is set up with the Oracle 12c (OGG) binaries. Similarly, an Oracle 11g Connection only works for a Replicate Data Task if its agent is set up with the Oracle 11g (OGG) binaries. You can download these binaries from the Agents page. See Download Agent Binaries

To create a target Connection:

1. Go to the Agents page of the Data Integration Platform Console.
2. Ensure that the target agent is listed and running.
3. Go to the Home page of the Data Integration Platform Console.
4. Click Create in the Connection tile.
5. Complete the fields in the General Information section.
   The Identifier is to identify this connection through a string with no spaces. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.
   • Agent: <target agent>
   • Type: Oracle Database
   • SubType: Oracle
6. In the Connection Settings enter the Hostname, Port and Service Name. See Find Target Database Information.
7. Enter the rest of the fields for the connection:
   • Username: Use the username you created for Data Integration Platform Cloud such as DIPC_TGT. See Prepare Target Database.
   • Password: Enter the password associated with the username.
   • Schema Name: Use the same schema as the source.
   • CDB Connection: Leave blank.
8. Click Test Connection.
9. If the test succeeds, then click Save.

Create a Replicate Data Task

Here’s how you can set up a Replicate Data Task in Data Integration Platform Cloud.

1. Go to the Home page of the Data Integration Platform Console.
2. Click Create in the Replicate Data tile.
3. Name and describe your task in the in the General Information section. The Identifier is to identify this task through a string with no spaces. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.
4. Select an encryption level for the captured data that's sent across the network. The higher the number, the harder it is for hackers to decrypt the data. Choosing None will send your original data, without encryption to the target.
5. If you choose an encryption level, then generate a wallet according to Replicate Data - Generate Wallet and Master Key instructions, before you run the Replicate Data task.
6. Click Design to select your connections and mapping pattern.
7. Click Source to display properties for the source database.
8. In the Properties for Source, in the Details tab, select the PDB Connection that you have created for the source.
9. In the Properties for Source, in the Schemas tab, select a schema from the list and then click the plus icon to add it as a mapping rule.

   For example, if you select schema A and then click the plus icon, the rule Include A.* appears in the Rule Applied section.

   Only one rule is allowed, so only one schema can be included in a mapping rule. You can further narrow down a mapping rule for a schema by including a pattern. For example, instead of A.*, you can edit the field to A.EMP* to only include tables in schema A that start with EMP.

   To reset a mapping rule, you can click Reset. The default rule uses the schema assigned to the Source Connection. You can edit the default schema for a Connection in its detail page or override the rule here by replacing it with another schema by using the plus icon.

10. Click Target to display properties for the target database.
11. In the Properties for Target, in the Details tab, select the target database Connection.
12. In the Properties for Target, in the Schemas/Topics tab, select a schema in the target for an auto-match mapping pattern. See Replicate Data - What's Auto-match?:
13. For Mapping Pattern, select auto-match.
14. Click Save to save your task and run it later.

Run the Replicate Data Task

Here are the steps to run a Replicate Data Task:

1. Go to the Catalog page of the Data Integration Platform Console.
2. Change the filter option from All to Tasks.
3. Click the name of your Replicate Data Task.
4. If you want to change the schema, or view the details of this task, click Edit.
5. If you are happy with the setup, then click Run.
6. Wait until the Start Delivery option in the Monitor page shows a Running status.
7. Connect to the target database with SQL Developer. See Connect to a Database Cloud Service with Oracle SQL Developer. .
8. Go to the schema you chose for the target and perform some queries to monitor the replication.