SSH Tunneling to SCAN listener

I needed to connect via SQL*NET to a RAC 11.2 SCAN listener today, and initially couldn’t get it to work using my Putty tunnels.

I wanted to use SQL Developer, which was giving me this error –

ORA-12516, TNS:listener could not find available handler with matching protocol stack

Initially I was just tunneling my local port 127.0.0.1:1521 to dm01-scan:1521. But we know that the SCAN listener replies back to incoming connections with the name of the local node (VIP) listener which it wants the client to connect to. So I then also set up additional tunnels in Putty for each VIP name and added these to my Windows hosts file (in C:\Windows\System32\drivers\etc\hosts). My configuration ended up looking like this –

But still no joy, it wasn’t working. So I traced the SQL*NET session and found out that the SCAN listener was actually replying with the *IP ADDRESS* of the node listener rather than the name. This means my local hosts file name resolution wouldn’t work and it wouldn’t use my tunnel.

In order to fix this I had to change the LOCAL_LISTENER instance parameters. They were initially pointing to the IP address of the VIP –

SQL> show parameter local_li

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=
10.200.132.13)(PORT=1521))

I wanted them to point to the VIP by name –

alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=dm01client01-vip)(PORT=1521))' sid='dbm1';
alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=dm01client02-vip)(PORT=1521))' sid='dbm2';

I then ran an “ALTER SYSTEM REGISTER;” on both instances to force that through to the listener straight away. You can check everything went through fine by running the following –

lsnrctl services LISTENER_SCAN1

NOW when I try to connect with SQL Developer I still get this strange error. The final piece of the puzzle was to use a tnsnames.ora file within the SQL Developer application. Set the location in Tools > Preferences > Database > Advanced > Tnsnames Directory and add a simple entry as follows to the file –

DBM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbm)
)
)

Finally, everything now works.

We connect to the SCAN listener on dm01-scan which resolves via our hosts file to 127.0.0.1 which goes via our first Putty tunnel. The SCAN listener then replies to us and says “Hey, actually I’d like you to talk to dm01client01-vip on port 1521”. So our client then connects to dm01client01-vip which is resolved via our hosts file to 127.0.0.2 which goes via our second Putty tunnel.

And we’re now connected.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: