Policy Server doesn't connect back to the first Session Store

Document ID : KB000130316
Last Modified Date : 01/04/2019
Show Technical Document Details
Question:
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 ?
Environment:
  Policy Server 12.52SP1CR02 on RedHat 5; 
  JDK 1.7.0_141 32bit; 

  2 Session Store on ODBC Oracle; 
 
Answer:
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.