Thursday, 4 January 2018

Step 7 - Oracle DBCS : Connecting To a Database Service Using Oracle SQL Developer

You can connect to the Oracle database in Oracle Cloud using Oracle SQL Developer or similar tool to perform the database operations as you would similar to an on-premises databases. When you create a Database Cloud Service database deployment on Oracle Cloud, by default the Oracle Net Listener port 1521 is Disabled to ensure network security. Use the Oracle Database Cloud Service console to enable access to port 1521 on a compute node to make a SQL Developer connection to the Oracle Database.

Note: If you DO NOT want to open the Oracle Net Listener port 1521 for security reasons, you can create an SSH tunnel to port 1521 on the compute node associated with Oracle Database Cloud Service. Then you can use SSH client software that supports tunneling. Oracle SQL Developer supports SSH and it can used to create an SSH tunnel to port 1521 on the compute node if you don't want to enable port 1521 explicitly.

In this article we will demonstrate how to connect to Oracle Database in Cloud using Oracle SQL Developer Remotely

This article assumes that Oracle Net Listener port 1521 is enabled. To enable port 1521, follow the steps outline at:
http://netsoftmate.blogspot.in/2018/01/step-6-oracle-dbcs-enable-access-to.html

Prerequisites
  • IP address of Compute node
  • Enable port 1521 - This port is used by SQL*Net
  • Database Service name
  • Database username and password to connect to the database
  • Download and unzip SQL Developer software. Use the below link to download latest SQL Developer software
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Steps to connect to Oracle Database in Cloud using Oracle SQL Developer


  • Open a web browser and enter the URL you received in the Welcome email to login to Oracle Cloud Account
https://myservices-xxxxx-xxxxxxxxxxef4b21bb7ee3b2cf4123d1.console.oraclecloud.com/mycloud/faces/dashboard.jspx


  • Enter your username and password


  • On the home page, Click "Menu" under "Database" Cloud Service as shown below


  • Click "Open Service Console"


  • Click on Service Name. In my case "NSM-DBaaS" is the service name


  • Hover mouse on the "Connect String", it will pop up the complete Connect String. Copy the Service Name as shown below


  • You can also get the service name of you Container DB and Pluggable DB using "lsnrctl status" Command as shown below


  • Open Oracle SQL Developer on your Desktop/Laptop and Click the Add button


  • Fill in all the details, Connection name, username, password, Host name, Service Name


  • Click "Test" button and make sure the Status is Success


  • Execute some SQL Statement to ensure you can fetch the data from database


  • From  SQL Developer Click "View" menu select DBA to display DBA menu on left pane of SQL Developer


  • Under DBA menu click Add button to add database connection


  • Select a Connection and click OK


  • Navigate to DBA Menu, select your database connection to explore database configuration, status, other DBA options

Conclusion

In this article we have learned how to connect to Oracle Database in Oracle Cloud using Oracle SQL Developer remotely. You can connect to the Oracle database in Oracle Cloud using Oracle SQL Developer or similar tool to perform the database operations as you would similar to an on-premises databases. Oracle Net Listener port 1521 should be enabled on the compute node to make a SQL Developer connection to the Oracle Database.

No comments:

Post a Comment