Liquibase Enterprise was formerly known as Datical DB.

Testing Oracle Connections

Owned by Former user (Deleted)
Last updated: Apr 19, 2022 by Former user (Deleted)
3 min read


This command-line script helps you test normal, TNSNAMES, SSL, and LDAP connections to Oracle databases. 

It reports on the following:

  • Use of environment variables to locate configuration files
  • Location of configuration files
  • tnsping results when the connection is tested
  • JDBC driver URL, version, and connection test

Running the OraConnect.groovy Test Script

Important

We strongly recommend that you use the OraConnect.groovy test script to validate your Oracle database connections BEFORE attempting to use the connection for Datical DB operations (package, forecast, deploy). 

The OraConnect.groovy test script checks the inter-dependencies in Oracle database connections. It tests for the presence, settings, and relationships of environment variables, configuration files, and DbDef configuration. 

  1. In a command window, go to your Datical DB project directory. 

  2. Run the OraConnect.groovy script. Use a project step (DbDef) name as a parameter.

    hammer groovy OraConnect.groovy <DaticalDBDefName>

Note

The command and parameters are case-sensitive. 

Sample Output for TNSNAMES Connections

$ hammer OraConnect.groovy REF
EXECUTING Hammer from  /home/wesley/workspace/DaticalDB-CLI-tests

daticaldb.properties file found at:
/home/wesley/datical/Wallet2/daticaldb.properties

Datical DB Admin License
Issued to : CN=This is not a real license! Do Not Distribute
Issued on : Mon May 06 12:29:25 CDT 2019
Expires on: Thu Jun 06 12:29:21 CDT 2019


============================= Environment variables =============================
ORACLE_HOME:  /home/wesley/app/12.2.0.1.0/wesley/product/12.2.0.1.0/client_1
TNS_ADMIN:    /home/wesley/admin

============================= Configuration files =============================
sqlnet.ora is located at:
/home/wesley/app/12.2.0.1.0/wesley/product/12.2.0.1.0/client_1/network/admin/sqlnet.ora

Wallet location directory is:
/home/wesley/admin
Wallet file:  cwallet.sso

ojdbc.properties is located at:
/home/wesley/admin/ojdbc.properties

Wallet location directory is:
/home/wesley/app/wallet
Wallet file:  cwallet.sso

The order of naming methods is:
[TNSNAMES, EZCONNECT]

tnsnames.ora is located at:
/home/wesley/admin/tnsnames.ora

============================= Testing connections =============================

Testing sqlplus and Data Pump (expdp/impdp) connections:

Executing tnsping for TNS name:
ORA_AWS


TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 24-MAY-2019 18:01:37

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/home/wesley/app/12.2.0.1.0/wesley/product/12.2.0.1.0/client_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS) (HOST = aws-db1-rhel6.datical.net) (PORT = 2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl.datical.net)))
OK (240 msec)

JDBC URL:

jdbc:oracle:thin:@ORA_AWS
JDBC CONNECTION SUCCESSFUL


Sample Output for LDAP Connections

EXECUTING Hammer from  /home/wesley/workspace/DaticalDB-CLI-tests

daticaldb.properties file found at:
/home/wesley/datical/Wallet2/daticaldb.properties

Datical DB Admin License
Issued to : CN=This is not a real license! Do Not Distribute
Issued on : Mon May 06 12:29:25 CDT 2019
Expires on: Thu Jun 06 12:29:21 CDT 2019


============================= Environment variables =============================
ORACLE_HOME:  /home/wesley/app/12.2.0.1.0/wesley/product/12.2.0.1.0/client_1
TNS_ADMIN:    /home/wesley/admin
LDAP_ADMIN:   /home/wesley/ldap

============================= Configuration files =============================
sqlnet.ora is located at:
/home/wesley/admin/sqlnet.ora

Wallet location directory is:
/home/wesley/app/wallet
Wallet file:  cwallet.sso

ojdbc.properties is located at:
/home/wesley/admin/ojdbc.properties

Wallet location directory is:
/home/wesley/app/wallet
Wallet file:  cwallet.sso

The order of naming methods is:
[LDAP, TNSNAMES, EZCONNECT]

tnsnames.ora is located at:
/home/wesley/admin/tnsnames.ora

ldap.ora is located at:
/home/wesley/admin/ldap.ora

============================= Testing connections =============================

Testing sqlplus and Data Pump (expdp/impdp) connections:

Executing tnsping for TNS name:
ORA_AWS_WALLET


TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 24-MAY-2019 18:08:46

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/home/wesley/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=aws-db1-rhel6.datical.net)(PORT=2484)))(CONNECT_DATA=(SERVICE_NAME=pdborcl.datical.net)))
OK (200 msec)

JDBC URL:

jdbc:oracle:thin:@ldap://ec2-aws.com:3060/ORA_AWS_WALLET,cn=OracleContext,dc=us,dc=oracle,dc=com
JDBC CONNECTION SUCCESSFUL


Sample Output for SSL Through Oracle Wallet Connections

EXECUTING Hammer from  /home/wesley/workspace/DaticalDB-CLI-tests

daticaldb.properties file found at:
/home/wesley/datical/Wallet2/daticaldb.properties

Datical DB Admin License
Issued to : CN=This is not a real license! Do Not Distribute
Issued on : Mon May 06 12:29:25 CDT 2019
Expires on: Thu Jun 06 12:29:21 CDT 2019


============================= Environment variables =============================
ORACLE_HOME:  /home/wesley/app/12.2.0.1.0/wesley/product/12.2.0.1.0/client_1
TNS_ADMIN:    /home/wesley/admin
LDAP_ADMIN:   
JAVA_HOME:    /usr/lib/jvm/jdk1.8.0_121/jre

============================= Configuration files =============================

SQLNET.ORA
===========

sqlnet.ora is located by TNS_ADMIN:
/home/wesley/admin/sqlnet.ora

The order of naming methods is:
[LDAP, TNSNAMES, EZCONNECT]

   Wallet location directory is:
   /home/wesley/app/wallet

      Wallet file:  cwallet.sso

OJDBC.PROPERTIES
================

ojdbc.properties is located by TNS_ADMIN:
/home/wesley/admin/ojdbc.properties

   Wallet location directory is:
   /home/wesley/app/wallet

      Wallet file:  cwallet.sso

TNSNAMES.ORA
=============

tnsnames.ora is located by TNS_ADMIN:
/home/wesley/admin/tnsnames.ora

LDAP.ORA
========

ldap.ora is located by LDAP_ADMIN:
/home/wesley/ldap/ldap.ora

============================= Testing connections =============================

Testing sqlplus and Data Pump (expdp/impdp) connections:

Executing tnsping for TNS name:
ORA_AWS_WALLET


TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 31-MAY-2019 09:19:16

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/home/wesley/admin/sqlnet.ora


Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS) (HOST = aws-db1-rhel6.datical.net) (PORT = 2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl.datical.net)))
OK (170 msec)
TNSPING SUCCESSFUL

JDBC URL:

jdbc:oracle:thin:@ORA_AWS_WALLET

Oracle JDBC driver version is 18.3.0.0.0

JDBC CONNECTION SUCCESSFUL

Copyright © Liquibase 2012-2022 - Proprietary and Confidential