12 Days of #Cloud: Connecting to #Oracle #DBaaS using #SQLDev

12 Days of #Cloud: Day 1

There are two ways for SQL Developer to connect to a DBaaS Cloud Instance.

Review how to create a DBaaS Cloud Instance.

1. Using SQL*Net

By default, the Oracle DBaaS Cloud Service blocks access to 1521 port used by SQL*Net. You have to open the port before you can connect using SQL Developer.

Log in to the Oracle Database Cloud Service and click on the service name to see the details. Note down the Public IP address and the Connect String.

Click on the Database Cloud Service menu and choose “Access Rules”.

Enable the “ora_p2_dblistener” access rule, by clicking the menu under Actions.

Now, you can use SQL Developer from your desktop to connect to the Cloud Database as you would connect to any on-premise database. Use the Public IP address as the hostname and the service name in the connect string.

2. Using SSH

This method is a bit more secure. Let’s turn off the 1521 port to make sure our connection using SSH works indeed. Confirm SSH is enabled.

In SQL Developer menu, click on View –> SSH. Then right click and choose “Create New SSH Host”.

Provide the Cloud Database Service Public IP address and the SSH key file. Enable port forwarding on port 1521.

Oops, I chose a wrong key file. Instead of the private key, selected the public key file. Let me fix by editing the SSH host entry.

Now choose “New Connection” in SQL Developer, and configure a connection using connection type SSH. You still have to enter the service name of the cloud database.

When you click Test button,  you will be asked the SSH key passphrase.

And, that’s it. You are ready to go.