Setting Up Oracle Connection Manager

The Problem

It seems really simple. We have an Oracle database (on all nodes of a full rack Exadata, to be exact), which a lot of end-users connect to through apps designed in a rather adhoc and haphazard manner - on Excel spreadsheets, Access forms, TOAD reports and other assorted tools. We want to control the access from these machines and streamline them.

The database machine sits behind a firewall. To allow the adhoc tools accessing the database from the client machines mean we have to change the firewall rules. Had it been one or two clients, it would have been reasonable; but with 1000+ client machines, it becomes impractical. So I was asked to provide an alternative solution.

The Solution

This is not a unique problem; it's the same problem when the machines need to access resources that exist across firewalls. The easy solution is to punch a hole through the firewall to allow that access; but is not desirable for obvious security reasons. A better solution, often implemented, is to have a proxy server. The proxy sits between the two layers of access and can access the servers behind the firewall. Clients make the request to the proxy which it passes on to the server.

Such a proxy solves the problem; but we are looking for a simpler solution. Does one exist?

Yes, it does. The answer is Connection Manager from Oracle. Among its many functions, one stands out - it acts as a proxy between the different layers of access and passes through the request. It's not a separate product; but is an option in the Oracle Client software (not the database or grid infrastructure software). This  option is not automatically installed. When installing the client software, choose "Custom" and explicitly select "Connection Manager" from the list.




The Architecture

Let's quickly go through the architecture of tool. Assume there are three hosts:

• client1 - the machine where the client runs and wants to connect to the database
• dbhost1 - the machine where the database instance runs
• cmhost1 - the machine where the Connection Manager process runs

Here is a rough network diagram of the three machines

From client1 you can reach cmhost1 but not dbhost1:


C:\>ping dbhost1
Ping request could not find host dbhost1. Please check the name and try again.

Or, if the host is known but not reachable, you will notice a message like this:


C:\>ping dbhost1

Pinging dbhost1 [192.168.104.31] with 32 bytes of data:

Request timed out.

However, you can ping the dbhost1 from cmhost1 and cmhost1 from client1.

Normally the TNS entry at the client machine would have looked like this:



TNS_REG = 

  (DESCRIPTION = 
    (ADDRESS = 
      (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)
    )
    (CONNECT_DATA = 
      (SERVICE_NAME=srv1)
    )
  )


However, this will not work since the client does not even know the routing for dbhost1. Instead the client connects to the CM host. The connection manager has two processes

• The Connection Manager Admin Process (cmadmin)
• One or more Connection Manager Gateway Processes (cmgw)

The CMGW processes allows the client connections to come in through them. The admin process manages the gateways. We will cover more on that later.

After setting up the CM processes, you will need to rewrite the TNSNAMES.ORA in the following way:


TNS_CM = 
  (DESCRIPTION = 
    (SOURCE_ROUTE = YES)
    (ADDRESS = 
      (PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)
    )
    (ADDRESS = 
      (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521)
    )
    (CONNECT_DATA = 
      (SERVICE_NAME=srv1)
    )
  )

How it Works

Note the special parameter:

SOURCE_ROUTE = YES

This tells the client connection request to attempt the first address listed first and only then attempt the next one. This is different from load balance setups where you would expect the client tool to pick one of the addresses at random. So the client attempts this address first:

(PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950)

This is the listener for Connection Manager. The clients are allowed to connect to the port 1950 (the port where CM listener listens on) on the host cmhost1.

After that the connection attempts the second address

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

However, it will fail since the client does not have access to the port 1521 of the host dbhost1. This is where CM comes in. The client does not make the request; CM does on behalf of the client connection that just came in. The connection manager (running on cmhost1) makes the request with that address. Since the host cmhost1 can access dbhost1 on port 1521, that connection request goes through successfully. When the response comes back from the database, CM passes it back to the original client.

A single CM connection can handle many client connection requests.

Setting Up

Now that you know how CM works, let's see how to enable it, step by step.

(1) Install CM, if you don't have already. Check for a file cmctl under $ORACLE_HOME/bin. If you have it, CM may have been installed already. If not, install CM by running the installer from Oracle Client (not Database or Grid Infra) software. Choose Custom Install and explicitly choose Connection Manager.

(2) Go to $OH/network/admin (remember the $OH of the client software home; not the database home)

(3) You need to create a configuration file called cman.ora. Instead of creating it from scratch, go to the samples subdirectory and copy the cman.ora sample file back into the admin directory.

(4) In the file cman.ora, make the changes to the following lines. Of course, I assumed cmhost1 as the server running Connection Manager process. Substitute by whatever name you choose for the CM server. I also assumed you would use port 1950 for the CM listener. It does not have to be. Whatever you choose will need to be opened in the firewall.

The lines you are changing will be at the beginning and the end of the cman.ora file.

cman_cmhost1 =

(configuration=

  (address=(protocol=tcp)(host=cmhost1)(port=1950))

  (parameter_list =

...

...

...

# conn_stats  = connect_statistics

(rule_list=

  (rule=

    (src=*)(dst=*)(srv=*)(act=accept)

    (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))

  )

 )

)

Keep the remaining lines as is, for now. I will explain the meaning of these parameters later.

(5) Start the CM command line interface by executing "cmctl"

# cmctl

CMCTL for Linux: Version 11.2.0.1.0 - Production on 29-AUG-2011 15:16:01

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

Welcome to CMCTL, type "help" for information.

CMCTL> 

This will show a prompt "CMCTL>". Here you will enter different admin commands for the CM processes, much like LSNRCTL command line interpreter.

(6) Start the administration process by typing "administer"

CMCTL> administer

Current instance CMAN_cmhost1 is not yet started

Connections refer to (address=(protocol=tcp)(host=cmhost1)(port=1950)).

The command completed successfully.

CMCTL:CMAN_cmhost1> 

Note how the prompt changed showing the name of the CM.

(7) Start the connection manager processes by issuing "startup"

CMCTL> startup

Starting Oracle Connection Manager instance CMAN_cmhost1.proligence.com. Please wait...

TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.

CMAN for Linux: Version 11.2.0.1.0 - Production

Status of the Instance

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

Instance name             cman_cmhost1.proligence.com

Version                   CMAN for Linux: Version 11.2.0.1.0 - Production

Start date                29-AUG-2011 17:25:48

Uptime                    0 days 0 hr. 0 min. 9 sec

Num of gateways started   2

Average Load level        0

Log Level                 OFF

Trace Level               OFF

Instance Config file      /opt/oracle/product/11gR2/client1/network/admin/cman.ora

Instance Log directory    /opt/oracle/product/11gR2/client1/network/log

Instance Trace directory  /opt/oracle/product/11gR2/client1/network/trace

The command completed successfully.

(8) If everything goes well, you should see the CM status

CMCTL:CMAN_cmhost1> show status

Status of the Instance

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

Instance name             cman_cmhost1

Version                   CMAN for Linux: Version 11.2.0.1.0 - Production

Start date                30-JUN-2011 13:26:16

Uptime                    60 days 2 hr. 15 min. 10 sec

Num of gateways started   2

Average Load level        1

Log Level                 ADMIN

Trace Level               OFF

Instance Config file      /opt/oracle/product/11gR2/client1/network/admin/cman.ora

Instance Log directory    /opt/oracle/product/11gR2/client1/network/log

Instance Trace directory  /opt/oracle/product/11gR2/client1/network/trace

If it does not start, refer to the troubleshooting section later in this blog.

(9) Make the TNSNAMES.ORA file change at the client as shown earlier.

(10) Make the connection using this new TNS connection alias:

C:\> sqlplus arup/arup@TNS_CM

You should be able to connect to the database server now. Note, you still can't access the database host directly. If you use the regular TNS connect string - TNS_REG - you will fail. This new connection was established through the connection manager.

(11) Check the number of connections coming through the CM, using CMCTL tool:

CMCTL:CMAN_cmhost1> show connections

Number of connections: 1.

The command completed successfully.

The output shows there is one connection through the CM listener. As you connect more, you will see the number next to "Number of connections:" increasing.

That's it. You have successfully configured Connection Manager interface. 

Fine Tuning

In the previous setup I asked you to enter some values without really explaining the significance of them. Let's go through them. 

One of the powerful features of the CM interface is to act as sort of a firewall, i.e. allow connections from/to certain hosts and for specific services. You can define these inside the RULES_LIST section as shown below:

