Just a quick post on how to connect Oracle SQL Developer SQLcl to an OCI database.

With shifting our development environment from localized development machines (laptops, etc.) to Oracle Cloud (OCI), one of the tasks that needed to be done is understanding how to connect to our development database.  We build our development database as an DBaaS instance within OCI.  With doing this, it required a bit more of security to get items connected.  The pre-requisites needed were:

• Connection details for DBaaS Instance
• Private SSH key
• SQLcl

Connection Details for DBaaS Instance:

Before trying to make a connection to the Oracle Database that is running in OCI, we needed to identify the connection string that we wanted to use.  This information can be found under the DB System Details, by clicking the button called DB Connection.  At that point, a Database Connection screen opens up from the right.

From here, you can copy the connection string of choice; either the Easy Connect or the Long version (TNS Names layout).  These connection strings will be used on within SQLcl in a bit.

Private SSH Key:

When building the OCI DBaaS instance, you are asked for a public SSH key to use.  This allows anyone who has the private key to access the host that the database is running on.  When attempting to make a connection via SQLcl, this private key will be need to establish an SSH Tunnel.  We typically keep our private keys located in the .ssh folder under the current user directory.  For this example, that would be in /Users/bocurtis/.ssh.  Just confirm that the private key exists.

SQLcl:

As you pulled together the items needed to make a connection to the OCI DBaaS instance, you may have noticed that the third item is to ensure that you have SQLcl installed.  Although this post is not covering how to install SQLcl, you can find those details here.

What the focus is however, is how to make the connection from SQLcl into the OCI DBaaS instance.  In order to do this, the two items mentioned earlier are needed.  Let’s take a look at how to make the connection now.  This will be explained in a series of steps:

1. Login to the SQLcl from a terminal window

$ cd /Application/sqlcl/bin
$ ./sql /nolog

This will open SQLcl command prompt and allow you to enter commands.

2. Establish an SSH Tunnel to OCI

SQL> sshtunnel opc@<public_ip_address>:22  -i /Users/bocurtis/.ssh/id_rsa -L 1521:<private_ip_address>:1521

The SSHTUNNEL command will establish a secure tunnel to the OCI instance and open the needed ports for the connection to happen.

3. Connect to the Oracle Database (with a small change)

SQL> conn sys/<password>@<public_ip_address>:1521/<database_service_name_from_oci> as sysdba

What you will notice here, is that the connection string that is identified in the Database Connection details is slightly different.  This is due to DNS translation and easier if you just use the IP address.  Meaning that the connection string needs to be updated when making the connection.

Once connected to the OCI DBaaS instance, it can be tested very quickly by running any type of SQL command needed.  In our case, we are just checking a table in the GGATE schema.

SQL> desc ggate.rd$hist_stats;

Name Null? Type
____________________ ___________ _______________
RD$SRC_TABLE_NAME NOT NULL VARCHAR2(50)
RD$OP_DATE NOT NULL TIMESTAMP(6)
RD$GG_PROCESS VARCHAR2(10)
RD$GG_LAG_SEC VARCHAR2(15)
RD$INSERTS NUMBER
RD$UPDATES NUMBER
RD$DELETES NUMBER
RD$TRUNCATES NUMBER
RD$RBA NUMBER

With that you can now make a quick, command line connection to your databases in OCI.

Enjoy!!!