5 Connecting to Data Sources

Learn about different methods of connecting Oracle GoldenGate data sources and targets and how to add Extract and Replicats.

• Configuring Data Sources for SQL Server and DB2
  After you have installed the SQL Server and DB2 client drivers, the next steps are to create connection data sources to your source and target databases.
• Creating Data Source Credentials in Oracle GoldenGate Microservices
  To create and run Extract and Replicat processes, enable Supplemental Logging and add Heartbeat and Checkpoint tables, you need to set up database credentials.
• Enabling Logging, Checkpoint Tables, and Implementing Heartbeat Monitoring
  After you have created Credentials for your source and target databases, you can enable transaction log for source databases, create checkpoint tables for target databases, and create heartbeat monitoring, all through the Credentials.
• Creating the Oracle GoldenGate CDC Cleanup Job for SQL Server
  After you have enabled TRANDATA for SQL Server, the next step is to disable the default SQL Server CDC Cleanup job and install the Oracle GoldenGate CDC Cleanup job.
• Connecting to Oracle Database (on-premises)
  
• Connecting Oracle GoldenGate on Marketplace with Different Oracle GoldenGate Instances
  Oracle GoldenGate on Oracle Cloud Marketplace enables you to connect and work with many existing and new Oracle GoldenGate instances.
• Connecting to Oracle Autonomous Database
  
• Connecting to DB2 z/OS (On-Premises)
  

5.1 Configuring Data Sources for SQL Server and DB2

After you have installed the SQL Server and DB2 client drivers, the next steps are to create connection data sources to your source and target databases.

For SQL Server, follow the instructions in Configuring a Database Connection on Linux and for DB2, see Using Oracle GoldenGate for DB2 for z/OS in Using Oracle GoldenGate for Heterogeneous Databases guide.

5.2 Creating Data Source Credentials in Oracle GoldenGate Microservices

To create and run Extract and Replicat processes, enable Supplemental Logging and add Heartbeat and Checkpoint tables, you need to set up database credentials.

1. Launch the Administration Services interface and log in.
2. Click Configuration from the Application Navigation pane.
3. Click the + sign next to Credentials, and set up your new credential alias, then click Submit.
4. Click the Login icon to verify that the new alias can correctly log in to the database.

   If an error occurs, then click the Alter Credential icon to correct the credential information, and then test the log in.

You can edit existing credentials to change the user name and password. Delete a credential by clicking the trash icon.

When you successfully log into your database, you can add and manage checkpoint tables, transaction information, and heartbeat tables. All of the tables can be searched using the various search fields.

5.3 Enabling Logging, Checkpoint Tables, and Implementing Heartbeat Monitoring

After you have created Credentials for your source and target databases, you can enable transaction log for source databases, create checkpoint tables for target databases, and create heartbeat monitoring, all through the Credentials.

For more information about how to create the processes, see Using Oracle GoldenGate Microservices Architecture in Using Oracle GoldenGate Microservices Architecture.

5.4 Creating the Oracle GoldenGate CDC Cleanup Job for SQL Server

After you have enabled TRANDATA for SQL Server, the next step is to disable the default SQL Server CDC Cleanup job and install the Oracle GoldenGate CDC Cleanup job.

For more information about these steps, see the Purging CDC Staging Data in Using Oracle GoldenGate for Heterogeneous Databases.

For the OCI Marketplace compute node, the ogg_cdc_cleanup_setup.sh file is located in the /u02/deployments/<deployment>/etc/conf/ogg directory.

5.5 Connecting to Oracle Database (on-premises)

You can use Oracle GoldenGate Microservices on Marketplace to remotely capture from and apply data to on-premises Oracle database resources. This allows you to enable replication and centrally manage the replication processes.

Use Cases for Replication

You can use Oracle GoldenGate Microservices to replicate data between data resources in the following use cases:

• Migrations
• Data Distribution
• Real-Time Data Warehousing
• Operational Reporting

Replicating Data from On-premises

Prerequisites

Ensure that the following are set up before you begin replication:

• Oracle GoldenGate Microservices
• Source Database
• Target Database

To move data from on-premises to the cloud or from on-premises to on-premises, perform the following tasks :

• Configure Oracle Database for Replication
  
• Configure Oracle GoldenGate Microservices Compute Node
  

5.5.1 Configure Oracle Database for Replication

To prepare your Database as a Service (DBaaS) instance for replication, perform the following tasks:

1. Configure Logging Properties
   • Enable Supplemental Logging
2. Enable Oracle GoldenGate within the Oracle Database
   • Update parameter for enable_goldengate_replication

For more details, see Preparing the Database for Oracle GoldenGate in Using Oracle GoldenGate for Oracle Database.

