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


17.3.1 Establishing Oracle GoldenGate Credentials

To capture from an Autonomous Database only 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 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:
To DROP Autonomous Database-level supplemental logging incase you decide to stop capturing from that PDB:
        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:

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

17.3.3 Configure 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 Extract to capture from an Autonomous Database:
  1. Install Oracle GoldenGate 21c for your Autonomous Database platform.

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


    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 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 chapter 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
      adw1_low. = (description=
                       (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 might be able to pass that 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:
            (LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS = (PROTOCOL = TCP)(HOST = adb-preprod.us-phoenix-1.oraclecloud.com)(PORT = 1522))


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

      Oracle recommends that you use ADWC_Database_Name_low with Oracle GoldenGate. See Predefined Database Service Names Autonomous Database in the Getting Started with Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure guide ‚Äčor Predefined Database Service Names in the Using Oracle Autonomous Database on Dedicated Exadata Infrastructure.

    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
      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"))) 
  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 to connect to the Autonomous Database from the command line, to perform commands that require a database connection. It will also be used in the USERIDALIAS parameter for the Extract database connection.
    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.
  8. Register Extract with the Autonomous Database. For example, to register an Extract named extp1, use the following command:

  9. Add and configure an 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 Extract (required for ADB capture) called extp1, and instructs it to 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.
    If you want to replicate SCOTT.EMP into TEST.EMPLOYEE, then your map statement would look like this:
  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:

    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:

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