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..aaaaaafakeocidahahah4234fasfw12Let'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..aaaaaafakeocidahahah4234fasfw12Find 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.a123123fakedetailsinherehihiDownload 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.zipDownload the wallet.zip:
root@isaacEXE:/home/ora# oci db autonomous-database \
generate-wallet --autonomous-database-id $DB \
--password GivePassw0rdToY0urWall3t \
--file /home/oracky/wallet/wallet.zipIf 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.zipChange 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=yesChange 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=yesAbout 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.
Prepare environment
a) Install the Python module:
root@isaacEXE:/home/ora# pip3 install cx_Oracleb) 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# ldconfigd) Install libaio1 to use Linux kernel asynchronous I/O system calls
root@isaacEXE:/opt/oracle# apt-get install libaio12. 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)
>>>Last updated