5.5.2 Configure Oracle GoldenGate Microservices Compute Node

To connect the Microservices Compute Node to any database, edit the tnsnames.ora file and point the entry to your database resources.

By default, the environment variable TNS_ADMIN is pre-configured for each deployment. But the files tnsnames.ora or sqlnet.ora are not readily available on the compute node. You need to create the files or copy them from an existing file.

Note:

• If you are using Oracle Autonomous products, see Configure Oracle GoldenGate for Replication with Autonomous Databases.
• If you want your networking directory structure to be consistent with other Oracle products, you must append /network/admin to the directory structure. For this you have to change the environment variable TNS_ADMIN. For more information on Local Naming Parameters, refer to Database Net Services Reference.

5.6 Connecting Oracle GoldenGate on Marketplace with Different Oracle GoldenGate Instances

Oracle GoldenGate on Oracle Cloud Marketplace enables you to connect and work with many existing and new Oracle GoldenGate instances.

This topic helps you with a few examples and steps required to set up and configure the architectures. The supported architectures are:

• On-premise to Oracle Cloud (Marketplace)
• Oracle Cloud to Oracle Cloud (Marketplace to Marketplace)

• Securely Connecting Oracle GoldenGate Microservices On Premise to Oracle GoldenGate Microservices on Marketplace
  
• Connecting Through Public IP Address
  

5.6.1 Securely Connecting Oracle GoldenGate Microservices On Premise to Oracle GoldenGate Microservices on Marketplace

You can connect your on-premises Oracle GoldenGate Microservices architecture to the Oracle GoldenGate Microservices architecture on Oracle Cloud Marketplace using the following methods:

• Connecting through Public IP Address
• Connecting through IPSec VPN
• Connecting through FastConnect

The following section helps you to connect through public IP address. To use the IPSec VPN or the FastConnect approach, refer to the respective VPN Connect and FastConnect documentation.

5.6.2 Connecting Through Public IP Address

By default, we configure Oracle GoldenGate Microservices on Oracle Cloud Marketplace behind the Nginx Reverse Proxy. This simplifies the architecture on Oracle Cloud and also makes the deployment secure. When you connect over a public IP address, on-premises Oracle GoldenGate Microservices architecture has to be secure.

When the deployment is secure and in order to connect to it securely, perform the following steps on the on premise machine and the Oracle Cloud compute node:

• In the On Premise Machine
  
• On Oracle Cloud Compute Node
  
• On Premise Machine
  

5.6.2.1 In the On Premise Machine

1. Test the connection to Oracle Cloud Compute Node using OpenSSL.

   $ openssl s_client -connect <public IP address>:443

2. Copy the ogg.pem file from the Oracle GoldenGate Microservices compute node to the on premise environment.
   1. Copy the ogg.pem to the local machine.

      $ scp opc@<public ip address>:/etc/nginx/ogg.pem 

   2. Check if the ogg.pem file is present on the local machine through the following command:

      $ ls -a

3. Update the local wallet for Oracle GoldenGate Microservices Distribution Service with the ogg.pem file. You have to assign it as a trusted certificate.

   Note:

   In Oracle GoldenGate Microservices 21c and later, certificates should be added using APIs.

   1. Find the required wallet.

      $ ps -ef | grep -i distsrvr

      1. Use the listed dat file.

         $ cat <dat file> | python -m json.tool

      2. Get the wallet location for the Distribution Service.

         For example: $DEPLOYMENT_HOME/etc/ssl/<distribution service wallet>

   2. Update the wallet.

      $ $OGG_HOME/bin/orapki wallet add -wallet <wallet directory> -trusted_cert -cert <certificate file path> -pwd <wallet password>

      For example:

      $OGG_HOME/bin/orapki wallet add -wallet /opt/app/oracle/gg_deployments/on-premises/etc/ssl/DistroClient -trusted_cert -cert /home/oracle/oci_cert.pem -pwd ********

   3. Check the wallet.

      $ $OGG_HOME/bin/orapki wallet display -wallet <wallet directory> -pwd <wallet password>

      For example:

      $OGG_HOME/bin/orapki wallet display -wallet /opt/app/oracle/gg_deployments/on-premises/etc/ssl/DistroClient -pwd ********

   4. As root user, update the on premise /etc/hosts file. You can find the information that needs to go in to the /etc/hosts file on the Oracle GoldenGate Oracle Cloud Compute Node Instance in the Details page. The required information includes:
      • Public IP Address
      • Internal FQDN

      From the Internal FQDN, you have to use the short hostname as well.

   5. Stop the Microservices components. Any running Extracts and/or Replicats are not affected.
      1. Stop the deployment
         • Login to ServiceManager.
         • Under Deployments, click Action, to stop the deployment.
      2. Start and Stop Deployments and Servers. See Setting Up Secure or Non-Secure Deployments in Using Oracle GoldenGate Microservices Architecture guide.
   6. Start the Microservices Components.
      1. Start the Deployment
         • Login to ServiceManager.
         • Under Deployments, click Action, to start the deployment.

