A recent post talked about setting up a Base DB Service in OCI and finished with logging into the OS of the srvice and there connecting to the DB. What we often want to do is to be able to connect to it directly from our own computer to use the tools and workflow we have in place. This post is about how to set that up.

First you head back into OCI and look at the properties of your sevice. On the page for DB Systens Details on the bottom leeft you find Resources. Click on nodes and grab the Public IP Address and the private IP.

The next thing you need is the keys you downloaded when you created the service. Look in your downloads folder if you have not already moved them somewhere.

ssh -i ssh-key-nnnn-nn-nn.key opc@nnn.nnn.nnn.nnn

The ssh-key* argument is the name of the private ssh-key you downloaded. The above assumes it is in your current working directory. nnn.nnn.nnn.nnn is a placeholder that you replace with your public IP.

That will get you logged in to the database server. That is just to prove it works. Once your get it to work, just exit out.

Our next step is to route one port on your machine to the listener on your cloud DB server.

ssh -i ssh-key-nnnn-nn-nn.key -L 1521:n.n.n.n:1521 opc@nn.nn.nn.nn -N &

-L set up a local route of port 1521 on your machine to port 1521 on the cloud server. n.n.n.n is to be replace with the private IP. nn.nn.nn.nn is to be replaced with the public IP.
-N say that you don’t want a shell just the routing. Without it you end up logged in on the server as well as having the route set up.
& – put it in the background so ti doesn’t lock up your terminal.

With this in place you can now log in with the sqlcl you have on your machine.

sql -L system@localhost:1521:xxxxxxxx

Replace xxxxxxxx with the name you gave your database. If you don’t remember you can see it on the DB Service Details in OCI. Click on Databases on the resources and use what is listed under “Name”.

That was logging in to the CDB, for the PDB you’d use the following.

First let’s find the service-name you’ll use to connect. On the Page in OCI With DB System Details click on Databases in Resources on the lower left part. Then click on the database name. This brings you to the Database Details page. Here you click on pluggable databases in the resource list on the lower left. Now click on the three dots on tha far right on the row with the PDB and select “PDB Connection”. In the “Eazy Connect” you want to grab the part that comes after the slash towards the end.

sql -L system@localhost:1521/xxxx.public.xxxxxxx.oraclevcn.com

The xxx:es are of course portions I editied. But your command should looks something like that and if your have it correct you will be logged in to the PDB.

The above will of course work in SQL*Plus or SQL Developer or any other tool using such connect strings.

Enjoy.