Connecting to an Autonomous Database in the Oracle Cloud

Look, this is a post about setting up and configuring databases in the Cloud.
Therefore, by law, I am required to make numerous references to Captain Scarlett and the Mysterons.
This is because Spectrum has an HQ called Skybase, that looks like this :

A new Oracle Cloud data centre ?

Meet Lieutenant Green. He is the entirety of Spectrum’s IT Department, responsible for the system that runs Skybase, and who therefore understands the full implications of the phrase “unplanned downtime”.
Indestructable as Captain Scarlett may be, it’s Lieutenant Green whose indispensible.

We’re going to follow the Lieutenant as he :

• creates an Oracle Autonomous Database in the Free Tier of the Oracle Cloud Infrastructure (OCI)
• connects to the new database from his local copies of SQLDeveloper and SQLCL using a Cloud Wallet
• configures the TNS settings to allow him to initiate Thick client connections from other client tools
• gets to the bottom of whether SQLDeveloper Web has been kidnapped by Mysterons

Right, S.I.G….

Sign Up

Green starts by signing up for an Oracle Cloud Account having broadly following the steps detailed by Todd Sharp.

In this case, the database is called “spectrum”.

Following Todd’s instructions all the way, Green now has a file in the Downloads folder called Wallet_spectrum.zip.

We’ll take a peek inside the zip in a bit.
However, the first order of business for Green is to connect using his favourite Oralce Database IDE…

SQLDeveloper Cloud Wallet Connection

Green currently has a fairly up-to-date version of SQLDeveloper ( 21.2) on his laptop.

Before he starts testing connectivity to the new database, he makes sure that he’s working with a clean configuration by isolating the new zip file in it’s own directory, away from the existng $ORACLE_HOME/network/admin directory used by his Oracle Client.

cd $HOME
mkdir cloud_tns
cp $HOME/Downloads/Wallet_spectrum.zip $HOME/cloud_tns/.
export TNS_ADMIN=/home/green/cloud_tns

With TNS_ADMIN pointing at the zip, he now starts SQLDeveloper :

sh /opt/sqldeveloper212/sqldeveloper/sqldeveloper.sh

NOTE – if you happen to fall over “ORA-12529 – TNS : connect request rejected based on current filtering rules”, then it’s a fair bet that there’s a conflicting setting somewhere in your existing client TNS setup.

Using the zip file he creates a new connection with a Connection Type of Cloud Wallet :

As we can see :

• The User Info is specified as normal ( i.e. database username and password)
• The Connection Type is Cloud Wallet
• The Configuration File value is simply the full path to the .zip file we downloaded.
• The Service drop-down is populated with the services available in the .zip

SQLcl

As you might expect, SQLcl is also able to use the wallet to connect.
This requires the wallet being specified before the database connection is attempted :

sql /nolog

set cloudconfig <path_to_wallet_zip>

connect uid/password@connect_str

Thick Client connections

For other client tools which are using Thick Client connections, we need to do a bit more legwork.
Time to have a look at the contents of the zip file :

unzip -l Wallet_spectrum.zip

Archive:  Wallet_spectrum.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
     7475  2022-08-06 18:37   ewallet.pem
     2782  2022-08-06 18:37   README
     6701  2022-08-06 18:37   cwallet.sso
     1750  2022-08-06 18:37   tnsnames.ora
     3378  2022-08-06 18:37   truststore.jks
      691  2022-08-06 18:37   ojdbc.properties
      114  2022-08-06 18:37   sqlnet.ora
     6656  2022-08-06 18:37   ewallet.p12
     3191  2022-08-06 18:37   keystore.jks
---------                     -------
    32738                     9 files

That looks rather like a set of files we might find in the $ORACLE_HOME/network/admin directory of an Oracle client installation.

If we take a look at the sqlnet.ora in the zip file, it seems to confirm this impression :

unzip -c Wallet_spectrum.zip sqlnet.ora

Archive:  Wallet_spectrum.zip
  inflating: sqlnet.ora              
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes
 

Therefore by unzipping the files into /home/green/cloud_tns…

unzip Wallet_spectrum.zip -d /home/green/cloud_tns

…editing the sqlnet.ora to point to the correct loctaion…

…and pointing the client at this directory…

export TNS_ADMIN=/home/green/cloud_tns

…Green can connect to his cloud database in the usual way :

Where is SQLDeveloper Web ?

Green has been looking forward to having a good look round SQLDeveloper Web. However locating it is proving something of a challenge.
Whilst Mysteron activity cannot be entirely ruled out, the most likely causes for this absence are :

• it’s called Database Actions in OCI
• it’s not enabled by default for non-Admin users

As ADMIN, Green first creates a database user by doing the following :

Open the Database Actions Link and selecting the Database Users option from the hamburger menu.

Click Create User

Here, he completes the form :

Incidentally, by toggling the Show Code button at the bottom of the form, he can see the code that will actually be executed.

By clicking the CREATE USER button he should now get a new database user. As he’s specified that they are Web enabled, he can connect to SQLDeveloper Web as this new user, using the URL specified in their user card :

When this users goes to the URL, they need to provide their database username and password. Once connected, they should see the Database Actions Launchpad.

Select the SQL tab and things look rather familiar :

Whilst it won’t prove decisive in the battle with the Mysterons, Lieutenant Green is confident that having Skybase connected to a Cloud Database should at least reduce latency.