Prepare Database Connection

Oracle GoldenGate for Yugabyte (PostgreSQL variant) connects to Yugabyte databases using a pre-packaged ODBC driver. Connections can be established using a Data Source Name (DSN) or using a direct connection and supplying the database server host, port, database, and other information.

Using DSN connections requires connection details to be listed in an odbc.ini file, while using direct entries are entered manually when adding a database connection from the web interface or from the Admin Client.

Note:

PgBouncer is not supported for Oracle GoldenGate connections.

Note:

Oracle GoldenGate does not support connections to Yugabyte that use Pgpool.

After performing the steps given below to create the DSN entries or you plan to use direct connections, proceed to the Add Database Connections topic to know how to create database connections.

Set up a DSN-based Connection

To create a DSN connection for Oracle GoldenGate processes, you will first need to add an environment variable for the Oracle GoldenGate for Yugabyte deployment and then create an odbc.ini file to store the connection attributes.

  1. Log in to the Service Manager web interface.

  2. From the left navigation pane, click Deployments and then select the deployment name for Yugabyte. This expands the various settings for the deployment.

  3. Click Configuration and then click the plus sign (+) next to Environment Variables.

  4. Provide the following information in the two available fields. For the Environment Variable Value field, ensure it is the absolute path of the odbc.ini file:

    Environment Variable Name = ODBCINI

    Environment Variable Value = /ogg/deployment/yugabyte/odbc.ini

  5. Click Submit to create the new variable and then restart the deployment from the Deployments pane, for the changes to take effect.

  6. In the Oracle GoldenGate installation's deployment folder, manually create an odbc.ini file and add data sources in this file.

Use the following minimum settings when creating the DSN file:

  • Data Source Name – A user defined name of a source or target database connection that will be referenced by Oracle GoldenGate processes, such as Extract or Replicat. DSN names are allowed up to 32 alpha-numeric characters in length, and can include only underscore (_) and dash (-) from special characters.

  • IANAAppCodePage=4 – Is the default setting but can be modified according to the guidance specified on the https://docs.progress.com/bundle/datadirect-connect-odbc-71/page/IANAAppCodePage_9.html#IANAAppCodePage_9 page when the database character set is not Unicode.

  • InstallDir – Is the name of the Oracle GoldenGate PostgreSQL wire protocol driver path, and can use a relative path, like: InstallDir =./datadirect

  • Driver: Is the name of the Oracle GoldenGate PostgreSQL Wire Protocol driver file, and can use a relative path, like: Driver=./datadirect/lib/ggpsql25.so.

  • Database: This is the name of the source or target database.

  • Hostname: This is the database host IP address or host name.

  • PortNumber: This is the listening port of the database.

  • Oracle GoldenGate for Yugabyte support multiple hosts and ports for Yugabyte connections. This feature is useful in the Oracle GoldenGate High Availability (HA) setup. In a Yugabyte HA setup there is one primary and multiple standby servers. You can set up the connection string format to specify multiple hosts:

    Connection String Description 
    db-user@host1,host2,host3/db-name
    		 The connections would be attempted using default Yugabyte database port 5433 for all the hosts (host1, host2, host3). 
    db-user@host1:1234,host2:2345,host3:3456/db-name
    		 The connections would be attempted using the ports associated with each host specified in the connection string. 
    db-user@host1,host2,host3:1234/db-name
    		 All hosts using the same port number (1234) on all the hosts (host1, host2, host3). The connections would be attempted using the port 1234 for all the hosts (host1, host2, host3). 
    db-user@host1,host2,host3:1234/db-name
    		 All hosts using the same port number (1234) on all the hosts (host1, host2, host3). The connections would be attempted using the port 1234 for all the hosts. 
    db-user@host1:3456,host2,host3:1234/db-name

    If the connection string is specified in other formats where the port number is specified for some hosts while the other hosts are specified without any ports, then the last or right-most port specification will be used as the port number for all the hosts which do not have a port number specified in the connection string. As shown in the connection string:

    db-user@host1:3456,host2,host3:1234/db-name

    The port number for host2 would be 1234, which is the right-most port number specified in the connection string.

  • You can also provide a LogonID and Password for the Extract or Replicat user, but these will be stored in clear text. It is recommended to leave these fields out of the DSN and instead store them in the Oracle GoldenGate wallet as a credential alias, and reference them with the USERIDALIAS parameter in Extract and Replicat.

The following is an example odbc.ini file with two DSN entries. The Data Source names used in the example below are PG_src and PG_tgt.

  1. Save and close the odbc.ini file.

  2. To set up the database connection from Oracle GoldenGate for a Yugabyte deployment, see Add Database Connections.