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.
