Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, December 19, 2010

Using Connection Manager

Oracle Connection Manager is a proxy server, an intermediate server that forwards connection requests to database servers or to other proxy servers. It has two primary functions:
  • Session multiplexing
  • Access control
With session multiplexing, you can quickly enable Oracle Connection Manager to funnel multiple client sessions through a network connection to a shared server destination.
With access control, you can use rule-based configuration to filter out certain client requests and accept others.
Proxy Server
A server that substitutes for the real server, forwarding client connection requests to the real server or to other proxy servers. Proxy servers provide access control, data and system security, monitoring, and caching.
Oracle Connection Manager  Host
The machine on which Oracle Connection Manager is installed. It must not be application tier or database tier host.  
Key Features of Oracle Connection Manager
  • In its session multiplexing role, Oracle Connection Manager reduces operating system and network resource requirements by minimizing the number of network connections made to a server. Network bottlenecks are thus reduced, increasing system scalability and potentially enabling thousands of simultaneous users to access a single database.
  • You can define connection routing  to use each specified address (in tnsnames.ora) in order, until the destination is reached.
  • Allows clients to connect to database servers even when the client is using a network protocol that is different from that used by the server. This enables you to more easily support clients in heterogeneous environments. Also supports industry-standard protocols used in the client/server connection.
  • The Firewall Access Control feature can be utilized to grant or deny client access to a particular database service or a computer, and hence enhance network security.
  • Connection to the database is via Forms Applet if the database is on a different host than the web server.
Configuration
In general, to implement Oracle Connection Manager, you will set up a three (or more) node installation. For example:
  • Application Tier:  (application server, sqlplus etc)
  • Oracle Connection Manager Host: (192.168.12.81)
  • Oracle Connection Manager Listener Port: (1950  or some other)
  • Database Tier: (10.10.2.46)
  • Database Listener Port: 1621
  • Database SID: (FRADB)
  • Domain Name: (home.com)


