How to Connect Oracle Cloud Database using SQL Developer

SQL Developer ssh window

Oracle Database client uses port ranges in from 1520-1530 to connect database over TCP/IP, default listener port is 1521. These ports are not available on the secure Oracle public cloud.

So how to create a connection to a remote Oracle cloud database?

Connect Oracle Cloud Database using SQL Developer

SQL Developer allows you to connect to the database using a secure shell (SSH) and port forwarding. Before going in details, let’s understand SSH and Port Forwarding in brief.

As per Wikipedia,

Secure Shell (SSH) is a cryptographic network protocol for operating network services securely over an unsecured network.

Port forwarding or port mapping is an application of network address translation (NAT) that redirects a communication request from one address and port number combination to another while the packets are traversing a network gateway, such as a router or firewall.

We will use Oracle Apps ERP database on cloud and SQL Developer for demonstration. If you do not have SQL Developer, Download it from here.

Setup New SSH Host in SQL Developer

Open SQL Developer and click on View -> SSH toolbar. This brings ssh window as shown below. Select SSH Hosts and right click to create New SSH Host…

SQL Developer ssh window

Enter details as shown in below image and click OK. You need to use open ssh key generated while instance setup and add a local port forward. Check the respective checkbox and select appropriate information.

oracle-sql-developer-ssh-details

  • Name: Any valid user defined name
  • Host: IP Address of Oracle Cloud Instance
  • Username: OPC is default username
  • Use key file: Browse and select putty generated open ssh key
  • Check Add a Local Port Forward
  • Name: Specify proper name
  • Host: IP Address of Oracle Cloud Instance

Test SSH connection

SSH setup is complete and now let’s connect and test it before connecting to the database. Navigate to SSH Hosts window. Select newly created connection, right-click and you can either click on Test or Connect.

oracle-sql-developer-ssh-connect

If your key is secured by Passphrase, enter that key and click on connect.

oracle-ssh-connection-test-connect

Connect to Database

Navigate to Connections window, select Connections, right-click and click on a new connection.

Enter connection name, username, and password. Choose connection type as SSH as we are not using TCP/IP here. Choose Port Forward as recently created SSH connection and service name (Oracle Database Service Name). Click Save button to save all details so that you do not have to enter those again. Click the connect button.

oracle-sql-developer-database-ssh-connection

Open a SQL sheet and run any query to see the results.

sql-developer-successful-ssh-database-connection

Let me know if you find this article useful in the comments section below. Do let me know if you need any help to set up it.

Share This: