Here is a quick guide showing you how to connect SQL Developer running on your computer to a Base Database Service in Oracle Cloud Infrastructure. The Base Database Service does not allow connections from the outside except SSH. So, you can use an SSH tunnel.

Create SSH Connection

1. First, you start SQL Developer on your computer (of course, the latest version). Go to the View menu, and select SSH.
2. To the left you now find the SSH Hosts panel, right-click and choose New SSH Host.
3. Enter the following information and click OK:
   • Name: Give it a meaningful name.
   • Host: The public IP address of your Base Database Service.
   • Username: Set it to oracle.
   • Use key file: Tick it off and select your private key file.
   • Add a Local Port Forward: Tick it off.

Create Database Connection

1. In the Connections panel, click on the plus icon to create a new connection.
2. Enter the following information:
   • Name: Give the database connection a meaningful name.
   • Supply a set of user credentials (Username, Password, Role).
   • Connection Type: Set it to SSH.
   • Port Forward: Select the SSH connection you just created.
   • Service name: The fully qualified service name (use lsnrctl status to find it).
3. Click Test to ensure the connection works. If you get an error, jump to the troubleshooting part.
4. Click Save and Connect.

That’s it!

Now you can connect to your OCI instance from SQL Developer on your own computer.

Remember to use the latest version of SQL Developer to get access to all the goodies.

For Production?

It’s very unusual for a production database to have a public IP address. In a more secure environment, this is a safer approach.

Troubleshooting

IO Error: Got minus one from read call

IO Error: Connection reset by peer

Vendor code 17002

I have run into this a few times. I haven’t found the root cause (yet), but this usually works for me:

1. Stop the listener and take a backup of listener.ora:

   lsnrctl stop
   cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora2
   

2. Remove all references in listener.ora to the listener called LISTENER:

   vi $ORACLE_HOME/network/admin/listener.ora
   

   • LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
   • ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
   • VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET
3. Restart the listener:

   lsnrctl start
   

4. Connection from SQL Developer should work now.
5. Now comes the odd part. Add the parameters back into listener.ora and restart:

   vi $ORACLE_HOME/network/admin/listener.ora
   
   lsnrctl stop
   lsnrctl start
   

6. The connections should still work.