Database Connections

Database connections store the credentials and parameters required to access databases that serve as sources or targets in GoldenGate Studio. This configuration enables secure and efficient communication for designing and deploying data replication pipelines.

Database connections in GoldenGate Studio represent the configuration information required to communicate with your source and target databases. These connections are the foundation for designing, deploying, and managing data integration and replication solutions within the GoldenGate Studio. They enable you to visually map the data flow across diverse environments.

For details, see the Database Connection, System and Parameter Settings in Oracle GoldenGate Microservices Documentation.

About Database Connections

Learn about the connection types you can use with GoldenGate Studio and how to create them.

A database connection refers to the configuration information and credentials GoldenGate Studio needs to connect to a database. This connection allows GoldenGate Studio to access and interact with the source and target databases as part of designing, deploying, and managing data replication solutions.

GoldenGate Studio currently supports Oracle AI Database connections.

Supported Connections

Check the following list for supported Oracle Database variants and MySQL databases you can use as sources and targets in GoldenGate Studio.

GoldenGate Studio supports Oracle Database versions 19c, 21c, 21c XE, 23ai, 23ai Free, and Oracle AI Database 26ai. Starting with GoldenGate Studio 26ai, additional variants of Oracle database are supported. See Supported Oracle Databases.

GoldenGate Studio 26ai and higher support source and target connections to MySQL database variants. See Supported MySQL Databases for the complete list.

Supported Cloud Platforms for Oracle and MySQL Database

Learn about the supported cloud platforms for Oracle and MySQL databases.

When planning cloud deployments of Oracle and MySQL databases, it is important to consider the range of supported cloud platforms. Both database technologies can be deployed across various leading cloud service providers, including:

  • Oracle Cloud Infrastructure (OCI)
  • Amazon Web Services (AWS)
  • Micosoft Azure
  • Google Cloud Platform (GCP)
Supported Oracle Databases

GoldenGate Studio supports Oracle AI Database platforms, including on-premises, cloud, and multi-cloud environments.

The following table lists all currently supported Oracle AI Database types and deployment options, along with key connection requirements and supported recipe types.

Note:

You can use Oracle AI Database for older versions of Oracle Databases.
Technology Type Deployment Environment Supported Recipes
Oracle AI Database On-premises All
Oracle Exadata Database On-premises All
Oracle Autonomous AI Database Oracle Cloud All
Oracle Database@Azure - Autonomous Database Microsoft Azure All
Oracle Database@Azure - Exadata Microsoft Azure All
Oracle Database@AWS - Autonomous Database Amazon Web Services All
Oracle Database@AWS - Exadata Amazon Web Services All
Oracle Database@Google Cloud - Autonomous Database Google Cloud Platform All
Oracle Database@Google Cloud - Exadata Google Cloud Platform All
Amazon RDS for Oracle Amazon Web Services All
Supported MySQL Databases

GoldenGate Studio enables you to configure integration and migration pipelines for various MySQL databases, including their leading managed cloud offerings.

The following table outlines all supported MySQL databases, the available recipe types, and important configuration details:
Technology Type Deployment Environment Supported Recipes
MySQL Database Server On-premises One-way, Database Migration
Amazon Aurora MySQL AWS Cloud One-way, Database Migration
Amazon RDS for MySQL AWS Cloud One-way, Database Migration
Azure Database for MySQL Microsoft Azure Cloud One-way, Database Migration
Google Cloud SQL for MySQL Google Cloud Platform One-way, Database Migration
HeatWave MySQL on OCI Oracle Cloud One-way, Database Migration
HeatWave MySQL on Azure Microsoft Azure One-way, Database Migration
HeatWave MySQL on AWS Amazon Web Services One-way, Database Migration

Best Practices for Connections

Discover best practices for Connections in GoldenGate Studio.

Plan Before Creating the Connection

  • Identify Connection Requirements: Gather details such as database type, hostname, port, service name/SID, user credentials, and SSL requirements before starting.
  • Verify Access Privileges: Ensure the user account has the required permissions for GoldenGate operations (e.g., replication privileges, read/write access to specific schemas).

