Configuring Oracle GoldenGate with TLS to Securely Connect to the Oracle Database

Transport Layer Security (TLS) helps keep your connections secure between an Oracle Database client and server by encrypting data. You can choose between a self-signed certificate or one from a public Certificate Authority (CA).

With TLS, you can use one-way authentication, where only the server proves its identity, or mutual TLS (mTLS), where both the client and server verify each other. Sometimes, a client wallet is needed to store certificates, but this isn’t always true.

Using Distinguished Name (DN) matching to ensure you connect to the right server is recommended. While you can customize TLS settings, sticking with the default options for optimal security is usually best.

For this week, #GoldenTuesday, I am playing with configuring Oracle GoldenGate (OGG) to use TLS for secure communication with an Oracle Database, which requires configuring both the GoldenGate and Database Server to use TCPS (TLS-encrypted communication). Extract and Replicat are in the same database for demonstration purposes using two distinct schemas (SOURCE_HR and TARGET_HR).

Ensure there are no local or network firewalls blocking communication with the server on port 2484. The examples in this article use self-signed certificates, but you can just as easily use proper certificate authority certificates if you prefer.

Step 1: Enable TLS on the Oracle Database

Ensure your Oracle Database is set up to accept TLS (TCPS) connections.

1.1 Create an Oracle Wallet for TLS Certificates

On the Oracle Database Server, create a wallet for storing TLS certificates:

$ mkdir -p /u01/app/oracle/admin/wallet

$ orapki wallet create -wallet /u01/app/oracle/admin/wallet -auto_login -pwd Welcome##123

Generate a self-signed certificate:

$ orapki wallet add -wallet /u01/app/oracle/admin/wallet -dn "CN=dbserver,OU=DBA,O=YourOrg,L=YourCity,ST=YourState,C=YourCountry" -keysize 2048 -self_signed -validity 3650 -pwd Welcome##123

Export the public certificate:

$ orapki wallet export -wallet /u01/app/oracle/admin/wallet -dn "CN=dbserver,OU=DBA,O=YourOrg,L=YourCity,ST=YourState,C=YourCountry" -cert /u01/app/oracle/admin/wallet/server_cert.pem -pwd Welcome##123

Check the certificate:

$ cat /u01/app/oracle/admin/wallet/server_cert.pem
-----BEGIN CERTIFICATE-----
MUUDTBisnAQAwDQYJKoZIhvcNAQEEBQAwHjEcMBoGA1UEAxMTb2w3LTEyMS5sb2NhbGRvbWFp
bjAeFw0xNTA2MjYxNDQyMDJaFw0yNTA2MjMxNDQyMDJaMB4xHDAaBgNVBAMTE29sNy0xMjEubG9j
YWxkb21haW4wgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAJIrU1fGWAwMxRobFsS0UZBD1jFU
wAvnH9blsynhrQrZSkwyMBWGPRFq5tufRpaifoNVVHSrjJm/nti62A6RXECAKsug9rHL8T11FOgP
3R/+Itw2jLzwpdk7MbHMxpNHz6Y2IPCmBsJ5+625dRxugVKhLsIitAW5cUpT28bkrMl9AgMBAAEw
DQYJKoZIhvcNAQEEBQADgYEABqQaP056WcPNgzSAOhJgTU/6D8uAFGCgUN57HoraXxgAN3HgmeGq
hQfpb8tP+xeTF3ecqvWqJQHGdAJbuhRwpNR1rRovvlOxiv4gl0AplRzRuiygXfi6gst7KNmAdoxr
TOcUQsqf/Ei9TaFl/N8E+88T2fK67JHgFa4QDs/Ne3M==
-----END CERTIFICATE-----
$

1.2 Configure the Database Server for TCPS

Modify the listener.ora file:

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/wallet)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = dbserver)(PORT = 2484))
    )
  )

Modify sqlnet.ora:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/wallet)
    )
  )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE

Restart the listener:

lsnrctl stop

lsnrctl start

lsnrctl status

Ensure TCPS is listening on port 2484:

lsnrctl services

Step 2: Configure Oracle GoldenGate for TLS Connection

2.1 Copy the Database Certificate to the GoldenGate Server

On the OGG server, create a wallet and import the database’s server certificate.

$ mkdir -p /u01/app/ogg/wallet
scp oracle@db_server:/u01/app/oracle/admin/wallet/server_cert.pem /u01/app/ogg/wallet

$ orapki wallet create -wallet /u01/app/ogg/wallet -auto_login -pwd Welcome##123

$ orapki wallet add -wallet /u01/app/ogg/wallet -trusted_cert -cert /path/to/server_cert.pem -pwd Welcome##123

2.2 Configure tnsnames.ora for OGG

Modify tnsnames.ora on the GoldenGate server to use TCPS:

OGGDB_TLS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = dbserver)(PORT = 2484))
    (CONNECT_DATA =
      (SERVER=dedicated)      
      (SERVICE_NAME = west)
    )
  )

Modify sqlnet.ora on the GoldenGate server:

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/ogg/wallet)
    )
  )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)
SSL_CLIENT_AUTHENTICATION = FALSE

Step 3: Test TLS Connection from GoldenGate

Test if the connection over TCPS is working:

sqlplus username/password@OGGDB_TLS

To verify the connection protocol:

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') FROM dual;

If successful, it will return TCPS.

Step 4: Configure GoldenGate Extract and Replicat with TCPS

Modify Extract (EWEST.prm) and Replicat (RWEST.prm) parameter files:

EXTRACT EWEST
EXTTRAIL ew
USERIDALIAS ogg_tls DOMAIN oracle_db_wallet
DDL INCLUDE MAPPED
TABLE SOURCE_HR.*;

REPLICAT RWEST
USERIDALIAS ogg_tls DOMAIN oracle_db_wallet
DDL INCLUDE MAPPED
MAP SOURCE_HR.*, TARGET TARGET_HR.*;

Create the GoldenGate credential store entry:

$ cd $OGG_HOME

./adminclient

ADD CREDENTIALSTORE

ALTER CREDENTIALSTORE ADD USER oggadmin@OGGDB_TLS PASSWORD Vip!only## ALIAS ogg_tls DOMAIN oracle_db_wallet

Verify:

INFO CREDENTIALSTORE

Step 5: Start GoldenGate Processes

Start the Extract:

START EXTRACT EWEST

Start the Replicat:

START REPLICAT RWEST

Check logs:

VIEW REPORT EWEST

VIEW REPORT RWEST

Step 6: Validate Secure Replication

To check TCPS is being used, query the GoldenGate LogMiner session:

SELECT SESSION_ID, NETWORK_SERVICE_BANNER
FROM V$SESSION_CONNECT_INFO
WHERE NETWORK_SERVICE_BANNER LIKE '%TCPS%';

If successful, you should see an entry with TCPS.

Troubleshooting

1. Check if TCPS is Listening

Run:

$ netstat -tnlp | grep 2484

2. Check GoldenGate Connection

Run:

dblogin useridalias ogg_tls domain oracle_db_wallet

3. Verify Oracle Wallet

Run:

$ orapki wallet display -wallet /u01/app/ogg/wallet

4. Enable More Logging

In sqlnet.ora, add:

TRACE_LEVEL_SERVER = SUPPORT

TRACE_LEVEL_CLIENT = SUPPORT

Check logs:

$ tail -f $ORACLE_HOME/diag/tnslsnr/$(hostname)/listener/alert/log.xml

Now, your GoldenGate environment connection to the Oracle Database should be secured using TLS (TCPS)!

Reference

Thanks to Tim Hall for the reference in his post:

https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections

Alex Lima