10 Using Oracle GoldenGate with Autonomous Database

You can replicate data to Oracle Autonomous Database using Oracle GoldenGate.

Topics:

• About Capturing and Replicating Data Using Autonomous Databases
  
• Details of Support When Using Oracle GoldenGate with Autonomous Databases
  
• Configuring Extract to Capture from an Autonomous Database
  
• Configuring Replicat to Apply to an Autonomous Database
  

About Capturing and Replicating Data Using Autonomous Databases

You can capture changes from the Oracle Autonomous Database instance and replicate to any target database or platform that Oracle GoldenGate supports, including another Oracle Autonomous Database instance.

See Autonomous Database Quickstart Workshop to know more.

Use Case: When Using Oracle GoldenGate with Autonomous Databases

Using Oracle GoldenGate in the Oracle Autonomous Database can be configured to support the following scenarios:

• Scalable Active-Active architecture: Synchronize changes made across two or more databases to scale out workloads, provide increase resilience and near instantaneous failover across multiple data centers or regions.

• Real-Time Data Warehouse: Provide continuous, real-time capture and delivery of changed data between Oracle Autonomous Database systems.

• Big Data Integration: With Oracle GoldenGate for Big Data you can replicate data from the Oracle Autonomous Database to provide real-time streaming integration to all platforms supported by Big Data targets.

• Real-Time Streaming Analytics: Oracle GoldenGate integrates seamlessly with Oracle Stream Analytics to enable users to identify events of interest by executing queries against event streams in real time. It allows creating custom operational dashboards that provide real-time monitoring, transform streaming data, or raise alerts based on stream analysis.

• Hybrid Replication: Oracle GoldenGate replicates data from the Oracle Autonomous Database instance back to on-premise or to another cloud database or platform.

The following features are not available with Always Free Autonomous Databases:

• Supplemental logging

• Oracle GoldenGate Extract

See Always Free Autonomous Database for details.

Details of Support When Using Oracle GoldenGate with Autonomous Databases

Review the supported data types and limitations before replicating data to an Oracle Autonomous Database.

Oracle GoldenGate Replicat Limitations for Autonomous Databases

These are the limitations of Oracle GoldenGate when replicating to or from the Oracle Autonomous Database.

Supported Replicats

The following combinations of Replicats are supported in different modes when using Oracle GoldenGate with Oracle Autonomous Database:

• Parallel Replicat in integrated mode is supported for Oracle Autonomous Database.

• Classic and coordinated Replicats in integrated mode are not supported for Oracle Autonomous Database.

• Classic, coordinated, and parallel Replicats in non-integrated mode are supported for Oracle Autonomous Database.

Data Type Limitations for DDL and DML Replication

See the section Non-Supported Oracle Data Types.

Also see Limitation on the Use of Certain Data Types in the Autonomous Database on Dedicated Exadata Infrastructure Documentation and Data Types in the Using Oracle Autonomous Database Serverless guide.

DDL replication is supported depending on the restrictions in the Autonomous Databases.

Details of Support for Archived Log Retention

The two types of Autonomous Databases, Oracle Autonomous Database Serverless and Oracle Autonomous Database on Dedicated Exadata Infrastructure have different log retention behavior.

• Oracle Autonomous Database Serverless: Archived log files are kept in Fast Recovery Area (FRA) for up to 48 hours. After that, it is purged and the archived log files are moved to NFS mount storage, which is accessible by logminer. Three copies are created. The logminer should be able to access any of the copies. This is transparent to Oracle GoldenGate Extract. After it reaches 7 days, the NFS mounted copy is permanently removed. The Extract abends with the archived log unavailable error if the required archived log file is older than 7 days.

• Oracle Autonomous Database on Dedicated Exadata Infrastructure: When Oracle Autonomous Data Guard or Oracle GoldenGate is enabled, archived log files are kept in Fast Recovery Area (FRA) for up to 7 days. After that, the files are purged. There is no NFS mount location available for logminer to access archived log files that are older than 7 days. The Extract abends with the archived log unavailable error if the required archived log file is older than 7 days.

  Note:

  If the database instance is closed for more than 15 minutes, then the retention time is set back to 3 days. This implies that retention of archived log files is confirmed only for 3 days, regardless of whether the database instance is closed. The files are retained for 7 days only if the database instance is not closed.

