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 GoldenGate Studio 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.

Supported Connections

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

GoldenGate Studio supports Oracle AI 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 AI Database@Azure - Autonomous AI Database	Microsoft Azure	All
Oracle AI Database@Azure - Exadata	Microsoft Azure	All
Oracle AI Database@AWS - Autonomous AI Database	Amazon Web Services	All
Oracle AI Database@AWS - Exadata	Amazon Web Services	All
Oracle AI Database@Google Cloud - Autonomous AI Database	Google Cloud Platform	All
Oracle AI 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 Studio 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 Studio 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 AI 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 Studio 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 GoldenGate Studio 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 GoldenGate Studio monitoring tools to check for connection drops, slow queries, or authentication failures.

Create Oracle AI Database and Oracle Exadata Connections

To create Oracle AI Database and Oracle Exadata connections:

1. From the GoldenGate Studio Home page, select 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 select 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 select 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 AI Database 21c or above.

      • If your Oracle AI Database version is 19c or below, select Pluggable database (PDB) in Oracle AI 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, select Create.

Configure and Download SSL Wallet for Non Autonomous AI Database

Wallet configuration is required for 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.

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.

Terms you may need to know:

• 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

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 AI 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

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.

To download/configure Manual Wallet:

1. Download the required certificates for the parUrl- typically a root certificate and an intermediate certificate.

2. Log in to the database host, and then run the following commands to create a wallet directory:

   bash-4.2$ mkdir -p <wallet-path-accessible-to-db-user>

3. Initialize a wallet using orapki utility available in Oracle base.

   bash-4.2$ orapki wallet create -wallet <wallet-path-accessible-to-db-user> -pwd <pwd> -auto_login

4. Add certs downloaded from parUrl to wallet using orapki utility.

   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.

Create the connection

To create an Oracle Autonomous AI Database connection:

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

2. The Create Connection panel consists of three pages.

   1. On the General Information page, complete the fields as follows, then select 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 select Next:

      1. Enter a Username and Password for the GoldenGate Studio admin user.

      2. Select Add Wallet File and upload the Oracle Autonomous AI Database instance's wallet.

      3. Select Test Connection.

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

3. Select Create.

Create MySQL Database Connection

Learn about the prerequisites for creating a MySQL database connection and the steps to create the connection in GoldenGate Studio.

Before You Begin

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

To apply configuration changes, 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.

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)

Set GoldenGate Studio server timezone to be the same as the MySQL server timezone.

1. Check the MySQL timezone:

   SELECT @@system_time_zone;

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

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.

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;

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.

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.

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.

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.

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;

Upgrade to MySQL 5.7 or higher.

1. Run the following command:

   SHOW VARIABLES LIKE 'version';

2. If you have lower version, then ask the administrator to upgrade.

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;

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;

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.

Remove functional indexes.

1. Review the table definition, then run the following:

   ALTER TABLE table DROP INDEX index_name;

Enable gtid_mode or use special GoldenGate Studio paramaters.

1. Run the following command:

   SHOW VARIABLES LIKE 'gtid_mode';

2. Set the _DISABLEGTIDRECOVERY_true parameter in GoldenGate Studio.

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);

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.

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.

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 administrator for application change.

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.

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.

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.

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.

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.

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, select Create connection.

2. The Create Connection panel consists of three pages.

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

      • Enter a Connection Name.

      • (Optional) Enter a Description.

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

   2. On the Connection Details page, complete the fields as follows, and select Next:

      1. Enter a Username and Password for the GoldenGate Studio 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.

      3. Select Test Connection.

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

3. Select Create.