Recently I have been working in the Oracle Cloud space on a project where I needed to interact with the remote databases for data manipulation, migration, backup and copying schemas from one database to another database. There are a lot of resources out there about how to connect to remote Oracle server and interact with the database by altering sessions etc. Unfortunately, I could not find any easy way to interact with the pluggable database and browse DB objects for different schemas. Here is the solution I found easy to integrate and connect to your oracle cloud server and manage the database.
Step One: Get the SSH Key
Download the SSH key from the Oracle cloud service to connect to the remote VM instance. Usually, the key gets generated at the time of Oracle VM instance creation.
You also need the username which is by default oracle to connect to the Oracle VM.
Step Two: Create SSH Connection
Once you have downloaded the SSH key, note the download location. Please make sure if you keep this SSH key in safe place. Anyone can access the VM using this key as long as they know the public IP address of the VM.
Open SQL Developer tool and click on View then click on SSH from the top menu as shown below.
This will open up the SSH Hosts window in the left. Now right click on SSH Hosts then click on New SSH Hosts. This will open a window to create the SSH host connection. Make sure you tick the checkbox Use key file then browse to the downloaded SSH key file.
Note, I have used oracle as username which is the default username for the Oracle VM. Once you selected the key file, click on ok to close this window. Unfortunately, you cannot test the connection from this window. To test the connection, you need to right-click on the connection then click on test. This should display a message of the SSH connection result.
[attention title=”Port Forwarding”]Make sure you tick the box “Add a Local Port Forward”. This will basically forward the connection request to DB port. Most cases you have the default database port 1521.[/attention]
Step Three: Retrieve database connection parameters
Now let’s create a database connection using the created SSH connection.
Before we create the database connection, we need to know the connection parameters for the database. In this instance, I have two classic databases on the Oracle Cloud server. Click on the database name to display the properties of the database.
We need to know the username, password and service name for the database. You can get the service name from the Oracle database connection string. The second part of the connection string after the / is basically the service name, in this case, starts with PDB1. See the screenshot below.
Step Four: Create a Database Connection
Now we are ready to create a database connection in the SQL developer. To create a connection click on New Database Connection, this will open the connection properties. Enter the username sys and password for the user. Make sure you select Role as sysdba. In the connection type select SSH, this should automatically select the port forward as we created in the previous step. Enter the service name that we got from the oracle database. You can now test the connection. This should display a success message if everything fine like below.
Now you are connected to the remote Oracle Cloud services database using SSH connection. In fact, you can use this SSH connection to connect to different Schemas directly in the PDB1 and browse database objects including managing the database as sysdba.
Happy programming.