In these days ssh is the de facto method for establishing a remote connection to a host. It can also be used to tunnel and encrypt network communication between two hosts. SSH tunneling can be implemented for encrypting network communication between a computer used by a DBA for remote database administration, but is also suitable to be used with Data Guard for encrypting log shipping. In this recipe we will encrypt the network communication between nodeorcl1 and nodeorcl5 using ssh.
To use tunneling you must enable ssh port forwarding on the server. Open the /etc/ssh/sshd_conf configuration file and uncomment the following line:
AllowTcpForwarding yes
Save and close the file and restart sshd service as follows:
service sshd restart
- To forward the listening port to the
sshport execute the following command:[oracle@nodeorcl1 ~]# ssh -N -L1530:nodeorcl1:1521 oracle@nodeorcl1 oracle@nodeorcl1's password:
- On the client side
nodeorcl5to open the tunnel execute the following command:[oraclient@nodeorcl5 ~] ssh -N –L 1530:localhost:1521 oracle@nodeorcl1 oracle@nodeorcl1's password:
- On
nodeorcl5create a network service namedHACKDB_SSH:HACKDB_STUNNEL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1530)) ) (CONNECT_DATA = (SERVICE_NAME = HACKDB) ) )
- On nodeorcl5 use
tnspingto verify if the network service is working:[oraclient@nodeorcl5 ~]$ tnsping HACKDB_SSH TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 07-OCT-2012 16:50:16 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /u01/app/oraclient/product/11.2.0/client_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1530))) (CONNECT_DATA = (SERVICE_NAME = HACKDB))) OK (10 msec) [oraclient@nodeorcl5 ~]$
- Finally use the connection as follows:
[oraclient@nodeorcl1 ~]#sqlplus HR@HACKDB_SSH SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 09:12:58 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
The –N switch used in command specifies to do not execute any remote command. The –L switch specifies that the given port on the local host is to be forwarded to the given host and port on the remote side.