(rule_list=

  (rule=

    (src=x)(dst=x)(srv=*)(act=accept)

    (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))

  )

 )

Here are the parameters and what they mean:

• src = the source server where the connection request would come from. If you want to leave it unrestricted, use "*", as a wildcard.
• dst = the destination server, which is probably the database server the request would go to. Again, unrestricted access would be given as "*".
• srv = the service. enter "*" for all types of services. 
• act = the action, e.g. accept, reject or drop the request

On src and dst parameters you can give hostnames, IP addresses as well as wildcards. You would use this section to allow or deny the access between different servers, making it a really powerful firewall-like tool.

The action_list parameter allows you to fine tune the actions on the connection. 

• aut = whether the Oracle Advanced Security Option authentication filter should be applied. The value shown here is OFF, means this is not to be applied.
• moct = after how long the outbound connection established should timeout. The value set here is 0, means the outbound connection is never to be timed out.
• mct = after how long the session should disconnect. The value is 0, i.e. never.
• mit = the timeout duration for idle connections
• conn_stats = whether the connection statistics be maintained.

Note the use of parentheses. You can use different rules and actions for each combination of sources and destinations. It allows you to finetune the access. For instance, database D1 is highly secure and you would want ASO filter; but not database D2. For the request coming from the same client, you can have a different set of actions for each destination. For D1, the more secure database host, you can establish various timeouts.

CMCTL Primer

Now that you know about the CMAN.ORA file, let's see the activities you can perform in CMCTL. The first command you should explore should be "help".

CMCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

administer      close*          exit            quit            

reload          resume*         save_passwd     set*            

show*           shutdown        sleep           startup         

suspend*       

You can get help on a specific command as well:

CMCTL> help administer

administer [] [using ] - Sets up a context for administering the given Oracle Connection Manager instance

Two commands you will be using a lot are - SHOW and SET. 

CMCTL> help SHOW

The following operations are available after show

An asterisk (*) denotes a modifier or extended command:

all             connections     defaults        events          

gateways        parameters      rules           services        

status          version         

CMCTL> help set

The following operations are available after set

An asterisk (*) denotes a modifier or extended command:

aso_authentication_filter     connection_statistics         

event                         idle_timeout                  

inbound_connect_timeout       log_directory                 

log_level                     outbound_connect_timeout      

password                      session_timeout               

trace_directory               trace_level   

Most of these modifiers are self explanatory, e.g. show status will show you the status of CM; show connections will show connections established through CM, etc.

Troubleshooting

Of course things may not go well the first time. Don't despair. You can perform extensive diagnostics and enable logging and tracing. 

The most common error may come during the startup:

TNS-04012: Unable to start Oracle Connection Manager instance.

Unfortunately it's a generic, catch-all error. The most common reason is an incorrectly constructed CMAN.ORA file, e.g. with non-identifiable hostnames and port numbers, log/trace directories that do not yet exist, invalid parameters and values or even mismatched parantheses. It's difficult to guess what caused the issue. The best option for you is to copy the sample CMAN.ORA file and replace the values with your own, paying special attention to the directory names.

Another cause of this error is not using Fully Qualified Names. For instance, you must use cmhost1.proligence.com instead of "cmhost1".

Sometimes the issues still linger. You can perform extensive diagnostics by using extended tracing and logging. To enable logging and tracing, you have to set the parameters in CMAN.ORA:

log_level : set to SUPPORT

trace_level : set to SUPPORT 

The default for both is OFF, means no logging and tracing.

When enabled, CM emits tracings and logging in the appropriate directories that may lead to the source of the problem. Here is an excerpt from the log:

(LOG_RECORD=(TIMESTAMP=29-AUG-2011 16:22:26)(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=cmhost1)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)

This shows clearly the issue. We have to make the appropriate entry to make CM work. You can also enable these dynamically

CMCTL:CMAN_cmhost1.proligence.com> set trace_level support

CMAN_cmhost1.proligence.com parameter trace_level set to support.

The command completed successfully.

Takeaways

Connection Manager is a great product form Oracle Network family of products that can, among many things, perform as a connection concentrator from multiple client requests, act as a rule based mini-firewall for the database requests and act as a proxy between different access domains. Here you learned how to set it up, fine tune the parameters and manage it effectively. Hope you liked it. As always, please provide your feedback.