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

OCI - ATP Database - Basic connectivity - SQL Developer

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

Once the database is provisioned, you can connect from various clients.

1. Credential Zip File

The Credential zip contains the following files:

• Wallet and keystore:
• cwallet.sso
• ewallet.p12
• keystore.jks
• truststore.jks
• Configuration
• sqlnet.ora
• tnsnames.ora

TNS entries are as follows:

• High
• Low
• Medium
• TP
• TPurgent

Connecting to SQLDeveloper

Various versions will have different options when it comes to the connection type. I am sharing a couple of things I have seen. Either way, here are a few pointers. 

Download the latest SQL Developer. Cloud connections and secure connections are not available in older versions.

SQL Developer 19.2

SQL Developer 18.4

Open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.

Note:

Depending on your version of SQL Developer, do not right-click Cloud Connection or Database Schema Service Connections. That menu selection is for connecting to a different Oracle cloud service, the Oracle Database Schema Service.

To start a new connection, start with New Connection, not the Cloud Connection.

The New/Select Database Connection dialog appears. Enter the following information:

• Connection Name - Enter the name for this cloud connection.
• Username - Enter the database username. Use the default administrator database account (admin) that is provided as part of the service.
• Password - Enter the admin user's password that you or your Autonomous Transaction Processing administrator specified when creating the service instance.
• Connection Type - Select Cloud Wallet. (or Cloud PDB)
• Configuration File - Click Browse, and select the Client Credentials zip file, downloaded from the Autonomous Transaction Processing service console by you.
• Service - In the drop-down menu, service selections are prepended with database names. Select the tpurgent, tp, high, medium, or low menu item for your database. These service levels map to the TPURGENT, TP, HIGH, MEDIUM and LOW consumer groups, which provide different levels of priority for your session. 

OR

Click Test, Save and Connect.

Link to the ObE - Connecting SQL Developer to Autonomous Transaction Processing

Next: OCI-Oracle ATP- Connect via SQLPlus on Linux

Sachin
Chief Architect
Oracle Apex Enthusiast
BuzzClan