2012-01-31

Setting Up Oracle Connection Manager (without SOURCE_ROUTE)


This post must be seen as a direct follow up to Arup Nandas Setting Up Oracle Connection Manager.
As there are many references to this post, please read it first. Problem and Solution are quite similar, only the architecture is a little bit different:

The Architecture

 The network diagram of the three machines is slightly different:


There is a new needed connection: from the instance on dbhost1 to the connection manager on cmhost1.

After changing the setup, you will need to rewrite the TNSNAMES.ORA in the following way:

TNS_CM = 
  (DESCRIPTION = 
    (ADDRESS = 
      (PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)
    )
    (CONNECT_DATA = 
      (SERVICE_NAME=srv1)
    )
  )

You see, the (SOURCE_ROUTE = YES) disappeared as well as the ADDRESS of the listener on dbhost1.

How it Works


Note, all the special parameters and settings on the clients TNSNAMES.ORA disappeared. But the cman must know about the SERVICE_NAME it has to serve. As the cman can be seen as a special kind of listener, there is a common way a listener gets informed about a SERVICE_NAME: the Instance has to register the services to the listener. In general this is done by pmon at registering to logal_listener and remote_listener. In this case, remote_listener is the magic parameter.

Setting Up


You can follow step (1) to (9) as in Arups blog.
But before (10) an additional step is required:

(x) on the instance add the cman to remote_listener:

Alter System Set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=cmhost1)(PORT=1950))))' scope=both;

If there is already an entry in remote_listener, e.g. in a RAC, you can separate the different connection strings by comma. An example can be

Alter System Set remote_listener='SCAN-IP:1521,(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=cmhost1)(PORT=1950))))' scope=both;

(For more details about SCAN I'd recommend this PDF)

CMCTL Primer

As we have now the services registered also on cman, we can see it there. The  SHOW command has a 2nd parameter services. Here an example

Services Summary...
Proxy service "cmgw" has 1 instance(s).
  Instance "cman", status READY, has 2 handler(s) for this service...
    Handler(s):
      "cmgw001" established:1 refused:0 current:0 max:256 state:ready
         <machine: 127.0.0.1, pid: 16786 >
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44391))
      "cmgw000" established:1 refused:0 current:0 max:256 state:ready
         <machine: 127.0.0.1,pid: 16784>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44390))
Service "INSTANCE1" has 1 instance(s).
  Instance "INSTANCE1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=dbhost1)(PORT=1521))
Service "cmon" has 1 instance(s).
  Instance "cman", status READY, has 1 handler(s) for this service...
    Handler(s):
      "cmon" established:3 refused:0 current:1 max:4 state:ready
         <machine: 127.0.0.1, pid: 16759>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=44374))
The command completed successfully.

Fine Tuning

I try to create a dedicated service for all (or a well known set of) connections via the connection manager. By doing so it's sometimes easier to separate or identify different kinds of sessions.