Netsoftmate

Tag: SQL*Net

  • Step 8 – Oracle DBCS : SSH Tunneling and Connecting To a Database Service Using Oracle SQL Developer

    In the previous article we have demonstrated how to connect to Oracle Database in Oracle Cloud using Oracle SQL Developer when the Oracle Net Listener port 1521 was enabled.

    Let’s assume that you DO NOT want to enable the Oracle Net Listener port 1521 for security reasons. In that case 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 Oracle Database in Oracle Cloud using SQL Developer and SSH Tunneling.

    Prerequisites

    • IP address of Compute node
    • 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 and SSH Tunneling

    • From the below screen shot we can see that SSH port 22 is enabled and Oracle Net Listener port 1521 is disabled



    • 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. As the port 1521 is disabled we can’t connect to the Oracle Database. 

    We should use SSH tunneling to establish a successful database connection



    • From  SQL Developer Click “View” menu and select SSH



    • On the left pane, click on SSH host and select “New SSH Host”



    • Fill in all the details, and click OK

    Name: Any name of your choice
    Host: IP address of Compute node
    Username: opc or oracle
    Use Key file: Select the private key from your desktop/laptop that matches the Public Key for your Deployment
    Name: Any name of your choice
    Host: localhost
    Port: 1521 used by SQL*Net



    • Now 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 and click Test button. We can now see that he Status is Successful. Click connect



    • 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 when port 1521 is disabled for security reason. 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.