Before You Begin
This tutorial covers how to connect to a Oracle Database Cloud Service (DBCS) instance using Oracle SQL Developer.
Time to Complete
Approximately 30 minutes.
When you create an Oracle Java Cloud Service (JCS) instance you always need to provide a pre-existing Oracle Database Cloud Service instance to which your JCS instance will connect.
This tutorial shows you how to identify the underlying DB service details and use them to create a connection from a local machine running Oracle SQL Developer.
Oracle SQL Developer
Oracle SQL Developer must be installed on your local machine.
Before starting this tutorial, you should have:
- A JCS instance already available.
- Oracle SQL Developer installed your computer, as listed above.
Identifying Oracle DB Cloud Service in use
Find the Oracle Database Cloud Service instance in use
Sign in to the My Services application at https://cloud.oracle.com/sign_in after selecting the appropriate Public Cloud Services Data Center option. The My Services Dashboard appears.
In the Oracle Java Cloud Service section, click Open Service Console. The Oracle Java Cloud Service Console page is displayed. An example is shown in the following illustration.
On your Oracle Java Cloud Service Console, click the link that represents the service instance you want to manage.
The details of the selected service instance show up, including a list with all the machines that make up the service. Right below the machine list, there is the Associated Services section. There you will find the name of the Oracle Database Cloud Service instance this JCS instance is using.
Find the Oracle Database Cloud Service instance connection details
Scroll up to the top of the page. Click Consoles and then click on the Database Cloud Service item.
The Oracle Database Cloud Service Console page is displayed, showing the list of DBCS instances available to you.
On your Oracle Database Cloud Service Console page, click the link that matches the service instance name you collected in previous step. In our example wlsdb.
The details of the selected DBCS instance show up, including the DB machine public IP address, the listener port and the instance name. The Connect Descriptor attribute tells us about the service name that we will use.
Enabling Access to the DBCS Instance from the local machine
By default, the DBCS instance is not reachable to IP addresses outside your cloud service identity domain. In order to connect to the DBCS instance using desktop tools, such as Oracle SQL Developer, we have two choices:
- Create an SSH tunnel and configure port forwarding appropriately
- Enable the access rule that grants access to the DB service from external IPs
The steps that are required to create an SSH tunnel to gain access to specific services running on the Oracle Cloud Services VMs from the local computer are fully documented in a number of different tutorials, for example:
- Creating an SSH Tunnel to a Port in the Virtual Machine
- Connect to a Database Cloud Service (Database as a Service) Instance Through an SSH Tunnel
Locate the access rule that is preventing us from getting access to the DB service from external IPs
If not already signed in to the My Services application at https://cloud.oracle.com/sign_in, do so again after selecting the appropriate Public Cloud Services Data Center option. The My Services Dashboard appears. (Or you can access the Compute Cloud Service from the Consoles menu, like we did above when navigating from JCS to DBCS)
In the Oracle Compute Cloud Service section, click Open Service Console. The Oracle Compute Cloud Service Console page is displayed. An example is shown in the following illustration.
Click Network to see the existing list of access rules
Enter ora_p2_dblistener as the access rule name and click the magnifier icon . That will filter out from the list access rules that are of no interest at this moment.
Hover the access rules so that a tooltip shows up. Locate the VM that hosts the database Service to which you want to get access. If you have trouble finding the rule, remember that it is initially disabled and therefore decorated with the icon
Edit and enable the access rule
Click the preferences icon and select Update
On the Update Access Rule dialog, change Status to Enabled and click Update.
Connecting to the DB using Oracle SQL Developer
Once we know the connection details to the underlying DB service and access from the local machine has been enabled, we may connect to the DBCS instance from Oracle SQL Developer as we would do for an on-prem database service.
Connecting to the DB using Oracle SQL Developer
Start Oracle SQL Developer
In the Connections view click on the icon to create a new DB connection
Use the public IP address, the listener port and the service name details obtained in Identifying Oracle DB Cloud Service in use to fill out the form. Click Test and, when succeeded, click Connect.
Test your connection
Issue any valid SQL clause to test your newly created connection.