How to connect to Oracle ATP using SQL Plus

I have created ATP instance using terraform from OCI compute instance. Here are the steps to connect to ATP instance using sqlplus, from OCI compute instance.

Install Oracle Instant Client RPM

I have provisioned compute instance using Oracle Linux 7.6 image. From OCI compute instance, run below steps to install instant client.

to pull the correct yum repo, need to figure out the OCI Region. My account is in Ashburn region(Home Region). Use below steps to figure out the region code.

cd /etc/yum.repos.d
 
# specific to OCI compute instances, get the yum mirror for the region
export REGION=`curl http://169.254.169.254/opc/v1/instance/ -s | jq -r '.region'| cut -d '-' -f 2`

# use below commands to verify that the region is set
export | grep REGION
or
echo $REGION

I am in Ashburn region. So above commanded returned iad

#  below command will download the yum repo file your your region
sudo -E wget http://yum-$REGION.oracle.com/yum-$REGION-ol7.repo

# Since I am in Ashburn region, it downloaded yum-iad-ol7.repo
# open yum-iad-ol7.repo
less yum-iad-ol7.repo

TBD # List available packages before enabling the repo - include image
# list all packages with name 
yum list oracle-instantclient*   

# There is and entry with name "ol7_oci_included" in yum-iad-ol7.repo
# enable repository wih name "ol7_oci_included" using yum-config-manager 
sudo yum-config-manager --enable ol7_oci_included

list all packages after enabling the repo

yum list oracle-instantclient*    

We can see that oracle-instantclient* packages are available now.

install required pkgs

sudo yum install -y  oracle-instantclient18.3-basic.x86_64 oracle-instantclient18.3-devel.x86_64 oracle-instantclient18.3-jdbc.x86_64 oracle-instantclient18.3-sqlplus.x86_64 oracle-instantclient18.3-precomp.x86_64 oracle-instantclient18.3-tools.x86_64

Now sqlplus binary is available under /usr/lib/oracle/18.3/client64/bin

Download and unzip wallet zip file

Since I have created ATP instance using terraform, from this OCI compute instance, the wallet zip file is already available under "/home/opc/atp-example/autonomous_database_wallet.zip"

cd /home/opc/atp-example/

jar -tvf /home/opc/atp-example/autonomous_database_wallet.zip 6661 Fri Apr 12 13:53:16 GMT 2019 cwallet.sso 3422 Fri Apr 12 13:53:16 GMT 2019 tnsnames.ora 3336 Fri Apr 12 13:53:16 GMT 2019 truststore.jks 87 Fri Apr 12 13:53:16 GMT 2019 ojdbc.properties 114 Fri Apr 12 13:53:16 GMT 2019 sqlnet.ora 6616 Fri Apr 12 13:53:16 GMT 2019 ewallet.p12 3243 Fri Apr 12 13:53:16 GMT 2019 keystore.jks

Download Wallet zip from Service Console

Otherwise you need to login to ATP Instance's Service Console. Default user name is "admin". After login, go to Administration link on left side. And click "Download Client Credentials (Wallet)"

Provide a wallet password to download the wallet zip file. Now this client credentials (wallet) can used to login from SQL Developer and other sql clients liek SQL Plus. If you have downloaded the zip from windows, you would need to scp the file to OCI compute host.

Once the wallet zip is present on oci compute host, continue with below steps.

Unzip wallet archive

unzip autonomous_database_wallet.zip cd autonomous_database_wallet

Modify sqlnet.ora

Modify sqlnet.oraand give path to walet folder as "/home/opc/atp-example/autonomous_database_wallet"

Here is cat /home/opc/atp-example/autonomous_database_wallet/sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/opc/atp-example/autonomous_database_wallet"))) SSL_SERVER_DN_MATCH=yes

OR set TNS_ADMIN variable to point to wallet folder. And Modify sqlnet.ora And give DIRECTORU=$TNS_ADMIN

cat /etc/ORACLE/WALLETS/ATPDB2/sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="$TNS_ADMIN"))) SSL_SERVER_DN_MATCH=yes

#Now export TND_ADMIN variable to point to wallet folder export TNS_ADMIN=/home/opc/atp-example/autonomous_database_wallet

#wallet zip is extracted and TNS_ADMIN env var pointing to the wallet folder. Refer https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/connect-sqlplus.html#GUID-A3005A6E-9ECF-40CB-8EFC-D1CFF664EC5A

Connect to ATP instane using SQL Plus

/usr/lib/oracle/18.3/client64/bin/sqlplus admin/Welcome#1234@atpdb2_low

create schema in ATP instance using SQL Plus

/usr/lib/oracle/18.3/client64/bin/sqlplus admin/Welcome#1234@atpdb2_low @/home/opc/schema/create_tables.sql