# 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.`                                                                &#x20;

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

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MRG3EpN_RF1YEJuTNGX%2F-MRG3i2g2HJ4ZzkgVEaX%2F25.png?alt=media\&token=49d69782-24d2-4351-bf4d-5c83c9c88912)

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

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MRG3EpN_RF1YEJuTNGX%2F-MRG4A0PkODeDlNo-GMh%2F26.png?alt=media\&token=a7ed7767-93fe-4636-afdc-4dd936a6a291)

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:

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MRG3EpN_RF1YEJuTNGX%2F-MRG4ikAJ4FPvyjyc3qF%2Fcontent_wallnet.png?alt=media\&token=835e003e-6fc3-4e51-b732-bfe48ba7c9dd)



_**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)._&#x20;

_For those that do not use WSL yet,  the OCI CLI for Autonomous database  should be applied just the same  way from Powershell terminal._&#x20;

> _... need to create a tutorial on how to setup OCI CLI for Windows ..._&#x20;

_For setting up OCI CLI on Linux, check tutorial_ [_"Install and configure OCI CLI"_](../1.-deployment-instance/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&#x20;

`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`_ ](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/manage-priorities.html#GUID-610D50B3-A0F0-4059-B940-324E305C5F55)

`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`](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#installing-cx-oracle-on-linux)`.`



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
[...]
```

&#x20;`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)
>>>
```



