Using SSH from SQL Developer

Last week I showed a how to for use tunnels ssh by using Putty, today I like show how connect to Oracle database using ssh directly from SQL Developer.

Click en View menu and select SSH


Right buttom and New SSH Host



Set up NAME for the connection (in this case NEW_SSH_CONNECTION)
Set up HOST (The jumper host or ssh gateway) This is a tunnel finisher, from this machiene we jump to the machine where is the database
Set up USERNAME (later we are request the pass)

Click on “Add a Local Port Forward”
Set up HOST (host where is the database)
Set up Port, port of listener

Click on “Use specific local port” I prefer to define a port instead of SQL Developer choose one randomly. I use in this case 7777


Add new connection to database.

Username and pass for database

Select SSH in Connection Type

Choose the name (created before) in Port Forward

Service name for you database

Click on Test or Connect


Now a pop up request the pass for the user in the SSH Gateway




SSH Tunneling Using MobaXterm

In this post I will show  how to create a SSH Tunnel for connections to Oracle database. I will use a jumper host to get the database host. Remember that the information between your computer and the jump server is encrypted, but from the server to the database server it does not.



Push on New SSH Tunnel


You see a window like this


Check Local port forwarding


Now push Save buttom, you have a tunnel. Now you only need to activate the tunnel every time you use it. MobaXterm in its free version only allows a maximum of three tunnels. When there will be a tunnel, it will ask us for the user password of the jump server.

Now every time you connect to an Oracle database, you must specify in the connect string, the HOST must be your localhost or and in the port of connection to the listener, the port that we have put before as forwarded port. In this example the 7777.



Difference Between “whoami” And “who am I”

We are going to see the following example, in the we connect to unix-box as a nominal user and we make a su command to another user

login as: anton
Using keyboard-interactive authentication.
Last login: Fri Nov 23 08:39:43 2018 from
Oracle Corporation      SunOS 5.11      11.3    June 2018
anton@houston:~$ whoami
anton@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)
anton@houston:~$ su - dbowner
Oracle Corporation      SunOS 5.11      11.3    June 2018
dbowner@houston:~$ whoami
dbowner@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)
dbowner@houston:~$ # ********* do su one more time  *********
dbowner@houston:~$ su - dbowner
Oracle Corporation      SunOS 5.11      11.3    June 2018
dbowner@houston:~$ whoami
dbowner@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)

You can execute it as many times as you want “who am I” will always return the user with the one you have connected to the system and whoami will only return the user with whom you are working at a given moment.


Errors ORA-19625 ORA-27037 ORA-19600 ORA-19601

Yesterday a co-worker was creating a dataguard by using the command duplicte for standby

channel c1: restoring control file
ORA-19625: error identifying file /tbe/prod/uti/bck/controle_cdn_standby.ctl
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is control file  (/tbe/prod/uti/bck/controle_cdn_standby.ctl)
ORA-19601: output file is control file  (/tbe/prod/data/cdn/tbectrl1.con)
failover to previous backup

He is executing a duplicate like show below;


He is using a tape backup of the primary database, but the file RMAN is looking to restore, was on disk instead of tape, RMAN can not find it.

After a bit of investatiion, we have seen that my partner, after the tape backup, executed the following command


The alter system has been registered in the control file of the primary database, to which we are connected from the stanby to execute the duplicate, as a backup of the control file. The duplicate that is executed does not include any set until clauses, so Oracle looks for the more recently backup of the CF, in this case the copy on disk (with the same path), but has not been copied from the source machine to the destination (the rest of the backup is on tape)

Copy this backup to the CF disk in the same route in the machine where the standby solves the problem. Other solution would be set until time (or scn) to a point before the CF backup to disk.


Creat A SSH TUNNEL Using Putty

The ssh tunnels are used for many things, but basically it is a point-to-point encryption (until the ssh gateway) with the purpose of not being able to capture the information that circulates through the channel. Even if someone uses a sniffer (it will capture the data packets) it will not be able to read the information that is sent or received.

As I said before there are many purposes to use the tunnels, in our case we will see from the point of view of the databases, and mainly from the perspective of the database administrator, which by the functions of the post entails Many times sensitive information.

Not long ago post a post to demonstrate how easy it is to see and capture network packets when we send an “alter user xxx identified by values ​​and and” statement, using a tunnel this information will be encrypted from the time it leaves my laptop until it reaches the ssh gateway. Okay on the laptop and once the data passes the gateway until the targete machine where is the sistener  would be captured and read. Normally this segment of network is a local area and is more or less secure.

We must define;

  •  ssh gateway or jumped host; as the entry point to the tunnel (or where we are going to bounce the signal).
  • target machine; as the host to which we want to connect.
  •  local port (source port named by Putty); which we will use on our computer (in my case laptop) and when we reference it will translate to the port of the listener that is on the target machine.


Open Putty and set the ssh gateway and port 22 (necessary to encript the channel)


Drill down in the lest menu and click on tunnels, set source port and destination (target machine).


Click on add button and after click on Open.


Now from SQL Developer We need open a new connection and set username and pass as usual and the new for use the tunnel;

Set hostname to localhost (or

Set Port to 7000, defined as local port (in our workstation)

Set Service_name to the database