17.3 Configuring Extract to Capture from an Autonomous Database

The Autonomous Databases have tighter integration with Oracle GoldenGate and there are a number of differences when setting up Extract for Autonomous Databases compared to a traditional Oracle Database.

The Autonomous Databases are multitenant databases and like all multitenant databases, there is only one set of redo logs which contain the transactions for all pluggable databases. The Autonomous Database security has been enhanced to ensure that Extract is only able to capture changes from the specific tenant it connected to. Downstream integrated Extract is not supported.

Note:

The Autonmous Database capture support is available for Autonomous Database Shared environments only. Also, this functionality is limited to Oracle Public Cloud (OPC) support only and is not available on Autonomous Databases running on Exadata Cloud @ Customer.

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 Databse.

  2. Obtain the Autonomous Databse client credentials.

Topics:

17.3.1 Establishing Oracle GoldenGate Credentials

To capture from an Autonomous Database the GGADMIN account is used. This GGADMIN account is created inside the database when the Autonomous Database is provisioned and 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;

17.3.2 Prerequisites for Configuring Oracle GoldenGate Extract to Capture from Autonomous Databases

Prior to configuring and starting the Integrated Extract process to capture from the Autonomous Database, make sure that the following requirements are met:
  • Oracle Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP) environment 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 PDB:
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:
SQL> 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.

17.3.3 Configure Oracle GoldenGate Extract to Capture from an Autonomous Database

The steps to set up and run an Autonomous Database capture are similar to a non-multitenant database. Learn the essential steps to capture from an Autonomous Database:

Here are the steps to configure an integrated Extract to capture from an Autonomous Database:
  1. Install Oracle GoldenGate 19c (19.1.0.0.201013_MLR32153823) or higher for your Autonomous Database platform. If using Oracle GoldenGate on OCI Marketplace, ensure that you are using version 19.1.0.0.201013_MLR32153823 or higher. If your environment was provisioned prior to December 10th, 2020, you may need to upgrade first.

  2. (Microservices only)Create a deployment for your Oracle GoldenGate environment. This is the deployment where the Extract that captures data from the Autonomous Database (ADB) will be created. See How to Create Deployments for steps to add a deployment.

  3. Obtain Autonomous Database Client Credentials.

    To establish connection to your Autonomous Database, download client credentials files from the Autonomous Database service console. 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.

    1. Log into your Autonomous Database account.

    2. From the Instance page, click the menu option for the Autonomous Database instance and select Service Console.

    3. Log into 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.

    The credentials zip file contains the following files:
    • cwallet.sso

    • ewallet.p12

    • keystore.jks

    • ojdbc.properties

    • sqlnet.ora

    • tnsnames.ora

    • truststore.jks

    Refer to the sqlnet.ora and tnsnames.ora files while configuring Oracle GoldenGate On Premises to work with the Autonomous Database.
  4. Configure the server where Oracle GoldenGate is running to connect into the Autonomous Database.

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

    2. Transfer the credentials zip file that you downloaded from Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing database 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 graphdb1_low connection string example and move it to your local tnsnames.ora file. See Local Naming Parameters in the tnsnames.ora File in the Oracle Database Net Services Reference guide.

    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
      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 Extract becomes unresponsive due to a network timeout or connection lost, 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))

      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, use ADWC_Database_Name_low. See Predefined Database Service Names for Autonomous Data Warehouse Cloud.
    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
    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
  5. Use Admin Client or GGSCI to log into the Oracle GoldenGate deployment, depending on whether you are using Microservices or Classic Architecture.
  6. Create a credential to store the GGADMIN user and password. This user will be used in GGSCI or Admin Client to connect to the Autonomous Database to perform commands that require a database connection. It will also be used in the USERIDALIAS parameter for the Extract database connection.
    ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER ggadmin@databasename_low PASSWORD complex_password alias adb_alias
  7. Connect to the database using DBLOGIN. The DBLOGIN user should be the adb_alias account user.
    DBLOGIN USERIDALIAS adb_alias
  8. Register Extract with the Autonomous Database. For example, to register an integrated Extract named extp1, use the following command:

    REGISTER EXTRACT extp1 WITH DATABASE
  9. Add and configure an integrated Extract to capture from the Oracle Autonomous Database. See How to Add Extracts for steps to create an Extract.

    Although the Autonomous Database is a multitenant database, Extract is designed to work with Autonomous Databases to ensure that it is only able to capture from the specific pluggable database (PDB) assigned to you. This also means that the PDB name is not needed for any TABLE or MAP statements. The following example creates an integrated Extract (required for ADB capture) called extp1, and instructs it to begin now.
    ADD EXTRACT extp1, INTEGRATED TRANLOG, BEGIN NOW 
    Do not use the traditional 3 part naming convention for multitenant databases. For example, to capture from the table SCOTT.EMP, in your Autonomous Database, use this entry in the Extract parameter file.
    TABLE SCOTT.EMP
    If you want to replicate SCOTT.EMP into TEST.EMPLOYEE, then your map statement would look like this:
    MAP SCOTT.EMP, TARGET TEST.EMPLOYEE;
  10. 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 PDB, so there is no need to include the PDB name in these commands. Here's an exmaple:
    ADD TRANDATA SCOTT.EMP
    or
    ADD SCHEMATRANDATA SCOTT

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

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

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