Oracle Connection Manager is a component of Oracle Net Services which comes with Oracle Database CD. To install, follow the steps below:
  1. Run the runInstaller utility from the database CD.
  2. Select 'Advanced Installation' option
  3. Select type of installation as 'Custom'
  4. Specify Install directory ( Oracle Home on CMAN server)
  5. Deselect components included by default (except required components)
  6. Select 'Oracle Net services -> Oracle Connection Manager' from Available Product Components and continue with runInstaller instructions. Refer to the example shown below in Figure

     My Environment (Windows 2003)
    1- DB Server  = 10.10.246 - Database FRADB
    2- CMAN Host  = 192.168.12.81
    3- Client Machine = 172.55.12.101 , (Win XP,Client SW SQL*Plus)


    Configure Connection Manager - On CMAN HOST
    Installed the cman component , I installed only the software to a oracle home named "cman"
    in ORACLE_HOME\network\admin\SAMPLE, copied cman.ora file to ORACLE_HOME\network\admin
    folder and made the following entry

    C:\oracle\product\10.2.0\cman\network\admin\cman.ora
      zCMAN1=(CONFIGURATION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955))
      (RULE_LIST=
       (RULE=(SRC=*)(DST=10.10.2.46)(SRV=FRADB)(ACT=accept))
       (rule=(src=192.168.12.81)(dst=127.0.0.1)(srv=cmon)(act=accept))))

    The Oracle Connection Manager Control utility (CMCTL) enables you to administer Oracle Connection Managers. Oracle Connection Manager does not start without the cman.ora file.

    Above is an example of a cman.ora file that contains a configuration entry for an Oracle Connection Manager called zCMAN1. There should be at least one rule for client connections and one rule for CMCTL connections as shown in the Example CMAN.ORA

    The first rule allows incoming connection from any machine to the db_server for any FRADB database service name.
    The second rule allows CMCTL connections from the host "test" ( this is actually the host where CMAN is running).
    CMAN.ORA supports usage of "*" as wildcards. An askterisk can only be used to include or exlclude an entire value for a RULE parameter. Partial values are not allowed.
    Example you can use the following rule
    (RULE=(SRC=*)(DST=)(SRV=*)(ACT=accept))
    This rule will allow incoming connection from client with any ip address to the db_server for any database service name.

    But the following rule is not allowed
    (RULE=(SRC=X.Y.0.*)(DST=)(SRV=*)(ACT=accept))

    Instead you can use ip_address/nn notation for subnet addresses. For example a rule like following will alow incoming connection from any client ip whose first 16 bit matches with 162.158.
    (RULE=(SRC=162.158.0.0/16)(DST=)(SRV=*)(ACT=accept))
     Sub-Parameters
    The parameter RULE to specify an access control rule list to filter incoming connections. A rule list specifies which connections are accepted, rejected, or dropped.
    It has  the following sub-parameters:
    • SRC: Specify the source host name or IP address in dot notation of the client.
    • DST: Specify the destination server host name or IP address in dot notation of the database server.
    • SRV: Specify database service name of the Oracle Database (obtained from the SERVICE_NAME parameter in the initialization parameter file).
    • ACT: Specify accept to accept incoming requests or reject to reject incoming requests.
    The ACTION_LIST parameter specifies rule-level parameter settings for some parameters, as follows:
    • AUT: Oracle Advanced Security authentication on client side
    • MCT: Maximum connect time
    • MIT: Maximum idle time-out
    • MOCT: Maximum outbound connect time
    • CONN_STATS: log input and output statistics
     Configure DB SERVER

    Configuring the Oracle Database Server for Oracle Connection Manager

    Configuring the database server is a two-part process that involves registering database information remotely with Oracle Connection Manager and, optionally, configuring the server for multiplexing.
    Service Registration Configuration
    LOCAL_LISTENER and REMOTE_LISTENER parameter need to be configuered in the database so that the database register itself both with the local listener and the CMAN. LOCAL_LISTENER should point to the listener running in the database server and REMOTE_LISTENER parameter should point to the CMAN listener port like

    ALTER SYSTEM SET LOCAL_LISTENER="(address=(protocol=tcp)(host=)(port=))"

    ALTER SYSTEM SET REMOTE_LISTENER="(address=(protocol=tcp)(host=)(port=))" 

    After the alias is specified, it must be resolved with a service name entry in the tnsnames.ora file on the database tier. 

    For example, an alias for an Oracle Connection Manager listener on the CMAN server might look like this in the init.ora(initFRADB.ora)

    ############################
    D:\FRATEST\FRADB\pfile\initFRADB.ora
    control_files=("D:\FRATEST\FRADB\fracontrol01.ctl", "D:\FRATEST\FRADB\fracontrol02.ctl", "D:\FRATEST\FRADB\fracontrol03.ctl")
    db_name = FRADB
    db_block_size = 8192
    sga_max_size = 1073741824
    sga_target = 1073741824
    background_dump_dest=D:\FRATEST\FRADB/admin/bdump
    core_dump_dest=D:\FRATEST\FRADB/admin/cdump
    user_dump_dest=D:\FRATEST\FRADB/admin/udump
    audit_file_dest=D:\FRATEST\FRADB/adminĜ¸admin/adump
    undo_management=AUTO

    LOCAL_LISTENER="(address=(protocol=tcp)(host=10.10.2.46)(port=1621))"
    REMOTE_LISTENER="(address=(protocol=tcp)(host=192.168.12.81)(port=1955))"
    # OR you can have the following entries for remote and local listener
    #REMOTE_LISTENER=listener_cman
    #LOCAL_LISTENER=LSNRFRADB_LOCAL
    #undo_tablespace=UNDOTBS1
    #temp_tablespace=temp
    ############################

    The alias listener_cman would then be resolved to the following entry in the tnsnames.ora (DB Server)

    listener_cman=
       (DESCRIPTION=
         (ADDRESS_LIST=
           (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955))
         )
       )

    LSNRFRADB_LOCAL=
       (DESCRIPTION=
         (ADDRESS_LIST=
           (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.2.46)(PORT=1621))
         )
       )

    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup pfile=D:\FRATEST\FRADB\pfile\initFRADB.ora
    ORACLE instance started.

    Total System Global Area 1073741824 bytes
    Fixed Size                  1253124 bytes
    Variable Size             264241404 bytes
    Database Buffers          801112064 bytes
    Redo Buffers                7135232 bytes
    Database mounted.
    Database opened.
    SQL>

     

    ON CMAN HOST Verify Database Connectivity

    Create the entry for the database server in tnsnames.ora on the CMAN host.

    FRADB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1621))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = FRADB)
        )
      )

     Start the CMAN server:
    C:\Documents and Settings\inam>set ORACLE_HOME=C:\oracle\product\10.2.0\cman
    C:\Documents and Settings\inam>set path=C:\oracle\product\10.2.0\cman\bin

    C:\Documents and Settings\inam>cmctl

    CMCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-DEC-2010 13:27:23
    Copyright (c) 1996, 2005, Oracle.  All rights reserved.
    Welcome to CMCTL, type "help" for information.


    To administer CMAN, issue the command 'administer ' at the CMCTL command prompt.



    CMCTL> administer zcman1
    Current instance zcman1 is not yet started
    Connections refer to (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955)).
    The command completed successfully.

    Issue the 'startup' command to start CMAN server processes.
    CMCTL:zcman1> startup
    Starting Oracle Connection Manager instance zcman1. Please wait...
    TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
    CMAN for 32-bit Windows: Version 10.2.0.1.0 - Production
    Status of the Instance
    ----------------------
    Instance name             zcman1
    Version                   CMAN for 32-bit Windows: Version 10.2.0.1.0 - Producti
    on
    Start date                18-DEC-2010 15:56:49
    Uptime                    0 days 0 hr. 0 min. 12 sec
    Num of gateways started   2
    Average Load level        0
    Log Level                 SUPPORT
    Trace Level               OFF
    Instance Config file      C:\oracle\product\10.2.0\cman\network\admin\cman.ora
    Instance Log directory    C:\oracle\product\10.2.0\cman\network\log\
    Instance Trace directory  C:\oracle\product\10.2.0\cman\network\trace\
    The command completed successfully.
    CMCTL:zcman1> startup
    TNS-04018: Instance already started
    CMCTL:zcman1> administer zcman1
    TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
    Current instance zcman1 is already started
    Connections refer to (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955)).
    The command completed successfully.
    CMCTL:zcman1> show services
    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:1 max:256 state:ready
            
             (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1670))
          "cmgw000" established:1 refused:0 current:0 max:256 state:ready
            
             (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1668))
    Service "FRADB_XPT" has 1 instance(s).
      Instance "fradb", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:2 refused:0 state:ready
             REMOTE SERVER
             (address=(protocol=tcp)(host=10.10.2.46)(port=1621))

    Service "cmon" has 1 instance(s).
      Instance "cman", status READY, has 1 handler(s) for this service...
        Handler(s):
          "cmon" established:2 refused:0 current:1 max:4 state:ready
            
             (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1660))
    Service "fradb" has 1 instance(s).
      Instance "fradb", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:2 refused:0 state:ready
             REMOTE SERVER
             (address=(protocol=tcp)(host=10.10.2.46)(port=1621))
    The command completed successfully.
    CMCTL:zcman1>

    NOTE: 
    - You will see the services like below in windows when you startup the zCMAN1 - connection manager process.
    OracleOraDb10g_home2CMAdminzcman1
    OracleOraDb10g_home2TNSListenerzcman1
    - logs for the cman found at ORACLE_HOME\network\log &trace
    Oracle Connection Manager generates four types of log files: one each for its listener, gateway, and CMADMIN processes and one for alerts.

    i) CMAN-instance-name_ pid.log : This file is log file for the CMAN listener. like normal listener.log it shows the service registration, sevice update and connect informations.

    ii)CMAN-instance-name_cmgw_pid.log This is the log file for the gateway process

    iii) CMAN-instance-name_cmadmin_pid.log This is log file for CMADMIN processes.

    iv) CMAN-instance-name_alert.log CMAN alert log alert log captures information about instance startup and shutdown and also chronologically record of all critical errors.
    ON CLIENT
    Create the entry for the cman in tnsnames.ora on the application tier or the client.

    cmantest =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.81)(PORT = 1955))
        (CONNECT_DATA =
          (SERVER = dedicated)
          (SERVICE_NAME = FRADB)
        )
      )

    Test it

    C:\Documents and Settings\inam>sqlplus scott/tiger@cmantest

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 09:40:47 2010

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Error accessing PRODUCT_USER_PROFILE
    Warning:  Product user profile information not loaded!
    You may need to run PUPBLD.SQL as SYSTEM

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL@ cmantest>



    Setting up a password in the CMAN
    Oracle recomends setting a password in the CMAN to secure it. Password can be set up in the following way, The password is encrypted by default and can be set only by the below method.

    CMCTL:zcman1> set password
    Enter Old password:        -- I just entered
    Enter New password:      -- oracle
    Reenter New password: --- oracle
    The command completed successfully.
    Just hit key for old password since no previuos password is set. The passwords you entered will not be echoed. This procedure does not save the password in the CMAN.ORA.  For this you have to execute save_passwd as below:

    CMCTL:zcman1> save_passwd
    The command completed successfully.
    Some Tests1-
    ON CMAN HOST
    CMCTL:zcman1> shutdown
    The command completed successfully.
    ON CLIENT
    C:\Documents and Settings\inam>sqlplus scott/tiger@cmantest

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 09:58:50 2010

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    ERROR:
    ORA-12519: TNS:no appropriate service handler found

    2- 
    ON CMAN HOST
    CMCTL:zcman1> startup
    Starting Oracle Connection Manager instance zcman1. Please wait...
    TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
    CMAN for 32-bit Windows: Version 10.2.0.1.0 - Production
    Status of the Instance
    ----------------------
    Instance name             zcman1
    Version                   CMAN for 32-bit Windows: Version 10.2.0.1.0 - Production
    Start date                19-DEC-2010 10:00:48
    Uptime                    0 days 0 hr. 0 min. 12 sec
    Num of gateways started   2
    Average Load level        0
    Log Level                 SUPPORT
    Trace Level               OFF
    Instance Config file      C:\oracle\product\10.2.0\cman\network\admin\cman.ora
    Instance Log directory    C:\oracle\product\10.2.0\cman\network\log\
    Instance Trace directory  C:\oracle\product\10.2.0\cman\network\trace\
    The command completed successfully.
    ON CLIENT
    C:\Documents and Settings\inam>sqlplus scott/tiger@cmantest

    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 10:01:12 2010

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Error accessing PRODUCT_USER_PROFILE
    Warning:  Product user profile information not loaded!
    You may need to run PUPBLD.SQL as SYSTEM

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL@ cmantest>

    3-
    I changed cman.ora config like below , shutdown zcman1 and startup again and received following on client(xp)
    zCMAN1=(CONFIGURATION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955))
      (RULE_LIST=
       (RULE=(SRC=172.55.12.101)(DST=10.10.2.46)(SRV=FRADB)(ACT=reject))
       (rule=(src=192.168.12.81)(dst=127.0.0.1)(srv=cmon)(act=accept))))

    C:\Documents and Settings\inam>sqlplus scott/tiger@cmantest
    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 19 10:42:38 2010
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    ERROR:
    ORA-12529: TNS:connect request rejected based on current filtering rules


    Enter user-name:

    Another cman.ora change example
    following change will allow  incoming connection from any client ip whose first 16 bit matches with 10.10
    zCMAN1=(CONFIGURATION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955))
                (RULE_LIST=
                    (RULE=(SRC=172.55.12.101)(DST=10.10.2.46)(SRV=FRADB)(ACT=ACCEPT))
                    (rule=(src=192.168.12.81)(dst=127.0.0.1)(srv=cmon)(act=accept))
                    (RULE=(SRC=10.10.0.0/16)(DST=10.10.2.46)(SRV=FRADB)(ACT=ACCEPT))
                )
       )

    4-
    If some client is already connecting to db via cman listener and you shutdown and try to startup again while client is still using connection , you will get the following error
    CMCTL:zcman1> shutdown
    The command completed successfully.
    CMCTL:zcman1> startup
    TNS-04017: Please wait. Shutdown in progress.
    When all clients exit from their sessions you will be able to startup.
    5- Try some other parameters (cman.ora)
    We will set IDLE_TIME=600 so that if a connection remain idle for 600 sec (i.e no data exchange takes place in 600 sec) CMAN will disconnect the session. We will also set MAX_CONNECTIONS=512 so that the maximum connections that can be handled by a gateway process increased to 512.

    zCMAN1=(CONFIGURATION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955))
                (RULE_LIST=
                    (RULE=(SRC=172.55.12.101)(DST=10.10.2.46)(SRV=FRADB)(ACT=ACCEPT))
                    (rule=(src=192.168.12.81)(dst=127.0.0.1)(srv=cmon)(act=accept))
                    (RULE=(SRC=10.10.0.0/16)(DST=10.10.2.46)(SRV=FRADB)(ACT=ACCEPT))
                )
       )
    (PARAMETER_LIST=
        (IDLE_TIMEOUT=600)
        (MAX_CONNECTIONS=512)
    )


    6-
    CMCTL:zcman1> show parameters
    listener_address          | (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.81)(PORT=1955))
    aso_authentication_filter |   OFF
    connection_statistics     |   OFF
    event_group               |   OFF
    log_directory             | C:\oracle\product\10.2.0\cman\network\log\
    log_level                 | SUPPORT
    max_connections           |   256
    idle_timeout              |     0
    inbound_connect_timeout   |     0
    session_timeout           |     0
    outbound_connect_timeout  |     0
    max_gateway_processes     |    16
    min_gateway_processes     |     2
    max_cmctl_sessions        |     4
    password                  |   OFF
    remote_admin              |   OFF
    trace_directory           | C:\oracle\product\10.2.0\cman\network\trace\
    trace_level               |   OFF
    trace_timestamp           |   OFF
    trace_filelen             |     0
    trace_fileno              |     0
    The command completed successfully.
    CMCTL:zcman1>

    Ref:
    298916.1
    305220.1
    579660.1

    1 comment:

    Yung Cong-Huyen said...

    Could you share the system requirements for an Oracle Connection Manager machine? Thank you.

    Yung