Search

Top 60 Oracle Blogs

Recent comments

SQL Developer and Oracle DBaaS

Connecting to Oracle DBaaS from SQL Developer

Introduction

So, you have access to an Oracle DBaaS instance. How do you connect so that you can run use SQL Developer’s navigator or execute SQL and PL/SQL scripts? An earlier post showed how easy it is to create an Oracle DBaaS instance. This article will show you how to connect to DBaaS from SQL Developer.

When a DBaaS instance is created it is necessary to supply a private/public key pair to enable more-secure access via SSH (Secure Shell). By adjusting the DBaaS properties, you can also expose the CDB and PDB using the IP address without the protection of SSH (probably not a good idea for production use).

SSH Public Key

When creating a DBaaS instance you are required to provide a valid SSH key to protect your cloud resource. On UNIX/Linux/Mac this means running the “ssh-keygen” line command, on Windows this is usually accomplished using PuTTYgen’s SSH-2 RSA feature. A “passphrase” along with public key and a private key are specified. You use the public key when creating the DBaaS service instance and the private key when accessing it.

Finding IP and Database Service Name Information

Use the DBaaS dashboard to display the IP address and Service name along with other useful information as shown below.

dbaassqldev01

Using DBaaS from SQL Developer (using SSH)

To access the DBaaS from SQL Developer you first define an SSH Host. You might need to open the SSH View (View->SSH from the SQL Developer menu) to see the display below.

Right-click and choose “New SSH Host” to continue.

dbaassqldev02

Creating A New SSH Host

Use the information from DBaaS instance to define a new SSH Host.

  • Name: your choice
  • Host: IP address from DBaaS service display
  • Port: 22
  • Username: oracle
  • Check “Use key file” and browse to private key matching the DBaaS public key file
  • Check “Add a Local Port Forward” and pick a name, host, and port
  • Click “OK” when done

dbaassqldev03http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev03.png 390w" sizes="(max-width: 386px) 100vw, 386px" />

Create SQL Developer Connection to CDB

To create a SQL Developer connection to the CDB specify a username and password (system password specified at DBaaS creation); choose Connection Type “SSH” and specify the Port Forward name from the previous step. Use the SID from the DBaaS service; be sure to test before saving.

dbaassqldev04

Create SQL Developer Connection to PDB

To create a SQL Developer connection to the PDB specify a username and password (system password specified at DBaaS creation); choose Connection Type “SSH” and specify the Port Forward name from the previous step. Specify the Service Name connect string from the DBaaS service; be sure to test before saving.

dbaassqldev11http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev11.png 752w" sizes="(max-width: 756px) 100vw, 756px" />

Can I Avoid Using SSH?

Using SSH (Secure Shell) is a good way to ensure the integrity of your database connections. However, occasionally you might want to access the database using the IP address and a “normal” (less-secure) connection. Here’s how you set it up.

First, open the “Service Console” and use the icon on the right to display a list of options. Choose “Access Rules” to continue.

dbaassqldev06http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev06-10... 1024w" sizes="(max-width: 781px) 100vw, 781px" />

Enabling “Normal” Access via DB Listener

From the “Access Rules” panel you can enable and/or disable accesses of different types for your DBaaS service instance. To enable use of the port 1521 listener, click on “Enable” and confirm in the dialog that displays.

dbaassqldev07http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev07-10... 1024w, http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev07.png 1037w" sizes="(max-width: 740px) 100vw, 740px" />

 dbaassqldev08http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev08.png 381w" sizes="(max-width: 342px) 100vw, 342px" />

DB Listener Enabled

Once enabled, the “Access Rules” panel shows the current status and allows you to disable the access again should you desire.

dbaassqldev10http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev10-10... 1024w, http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev10.png 1046w" sizes="(max-width: 648px) 100vw, 648px" />

Using SQL Developer and the Listener

Once the listener connection is enabled; you may access the CDB (via SID) or the PDB (via Service Name) to create a new SQL Developer connection (as shown below).

dbaassqldev11http://kingtraining.com/blog/wp-content/uploads/2016/10/dbaassqldev11.png 752w" sizes="(max-width: 643px) 100vw, 643px" />

Congratulations!

Congratulations, you have now accessed an Oracle DataBase as a Service (DBaas) service instance from SQL Developer.