Oracle's SQLDeveloper and Vagrant

Refers to github project train.oracle12.migration

The Problem

You want to run your SQLDeveloper on the host and you want to let it work on the databases inside the guest VM.  How do we tell SQLDeveloper, that it should connect to the databases inside the guest VM ?

There is a host with the name e.g. hostname = thinkpad3 and it hosts a database CDB1 as of Oracle12c.

There is a guest VM and it's hostname is e.g. hostname = localhost.localdomain and it hosts the database CDB1 as of Oracle12 and the database UPGR as of Oracle11g. The default port for the databases on both the host and the guest VM is likewise 1521. 

The Solution

click on the pictures to enlarge

Consider this Vagrantfile and note the line config.vm..network "forwarded_port", guest: 1521, host: 11521.

This line tells any application on the host - e.g. your own Java programs or Oracle's SqlDeveloper (which is a Java program) - that it can use the port 11521 , when you want it to work inside the guest VM.

SQLDeveloper Connections

You are familiar with creating new connections in SQLDeveloper and checking the properties of existing connections.

Connection to CDB1 on the host

This connection does already exist.

Note the port 1521 and the SID cdb1.  And here is a query on the local CDB1.

Connection to CDB1 inside the guest VM

Let's create a new connection with the following properties:

Note the port 11521 and the Servicename cdb1. (This is because of the listener in the VM.)  And it works - see the status of the connection test. Here is the same query as before on the CDB1 in the guest VM:

Note the different hostname and startup-time. 

 

Connection to a pluggable database inside the guest VM

Yes, this is possible too, although a PDB is not a database instance as we know it, but just a service inside the container database, I.e. pluggable databases have no processes like SMON, PMON, etc. They are purely driven by the processes of the container database. This saves resources by avoiding overheads. On the other hand you should increase the number of processes in the container database.

Note the service name pdb1 and the status of the connection test.

Connection to UPGR inside the guest VM

On the host I have no Oracle11g installed, but inside the guest VM does exist the UPGR database as of Oracle11g, because we want to train the different upgrade methods to Oracle12c. Let's create new connection:

Note the service name, which I created deliberatelly to demonstrate this effect, and see the status of the connection test.

 And here is the banner of the UPGR:

Note the early Oracle Release 11.2.0.1, for which we opted in the guest VM, because it is available for holders of a standard Developer License.

 

 

 

 

Conclusion: The preceding is the proof of concept - or qed as the ancients used to say.