Connection manager - why does this seem to be a secret feature?

While looking into an issue with not having routing available to a certain network one of my colleagues suggested connection manager - something he just found when googling the problem. Now I'd heard of this from many years ago ( i thought with reference to trying to communicate with some other strange network type) but i knew basically nothing about it.

I was initially just going to suggest how we got round this sort of thing before like this http://dbaharrison.blogspot.de/2016/02/sneaking-past-firewalls-to-database.html

but then i thought i'd check what connection manager was all about (as it was also mentioned to me when i presented the technique above at a user group meeting - which i then promptly forgot about 5 minutes later until this triggered the memory again)

I then did a quick bit of googling myself and found a couple of articles -

This one by Arup http://arup.blogspot.de/2011/08/setting-up-oracle-connection-manager.html

and this one by Martin https://martincarstenbach.wordpress.com/2011/07/11/using-connection-manager-to-protect-a-database/

Both pretty old - but both useful - what confused me though is that they both seem to have fixed the same issue but used connection manager in quite different ways. Arup's doesn't need any changes to the database you want to connect to and connection manager just seems to act as a middle man just handing the connection over to the next connection in the chain. In Martin's case the remote database actually register something back with the connection manager (similar to how normal dynamic listener registration works).

Choosing the laziest option i went with how Arup had done it - so here is how i implemented my version of that

Here is the infra i had (nice drawing skills eh?)

In this case my client is in our corporate network, the hop server is in a network in azure that is 'privately peered' to our corporate network - i.e. it appears as just an internal address to us and is accessed over a private express route connection. The final destination database is also in Azure but is in a network that is not directly peered so a direct client to database connection is not possible.

So how did i go about this then.

Well the first issue was actually tracking down the connection manager software.....

It's not included with a full EE install surprisingly, it's also not included with a full admin client install - so where the hell is it? Well turns out you get it from a custom install of the client where you specifically choose it - which kind of hints to me that this is not going to be widely used.......

Anyway once i tracked it down i installed this on the 'hop' server in the picture above - using the 12.2 client version (again being lazy - this was the easiest to find on the website).

Once the client was installed i had a look and sure enough the utility cmctl is there and we're ready to go.

In the $ORACLE_HOME/network/admin file i created the following config file cman.ora

CMAN_thishostname =
  (configuration=
    (address=
      (protocol=tcp)(host=thishostname)(port=1555)
    )
    (rule_list=
      (rule=(src=thishostname)(dst=127.0.0.1)(srv=cmon)(act=accept))
      (rule=(src=*)(dst=destdbhostname)(srv=*)(act=accept))
    )
  )

This is largely based on the 2 blogs above - the first bit of config says i want connection manager on port 1555 (port picked at random - has no special meaning), the second part adds some rules - the first is required for the thing to just work (well i didn't try without this just went with what Martin said) - the second line is saying from anywhere allow connection to destdbhost for any service - this is basically a firewall rule more than anything else.

Once that is saved i just run

cmctl startup (as i used default name for the file contents CMAN_thishostname)

CMCTL for Linux: Version 12.2.0.1.0 - Production on 12-MAR-2017 13:17:00

Copyright (c) 1996, 2016, Oracle.  All rights reserved.

Current instance CMAN_thishostname is not yet started

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=thishostname)(port=1555)))

Starting Oracle Connection Manager instance CMAN_thishostname. Please wait...

CMAN for Linux: Version 12.2.0.1.0 - Production

Status of the Instance

----------------------

Instance name             cman_thishostname

Version                   CMAN for Linux: Version 12.2.0.1.0 - Production

Start date                12-MAR-2017 13:17:00

Uptime                    0 days 0 hr. 0 min. 9 sec

Num of gateways started   2

Average Load level        0

Log Level                 SUPPORT

Trace Level               OFF

Instance Config file      /oracle/12.2.0.1.client/network/admin/cman.ora

Instance Log directory    /oracle/diag/netcman/thishostname/cman_thishostname/alert

Instance Trace directory  /oracle/diag/netcman/thishostname/cman_thishostname/trace

The command completed successfully.

And all looks good - no errors

Now all i need is a special format tnsnames entry like the following on my client

DESTDB =

  (DESCRIPTION =

    (SOURCE_ROUTE = YES)

    (ADDRESS =

      (PROTOCOL = TCP)(HOST = hopservername)(PORT = 1555)

    )

    (ADDRESS =

      (PROTOCOL = TCP)(HOST = destdbname)(PORT = 1521)

    )

    (CONNECT_DATA =

      (SERVICE_NAME=DESTDBSERVICE)

    )

  )

Now i can just connect as normal to the remote database that i can;t reach directly - the clever part in the tnsnames is that SOURCE_ROUTE=YES - that seems to tell it to go to the first thing you find (connection manager) then from there attempt to connect to the second thing you find in the config - and indeed it does that and it works fine.

Reading the notes seems to imply that you could have multiple levels of this connection manager acting as a series of passes down the line until you get to where you want.

This seems like quite a neat solution to out problem - and does seem less of a 'hack' than ssh tunneling.

Of course if the connection manager (or its host) goes down the connection will be dropped (same as for ssh tunneling). - SO for example if i do this o the hop server

 cmctl shutdown abort

then my client connection does this

select sysdate from dual

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 17512

Session ID: 632 Serial number: 1466

In summary - this looks like quite a neat feature (and I've only scratched the surface) - don't really know why it's as hidden as it appears to be - maybe there just aren't too many use cases for it.

I'm sure there are other useful parts to it and I'm sure the method that Martin explains in his blog probably has some advantage over the basic one i used here - need to look more into it to find out what that advantage is.