Configuring Extract to Capture from an Autonomous Database

Oracle Autonomous Database has a tight integration with Oracle GoldenGate. There are a number of differences when setting up Extract for an Autonomous database instance compared to a traditional Oracle Database.

Oracle Autonomous Database security has been enhanced to ensure that Extract is only able to capture changes from the specific tenant it connected to. However, downstream Extract is not supported.

Before You Begin

Before you start the process of capturing data from the Autonomous Database using Oracle GoldenGate you must first:

1. Unlock the pre-created Oracle GoldenGate database user ggadmin in the Autonomous Database.

2. Obtain the Autonomous Database client credentials to connect to the database instance.

Topics:

• Establishing Oracle GoldenGate Credentials
  
• Prerequisites for Configuring Oracle GoldenGate Extract to Capture from Autonomous Databases
  
• Configure Extract to Capture from an Autonomous Database
  

Establishing Oracle GoldenGate Credentials

To capture from an Autonomous Database only the GGADMIN account is used. The GGADMIN account is created inside the database when the Autonomous Database is provisioned. This account is locked. It must be unlocked before it can be used with Oracle GoldenGate. This account is the same account used for both Extracts and Replicats in the Autonomous Database.

Run the ALTER USER command to unlock the ggadmin user and set the password for it. See Creating Users with Autonomous Database with Client-Side Tools.

This ALTER USER command must be run by the admin account user for Autonomous Databases.

ALTER USER ggadmin IDENTIFIED BY PASSWORD ACCOUNT UNLOCK;

Prerequisites for Configuring Oracle GoldenGate Extract to Capture from Autonomous Databases

Prior to configuring and starting the Extract process to capture from the Autonomous Database, make sure that the following requirements are met:

• Oracle Autonomous Database environment is provisioned and running.

• Autonomous Database-level supplemental logging should be enabled by the ADMIN or GGADMIN.

Configuring Autonomous Database Supplemental Logging for Extract

To add minimal supplemental logging to your Autonomous Database instance, log into the instance as GGADMIN or ADMIN account and execute the following commands:

ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;

To DROP Autonomous Database-level supplemental logging incase you decide to stop capturing from that database instance:

ALTER PLUGGABLE DATABASE DROP SUPPLEMENTAL
        LOG DATA;

You can verify that the Autonomous Database-level supplemental logging is configured properly by issuing this SQL statement:

SELECT MINIMAL FROM dba_supplemental_logging;

The output for this statement is:

MINIMAL
-------
YES

The MINIMAL column will be YES if supplemental logging has been correctly set for this Autonomous Database instance.

Configure Extract to Capture from an Autonomous Database

Following are the steps to configure an Extract to capture from an Oracle Autonomous Database :

1. Install Oracle GoldenGate for your Oracle Autonomous Database instance.

2. Obtain Autonomous Database Client Credentials.

   To establish connection to your Oracle Autonomous Database instance, download the client credentials file. To download client credentials, you can use the Oracle Cloud Infrastructure Console or Database Actions Launchpad. See Downloading Client Credentials (Wallets).

   Note:

   If you do not have administrator access to the Autonomous Database you should ask your service administrator to download and provide the credentials files to you.

   The following steps use the Database Actions Launchpad to download the client credentials.

   1. Log in to your Oracle Autonomous Database account.

   2. From the Database Instance page, click Database Actions. This launches the Database Actions Launchpad. The Launchpad attempts to log you into the database as ADMIN. If that is not successful, you will be prompted for your database ADMIN username and password.

   3. On the Database Actions Launchpad, under Administration, click Download Client Credentials (Wallets).

   4. Enter a password to secure your Client Credentials zip file and click Download.

      Note:

      The password you provide when you download the wallet protects the downloaded Client Credentials wallet.

   5. Save the credentials zip file to your local system.

   The credentials zip file contains the following files:

   • cwallet.sso

   • ewallet.p12

   • keystore.jks

   • ojdbc.properties

   • sqlnet.ora

   • tnsnames.ora

   • truststore.jks

   • ewallet.pem

   • README.txt

   Refer and update (if required) the sqlnet.ora and tnsnames.ora files while configuring Oracle GoldenGate to work with the Autonomous Database instance.