5.6.2.2 On Oracle Cloud Compute Node

Follow the below steps from the Administration Service tab on the Oracle GoldenGate Microservices configuration page on Oracle Cloud:

1. Create a user, who can login to the environment and connect to the Receiver Service.
   • Log in to the Administration Service. For this, you will need the oggadmin password available in the ogg-credentials.json file, unless you changed it after the initial setup.

     https://<public id address>/<deployment name>/adminsrvr

   For example-

   https://<public ip address>/OCI-BASE/adminsrvr

2. Open the context menu and select the Administrator option.
3. Click the plus ( + ) icon, to add a new user with Operator role. This account is used by the on premise Distribution Service to login through the Reverse Proxy.
   Provide the following information:

   Username: streamnetwork
   Role: Operator
   Type: Basic
   Info: Network User
   Password: **********
   Verify Password: **********

5.6.2.3 On Premise Machine

In the on premise machine:

1. From the Administration Service tab on the on premise environment, create an alias that can connect to the network user created in the previous step. This alias is used by the Distribution Service to connect to the Receiver Service on Oracle Cloud.
   • Log in to the Administration Service.
   • Open the context menu and select Configuration.
   • Click the plus (+) icon, to add a new credential.
   • Add a new Credential by providing the following information:

     Credential Domain: Network
     Credential Alias: streamnetwork
     User ID: streamnetwork
     Password: **********
     Verify Password: *********

     Note:

     You cannot validate the new credential, as it is not logged in to the database.
2. In the Distribution Service on the on-premise environment,
   • Provide information needed for the Distribution Path. The basic information required are:

     Path Name: OP2OCI
     Reverse Proxy Enabled: Toggle to on
     Use Basic Authentication: Toggle to on
     Source: Select Extract and provide source trail file info
     Target: 
     	Keep the WSS protocol
     	Provide the Hostname of the OCI Compute node – IP will not work
     	Provide remote trail file name
     	Provide Deployment name
     	Domain: Network 
     	Alias: securitynetwork
     Trail Size (MB): set to desired size

   • Click Create or Create and Run.

5.7 Connecting to Oracle Autonomous Database

You can replicate data to an Oracle Autonomous Database instance and capture data from an Oracle Autonomous Database instance by using Oracle GoldenGate Microservices on Oracle Cloud Marketplace. The steps described in this section, streamline the approach for making a remote connection to Oracle Autonomous Database.

To learn about replicating data with an Autonomous Database, see Configure Autonomous Database Capture for Replication in the Using Oracle GoldenGate for Oracle Database guide.

• Configure the Autonomous Database for Replication
  
• Autonomous Database Client Credentials
  
• Configure Oracle GoldenGate Microservices for Replication
  Perform the following steps for establishing a successful connection to the Autonomous Database with Oracle GoldenGate Microservices.

5.7.1 Configure the Autonomous Database for Replication

Unlock the Pre-created Oracle GoldenGate User (ggadmin)

Perform the following steps to configure the Autonomous Database for Replication:

1. Unlock and change the password for the pre-created Oracle GoldenGate user (ggadmin) within the Autonomous Data Warehouse. Use any SQL client tool to unlock the account.

   For more details, see Configure the Autonomous Database for Replication in Using Oracle GoldenGate for Oracle Database.

   SQL> select * from dba_users order by username;
   SQL> alter user ggadmin identified by password account unlock;

2. Check whether the parameter enable_goldengate_replicaton is set to true. If not, then modify the parameter.

   SQL> select * from v$parameter where name = 'enable_goldengate_replication';
   SQL> alter system set enable_goldengate_replication = true scope=both;

Create Target Schema

Complete the following steps to create schema and target objects that can be used in replication. This schema and associated objects does not support DDL replication.

1. Create a new application user/schema. This user/schema stores the target objects for replication.

   Note:

   appadmin is an example user.

   SQL> create user appadmin identified by ********
   SQL> grant create session, resource, create view, create table to appadmin;
   SQL> alter user appadmin quota unlimited on data;
   

2. Connect to Oracle Autonomous Database as a user/schema and create your application tables.

5.7.2 Autonomous Database Client Credentials

Obtain the Autonomous Database Client Credentials

To establish connection to your Autonomous Database, you must download the client credential files from the Autonomous Database Service Console. For more information, see Downloading Client Credentials section of Using Oracle Autonomous Data Warehouse guide .

Note:

