Monday, August 29, 2011

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.















29 comments:

Kumar Madduri said...

Hi Arup
Your presentation style is very lucid and clear.
I liked the idea of connection manager. But if you used purely from a point of replacing the http proxy server, I dont see lot of advantage or simplicity in doing this way as opposed to setting up proxy server using apache2 (may be that is my bias).

Thank you for your great articles.

Kumar

Mohammed Taj said...

Nice post

royski said...

Hi Arup,

Excellent post! Would you say this tool in someway does the same thing with DB Vault? :)

Regards,
Roy

Arup Nanda said...

@Kumar CM and http proxies are like apples and oranges. HTTP proxies are for http traffic. CM is for Oracle Net (aka SQL*Net) traffic. They can't be compared and one can't replace the other.

Arup Nanda said...

@royski Not at all. DB Vault separates the accesses of SYSDBA and schema. CM merely acts as a gatekeeper of the connection requests

Martin Berger said...

Arup,
Is there a reason why you prefered SOURCE_ROUTE against REMOTE_LISTENER config? I like the REMOTE_LISTENER method more, as a change in the setup does not require a rollout of tnsnames.ora to the clients.

Arup Nanda said...

@Martin - the target database is a 11.2 RAC one, where a SCAN listener is used for client connections. That's the one I have used. However, it can be any listener - not necessarily the SCAN one.

royski said...

Hi Arup,

I was thinking of restricting applications like toad,sqldev access to the database. I guess OCM and DB Vault can be complimentary. :)

Regards,
Roy

Martin Berger said...

Arup,
I fear I was not clear in my question, as you know the setup for sure:
You are using the SOURCE_ROUTING config - so every clients tnsnames.ora need to be configured with this parameter.
I prefer a small tnsnames.ora on the clients side, with only (PROTOCOL = TCP)(HOST = cmhost1)(PORT = 1950), but without the SOURCE_ROUTING and without the (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521). For this configuration I have to change the REMOTE_LISTENER in the related DBs spfile from dbhost1:1521 to a pre-created tnsnames.ora entry on all DB-hosts, eg. (in tnsnames.ora): SCAN_CMAN= .... ADDRESS_LIST=(ADDRESS ... (HOST=SCAN1) ... HOST=(SCAN2) ... (HOST=SCAN3) ...(HOST=host1-vip) ... (HOST= cmhost1) )
With the spfile having remote_listener=SCAN_CMAN, pmon registers it's services not only to it's local listeners, but also to CMAN.
By that method I can also see all the available services in CMCTL> show services

Arup Nanda said...

@Martin could you please email me at arup@proligence.com? I would like to follow up on this.

jcnars said...

Here's a dumb question from someone new to OCM:

"If not, install CM by running the installer from Oracle Client (not Database or Grid Infra) software".

I take it to mean "If not, install CM by running the installer from Oracle Client (not Database or Grid Infra) software INTO A NEW ORACLE HOME IN THE EXADATA (or the database)SERVER"

Will i right in assuming that?

Arup Nanda said...

@jcnars The CM is installed in a "different" server. Please refer to the figure. Installing CM on the same server as the database itself won't make any sense.

Oracle software comes in three broad categories:

(1) Grid Infrastructure
(2) Database
(3) Client

#2 includes #3 as well. On the different server (CM), you need to install the Client software; not the database software or GI software. Even if you have DB installed there, and even though DB software has client; you still need to install the client in a separate home.

Hope this helps.

Arup Nanda said...

@jcnars your last comment somehow does not show up here in the blog; but I got the comments via email. Can you repost the comment? Hopefully that will make the comment show up.

Jatin Gulati said...

Hello Arup,

Oracle open world is around this month-end,would be great to know if any upcoming presentation related to 11gR2 RAC from your side are in line for the same.

Thanks,
Jatin

Alexandre Marti said...

Hi Arup. Can I use cm with easy connect naming method?

Thank´s

Alexandre Marti.

Martin Berger said...

Alexandre,
easy connect only provides one host - so you can not use (SOURCE_ROUTE = YES). If you make the Instances register them to the cman, it will work.

Karuna said...
This comment has been removed by the author.
Karuna said...

Excellent ... You make it so easy to understand. Thanks

SEO tools said...

Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.

Val said...

Great post. What are your thoughts on using Oracle Database Firewall to control the sqlnet traffic?
Thanks
Val

Davide said...

Hi Arup,
this note is very usefull for me.
A question, if in tns_cm i use only cmhost and in the cman.ora I specify:

(RULE_LIST=
(RULE=(SRC=*)(DST=SCAN-IP)(SRV=*)(ACT=accept))
(rule=(src=CMHost)(dst=127.0.0.1)(srv=cmon)(act=accept))))

with RAC SCAN-IP, it works?

Arup Nanda said...

@Davide - by SCANIP, you mean the DNS hostname of the SCAN, correct? Well, I haven't tried it; but I did successfully use the three actual IPs associated with the SCANIP DNS hostname. You could do that way.

Anonymous said...

Hi Arup,

very interesting post. I am configuring CMAN in a 10R2 test environment, but following the docs I did not be able to make the client connection working.

The parameter SOURCE_ROUTE is not indicated in the doc, and I lost lot of time looking for solution.

So your post has been very appreciated!
Thank you very much

M.

DanyC said...

Hi Arup,

Very nice post, thx for sharing.
Have you had the chance to follow up with Martin B about REMOTE_LISTENER config?

If yes, would be possible to write/ update the current topic as i'm very interested to see the other approach?

Many thanks,
Dani

Martin Berger said...

DanyC,
I have created a follow up post to this one.
It's exactly about Setting Up Oracle Connection Manager (without SOURCE_ROUTE). Hopefully it answers your questions.
Martin

data recovery experts said...

Awesome Post Write it up. This is so useful and informative for me.. I am really appreciate about your thought. Vote of thanks to shared this information.

Free Antivirus Download said...

your post diagram is technically wrong please correct it. free antivirus download

Jitesh said...

Arup - Nice post and thx for sharing. Oracle Connection Manager works if client got Oracle at there end. Let's say client's connection is ODBC/JDBC...in that case Connection Manager Concept works ? If yes, than what set up would be requird ? If not, then do we have any work around for such solutions using Proxy in between Client and Oracle DB ?
Thanks
Jitesh R. (jrughani@gmail.com)

Unknown said...

Thanks for the amazing article here. I was searching for something like that for quite a long time and at last I have found it here. Your blog is better than others because of useful and meaningful posts. Keep posting them in the future too, I will be waiting for that!
Oracle Goldengate

Translate