3. Configure the server where Oracle GoldenGate is running to connect to the Autonomous Database instance.

   1. Log in to the server where Oracle GoldenGate was installed.

   2. Transfer the credentials zip file that you downloaded from Oracle Autonomous database instance to the Oracle GoldenGate server.

   3. In the Oracle GoldenGate server, unzip the credentials file into a new directory, for example: /u02/data/adwc_credentials. This is your key directory.

   4. To configure the connection details, open your tnsnames.ora file from the Oracle client location in the Oracle GoldenGate instance.

   5. Use the connection string with the LOW consumer group dbname_low, for example, graphdb1_low, and move it to your local tnsnames.ora file.

      See Local Naming Parameters in the tnsnames.ora File chapter in the Oracle Database Net Services Reference guide.

      Note:

      The tnsnames.ora file provided with the credentials file contains three database service names identifiable as:

      ADWC_Database_Name_low
      ADWC_Database_Name_medium
      ADWC_Database_Name_high

      Oracle recommends that you use ADWC_Database_Name_low with Oracle GoldenGate. See Predefined Database Service Names for Autonomous Database in the Using Oracle Autonomous Database Serverless guide ​or Predefined Database Service Names for Autonomous Databases for Oracle Autonomous Database on Dedicated Exadata Infrastructure.

   6. Edit the tnsnames.ora file in the Oracle GoldenGate instance to include the connection details available in the tnsnames.ora file in your key directory (the directory where you unzipped the credentials zip file downloaded from the Autonomous Database).

      Sample Connection String
      adw1_low. = (description=
                       (retry_count=20)(retry_delay=3)
                       (address=(protocol=tcps)(port=1522)(host=adb-preprod.us-phoenix-1.oraclecloud.com))
                       (connect_data=(service_name=okd2ybgcz4mjx94_graphdb1_low.adb.oraclecloud.com))
                       (security=(ssl_server_cert_dn="CN=adwc-preprod.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))
                     )

      If the database is within a firewall protected environment, you might not have direct access to the database. With an existing HTTP Proxy, you can pass the firewall with the following modifications to the sqlnet.ora and tnsnames.ora:

      • sqlnet parameters
      • address modification of tns_alias
      If Extract becomes unresponsive due to a network timeout or connection loss, then you can add the following into the connection profile in the tnsnames.ora file:

      (DESCRIPTION = (RECV_TIMEOUT=30) (ADDRESS_LIST =
            (LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS = (PROTOCOL = TCP)(HOST = adb-preprod.us-phoenix-1.oraclecloud.com)(PORT = 1522))

   7. To configure the wallet, create a sqlnet.ora file in the Oracle client location in the Oracle GoldenGate instance.

      cd /u02/data/oci/network/admin
      ls
      sqlnet.ora tnsnames.ora

      See Autonomous Database Client Credentials in Using Oracle GoldenGate on Oracle Cloud Marketplace.

   8. Edit this sqlnet.ora file to include your key directory.

      WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u02/data/adwc_credentials"))) 
      SSL_SERVER_DN_MATCH=yes

4. Start GGSCI.

   ./ggsci

5. Create credentials for the Extract database (or a user with same privileges). In this case, GGADMIN is the user and will be used to connect to the Autonomous Database, and perform commands that require a database connection. It will also be used in the USERIDALIAS parameter for the Extract database connection.

   ALTER CREDENTIALSTORE ADD USER 
   ggadmin@dbgraph1_low PASSWORD complex_password alias adb_alias

6. Connect to the database using DBLOGIN. The DBLOGIN user should be the adb_alias account user.

   DBLOGIN USERIDALIAS adb_alias

7. Configure supplemental logging on the tables, which you want to capture using ADD TRANDATA or ADD SCHEMATRANDATA. Remember that you are connected directly to the database instance, so there is no need to include the database name in these commands. Here's an exmaple:

   ADD TRANDATA HR.EMP

   or

   ADD SCHEMATRANDATA HR

   See Prerequisites for Configuring Oracle GoldenGate Extract to Capture from Autonomous Databases.

8. Add heartbeat table.

   ADD HEARTBEATTABLE

9. Add and configure an Extract to capture from the Oracle Autonomous Database instance. See Configuring Primary Extract for steps to create an Extract.

   Oracle GoldenGate Extract is designed to work with the Oracle Autonomous Database instance to ensure that it only captures from a specific database instance. This means that the database instance name is not needed for any TABLE or MAP statements.

   The following example creates an Extract (required for capturing from an Oracle Autonomous Database) called exte, and instructs it to begin now.

   ADD EXTRACT exte, INTEGRATED TRANLOG, BEGIN NOW 

   To capture specific tables, use the two part object names.. For example, to capture from the table HR.EMP, in your Oracle Autonomous Database instance, use this entry in the Extract parameter file.

   TABLE HR.EMP;

   If you want to replicate HR.EMP into COUNTRY.EMPLOYEE, then your map statement would look like this:

   MAP HR.EMP, TARGET COUNTRY.EMPLOYEE;

10. Register Extract with the Oracle Autonomous Database instance. For example, to register an Extract named exte, use the following command:

    REGISTER EXTRACT exte DATABASE

11. You can now start your Extract and perform data replication to the Oracle Autonomous Database instance. Here's an example:

    START EXTRACT exte

    This completes the process of configuring an Extract for Oracle Autonomous Database and you can use it like any other Extract process.

Configuring Replicat to Apply to an Autonomous Database

You can replicate into the Autonomous Database from any source database or platform that is supported by Oracle GoldenGate.

Topics:

• Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database
  Learn about the prerequisites for configuring Oracle GoldenGate data replication to Autonmous Databases.
• Configure Replicat to Apply to an Autonomous Database
  

Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database

Learn about the prerequisites for configuring Oracle GoldenGate data replication to Autonmous Databases.

You should have the following details available with you:

• Your source database with Oracle GoldenGate Extract processes configured and writing trails to where the Replicat is running to apply data to the Autonomous Database target.

• Oracle Autonomous Database environment provisioned and running.

To deliver data to the Autonomous database instance using Oracle GoldenGate, perform the following tasks:

• Configure Oracle GoldenGate Replicat for an Autonomous Database
  Learn the steps to configure Oracle GoldenGate Replicat for an Autonomous Databases.
• Obtain the Autonomous Database Client Credentials
  Learn how to establish connection to your Autonomous Databases.

Configure Oracle GoldenGate Replicat for an Autonomous Database

Learn the steps to configure Oracle GoldenGate Replicat for an Autonomous Databases.

Here are the steps to complete the configuration tasks:

Note:

Instructions are based on the assumption that the source environment is already configured. Learn the steps required to configure replication into the Autonomous Database environment.

1. For Oracle GoldenGate on-premises, make sure that Oracle GoldenGate is installed.

   Oracle GoldenGate Classic Architecture support Autonomous Database capture using Marketplace for Oracle Autonomous Database Serverless.

2. Start GGSCI.

   ./ggsci

3. The Autonomous Database instance has a pre-created user created for Oracle GoldenGate on-premise called ggadmin. The ggadmin user has been granted the required privileges for Replicat to work. This is the user where any objects used for Oracle GoldenGate processing will be stored, like the checkpoint table and heartbeat objects. By default, this user is locked. To unlock the ggadmin user, connect to the Oracle Autonomous Database instance as the ADMIN user using any SQL client tool. See Create Users on Autonomous Database with Database Actions.

4. Run the ALTER USER command to unlock the ggadmin user and set the password for it. This will be used in GGSCI for any DBLOGIN operations on the Autonomous Database. It will be used in Replicat to allow Oracle GoldenGate to connect to the Autonomous Database and apply data. See Create Users on Autonomous Database with Database Actions.

   ALTER USER ggadmin IDENTIFIED BY p0$$word ACCOUNT UNLOCK;

Obtain the Autonomous Database Client Credentials

Learn how to establish connection to your Autonomous Databases.

To establish a connection with an Oracle Autonomous Database instance, you need to download the client credentials files. There are two ways to download the client credentials files: the Oracle Cloud Infrastructure Console or Database Actions Launchpad. See Downloading Client Credentials (Wallets).

Note:

If you do not have administrator access to the Oracle Autonomous Database, you should ask your service administrator to download and provide the credentials files to you.

The following steps use the Database Actions Launchpad to download the client credentials files.

1. Log into your Autonomous Database account.

2. From the Database Instance page, click Database Actions. This launches the Database Actions Launchpad. The Launchpad attempts to log you into the database as ADMIN. If that is not successful, you will be prompted for your database ADMIN username and password.

3. On the Database Actions Launchpad, under Administration, click Download Client Credentials (Wallets).

4. Enter a password to secure your Client Credentials zip file and click Download.

   Note:

   The password you provide when you download the wallet protects the downloaded Client Credentials wallet.

5. Save the credentials ZIP file to your local system. The credentials ZIP file contains the following files:

• cwallet.sso

• ewallet.p12

• keystore.jks

• ojdbc.properties

• sqlnet.ora

• tnsnames.ora

• truststore.jks

• ewallet.pem

• README.txt

Refer and update (if required) the sqlnet.ora and tnsnames.ora files while configuring Oracle GoldenGate to work with the Oracle Autonomous Database instance.

Configure Replicat to Apply to an Autonomous Database

This section assumes that the source environment is already configured and provides the steps required to establish replication in the Oracle Autonomous Database environment.

In the Oracle GoldenGate instance, you need to complete the following:

1. Follow the steps given in Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database.

2. Follow the steps given in Configure Oracle GoldenGate Replicat for an Autonomous Database.

3. Follow the steps given in Obtain the Autonomous Database Client Credentials.

4. Log into the server where Oracle GoldenGate was installed.

5. Transfer the credentials zip file that you downloaded from Oracle Autonomous Database to your Oracle GoldenGate instance.

6. In the Oracle GoldenGate instance, unzip the credentials file into a new directory /u02/data/adwc_credentials. This is your key directory.

7. To configure the connection details, open your tnsnames.ora file from the Oracle client location in the Oracle GoldenGate instance.

   cd /u02/data/adwc_credentials
   ls
   tnsnames.ora

8. Edit the tnsnames.ora file in the Oracle GoldenGate instance to include the connection details available in the tnsnames.ora file in your key directory (the directory where you unzipped the credentials zip file downloaded from Oracle Autonomous Database).

   Sample Connection String
   graphdb1_low = (description= 
                   (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb-preprod.us-phoenix-1.oraclecloud.com))
                   (connect_data=(service_name=okd2ybgcz4mjx94_graphdb1_low.adb.oraclecloud.com))
                   (security=(ssl_server_cert_dn="CN=adwc-preprod.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

   If Replicat becomes unresponsive due to a network timeout or lost connection, then you can add the following into the connection profile in the tnsnames.ora file:

   (DESCRIPTION =  (RECV_TIMEOUT=120)   (ADDRESS_LIST =
         (LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3) 
         (ADDRESS = (PROTOCOL = TCP)(HOST = adb-preprod.us-phoenix-1.oraclecloud.com)(PORT = 1522))

   Note:

   The tnsnames.ora file provided with the credentials file contains three database service names identifiable as:

   ADWC_Database_Name_low
   ADWC_Database_Name_medium
   ADWC_Database_Name_high

   For Oracle GoldenGate replication, use ADWC_Database_Name_low.
9. To configure the wallet, create a sqlnet.ora file in the Oracle client location in the Oracle GoldenGate instance.

   cd /u02/data/oci/network/admin
   ls
   sqlnet.ora tnsnames.ora

10. Edit this sqlnet.ora file to include your key directory.

    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u02/data/adwc_credentials"))) 
    SSL_SERVER_DN_MATCH=yes

11. Use GGSCI to log into the Oracle GoldenGate deployment.

12. Create a credential to store the GGADMIN user and password for the Replicat to use. For example:

    ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER ggadmin@databasename_low PASSWORD complex_password alias adb_alias

13. Add and configure a Replicat to deliver to Oracle Autonomous Database. When creating the Replicat, use the alias created in the previous step. For setting up your Replicat and other processes, see Configuring Oracle GoldenGate Replicat.

    Note:

    You can use classic Replicat, coordinated Replicat, and parallel Replicat in non-integrated mode. Parallel Replicat in integrated mode is also supported for Oracle Autonomous Database.
14. You can now start your Replicat and perform data replication to the Autonomous Database.

    Note:

    Oracle Autonomous Database times out and disconnects the Replicat when it is idle for more than 60 minutes. When Replicat tries to apply changes (when it gets new changes) after being idle, it encounters a database error and abends. Oracle recommends that you configure Oracle GoldenGate with the AUTORESTART profile to avoid having to manually restart a Replicat when it times out.