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
Unlock the pre-created Oracle GoldenGate database user ggadmin in the Autonomous Database.
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
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 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 21c for your Autonomous Database platform.
(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 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 chapter 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 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 might be able to pass that firewall with the following modifications to the
If Extract becomes unresponsive due to a network timeout or connection loss, then you can add the following into the connection profile in the
address modification of tns_alias
(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:
ADWC_Database_Name_low ADWC_Database_Name_medium ADWC_Database_Name_high
Oracle recommends that you use
ADWC_Database_Name_lowwith 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.
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 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
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 Extract named
extp1, use the following command:
REGISTER EXTRACT ext_A DATABASE
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
MAPstatements. The following example creates an 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.EMP;If 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.