If you do not have administrator access to the Autonomous Database, ask your service administrator to download and provide the credential files to you. Once you have the credential files for your Autonomous Database, you should upload the zip file to the Oracle GoldenGate Compute Node.

Perform the following steps to obtain the Oracle Autonomous Data Warehouse Cloud account details:

1. Log in to your Oracle Autonomous Data Warehouse Cloud account.
2. From the Instance page, click the menu option for the Autonomous Database instance and select Service Console.
3. Log in to the Service Console using the admin username, and its associated password.
4. In the Service Console, click the Administration tab.
5. Click Download Client Credentials.
6. Enter a password to secure your credentials zip file and click Download.
7. Save the credentials zip file to your local system.

Move Client Credentials to Oracle GoldenGate Compute Node

In order to establish a connection from Oracle GoldenGate to the Autonomous Data Warehouse, you need to move the client credentials to Oracle GoldenGate Compute Node. The following steps will illustrate how to move the credential zip file from your machine to Oracle GoldenGate Compute Node.

1. Connect to the Oracle GoldenGate Classic Compute Node using SSH and opc user credentials.

   ssh -i <private_key> opc@<public_ip_address>

2. Create a staging directory and grant the essential permissions and then exit the session.

   $ mkdir stage
   $ exit
   

3. Copy the credentials zip file to the Oracle GoldenGate Classic Compute Node.

   $ scp ./<credential_file>.zip opc@<public_id_address>:~/stage

4. Connect to the Oracle GoldenGate Classic Compute Node.

   ssh -i <private_key> opc@<public_ip_address>

5. Verify whether the credentials zip file is available in the stage location.

   $ cd ~/stage
   $ ls -ltr
   

Configure Oracle Goldengate Compute Node with Autonomous Client Credentials

After moving the ADWC Client Credentials to the Oracle GoldenGate Compute Node, you have to install the necessary files and ensure you have a connection to the Autonomous Data Warehouse. The following steps will help you configure the required SQL*Net components:

1. Log in to the Oracle GoldenGate Classic Compute Node using SSH and the opc user credentials.

   ssh -i <private_key> opc@<public_ip_address>

2. Unzip the client credentials file into a temporary directory.

   unzip ./<credential_file>.zip -d ./client_credentials

3. Copy the sqlnet.ora and tnsnames.ora files to the location of your TNS_ADMIN.

   $ cd ~/stage/client_credentials
   $ cp ./sqlnet.ora /u02/deployments/<deployment>/etc
   $ cp ./tnsnames.ora /u02/deployments/<deployment>/etc

   Note:

   If you want your networking directory structure to be consistent with other Oracle products, you must append/network/admin to the directory structure. For this you have to change the environment variable TNS_ADMIN. For more information on Local Naming Parameters, refer to Database Net Services Reference.
4. Edit the sqlnet.ora file and replace the directory parameter with the location of the information pointing to the location where the client credentials were unzipped.

   $ cd /u02/deployments/<deployment>/etc
   $ vi ./sqlnet.ora

   Change ?/network/admin to /home/opc/stage/client_credentials.

5. For testing purposes, set the TNS_ADMIN and ORACLE_HOME environment variables at the operating system level.

   Note:

   The Oracle GoldenGate Deployment(s) use the ORACLE_HOME and TNS_ADMIN environment variables that are set per deployment.

   $ export ORACLE_HOME=/u01/app/client/<oracle version>
   $ export TNS_ADMIN=/u02/deployments/<deployment>/etc
   

6. Test the connection to Autonomous Data Warehouse by connecting to one of the entries in the tnsnames.ora file.

   $ cd $ORACLE_HOME/bin
   $ ./sqlplus appadmin/**********@orcladw_low

5.7.3 Configure Oracle GoldenGate Microservices for Replication

Perform the following steps for establishing a successful connection to the Autonomous Database with Oracle GoldenGate Microservices.

Add Oracle GoldenGate Credential to connect to Autonomous Database

To add Oracle GoldenGate Credential details, to connect to Autonomous Database:

1. Log in to the Service Manager using the password for oggadmin.
2. From the Service Manager main page, select the hyperlink for the port number associated with the Administration Service.
3. Open the context menu in the top left corner of the Overview page.
4. From the context menu, select Configuration.
5. From the Database tab, click the plus ( + ) icon, to add a new credential.
6. Provide the following information and click Submit.

   Credential Domain: [Defaults to OracleGoldenGate]
   Credential Alias: [Name of the Alias]
   User ID: ggadmin@<adw_tnsnames_reference>
   Pasword: [Password for ggadmin]
   Verify Password: [Password for ggadmin]

7. Test the connection to the Autonomous Database by clicking the Log in Database icon after the credential has been added.