1. Using Oracle GoldenGate for Oracle Database
  2. Using Oracle GoldenGate with Autonomous Database

17 Using Oracle GoldenGate with Autonomous Database

You can replicate data to Oracle Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP) using Oracle GoldenGate.

Topics:

17.1 About Capturing and Replicating Data Using Autonomous Databases

You can capture changes from the Autonomous Database on Shared Infrastructure (ADB-S) and Autonomous Database on Dedicated Infrastructure (ADB-D) and replicate to any target database or platform that Oracle GoldenGate supports, including other Oracle Autonomous Databases.

See Autonomous Database Quickstart Workshop to know more.

Use Case: When Using Oracle GoldenGate with Autonomous Databases

Using Oracle GoldenGate in the Autonomous Database can be configured to support the following scenarios with the Autonomous Database environments:

  • 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 Autonomous Transaction Processing and Autonomous Data Warehouse systems.

  • Big Data Integration: With Oracle GoldenGate for Big Data you can replicate data from the Oracle Autonomous Databases to provides real-time streaming integration to all platforms support 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 ADB instance back to on-premise or to another cloud database or platform.

Parent topic: Using Oracle GoldenGate with Autonomous Database

17.2 Details of Support When Using Oracle GoldenGate with Autonomous Databases

Review the supported data types and limitations before replicating data to the Autonomous Database.

Oracle GoldenGate is supported for any type of Oracle Autonomous Database, including Autonomous Transaction Processing (ATP) and Autonomous Data Warehouse (ADW).

Understanding Limitations

Oracle Autonomous Data Warehouse is a fully-managed data warehouse designed to support all standard SQL and business intelligence (BI) tools and deliver scalable analytic query performance. Oracle Autonomous Data Warehouse provides all of the performance of the market-leading Oracle Database in a fully-managed environment that is tuned and optimized for data warehouse workloads. However, some data types, SQL commands, and database features are not available in Oracle Autonomous Data Warehouse.

For a complete list of database initialization parameter restrictions, database features restrictions, SQL commands restrictions, and data types restrictions, see Autonomous Data Warehouse Cloud for Experienced Oracle Database Users.

Oracle Autonomous Transaction Processing (ATP) is a cloud database service that eliminates the complexity of operating and securing high-performance databases. The service automates provisioning, configuring, tuning, scaling, patching, encrypting, and repairing of databases. Additionally, the service includes all of Oracle’s advanced database options, such as real application clusters (RAC), multitenant, partitioning, in-memory, advanced security, and advanced compression. The service is built to support everything from simple web apps to large and sophisticated applications that are critical for business operation. Autonomous Transaction Processing service is available in both the Oracle public cloud and your own data center on Exadata Cloud@Customer.

For more information on Autonomous Transaction Processing, see Getting Started wtih Autonomous Transaction Processing

The Oracle Database data types that are supported by Oracle GoldenGate can be replicated to Autonomous Databases. For a complete list of supported data types, see Details of Support for Oracle Data Types and Objects. The support limitations mentioned for replicating data to Oracle Database using Oracle GoldenGate apply to replicating data to Autonomous Database too.

There are additional limitations when replicating data into Autonomous Database as listed in the following section.

Oracle GoldenGate Replicat Limitations for Autonomous Databases

These are the limitations of Oracle GoldenGate when replicating to or from the Autonomous Databases.

Data Type Limitations for DDL and DML Replication

The following data types are not supported while capturing and replicating data to Autonomous Databases:

  • LONG

  • LONG RAW

  • XMLTYPE STORE AS OBJECT RELATIONAL

  • XMLTYPE STORE AS BINARY

  • BFILE

  • MEDIA

  • SPATIAL

Integrated Replicat and parallel Replicat in integrated mode are not supported.

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 (ADB), Autonomous Database on Shared Infrastructure (ADB-S) and Autonomous Database on Dedicated Infrastructure (ADB-D) have different log retention behavior.

  • Autonomous Database on Shared Infrastructure (ADB-S): 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 15 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 15 days.

  • Autonomous Database on Dedicated Infrastructure (ADB-D): When Active Data Guard (ADG) 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 PDB 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 PDB is closed. The files are retained for 7 days only if the PDB is not closed.

Parent topic: Using Oracle GoldenGate with Autonomous Database

17.3 Configuring Replicat to Apply to an Autonomous Databases

You can replicate into the Autonomous Database (ADB) from any source database or platform that Oracle GoldenGate supports, including Oracle Autonomous Databases.

Topics:

Parent topic: Using Oracle GoldenGate with Autonomous Database

17.3.1 Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database

Learn about the prerequisites for configuring Oracle GoldenGate data replication to Autonmous Databases.

You should have the following details available with you:

  • 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 (ADB) target.

  • Oracle Autonomous Data Warehouse or Autonomous Transaction Processing environment provisioned and running.