Use Secure Credentials Management

  • Avoid Hardcoding Credentials: Store usernames and passwords securely in the GoldenGate credential store instead of saving them in plain text. Do not store passwords in scripts or plain text.
  • Oracle Wallet or Oracle Cloud Wallet services: Use Oracle Wallet or Oracle Cloud Wallet services to store Oracle Autonomous Database credentials and encryption keys securely.
  • Use Role-Based Accounts: Create separate database accounts for replication activities, with only the necessary privileges.
  • Change Default Passwords: Immediately update default or shared account passwords to meet your organization’s security policies. Regularly rotate passwords according to your organization’s security policy.

Ensure Compatibility

  • Verify Database Version: Check that your source and target databases are compatible with the GoldenGate version you are using.
  • Install Required Drivers: Ensure JDBC drivers for the database type are installed and configured in GoldenGate Studio.

Optimize Connection Settings

  • Enable SSL/TLS if Available: Use encrypted connections to protect data in transit.
  • Set Connection Timeout: Configure timeouts to prevent long waits in case of connectivity issues.
  • Use Connection Pooling Where Possible: This improves performance for multiple Oracle GoldenGate processes using the same connection.

Validate the Connection

  • Test the Connection in Studio: Use the Test Connection feature in GoldenGate Studio to confirm connectivity and authentication.
  • Verify Schema Access: Ensure the connected user can access the specific schemas, tables, and views needed for replication.
  • Check Performance: Run small test queries to assess response time before deploying the connection in production.

Maintain and Monitor

  • Document the Connection Details: Maintain an internal record of connection parameters, privileges, and owner for auditing.
  • Regularly Rotate Credentials: Update stored passwords periodically to comply with security policies.
  • Monitor Connection Health: Use Oracle GoldenGate monitoring tools to check for connection drops, slow queries, or authentication failures.

Create Oracle AI Database and Oracle Exadata Connections

Learn how to create Oracle AI Database and Oracle Exadata connections.

  1. From the GoldenGate Studio Home page, click Create connection.

    You can also create a connection from the Connections page.

  2. The Create Connection panel consists of three pages. On the General Information page, complete the following fields, and then click Next:
    1. Enter a Connection Name.
    2. (Optional) Enter a Description.
    3. Select a Technology Type from the dropdown menu.
  3. On the Connection Details page, complete the following fields, and then click Next:
    1. Enter the database Username and Password.
    2. Enter the database Hostname and Port
    3. For Database Type:
      • If your Oracle AI Database version is 21c or above, select Pluggable database (PDB) in Oracle Database 21c or above.
      • If your Oracle AI Database version is 19c or below, select Pluggable database (PDB) in Oracle Database 19c or below and provide both the Pluggable database service name and the Container database service name
      • If your Oracle AI Database version is Non-container database, select Non-container database and then enter the Database service name.
  4. On the Confirmation page, click Create.

Configure and Download SSL Wallet for Non Autonomous AI Database

Learn about the different Wallet Configuration options.

Wallet configuration is required in non Autonomous AI Database to enable secure connectivity. This setup facilitates verified communication with endpoints such as parUrl using SSL verification or other Oracle Autonomous AI Database hosted in secure regions over HTTPS, especially needed to upload and download files from secured parUrl / bucketUri in initial load using Object Storage.

Why Is Wallet Configuration Important?

Without a properly configured wallet, attempts to connect securely to Object Storage or other endpoints using HTTPS will fail, leading to errors or inability to upload/download files. Configuring a wallet reduces these risks by ensuring that your database trusts the cloud endpoints you’re connecting to.

Important Terms

Term Definition
parUrl Pre-authenticated Request URL for secure, temporary access to objects in Oracle Cloud Object Storage.
bucketUri A Uniform Resource Identifier pointing to a specific bucket in Oracle Cloud Object Storage. A bucket is a storage container for objects (such as files or data dumps).
Oracle Cloud Endpoint URL for connecting to Oracle Cloud services.
ACL (Network Access Control List) Security rules that define which network traffic is allowed or denied to network resources.
Standard Oracle Wallet

Learn about Standard Oracle Wallet.

