17 Using Oracle GoldenGate with Autonomous Database
You can replicate data to Oracle Autonomous Database using Oracle GoldenGate.
Topics:
About Capturing and Replicating Data Using Autonomous Databases
You can capture changes from the Oracle Autonomous Database instance and replicate to any target database or platform that Oracle GoldenGate supports, including another Oracle Autonomous Database instance.
Use Case: When Using Oracle GoldenGate with Autonomous Databases
-
Scalable Active-Active architecture: Synchronize changes made across two or more databases to scale out workloads, provide increase resilience and near instantaneous failover across multiple data centers or regions.
-
Real-Time Data Warehouse: Provide continuous, real-time capture and delivery of changed data between Oracle Autonomous Database systems.
-
Big Data Integration: With Oracle GoldenGate for Big Data you can replicate data from the Oracle Autonomous Database to provide real-time streaming integration to all platforms supported by Big Data targets.
-
Real-Time Streaming Analytics: Oracle GoldenGate integrates seamlessly with Oracle Stream Analytics to enable users to identify events of interest by executing queries against event streams in real time. It allows creating custom operational dashboards that provide real-time monitoring, transform streaming data, or raise alerts based on stream analysis.
-
Hybrid Replication: Oracle GoldenGate replicates data from the Oracle Autonomous Database instance back to on-premise or to another cloud database or platform.
The following features are not available with Always Free Autonomous Databases:
-
Supplemental logging
-
Oracle GoldenGate Extract
See Always Free Autonomous Database for details.
Parent topic: Using Oracle GoldenGate with Autonomous Database
Details of Support When Using Oracle GoldenGate with Autonomous Databases
Review the supported data types and limitations before replicating data to an Oracle Autonomous Database.
Oracle GoldenGate Replicat Limitations for Autonomous Databases
- Supported Replicats
-
The following combinations of Replicats are supported in different modes when using Oracle GoldenGate with Oracle Autonomous Database:
-
Parallel Replicat in integrated mode is supported for Oracle Autonomous Database.
-
Classic and coordinated Replicats in integrated mode are not supported for Oracle Autonomous Database.
-
Classic, coordinated, and parallel Replicats in non-integrated mode are supported for Oracle Autonomous Database.
-
- Data Type Limitations for DDL and DML Replication
-
See the section Non-Supported Oracle Data Types.
Also see Limitation on the Use of Certain Data Types in the Autonomous Database on Dedicated Exadata Infrastructure Documentation and Data Types in the Using Oracle Autonomous Database Serverless guide.
DDL replication is supported depending on the restrictions in the Autonomous Databases.
- Details of Support for Archived Log Retention
-
The two types of Autonomous Databases, Oracle Autonomous Database Serverless and Oracle Autonomous Database on Dedicated Exadata Infrastructure have different log retention behavior.
-
Oracle Autonomous Database Serverless: Archived log files are kept in Fast Recovery Area (FRA) for up to 48 hours. After that, it is purged and the archived log files are moved to NFS mount storage, which is accessible by logminer. Three copies are created. The logminer should be able to access any of the copies. This is transparent to Oracle GoldenGate Extract. After it reaches 7 days, the NFS mounted copy is permanently removed. The Extract abends with the
archived log unavailable
error if the required archived log file is older than 7 days. -
Oracle Autonomous Database on Dedicated Exadata Infrastructure: When Oracle Autonomous Data Guard or Oracle GoldenGate is enabled, archived log files are kept in Fast Recovery Area (FRA) for up to 7 days. After that, the files are purged. There is no NFS mount location available for logminer to access archived log files that are older than 7 days. The Extract abends with the
archived log unavailable
error if the required archived log file is older than 7 days.Note:
If the database instance is closed for more than 15 minutes, then the retention time is set back to 3 days. This implies that retention of archived log files is confirmed only for 3 days, regardless of whether the database instance is closed. The files are retained for 7 days only if the database instance is not closed.
-
Parent topic: Using Oracle GoldenGate with Autonomous Database
Configuring Replicat to Apply to an Autonomous Database
You can replicate into the Autonomous Database from any source database or platform that is supported by Oracle GoldenGate.
Topics:
- Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database
- Configure Replicat to Apply to an Autonomous Database
Parent topic: Using Oracle GoldenGate with Autonomous Database
Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database
Learn about the prerequisites for configuring Oracle GoldenGate data replication to Autonomous Databases.
-
Your source database with Oracle GoldenGate Extract processes configured and writing trails to where the Replicat is running to apply data to the Autonomous Database target.
-
Oracle Autonomous Database environment is provisioned and running.
- Configure Oracle GoldenGate Replicat for an Autonomous Database
- Obtain the Autonomous Database Client Credentials
Parent topic: Configuring Replicat to Apply to an Autonomous Database
Configure Oracle GoldenGate Replicat for an Autonomous Database
Learn the steps to configure Oracle GoldenGate Replicat for an Autonomous Database.
Note:
Instructions are based on the assumption that the source environment is already configured.
-
For Oracle GoldenGate on-premises, make sure that Oracle GoldenGate is installed.
Oracle GoldenGate Classic Architecture supports Autonomous Database capture using Marketplace for Oracle Autonomous Database Serverless
-
(Microservices only) Create a deployment for your Oracle GoldenGate environment. This is the deployment where the Replicat that applies data into the Autonomous Database (ADB) will be created. See How to Create Deployments for steps to add a deployment.
-
The Autonomous Database has a pre-existing user created for Oracle GoldenGate on-premise called
ggadmin
. Theggadmin
user has been granted the required privileges for Replicat to work. This is the user where any objects used for Oracle GoldenGate processing will be stored, like the checkpoint table and heartbeat objects. By default, this user is locked. To unlock theggadmin
user, connect to the Oracle Autonomous Database instance as theADMIN
user using any SQL client tool. See Create Users on Autonomous Database with Database Actions. -
Run the
ALTER USER
command to unlock theggadmin
user and set the password for it. This will be used in GGSCI or Admin Client for anyDBLOGIN
operations on the Autonomous Database. It will be used in Replicat to allow Oracle GoldenGate to connect to the Autonomous Database and apply data. See Create Users on Autonomous Database with Database Actions.ALTER USER ggadmin IDENTIFIED BY p0$$word ACCOUNT UNLOCK;
Obtain the Autonomous Database Client Credentials
Learn how to establish a connection to your Autonomous Database.
To establish a connection with an Oracle Autonomous Database instance, you need to download the client credentials files. There are two ways to download the client credentials files: the Oracle Cloud Infrastructure Console or Database Actions Launchpad. See Downloading Client Credentials (Wallets).
Note:
If you do not have administrator access to the Oracle 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 files:
-
Log into your Autonomous Database account.
-
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 databaseADMIN
username 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 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
-
ewallet.pem
-
README.txt
Refer and update (if required) the
sqlnet.ora
andtnsnames.ora
files while configuring Oracle GoldenGate to work with the Oracle Autonomous Database instance. -
Configure Replicat to Apply to an Autonomous Database
This section assumes that the source environment is already configured and provides the steps required to establish replication in the Oracle Autonomous Database environment.
-
Follow the steps given in Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database.
-
Follow the steps given in Configure Oracle GoldenGate Replicat for an Autonomous Database.
-
Follow the steps given in Obtain the Autonomous Database Client Credentials.
-
Log into the server where Oracle GoldenGate was installed.
-
Transfer the credentials
zip
file that you downloaded from Oracle Autonomous Database to your Oracle GoldenGate instance. -
In the Oracle GoldenGate instance, unzip the credentials file into a new directory
/u02/data/adwc_credentials
. This is your key directory. -
To configure the connection details, open the
tnsnames.ora
file from the Oracle client location in the Oracle GoldenGate instance.cd /u02/data/adwc_credentials ls tnsnames.ora
-
Edit the
tnsnames.ora
file in the Oracle GoldenGate instance to include the connection details available in thetnsnames.ora
file in your key directory (the directory where you unzipped the credentialszip
file downloaded from Oracle 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 Replicat becomes unresponsive due to a network timeout or a lost connection, then you can add the following into the connection profile in thetnsnames.ora
file:(DESCRIPTION = (RECV_TIMEOUT=120) (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:
Thetnsnames.ora
file provided with the credentials file contains three database service names identifiable as:
For Oracle GoldenGate replication, useADWC_Database_Name_low ADWC_Database_Name_medium ADWC_Database_Name_high
ADWC_Database_Name_low
. -
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
-
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
-
Use the Admin Client or GGSCI to log into the Oracle GoldenGate deployment.
-
Create a credential to store the
GGADMIN
user and password for the Replicat to use. For example:ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER ggadmin@databasename_low PASSWORD complex_password alias adb_alias
-
Add and configure a Replicat to deliver to Oracle Autonomous Database. When creating the Replicat, use the alias created in the previous step. For setting up your Replicat and other processes, see Configuring Oracle GoldenGate Apply.
Note:
You can use classic Replicat, coordinated Replicat, and parallel Replicat in non-integrated mode. Parallel Replicat in integrated mode is also supported for Oracle Autonomous Database. -
You can now start your Replicat and perform data replication to the Autonomous Database.
Note:
Oracle Autonomous Data Warehouse times out and disconnects the Replicat when it is idle for more than 60 minutes. When Replicat tries to apply changes (when it gets new changes) after being idle, it encounters a database error and abends. Oracle recommends that you configure Oracle GoldenGate with
AUTORESTART
parameter (Classic Architecture) or configure theAUTORESTART
profile (Microservices Architecture) to avoid having to manually restart a Replicat when it times out.
Parent topic: Configuring Replicat to Apply to an Autonomous Database