We're running Policy Server and when we have several ODBC Session
Stores, we see the ODBC connection unequally distributed among the
ODBC instances. How can we get the connections distributed equally ?
Policy Server 12.52SP1CR02 on RedHat 5;
JDK 1.7.0_141 32bit;
2 Session Store on ODBC Oracle;
Even if the ODBC driver allows the usage of alternateservers and
loadbalancing features, these will proceed only with failover,
even if the feature is called loadbalancing. The value of
loadbalancing determine the order in which the failover occur on the
configuration (in serie or random) :
Configure an Oracle Data Source for CA Single Sign-On
AlternateServers=
If the primary server is not accepting connections, specifies the
connection failover to the other Oracle nodes.
Example:
(HostName=nete_servername2:PortNumber=1521:ServiceName=nete_servicename[,...])
LoadBalancing=1
Turns on client load balancing, which helps to distribute new
connections to keep RAC nodes from being overwhelmed with connection
requests. When enabled, the order in which primary and alternate
database servers are accessed is random.
https://docops.ca.com/ca-single-sign-on/12-7/en/installing/install-a-policy-server/configure-odbc-databases-as-policy-session-key-and-audit-stores/configure-odbc-databases-as-session-store/store-session-information-in-oracle
Inconsistent behavior from PS to DB Loadbalancing
https://comm.support.ca.com/kb/inconsistent-behavior-from-ps-to-db-loadbalancing/kb000119929
In order to avoid trailing connections to down ODBC instances, in
system_odbc.ini, configure the following :
- Set the KeepAlive parameter to 1 in order to
close any connection to a down server;
CA SSO Oracle Server Wire Protocol
KeepAlive=0 NOTE: The attributes in the example (above) reflect the
hardcoded, default values. Adding the attribute and modifying the
value will override the default value with the user-designated value.
https://comm.support.ca.com/kb/ca-sso-oracle-server-wire-protocol/kb000032766
TCP Keep Alive
KeepAlive (KA)
If set to 0 (Disabled), the driver does not enable TCPKeepAlive.
If set to 1 (Enabled), the driver enables TCPKeepAlive.
https://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/tcp-keep-alive.html
Load Balance Timeout
Specifies the number of seconds to keep inactive connections open in a
connection pool. An inactive connection is a database session that is
not associated with an ODBC connection handle, that is, a connection
in the pool that is not in use by an application.
https://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc%2Fload-balance-timeout.html%23
This will avoid the connections to be kept open to ODBC instances
which are down.
More, you can also configure 2 separated data source and remove the
configuration of alternateservers and loadbalancing from the driver
configuration will make all the odbc connection going back to the
Primary Session Store. Note that from documentation, we support
failover only, and the driver loadbalancing configuration stands for
failover too.
Here we've removed the alternateservers and LoadBalancing
configuration and we've defined 2 separate Session Store data sources,
1 for the Primary and another one for the Secondary.
system_odbc.ini :
[Session Primary]
Driver=/opt/CA/siteminder/odbc/lib/NSora27.so
Description=DataDirect 7.1 Oracle Wire Protocol
HostName=duspa01-u161157.ca.com
PortNumber=1521
LoginID=SMUSER
Password=xxxxx
ServiceName=XE
#SID=XE
CatalogOptions=0
ProcedureRetResults=0
EnableDescribeParam=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
DMCleanup=2
[Session Secondary]
Driver=/opt/CA/siteminder/odbc/lib/NSora27.so
Description=DataDirect 7.1 Oracle Wire Protocol
HostName=duspa01-u161156.ca.com
PortNumber=1521
LoginID=SMUSER
Password=xxxxx
ServiceName=XE
#SID=XE
CatalogOptions=0
ProcedureRetResults=0
EnableDescribeParam=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
DMCleanup=2
In the SmConsole, we've configured the Session Store connection
putting each of the datasources, separated with a coma "," :
sm.registry :
HKEY_LOCAL_MACHINE\SOFTWARE\Netegrity\SiteMinder\CurrentVersion\Database\SessionServer=235352101
Data Source=Session Primary, Session Secondary; REG_SZ
Enabled= 0x1; REG_DWORD
MaxConnections= 0x20; REG_DWORD
Password= {RC2}cJhuVJ9LzQf12y9hk6FdGc9ztDo+Y8Em; REG_SZ
ProviderNamespace= ODBC:; REG_SZ
Use Default= 0; REG_DWORD
User Name= SMUSER; REG_SZ
Once done, we've started Policy Server, and we've seen it using the
Primary Session Store. When the Primary Session Store went off line,
Policy Server moved connections to the Secondary Session Store. When
the Primary Session Store came back online, then Policy Server moved
back the connections to the Primary Session Store :
smps.log :
[13473/4151690960][Fri Dec 28 2018
03:08:08][CServer.cpp:4951][INFO][sm-Server-01430] Logging and Trace
output in local time. TimeZone: [GMT-5:00]. Daylight Savings: 0
[13473/3799419760][Fri Dec 28 2018
03:08:12][CSmDbSessionManager.cpp:585][INFO][sm-Server-04350] Using
ODBC 'Session Data' data source 'Session Primary'.
[13473/4151690960][Fri Dec 28 2018
03:08:12][SmSSInDBStore.cpp:299][INFO][sm_LoginLogout_02014]
Initialized Session Server. Schema version is 3, lVersion
[13473/3715414896][Fri Dec 28 2018
03:09:12][CSmDbSessionManager.cpp:180][INFO][sm-Server-04350] Using
ODBC 'Session Data' data source 'Session Primary'.
[13473/4065667952][Fri Dec 28 2018
03:17:38][CSmDbSessionManager.cpp:147][INFO][sm-Server-04330]
Failing over to 'Session Data' data source 'Session
Secondary'.
[13473/3715414896][Fri Dec 28 2018
03:22:12][CSmDbSessionManager.cpp:155][INFO][sm-Server-04340]
Failing back to 'Session Data' data source 'Session
Primary'.
Netstat output during the use case reproduction :
We start the Policy Server :
Fri Dec 28 03:08:13 EST 2018
tcp 0 0 10.162.31.240:11821 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:44748 10.162.31.240:1521 ESTABLISHED
12171/xe_pmon_XE
tcp 0 0 10.162.31.240:31701 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
We put load and Policy Servers build connections to the Primary
Session Store (.240):
Fri Dec 28 03:16:07 EST 2018
tcp 0 0 10.162.31.240:11821 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:59662 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:32757 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:44748 10.162.31.240:1521 ESTABLISHED
12171/xe_pmon_XE
tcp 0 0 10.162.31.240:20816 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:22829 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:31004 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 689 10.162.31.240:31701 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:65377 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
We shut down the Primary Session Server (.240) :
Fri Dec 28 03:17:37 EST 2018
tcp 0 0 10.162.31.240:11821 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 1 0 10.162.31.240:47888 10.162.31.240:1521 CLOSE_WAIT
13473/smpolicysrv
tcp 1 0 10.162.31.240:59662 10.162.31.240:1521 CLOSE_WAIT
13473/smpolicysrv
tcp 0 0 10.162.31.240:44748 10.162.31.240:1521 ESTABLISHED
12171/xe_pmon_XE
tcp 1 0 10.162.31.240:20816 10.162.31.240:1521 CLOSE_WAIT
13473/smpolicysrv
tcp 1 0 10.162.31.240:22829 10.162.31.240:1521 CLOSE_WAIT
13473/smpolicysrv
tcp 1 0 10.162.31.240:31004 10.162.31.240:1521 CLOSE_WAIT
13473/smpolicysrv
tcp 1 0 10.162.31.240:65377 10.162.31.240:1521 CLOSE_WAIT
13473/smpolicysrv
Policy Server fails over connections to the Secondary Session Store
(.106) :
Fri Dec 28 03:18:38 EST 2018
tcp 0 0 10.162.31.240:11821 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:17993 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:55709 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:36960 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:51719 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:22171 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:58619 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:48457 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
We start over the Primary Session Server :
Fri Dec 28 03:21:32 EST 2018
tcp 0 0 10.162.31.240:11821 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:45040 10.162.31.240:1521 ESTABLISHED
18172/xe_pmon_XE
tcp 0 0 10.162.31.240:17993 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:55709 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:36960 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:51719 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:22171 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:58619 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:48457 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
Policy Server notices it and starts to move back all connections to
the Primary Session Store :
Fri Dec 28 03:21:41 EST 2018
tcp 0 0 10.162.31.240:11821 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:45040 10.162.31.240:1521 ESTABLISHED
18172/xe_pmon_XE
tcp 0 0 10.162.31.240:39907 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:17993 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:55709 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:55979 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:36960 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:51719 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:22171 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:27555 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:58619 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:48457 10.162.31.106:1521 ESTABLISHED
13473/smpolicysrv
Until all connections are back to the Primary Session Store :
Fri Dec 28 03:21:48 EST 2018
tcp 0 0 10.162.31.240:45040 10.162.31.240:1521 ESTABLISHED
18172/xe_pmon_XE
tcp 0 0 10.162.31.240:39907 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:14352 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:36902 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 679 10.162.31.240:55979 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:27776 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:51390 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:27555 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
tcp 0 0 10.162.31.240:31303 10.162.31.240:1521 ESTABLISHED
13473/smpolicysrv
As stated earlier in this case, you won't be able to achieve
loadbalancing between the Session Stores. So you will never get
connections equally distributed between nodes.
As our documentation states, Session Stores instances should be set as
failover.
Policy Server to Session Store Communication
Allows for failover. If a primary session store fails, Policy Servers
failover to a secondary session store.