An Oracle Wallet is a secure, encrypted container that stores authentication and encryption credentials like private keys, certificates, and trusted certificates. It ensures that sensitive data is not kept in clear-text configuration files. You create these wallets using tools like the Oracle Wallet Manager or mkstore, and they require a password to open and access contents. To enable secure connectivity from a non-autonomous database, wallet setup is required.

Follow these steps to download/configure Standard Oracle Wallet:

  • Download the master wallet containing pre configured Oracle certificates. Contact the database administration team for this.
  • Extract the certificate files to a directory on the database host file system.
  • Specify this directory path in the SSL Wallet Path fields under the Advanced Options.

Note:

  • This wallet should serve the purpose for establishing secure connection with parUrl or bucketUri (typically used in case of Initial load via Object Storage).
  • Any required Network ACL configurations are automatically managed by the Studio application at runtime when access is initiated.

Using the Oracle-supplied wallet automatically enables secure connectivity for standard Oracle Cloud endpoints (like parUrl and bucketUri).

Manual Wallet Configuration

Learn how to configure Manual Wallets:

The Manual Wallet approach allows users to create and configure a wallet by manually adding specific security certificates that are not included in the Standard Oracle Wallet. This method is necessary when connecting to custom endpoints, using third-party certificate authorities, or addressing unique security requirements.

Follow the below steps to download/configure Manual Wallet:

  • Download the required certificates for the parUrl- typically a root certificate and an intermediate certificate.
  • Log in to the database host and create a wallet directory.
  • Initialize a wallet using orapki utility available in Oracle base.
  • Add certs downloaded from parUrl to wallet using orapki utility.
  • Commands to be used are :
    • bash-4.2$ mkdir -p <wallet-path-accessible-to-db-user>
    • bash-4.2$ orapki wallet create -wallet <wallet-path-accessible-to-db-user> -pwd <pwd> -auto_login
    • bash-4.2$ orapki wallet add -wallet <wallet-path-accessible-to-db-user> -trusted_cert -cert <path-to-root-certificate> -pwd <pwd>
    • bash-4.2$ orapki wallet add -wallet <wallet-path-accessible-to-db-user> -trusted_cert -cert <path-to-intermediate-certificate> -pwd <pwd>
    • bash-4.2$ orapki wallet display -wallet <wallet-path-accessible-to-db-user>

Create an Oracle Autonomous AI Database Connection

Learn to create a connection to Oracle Autonomous AI Database to use as a GoldenGate Studio source or target.

Before you Begin

Before you create a connection, ensure that you:

  • Turn on Archive Log Mode and restart your database.
  • Download the Oracle Autonomous AI Database instance's wallet from the Oracle Cloud console. If you don't have access, contact your administrator.

To create an Oracle Autonomous AI Database connection:

  1. From the GoldenGate Studio home page, click Create connection.
  2. The Create Connection panel consists of three pages.
    1. On the General Information page, complete the fields as follows, then click Next:
      1. Enter a Connection Name.
      2. (Optional) Enter a Description.
      3. Select Autonomous AI Database, from the Technology Type dropdown.
    2. On the Connection Details page, complete the fields as follows, and then click Next:
      1. Enter a Username and Password for the GoldenGate admin user.
      2. Click Add Wallet File and upload the Oracle Autonomous AI Database instance's wallet.
      3. Click Test Connection.
    3. On the Confirmation page, verify the connection details.
  3. Click Create.

Create MySQL Database Connection

Learn about the prerequisites for creating a MySQL database connection and the steps to set up the connection from GoldenGate Studio.

Before You Begin

Before running MySQL workloads that interact with Oracle GoldenGate or similar tools, ensure that the database environment satisfies all prerequisites.

To apply configuration changes, you need to edit the MySQL configuration file (my.cnf) and restart MySQL. If you do not have the required permissions to edit the my.cnf file, then contact the database administrator to attain the required privileges.

The following table includes the prerequisites, required action, and step-by-step guidance for implementation.

MySQL Prerequisite Action Required How to Set Up

Unsupported data types columns

