Connect remotely to ATP

Although ATP provides an SQL developer web interface, you may find yourself in situations where it is required from you to access the ATP instances remotely.

For this purpose, the ATP interface provides a "DB Connection" option, which contains the necessary information to log in to your instance remotely:

Click on "DB Connection" and it will take you to "Download Client Credentials". Choose the "Instance Wallet" type. And click "Download Wallet".

This action (example on Windows OS) will download an archive, wallet.zip, that once unzipped, it contains files as tnsname.ora, ojdbc and sqlnet.ora:

Note: from here on, the implemention of setting up the ATP connection is done in Windows subsystem for Linux (Ubuntu 20.04). This means that next steps can be applied not only on Ubuntu, but on other *nix flavours like CentOS (just replace "apt" with "yum"), and why not MacOS (make sure you use "brew" instead of "apt" command).

For those that do not use WSL yet, the OCI CLI for Autonomous database should be applied just the same way from Powershell terminal.

... need to create a tutorial on how to setup OCI CLI for Windows ...

For setting up OCI CLI on Linux, check tutorial "Install and configure OCI CLI"

Setting up the ATP connection

Find the compartment ID

Since I am in the tenancy (the root compartment), this makes it easier to search for the compartment-id:

root@isaacEXE:/home/ora# oci iam compartment list | grep compartment-id 
"ocid1.tenancy.oc1..aaaaaafakeocidahahah4234fasfw12",
root@isaacEXE:/home/ora# oci iam compartment list | grep compartment-id | awk {'print $2'} | sed -e 's/"//g' -e 's/,//g'
ocid1.tenancy.oc1..aaaaaafakeocidahahah4234fasfw12

Let's add it to an environment variable:

root@isaacEXE:/home/ora# export C=`oci iam compartment list | grep compartment-id | awk {'print $2'} | sed -e 's/"//g' -e 's/,//g'`
root@isaacEXE:/home/ora# echo $C
ocid1.tenancy.oc1..aaaaaafakeocidahahah4234fasfw12

Find the Database ID

Check if database 'testing' exists in that compartment ID:

root@isaacEXE:/home/ora# oci db autonomous-database list -c $C | grep display-name
      "display-name": "testing",
root@isaacEXE:/home/ora#

Now we need to find the database ID, and save it in an environment variable:

root@isaacEXE:/home/ora#  oci db autonomous-database list -c $C  --query "data[?\"db-name\"=='testing'].id | [0]" | sed 's/"//g'
ocid1.autonomousdatabase.oc1.eu-frankfurt-1.a123123fakedetailsinherehihi
root@isaacEXE:/home/ora#
root@isaacEXE:/home/ora# export DB=`oci db autonomous-database list -c $C  --query "data[?\"db-name\"=='testing'].id | [0]" | sed 's/"//g'`
root@isaacEXE:/home/ora# echo $DB
ocid1.autonomousdatabase.oc1.eu-frankfurt-1.a123123fakedetailsinherehihi

Download the Wallet

We have just discovered the database ID. Now we can use these details to download the archive wallet.zip

First of all, create a new directory and file where the wallet will be saved:

root@isaacEXE:/home/ora# mkdir wallet
root@isaacEXE:/home/ora# # create wallet.zip under newly created folder
root@isaacEXE:/home/ora# touch /home/ora/wallet/wallet.zip

Download the wallet.zip:

root@isaacEXE:/home/ora# oci db autonomous-database \
generate-wallet --autonomous-database-id $DB \
--password GivePassw0rdToY0urWall3t \
--file /home/oracky/wallet/wallet.zip

If no errors, following output should be noticed:

root@isaacEXE:/home/ora# oci db autonomous-database generate-wallet \
--autonomous-database-id $DB \
--password GivePassw0rdToY0urWall3t \
--file /home/oracky/wallet/wallet.zip

Downloading file  [####################################]  100%
root@isaacEXE:/home/ora#

root@isaacEXE:/home/ora# ls -ltr /home/ora/wallet/wallet.zip
-rw-r--r-- 1 root root 20544 Jan 17 16:44 /home/ora/wallet/wallet.zip

... and unzip the wallet.zip archive:

root@isaacEXE:/home/ora/wallet# unzip wallet.zip
Archive:  wallet.zip
  inflating: README
  inflating: cwallet.sso
  inflating: tnsnames.ora
  inflating: truststore.jks
  inflating: ojdbc.properties
  inflating: sqlnet.ora
  inflating: ewallet.p12
  inflating: keystore.jks
root@isaacEXE:/home/ora/wallet# 
root@isaacEXE:/home/ora/wallet# 
root@isaacEXE:/home/ora/wallet#  ls -ltr
total 64
-rw-r--r-- 1 root root  3335 Jan 17 14:44 truststore.jks
-rw-r--r-- 1 root root  1871 Jan 17 14:44 tnsnames.ora
-rw-r--r-- 1 root root   114 Jan 17 14:44 sqlnet.ora
-rw-r--r-- 1 root root   691 Jan 17 14:44 ojdbc.properties
-rw-r--r-- 1 root root  3274 Jan 17 14:44 keystore.jks
-rw-r--r-- 1 root root  6680 Jan 17 14:44 ewallet.p12
-rw-r--r-- 1 root root  6725 Jan 17 14:44 cwallet.sso
-rw-r--r-- 1 root root   317 Jan 17 14:44 README
-rw-r--r-- 1 root root 20544 Jan 17 16:44 wallet.zip

Change sqlnet.ora

The initial sqlnet.ora looks as below:

root@isaacEXE:/home/ora/wallet# more wallet/sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

Change the DIRECTORY with the path where wallet.zip is located (in our case, /home/ora/wallet). In the end, the sqlnet.ora file would have the following content:

root@isaacEXE:/home/ora/wallet# more wallet/sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="DIRECTORY="/home/ora/wallet")))
SSL_SERVER_DN_MATCH=yes

About tnsname.ora

This file contains (among others) the service-name of the database. If you take a look at it, you would find a few lines, each one of them starting with database name, followed by the level of performance

In our case (for low priority) would look like this:

testing_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=service_name.adb.oraclecloud.com))(security=(ssl_server_cert_dn=xxxxx")))

We also need to setup TNS_ADMIN as an environment variable. You will have to provide the path where tnsname.ora is located (in our case, under /home/ora/wallet folder)

root@isaacEXE:/home/ora/wallet#  export TNS_ADMIN=/home/ora/wallet
root@isaacEXE:/home/ora/wallet# 

Testing the ATP connection

For this step, we will be using the python module, cx_Oracle.

1. Prepare environment

a) Install the Python module:

root@isaacEXE:/home/ora#  pip3 install  cx_Oracle

b) Install the instantclient-basic libraries, specific to your Operating system (in this case, Linux), not before creating a new folder, where to download it and unpack it:

