Connecting Oracle PDB on Cloud using SQL Developer

In this post we will see how we can connect to Pluggable Database which is hosted on Oracle PaaS Cloud : Database as a Service.

As we know Pluggable databases is a new features in Oracle 12c Database so we will be using Oracle 12c Database on Oracle Database Cloud.

Pre Requisites :

• Oracle Public Cloud environment Access.
• Oracle Database as a Service subscription.
• Oracle 12c Database Instance created on OPC.
• Combination of Public key(required to create 12 instance in above step) and private key to access 12c DB instance using client - Putty and SQL Developer.
• Passcode of keys.
• Putty
• SQL Developer.

Once we have all the information available we are all set to make a connection to PDB on cloud from local SQL Developer.

To connect to PDB, first we need to have a user created if not created already which has access to PDB.

For this connect to DCS cloud environment using putty. 

Enter the public IP in the putty.

Click Auth in category on left side of Putty and browse and select private key for the public key which was used to create DB Instance.

Click Open and login using oracle as username.

It will ask PassPhrase which was provided at the time of generation of public/private keys. Provide it and you are into the shell of the VM on which DB Instance is hosted.

Now lets connect to sqlplus as sysdba.

Now change the current session to PDB so that we get into PDB and do the things related to PDB. Here TESTPDB is the name of PDB.

Now we need to create a user with which we will be connecting to the TESTPDB from SQLDeveloper.

Provide necessary grants to connect and do DML. For now we will give all rights by following :

grant all privileges to demouser;

So we are now all set on the server/cloud side. Now next part is to configure settings in sqldeveloper to be able to connect to cloud DB instance.

Open the SQL Developer and open SSH window by going View -> SSH.

Right Click on SSH Host and click on new SSH Host.

Enter the details , provide a name, host IP of Cloud VM, username and provide private key file. Remember to provide ssh format of the file which can be generated at the time of generation of public/private key combinations or you can load a key in puttygen and create new ssh private key.

Click OK after providing the details.

Now right click on DemoDCS Node and select Local Port Forwarding.

Provide any name, keep host as local host and port as 1521.

SSH window should look like below.

Now we just need to create a new connection. In new connection wizard, enter name of the connection, username/password. Select service name and enter the value in below format.

<PDB NAME>.<domain Identity>.oraclecloud.internal

That's it. Just connect and we are all set to go.

Click on the connection and expand it. It will ask password for demouser.

Next it will ask for PassPhrase of the private key which was provided at the time of creating private keys.

Now it will behave like a normal connection to any other database and operations can be performed on it.


Thanks