Remove or convert columns with unsupported types.
  1. Run the following query: 
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,COLUMN_TYPE FROM information_schema.COLUMNS WHERE COLUMN_TYPE IN ('geometry', 'linestring', 'polygon', 'multipoint', 'multistring', 'multipolygon', 'geometrycollection','set');

  2. ALTER TABLE to drop or change these columns (ask database administrator if needed)

Timezone match

Set Oracle GoldenGate server timezone to be the same as the MySQL server timezone.
  1. Check the MySQL timezone: 
    SELECT @@system_time_zone;

  2. Check Oralce GoldenGate host timezone: date (ask database administrator if needed)

ALLOW_INVALID_DATE in sql_mode

Remove from sql_mode or avoid invalid date entries.

  1. Run the following command:

    SHOW VARIABLES LIKE 'sql_mode';

  2. Edit my.cnf and remove ALLOW_INVALID_DATE from sql_mode.

  3. Restart MySQL.

AWS RDS log retention

Set binlog_retention_hours to at least 24.

  1. Run the following command:

    CALL mysql.rds_set_configuration ('binlog retention hours', 24);
  2. Check the current configuration parameters and their values for your RDS instance: 
    CALL mysql.rds_show_configuration;

binlog_format

Set binlog_format = ROW

  1. Run the following command:

    SHOW VARIABLES LIKE 'binlog_format';

  2. Edit my.cnf, set binlog_format=ROW.

  3. Restart MySQL.

binlog_transaction_compression

Set binlog_transaction_compression = OFF

  1. Run the following command:

    SHOW VARIABLES LIKE 'binlog_transaction_compression';

  2. Edit my.cnf, set binlog_transaction_compression=OFF.

  3. Restart MySQL.

Character set

Use only supported character sets at all levels.

  1. Run the following command:

    SHOW VARIABLES LIKE 'character_set_server';

  2. If using unsupported value, then edit my.cnf and restart.

Collation server

Set collation_server to your_charset_ci (not binary)

  1. Run the following command:

    SHOW VARIABLES LIKE 'collation_server';

  2. Edit my.cnf and set collation_server=your_charset_ci. For example, collation_server=utf8mb4_ci..

  3. Restart MySQL.

Storage engine (capture/apply)

Use only InnoDB tables.

  1. Run the following query:

    SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_schema';

  2. Run the following command:

    ALTER TABLE table ENGINE=InnoDB;

Database version

Upgrade to MySQL 5.7 or above.

  1. Run the following command:

    SHOW VARIABLES LIKE 'version';
  2. If you have lower version, then ask admin to upgrade.
Database user privileges (capture)

Grant the following privileges:SELECT, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW

  1. Run the following query:

    SELECT Select_priv, Repl_slave_priv, Repl_client_priv, Show_view_priv FROM mysql.user WHERE User='ogg_user';
  2. To grant user privileges: 
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'user'@'%'; FLUSH PRIVILEGES;

Database user privileges (apply)

Grant the following privileges or permissions:

SELECT, CREATE, CREATE VIEW, EVENT, INSERT, UPDATE, DELETE, DROP, EXECUTE

  1. Run the following query:

    SELECT Select_priv, Create_priv, Create_view_priv, Event_priv, Insert_priv, Update_priv, Delete_priv, Drop_priv, Execute_priv FROM mysql.user WHERE User='user';
  2. To grant the privileges: 
    GRANT SELECT, CREATE, CREATE VIEW, EVENT, INSERT, UPDATE, DELETE, DROP, EXECUTE ON *.* TO 'user'@'%'; FLUSH PRIVILEGES;

DDL replication only greater than and equal to 8.0

Upgrade to MySQL 8.0 plus and set binlog_row_metadata = FULL.

  1. Run the following commands: 
    SHOW VARIABLES LIKE 'version';
    SHOW VARIABLES LIKE 'binlog_row_metadata';

  2. Edit my.cnf for binlog_row_metadata=FULL;

  3. Restart MySQL.

Functional Indexes

Remove functional indexes.
  1. Review the table definition, then run the following: 
    ALTER TABLE table DROP INDEX index_name;

