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

setup_ssh_in_sqldeveloper_1

Right buttom and New SSH Host

setup_ssh_in_sqldeveloper_2

Now,

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

setup_ssh_in_sqldeveloper_3

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

setup_ssh_in_sqldeveloper_4

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

setup_ssh_in_sqldeveloper_5

 

HTH – Antonio NAVARRO

Advertisements

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.

 

moba_tunneling_1

Push on New SSH Tunnel

moba_tunneling_2

You see a window like this

moba_tunneling_3

Check Local port forwarding

moba_tunneling_4

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 127.0.0.1. and in the port of connection to the listener, the port that we have put before as forwarded port. In this example the 7777.

 

HTH – Antonio NAVARRO

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.
Password:
Last login: Fri Nov 23 08:39:43 2018 from 10.201.91.166
Oracle Corporation      SunOS 5.11      11.3    June 2018
anton@houston:~$ whoami
anton
anton@houston:~$ who am I
anton     pts/1        Nov 29 13:00    (x.x.x.x)
anton@houston:~$ su - dbowner
Password:
Oracle Corporation      SunOS 5.11      11.3    June 2018
dbowner@houston:~$ whoami
dbowner
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
Password:
Oracle Corporation      SunOS 5.11      11.3    June 2018
dbowner@houston:~$ whoami
dbowner
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.

 
HTH – Antonio NAVARRO

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;

DUPLICATE TARGET DATABASE FOR STANDBY

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

ALTER DATABASE BACKUP CONTROLFILE TO ‘/xxxx/xxxx/control.bck’;

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.

HTH – Antonio NAVARRO

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)

create_ssh_tunnel_with_putty_v1

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

create_ssh_tunnel_with_putty_v2

Click on add button and after click on Open.

create_ssh_tunnel_with_putty_v3

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 127.0.0.1)

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

Set Service_name to the database

create_ssh_tunnel_with_putty_v4

 

HTH – Antonio NAVARRO