ducttape.it

Problem

So you've configured your SSH tunnel properly through your corporate login machine and right into your Oracle 10g RAQ.

# ssh -L 1521:db.prod.corp.com:1521 login.corp.com

You've also pointed your tnsnames.ora to localhost 1521 and can successfully tnsping your connection. However, upon successfully logging in the machine hangs. strace reveals that the listener is directing your client to the production server ip address (ignoring your kind request to use SSH tunnels). This causes the client to timeout immediately after successful authentication.

Solution

You need to direct the client to use a specific instance (rather than allowing the listener to direct it). You need to specify the instance name in the CONNECT_DATA block. Your TNSNAMES should look something like...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

BLACKBEAR =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = 
       (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (ADDRESS = 
       (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
     (LOAD_BALANCE = YES)
   )
   (CONNECT_DATA =
     (SERVICE_NAME = BLACKBEAR)
     (SRVR = DEDICATED)
     (INSTANCE_NAME = BLACKBEAR1)
   )
 )

This basically makes load balancing useless and as such should probably not be used for production (use network acls instead) but should be fine on a development or staging machine. If the instance is down simply modify the INSTANCE_NAME value and connect again.