Group replication

 Enable gtid_mode or use special Oracle GoldenGate paramaters.

  1. Run the following command:

    SHOW VARIABLES LIKE 'gtid_mode';

  2. Set the _DISABLEGTIDRECOVERY_true parameter in Oracle GoldenGate.

Key columns (PK/UK)

Add a primary key or unique key to each table.

  1. Run the following query:

    SHOW INDEX FROM table WHERE Non_unique=0;
  2. To add primary key: 
    ALTER TABLE table name ADD PRIMARY KEY (column); or ALTER TABLE table ADD UNIQUE (column);

MariaDB binlog annotation

Set binlog_annotate_row_events = OFF for MariaDB 10.2.

  1. Run the following command:

    SHOW VARIABLES LIKE 'version';

  2. Run the following command:

    SHOW VARIABLES LIKE 'binlog_annotate_row_events';

  3. Edit my.cnf, set binlog_annotate_row_events=OFF;

  4. Restart MariaDB.

No LOB datatype in key column

Avoid using BLOB or TEXT columns as primary key.

  1. Run the following query:

    SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE COLUMN_KEY='PRI' AND (DATA_TYPE='blob' OR DATA_TYPE='text');

  2. Redesign the table, if needed.

No XA transactions

Avoid XA transactions on captured databases.

  1. Check: XA RECOVER.

  2. If needed, set binlog-ignore-db in my.cnf for non-captured databases.

  3. Ask database adminstrator for application change.

Server ID for remote capture

Set server_id to greater than 0.

  1. Run the following query:

    SHOW VARIABLES LIKE 'server_id';

  2. Set GLOBAL server_id=1; or set in my.cnf

  3. Restart MySQL.

STRICT_TRANS_TABLES in sql_mode

Add STRICT_TRANS_TABLES to sql_mode.

  1. Run the following query:

    SHOW VARIABLES LIKE 'sql_mode';
  2. Edit my.cnf, add STRICT_TRANS_TABLES.
  3. Restart MySQL.

Supported database versions

Use supported database versions only.

Refer to the certification matrix for supported database versions.

  1. Run the following query:

    SHOW VARIABLES LIKE 'version';

  2. Check cloud/variant-specific queries.

Single JSON-column table

Do not use single-column tables with only a JSON column for replication.

  1. Run the following query:

    SELECT table_name FROM information_schema.tables WHERE table_schema='schema';

  2. Check your design and alter the table as needed.

binlog_row_image

Set binlog_row_image = FULL.

  1. Run the following command:

    SHOW VARIABLES LIKE 'binlog_row_image';

  2. Edit my.cnf and set binlog_row_image=FULL;

  3. Restart MySQL.

log-bin

Set log_bin = ON.

  1. Run the following command:

    SHOW VARIABLES LIKE 'log_bin';

  2. Edit my.cnf and set log_bin=ON;

  3. Restart MySQL.

Create the MySQL Database Connection

Follow these steps to create MySQL database connection:

  1. From the GoldenGate Studio home page, click Create connection.

  2. The Create Connection panel consists of three pages.

    1. On the General Information page, complete the fields as follows, and then click Next:

      • Enter a Connection Name.

      • (Optional) Enter a Description.

      • Select any MySQL Database, from the Technology Type dropdown.

  3. On the Connection Details page, complete the fields as follows, and click Next:

    1. Enter a Username and Password for the GoldenGate admin user, Hostname, Port, and Database Name.

    2. From the Security Protocol dropdown menu, select one of the following options:

    • Plain: Select this option if you do not require encrypted communication. This sends information without any security.

    • TLS: Select this option if you require Transport Layer Security (TLS) to encrypt communications between your client and the server. If you select TLS, you will be prompted to specify how TLS is used. Select any of these options:

       

      • Required: It mandates an encrypted connection and terminates the session if TLS is not supported, ensuring maximum security.

      • Preferred: It attempts to use encrypted TLS connections first. If TLS negotiation fails, the connection automatically falls back to unencrypted mode for compatibility.

  4. Click Test Connection.

  5. On the Confirmation page, verify the connection details.

  6. Click Create.