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.
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
Unlock the pre-created Oracle GoldenGate database user ggadmin in the Autonomous Databse.
Obtain the Autonomous Databse client credentials.
17.3.1 Establishing Oracle GoldenGate Credentials
To capture from an Autonomous Database the
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.
alter user command to unlock the
ggadmin user and set the password for it. See Creating Users with Autonomous Database with
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
Oracle Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP) environment provisioned and running.
Autonomous Database-level supplemental logging should be enabled by the
Configuring Autonomous Database Supplemental Logging for Extract
ADMINaccount and execute the following commands:
ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;
DROPAutonomous Database-level supplemental logging incase you decide to stop capturing from that PDB:
ALTER PLUGGABLE DATABASE DROP SUPPLEMENTAL LOG DATA;
SQL> select minimal from dba_supplemental_logging;
MINIMAL ------- YES
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:
Install Oracle GoldenGate 19c (220.127.116.11.201013_MLR32153823) or higher for your Autonomous Database platform. If using Oracle GoldenGate on OCI Marketplace, ensure that you are using version 18.104.22.168.201013_MLR32153823 or higher. If your environment was provisioned prior to December 10th, 2020, you may need to upgrade first.
(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.
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.
Log into your Autonomous Database account.
From the Instance page, click the menu option for the Autonomous Database instance and select Service Console.
Log into the service console using the
adminusername and its associated password.
In the service console, click the Administration tab.
Click Download Client Credentials.
Enter a password to secure your credentials
zipfile and click Download.
Save the credentials
zipfile to your local system.
zipfile contains the following files:
tnsnames.orafiles while configuring Oracle GoldenGate On Premises to work with the Autonomous Database.
Configure the server where Oracle GoldenGate is running to connect into the Autonomous Database.
Log into the server where Oracle GoldenGate was installed.
Transfer the credentials
zipfile that you downloaded from Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing database to the Oracle GoldenGate server.
In the Oracle GoldenGate server, unzip the credentials file into a new directory, for example:
/u02/data/adwc_credentials. This is your key directory.
To configure the connection details, open your
tnsnames.orafile from the Oracle client location in the Oracle GoldenGate instance..
graphdb1_lowconnection string example and move it to your local
tnsnames.orafile. See Local Naming Parameters in the tnsnames.ora File in the Oracle Database Net Services Reference guide.
tnsnames.orafile in the Oracle GoldenGate instance to include the connection details available in the
tnsnames.orafile in your key directory (the directory where you unzipped the credentials
zipfile 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
(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))
tnsnames.orafile provided with the credentials file contains three database service names identifiable as:
For Oracle GoldenGate, use
ADWC_Database_Name_low ADWC_Database_Name_medium ADWC_Database_Name_high
ADWC_Database_Name_low. See Predefined Database Service Names for Autonomous Data Warehouse Cloud.
To configure the wallet, create a
sqlnet.orafile in the Oracle client location in the Oracle GoldenGate instance.
cd /u02/data/oci/network/admin ls sqlnet.ora tnsnames.ora
sqlnet.orafile to include your key directory.
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u02/data/adwc_credentials"))) SSL_SERVER_DN_MATCH=yes
- Use Admin Client or GGSCI to log into the Oracle GoldenGate deployment, depending on whether you are using Microservices or Classic Architecture.
Create a credential to store the
GGADMINuser 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
USERIDALIASparameter for the Extract database connection.
ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER ggadmin@databasename_low PASSWORD complex_password alias adb_alias
Connect to the database using
DBLOGINuser should be the
DBLOGIN USERIDALIAS adb_alias
Register Extract with the Autonomous Database. For example, to register an integrated Extract named
extp1, use the following command:
REGISTER EXTRACT extp1 WITH DATABASE
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
MAPstatements. 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 NOWDo 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.EMPIf you want to replicate
TEST.EMPLOYEE, then your map statement would look like this:
MAP SCOTT.EMP, TARGET TEST.EMPLOYEE;
Configure supplemental logging on the tables, which you want to capture using
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.EMPor
ADD SCHEMATRANDATA SCOTT
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.