Martins Blog

Trying to explain complex things in simple terms

Preventing a SPOF with CMAN 12c

Posted by Martin Bach on July 25, 2016

I have already written about the use of Connection Manager 11.2 to govern access to a database. While researching this piece I have updated the original post so that it’s relevant for 12c as well.

Although the idea of using Connection Manager is tempting, the obvious first question is about high availability of the process, you don’t want it to become a Single Point of Failure (SPOF). After all, if the gatekeeper to your database environment fails, you are effectively closed for business. One option would be to add another CMAN process that could be used.

Lab Setup

I have created a few VMs to simulate the scenario. I installed CMAN in 2 VMs running Oracle Linux 7 and CMAN 12c. The client homes have been patched up to the April PSU because that’s considered good practice (although I couldn’t find a reference to CMAN in the OPatch logs). The hosts are named oracledev and server4.

I have a 4 node RAC 12c database named NCDB to which the CMAN rules provide access. The SCAN is named rac12pri-scan.

And finally I have an Oracle 12c installation on server3 which will act as the client.

CMAN configuration

The CMAN configuration is quite straight forward. Following my earlier post’s instructions I created the following $ORACLE_HOME/network/cman.ora on oracledev:

[oracle@oracledev ~]$ cat $ORACLE_HOME/network/admin/cman.ora
cman1 =
  (configuration=
    (address=(protocol=tcp)(host=oracledev)(port=1521))
    (rule_list=
      (rule=(src=oracledev)(dst=127.0.0.1)(srv=cmon)(act=accept))
      (rule=(src=*)(dst=rac12pri-scan)(srv=NCDB)(act=accept))
    )
  )

Please note that the configuration file has been kept at its bare minimum to keep the discussion simple. You should review the net services reference guide for a list of available parameters and how to secure the system.

The configuration on host server4 is identical except that the CMAN host has changed. Using that information I can start the CMAN process:

[oracle@server4 ~]$ cmctl startup -c cman1

CMCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUL-2016 13:38:32

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

Current instance cman1 is not yet started
Connecting to (address=(protocol=tcp)(host=server4)(port=1521))
Starting Oracle Connection Manager instance cman1. Please wait...
TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
CMAN for Linux: Version 12.1.0.2.0 - Production
Status of the Instance
----------------------
Instance name             cman1
Version                   CMAN for Linux: Version 12.1.0.2.0 - Production
Start date                06-JUL-2016 13:38:32
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      /u01/app/oracle/product/12.1.0.2/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/server4/cman1/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/server4/cman1/trace
The command completed successfully.
[oracle@server4 ~]$ 

I can start the second one the same way.

Client configuration

So this is all fair and good, but how can I configure the client to connect to the database? I would like two things to happen:

  • Connect time load balancing. In other words, don’t hammer just one of the two CMAN processes with incoming connection requests while the second one is idling around
  • Ensure that the alternative CMAN is tried in case one of the two systems dies

Things seemed a bit more complicated because I have to use SOURCE_ROUTE to indicate to the client that a connection manager process is involved. For instance, if I have a single CMAN process, I would use the following entry in tnsnames.ora:

NCDB_1CMAN =
 (description=
   (address_list=
     (source_route=yes)
     (address=(protocol=tcp)(host=server4)(port=1521))
     (address=(protocol=tcp)(host=rac12pri-scan)(port=1521))
   )
   (connect_data=
    (service_name=NCDB)
   )
 )

Relying on connection manager on “server4” I connect through to the NCDB database on my RAC system.

[oracle@server3 tns]$ sqlplus martin@NCDB_1CMAN

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 6 13:44:46 2016

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

Enter password: 
Last Successful login time: Wed Jul 06 2016 13:44:34 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select count(*) from v$active_instances;

  COUNT(*)
----------
         4

But what happens if server4 is not available? Well you guessed right:

[oracle@server3 tns]$ sqlplus martin@NCDB_1CMAN

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 6 13:52:51 2016

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

Enter password: 
ERROR:
ORA-12541: TNS:no listener

Enter user-name: 

This is the single point of failure I would like to prevent.

Using Two CMAN processes in the tnsnames.ora file

After a bit of digging around on My Oracle Support I gave up and used my search engine skills, and that proved more effective. The Oracle Database Net Services References has the correct example in chapter 6, which looks as follows after I applied it to my environment:

NCDB_2CMANS =
  (description =
    (load_balance = on)
    (failover = on)
    (address_list =                   # first CMAN on server4
      (source_route = yes)
      (address = (protocol = tcp)(host = server4)(port = 1521))
      (address=(protocol=tcp)(host=rac12pri-scan)(port=1521))
    )
    (address_list =                   # second CMAN on oracledev
      (source_route = yes)
      (address = (protocol = tcp)(host = oracledev)(port = 1521))
      (address=(protocol=tcp)(host=rac12pri-scan)(port=1521))
    )
    (connect_data =
      (service_name = NCDB)
      (server = dedicated)
    )
  )

You read this as follows:

  • The service name entry starts with the DESCRIPTION tag.
  • On the top level LOAD_BALANCE and FAILOVER are enabled. This allows client side load balancing plus failover to the next CMAN entry
  • Each CMAN host has its own ADDRESS_LIST. Essentially it’s the NCDB_1CMAN entry with the SOURCE_ROUTE, CMAN host and SCAN
  • There is nothing different in the CONNECT_DATA section compared with the previous, single CMAN-entry

The Result

As a result, I can connect to the database now – I haven’t got a CMAN process on server4 but there is one on oracledev:

[oracle@server4 ~]$ cmctl show status -c cman1

CMCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUL-2016 14:17:16

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

Current instance cman1 is not yet started
Connecting to (address=(protocol=tcp)(host=server4)(port=1521))
TNS-04011: Oracle Connection Manager instance not yet started.
[oracle@server4 ~]$ 

[oracle@oracledev ~]$ cmctl show status -c cman1

CMCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUL-2016 14:17:45

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

TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
Current instance cman1 is already started
Connecting to (address=(protocol=tcp)(host=oracledev)(port=1521))
Status of the Instance
----------------------
Instance name             cman1
Version                   CMAN for Linux: Version 12.1.0.2.0 - Production
Start date                06-JUL-2016 13:18:19
Uptime                    0 days 0 hr. 59 min. 26 sec
Num of gateways started   2
Average Load level        0
Log Level                 SUPPORT
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/12.1.0.2/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/oracledev/cman1/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/oracledev/cman1/trace
The command completed successfully.
[oracle@oracledev ~]$ 

A connection can be established:

[oracle@server3 tns]$ sqlplus martin@NCDB_2CMANS

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 6 14:18:13 2016

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

Enter password: 
Last Successful login time: Wed Jul 06 2016 13:44:49 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select count(*) from v$active_instances;

  COUNT(*)
----------
         4

SQL>

Using an approach like this allows me to connect to the database even if one of the two CMAN systems are down. But what about load balancing? This appears to work as well, tailing the cman1.log file I can see that out of 10 sessions I requested in a bash for-loop they have been evenly spread across both CMAN hosts:

-- server4

06-JUL-2016 13:21:49 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19796)) * establish * NCDB * 0
06-JUL-2016 13:21:49 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19798)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19800)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19801)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19802)) * establish * NCDB * 0

-- oracledev

06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22637)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22639)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22643)) * establish * NCDB * 0
06-JUL-2016 13:21:51 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22644)) * establish * NCDB * 0
06-JUL-2016 13:21:51 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22645)) * establish * NCDB * 0

So both boxes ticked.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: