OCI-Oracle ATP- Connect via SQLPlus on Linux

Continuing from my OCI series: Moving On-Premises Oracle Apex application to OCI Oracle Autonomous Transaction Processing

To connect to the ATP instance from a Linux machine, we have to do a little more leg work.

Some things to review before we get started.
The client software should be 11.2.0.4 (full install) or higher to be able to connect to the ATP instance. Instant Client can be downloaded and installed from these links:

Oracle Instant Client Downloads
Instant Client Downloads for Linux x86-64 (64-bit)

Downloads

Download the appropriate version per your requirements. I have installed the 18.5 version.

• Basic Package - All files required to run OCI, OCCI, and JDBC-OCI applications
• Tools-optional Package SQL*Plus Package - The SQL*Plus command-line tool for SQL and PL/SQL queries
• Tools Package - Includes Data Pump, SQL*Loader and Workload Replay Client

I downloaded it to my local machine and then uploaded it to the Linux machine via FTP. There are ways to do "wget", but require more work as Oracle Authentication is required.

Once installed, I unzipped in the oracle user home

/home/oracle/instantClient

If you have the other client also installed on the machine, you may have to set-up things slightly different, but basic steps are the same.

Setup Wallet and configuration files

Download the client credentials (Wallet Zip) to the machine, and uninstall it to the home directory for oracle

drwxrwxr-x 2 oracle oracle  4096 Oct 13 17:28 Wallet_BuzzTAQDev
-rw-rw-r-- 1 oracle oracle 19814 Oct 13 17:25 Wallet_BuzzTAQDev.zip

Change the underlying file permissions to 600

chmod  600 *
[oracle@ip-172-30-0-39 Wallet_BuzzTAQDev]$ ll
total 36
-rw------- 1 oracle oracle 6669 Oct 12 23:21 cwallet.sso
-rw------- 1 oracle oracle 6624 Oct 12 23:21 ewallet.p12
-rw------- 1 oracle oracle 3244 Oct 12 23:21 keystore.jks
-rw------- 1 oracle oracle   87 Oct 12 23:21 ojdbc.properties
-rw------- 1 oracle oracle  114 Oct 12 23:21 sqlnet.ora
-rw------- 1 oracle oracle 1751 Oct 12 23:21 tnsnames.ora
-rw------- 1 oracle oracle 3336 Oct 12 23:21 truststore.jks

If Oracle database or client is already installed, we can put the tnsnames and sqlnet files in the current admin/network directory in ORACLE_HOME
If not, we will move the files to the instant client network/admin/ location

/home/oracle/instantClient/network/admin

Change the sqlnet.ora file :

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/Wallet_BuzzTAQDev")))

#SSL_SERVER_DN_MATCH=yes

Make sure that all the tnsnames in the downloaded file are in the final tnsnames.ora file

<>_high

<>_low

<>_mediom

<>_tp

<>_tpurgent

Client configuration:

Add the location of the client install in the current path variable:

export PATH=$PATH:/home/oracle/instantClient >> /home/oracle/.bash_profile

add the library path

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/oracle/instantClient >> /home/oracle/.bash_profile

If you already have sql client installed, you may have to rename the key files to ensure you are running the proper version

mv sqlplus sqlplus18

Going forward, we will use the sqlplus18 instead of sqlplus to run our scripts.

Test your connection

[oracle@ip-172-30-0-39 instantClient]$  sqlplus18 admin@buzztaqdev_medium
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Oct 15 18:13:44 2019
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Enter password:
Message 1549 not found;  product=SQLPlus; facility=SP2
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL>

Happy testing!!

Sachin
Chief Architect
Oracle Apex Enthusiast
BuzzClan