Wednesday, February 17, 2016

SQL Developer and SSH for DBAs

In version 4.0.3 SSH was introduced in SQL Developer; here's a post from Jeff Smith about it. Now this feature has become even easier to use with SSH configuration made independent of the database connections. 

DBAs typically have a jump server they connect to in order to login further to the database servers. With the new implementation (I'm using version 4.1.3 at the moment) you setup one SSH host, and for every listener you want to connect to you configure a tunnel through the jump server.

Go to View -> SSH to show the window for SSH configuration. Add a host with a right-click on "SSH Hosts". To begin with it is sufficient to add just a descriptive name, a hostname or IP address, and your username. Then with a right-click on the host select "New Local Port Forward" as shown here:


The name should be the database name if you use dedicated listeners for every database, or a descriptive name of the listener if it is used by many. If you are using RAC, don't use the hostname for the SCAN listener, but one of the vip-addresses. Also use the default "Automatically assign local port", because there is some nice magic behind the scene here.



Where I work I typically have many connections to the same database, each with a different Oracle user. With the new SSH feature in SQL Developer it gets very easy to setup. For every new connection select Connection Type SSH and you pick from the Port Forward list as shown here:


(By the way, did you notice I changed the Connection Color? One of my favourite features when dealing with production databases).

That's it! The point here is that you reuse each port forward aka tunnel as often as you need, you no longer need to configure the same SSH tunnel for each database connection. And if the listener is moved you change it only once. Exactly what port is used locally is taken care of, which makes this much easier to setup and use than creating the tunnels in Putty or SSH command line since you don't have to remember the local port.