root@isaacEXE:/home/ora# mkdir -p /opt/oracle
root@isaacEXE:/home/ora# cd /opt/oracle
root@isaacEXE:/opt/oracle# wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip
--2021-01-17 17:02:57--  https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip
Resolving download.oracle.com (download.oracle.com)... 2.20.97.99
Connecting to download.oracle.com (download.oracle.com)|2.20.97.99|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 79250994 (76M) [application/zip]
Saving to: ‘instantclient-basic-linux.x64-21.1.0.0.0.zip’

instantclient-basic-linux.x64-21.1.0.0.0.zip         100%[=====================================================================================================================>]  75.58M  7.57MB/s    in 12s

2021-01-17 17:03:11 (6.29 MB/s) - ‘instantclient-basic-linux.x64-21.1.0.0.0.zip’ saved [79250994/79250994]

root@isaacEXE:/opt/oracle#
root@isaacEXE:/opt/oracle# unzip instantclient-basic-linux.x64-21.1.0.0.0.zip
[...]

c) Set-up the LD_LIBRARY_PATH, by specifying the path where instantclient has been downloaded to, and the version (in our case, /opt/oracle/instantclient_21_1)

root@isaacEXE:/opt/oracle# # either setup as an environment variable
root@isaacEXE:/opt/oracle# export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1:$LD_LIBRARY_PATH
root@isaacEXE:/opt/oracle# # or create a link to shared libraries
root@isaacEXE:/opt/oracle# sh -c "echo /opt/oracle/instantclient_21_1 > /etc/ld.so.conf.d/oracle-instantclient.conf"
root@isaacEXE:/opt/oracle# ldconfig

d) Install libaio1 to use Linux kernel asynchronous I/O system calls

root@isaacEXE:/opt/oracle#  apt-get install libaio1

2. Test the connectivity

Now, time to implement a simple test:

root@isaacEXE:/opt/oracle# python3 
>>> import cx_Oracle as cx
>>> # create connection - use <database>_<priority> 
>>> con = cx.connect("ADMIN", "yourAdm!npassw0rd", "testing_low")
>>> # use cursor class to execute queries
>>> curs = con.cursor()
>>> query = cur.execute('select * from dba_users')
>>> rows = cur.fetchmany(4)
>>> for row in rows:
...     print(row)
...
('GGADMIN', 88, None, 'LOCKED', datetime.datetime(2020, 1, 27, 23, 37, 37), None, 'DATA', 'TEMP', 'TEMP', datetime.datetime(2020, 1, 27, 23, 37, 37), 'ORA_ADMIN_PROFILE', 'LOW', None, None, 'N', 'NONE', 'N', 'NO', None, 'N', 'NO', 'USING_NLS_COMP', 'NO', 'NO', None)
('SYSTEM', 9, None, 'OPEN', None, None, 'SYSTEM', 'TEMP', 'TEMP', datetime.datetime(2020, 1, 27, 22, 5, 35), 'DEFAULT', 'DEFAULT_CONSUMER_GROUP', None, '11G 12C ', 'N', 'PASSWORD', 'N', 'YES', None, 'Y', 'YES', 'USING_NLS_COMP', 'NO', 'NO', None)
('XS$NULL', 2147483638, None, 'EXPIRED & LOCKED', datetime.datetime(2020, 1, 27, 22, 21, 54), None, 'SYSTEM', 'TEMP', 'TEMP', datetime.datetime(2020, 1, 27, 22, 21, 54), 'DEFAULT', 'DEFAULT_CONSUMER_GROUP', None, '11G ', 'N', 'PASSWORD', 'N', 'YES', None, 'Y', 'YES', 'USING_NLS_COMP', 'NO', 'NO', None)
('DBSNMP', 57, None, 'OPEN', None, None, 'SYSAUX', 'TEMP', 'TEMP', datetime.datetime(2020, 1, 27, 22, 29, 13), 'DEFAULT', 'DEFAULT_CONSUMER_GROUP', None, '11G 12C ', 'N', 'PASSWORD', 'N', 'YES', None, 'Y', 'YES', 'USING_NLS_COMP', 'NO', 'NO', None)
>>>