Access External Data Sources from Oracle Database

  • May 11, 2018

Using OHSH with Oracle Wallet

As we have seen in several earlier blog posts, OHSH is an easy-to-use CLI to move data between Apache Hadoop and Oracle Database.

In this blog we will demonstrate how to use OHSH with Oracle Wallet.

The initial resources you will create when using OHSH are the SQL*Plus resource and JDBC resource to connect to Oracle Database.   For example,

ohsh> create sqlplus resource sql0 connectid=”<database-connection-URL>”

This OHSH command will prompt you for username and password.

This can be automated by using Oracle Wallet, which is the secure way to connect to Oracle Database.


Step 1.   Create an Oracle Wallet.   Refer to Oracle Wallet documentation for details. 

The Oracle Wallet documentation also explains the updates needed to tnsnames.ora and sqlnet.ora.  Below are examples.

tnsnames.ora entry (this might already exist)



    (ADDRESS = (PROTOCOL = TCP)(HOST = xyz.oracle.com)(PORT = <port number>))



      (SERVICE_NAME = <service name>)




sqlnet.ora entry


   (SOURCE =



      (DIRECTORY = <full path of wallet location>)







Step 2.   Configure OHSH to use Wallet.   On the client/edge node where you are running OHSH:   

Edit $OHSH_HOME/bin/ohsh_config.sh to update the following environment variables:

TNS_ADMIN=<location of tnsnames.ora and sqlnet.ora>

WALLET_LOCATION=<location of Oracle Wallet files>


Step 3.   Add the Oracle Wallet files and tnsnames.ora and sqlnet.ora to Hadoop cluster nodes.

Step 3.a. Copy the Oracle Wallet files, and tnsnames.ora and sqlnet.ora, to the same location in every node on the Hadoop cluster, and the Hadoop client/edge node where you launch OHSH.   On Oracle Big Data Appliance this task is easier since BDA has a shared NFS location that can be shared by all nodes.    You can copy the files to this shared NFS location.  On other clusters the location of Oracle Wallet files and *.ora files could be, for example,


The location of the tnsnames.ora and sqlnet.ora could be


These directories should be the same on all the nodes and the Hadoop client/edge node from where you launch OHSH.

If the location of the Oracle Wallet files and *.ora files on the Hadoop cluster nodes (set in Step 3.a) is different from the location of these files on the client/edge node (set in Step 2) where you are running OHSH, follow Step 3.b.

Step 3.b. Startup the OHSH CLI

$ ohsh

Set hadooptnsadmin and hadoopowalletlocation to the directory paths in Step 3.    For example,

ohsh> set hadooptnsadmin /home/oracle/tns_admin

ohsh> set hadoopwalletlocation /home/oracle/oracle_wallet


Step 4.   Startup the OHSH CLI.

Use the identifier in tnsnames.ora when creating the SQL*Plus and JDBC resources.  For example, if you have the following entry in tnsnames.ora:



    (ADDRESS = (PROTOCOL = TCP)(HOST = xyz.oracle.com)(PORT = <port number>))



      (SERVICE_NAME = <service name>)



You can create the sqlplus resource as follows:

ohsh> create sqlplus resource sql0 connectid=”orcl”

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.