To deliver data to the Autonomous Database using Oracle GoldenGate, perform the following tasks:

  • Configure the Autonomous Database for replication by unlocking the pre-created Oracle GoldenGate database user ggadmin in the Autonomous Database. This user is where any objects used for Oracle GoldenGate processing will be stored, like the checkpoint table and heartbeat objects.

  • Obtain the Autonomous Database client credentials.

  • Configure Oracle GoldenGate for replication.

    • Transfer client credentials ZIP file that you downloaded from the Autonomous Database.

    • Configure sqlnet.ora file.

    • Configure tnsnames.ora file.

    • Create useridalias for the ggadmin user.

  • Configure Oracle GoldenGate Replicat to deliver to the Autonomous Database.

Parent topic: Configuring Replicat to Apply to an Autonomous Databases

17.3.2 Configure Oracle GoldenGate Replicat for an Autonomous Database

Learn the steps to configure Oracle GoldenGate Replicat for an Autonomous Databases.

Here are the steps to complete the configuration tasks:

Note:

Instructions are based on the assumption that the source environment is already configured. Learn the steps required to establish replication into the Autonomous Database (ADB) environment.

  1. Install Oracle GoldenGate or use Oracle GoldenGate on OCI Marketplace for your Autonomous Database Platform. Oracle GoldenGate 21c supports Autonomous Database capture for both Classic Architecture and Microservices Architecture on Marketplace for 21c ADB Service Console (ADB-S).

  2. (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. SeeHow to Create Deployments for steps to add a deployment.

  3. The Autonomous Database has a pre-existing user created for Oracle GoldenGate On-Premise called ggadmin. The ggadmin user has been granted the right set of privileges for Replicat to work. By default, this user is locked. To unlock the ggadmin user, connect to your Oracle Autonomous Data Warehouse database as the ADMIN user using any SQL client tool. See About Connecting to Autonomous Data Warehouse Cloud.

  4. Run the alter user command to unlock the ggadmin user and set the password for it. This will be used in GGSCI or Admin Client for any DBLOGIN 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 Creating Users with Autonomous Data Warehouse Cloud.
    alter user ggadmin identified by password account unlock;

Parent topic: Configuring Replicat to Apply to an Autonomous Databases

17.3.3 Obtain the Autonomous Database Client Credentials

Learn how to establish connection to your Autonomous Databases.

To establish connection to your Autonomous Database, download client credentials files from the Autonomous Database service console. See Downloading Client Credentials (Wallets).

Note:

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 Oracle ADW or ATP 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, cwallet.sso, ewallet.p12, and tnsnames.ora files, while configuring Oracle GoldenGate to work with the Autonomous Database.

Parent topic: Configuring Replicat to Apply to an Autonomous Databases

17.3.4 Configure Replicat to Apply to an Autonomous Database

This feature supports Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing Database (ATP).

This section assumes that the source environment is already configured and provides the steps required to establish replication into the Autonomous Database environment.

In the Oracle GoldenGate instance, you need to complete the following:

  1. Follow the steps given in Prerequisites for Configuring Oracle GoldenGate Replicat to an Autonomous Database.

  2. Follow the steps given in Configure Oracle GoldenGate Replicat for an Autonomous Database.

  3. Follow the steps given in Obtain the Autonomous Database Client Credentials.

  4. Log into the server where Oracle GoldenGate was installed.

  5. Transfer the credentials zip file that you downloaded from Oracle Autonomous Data Warehouse to your Oracle GoldenGate instance.

  6. In the Oracle GoldenGate instance, unzip the credentials file into a new directory /u02/data/adwc_credentials. This is your key directory.

  7. To configure the connection details, open your tnsnames.ora file from the Oracle client location in the Oracle GoldenGate instance. 
    cd /u02/data/adwc_credentials
ls
tnsnames.ora

  8. 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 Oracle Autonomous Data Warehouse). 

    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 connection lost, then you can add the following into the connection profile in the tnsnames.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:

    The tnsnames.ora file provided with the credentials file contains three database service names identifiable as:
    ADWC_Database_Name_low
ADWC_Database_Name_medium
ADWC_Database_Name_high
    For Oracle GoldenGate replication, use ADWC_Database_Name_low.
  9. 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

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

  11. Use the Admin Client to log into the Oracle GoldenGate deployment.

  12. 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
  13. Add and configure a Replicat to deliver to Oracle Autonomous Data Warehouse. For setting up your Replicat and other processes, see How to Add Replicats.

    Note:

    The Replicats used to apply data into an Autonomous Database must not be integrated Replicats. That means you can use classic Replicat, coordinated Replicat, or parallel Replicat in non-integrated mode. When creating the Replicat, use the alias created in the previous step.
  14. 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 the AUTORESTART profile (Microservices Architecture) to avoid having to manually restart a Replicat when it times out.

Parent topic: Configuring Replicat to Apply to an Autonomous Databases