/
Testing Oracle Connections

Liquibase Enterprise was formerly known as Datical DB.

Testing Oracle Connections

Apr 19, 2022

 

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