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
Unlock the pre-created Oracle GoldenGate database user
ggadminin the Autonomous Database.
Obtain the Autonomous Database client credentials to connect to the database instance.
Establishing Oracle GoldenGate Credentials
To capture from an Autonomous Database only the
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.
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;
Prerequisites for Configuring Oracle GoldenGate Extract to Capture from Autonomous Databases
Oracle Autonomous Database environment is 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 database instance:
ALTER PLUGGABLE DATABASE DROP SUPPLEMENTAL LOG DATA;
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.
Configure Extract to Capture from an Autonomous Database
Install Oracle GoldenGate for your Oracle Autonomous Databaseinstance.
(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 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).
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
Log in to your Oracle Autonomous Databaseaccount.
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
ADMINusername and password.
On the Database Actions Launchpad, under Administration, click Download Client Credentials (Wallets).
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.
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 instance.
Configure the server where Oracle GoldenGate is running to connect to the Autonomous Database instance.
Log into the server where Oracle GoldenGate was installed.
Transfer the credentials
zipfile that you downloaded from Oracle Autonomous database instance 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.
Use the connection string with the
dbname_low, for example,
graphdb1_low, and move it to your local
See Local Naming Parameters in the tnsnames.ora File chapter in the Oracle Database Net Services Reference guide.
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 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
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 Databas.
Sample Connection Stringadw1_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
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))
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
See Autonomous Database Client Credentials in Using Oracle GoldenGate on Oracle Cloud Marketplace.
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 credentials for the Extract database (or a user with same privileges). In this case,
GGADMINis 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
USERIDALIASparameter for the Extract database connection.
ALTER CREDENTIALSTORE ADD USER ggadmin@dbgraph1_low PASSWORD complex_password alias adb_alias
Connect to the database using
DBLOGINuser should be the
DBLOGIN USERIDALIAS adb_alias
Configure supplemental logging on the tables, which you want to capture using
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 example:
ADD TRANDATA HR.EMP
ADD SCHEMATRANDATA HR
Add heartbeat table.
Add and configure an Extract to capture from the Oracle Autonomous Database instance. See Configuring the Primary Extract in Integrated Capture Mode 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
MAPstatements.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 NOWTo 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
COUNTRY.EMPLOYEE, then your map statement would look like this:
MAP HR.EMP, TARGET COUNTRY.EMPLOYEE;
Register Extract with the Oracle Autonomous Database instance. For example, to register an Extract named
exte, use the following command:
REGISTER EXTRACT exte DATABASE
You can now start your Extract and perform data replication to the Oracle Autonomous Database instance. Here is 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.