Policy Server does not automatically reconnect to ODBC Oracle Store after database failover

Products

• SITEMINDER
• CA Single Sign-On

Issue/Introduction

When the Policy Server loses connection to a backend Oracle database (User Store or Policy Store), it does not automatically reconnect or fail over to a standby instance.

In a disaster recovery or switchover scenario, the goal is to ensure the Policy Server automatically transitions to the next available database node (using DNS aliases or alternate server lists) without requiring a manual restart of the Policy Server services. If parameters are not tuned correctly, requests may pile up, leading to a service outage during the database transition.

Environment

Policy Server: 12.8 SP8 CR01 (or higher) on RedHat 7
Policy/Session Store: LDAP (CA Directory)
User Store: ODBC Oracle 19c
Driver: DataDirect Oracle Wire Protocol

Resolution

To enable the Policy Server to transition to a secondary database instance automatically, configure the DataDirect ODBC driver for Load Balancing and Alternate Servers (1). This ensures redundancy at the driver level.

1. Configure Connection Redundancy

   
   Modify your system_odbc.ini file (typically located in the siteminder/db directory) to include the failover parameters.
   
   Recommended Parameters (2)(3)(4):
   
   - LoadBalancing=1: Enables the driver to attempt connections to the primary and alternate servers.
   - AlternateServers: Defines the failover targets.
   - ConnectionRetryCount: Number of times the driver attempts to reconnect.
   - ConnectionRetryDelay: Seconds between each retry attempt.
   
   [!IMPORTANT]
   
   Setting ConnectionRetryCount=0 with a specific delay allows the driver to immediately attempt the next server in the AlternateServers list if the primary is unreachable, preventing long "hang" times where requests pile up in the Policy Server queues.
   
   

2. Sample Configuration (system_odbc.ini)

   
   [SiteMinder_DSN]

Driver=/opt/CA/siteminder/odbc/lib/NSora28.so
Description=DataDirect Oracle Wire Protocol
HostName=db-node1.example.com
PortNumber=1521
ServiceName=SM_SVC
AlternateServers=(HostName=db-node2.example.com:PortNumber=1521:ServiceName=SM_SVC)
LoadBalancing=1
ConnectionRetryCount=0
ConnectionRetryDelay=3
   
   

3. Benchmarking

   
   We strongly recommend benchmarking these values (specifically ConnectionRetryCount and ConnectionRetryDelay) in a lower environment. Every network and database failover (e.g., Oracle RAC or Data Guard) behaves differently; tuning these values is the only way to ensure the desired behavior in Production.

Important Considerations

Deprecation Notice: Please note that the DataDirect driver is slated for deprecation starting from SiteMinder version 12.9 SP1.

Request Accumulation: If the retry count is too high, the Policy Server may hold onto worker threads for too long, leading to a "hanging" state.

Additional Information

1. Configure Redundancy in an Oracle ODBC DSN Data Store for the Siteminder Policy Server
   
   
2. Policy Server Oracle Server Wire Protocol configuration
      
3. ODBC driver connection, pool, pooling, and load balance parameters usage Policy Server
   
   
4. Connectiontimeout and loadbalancetimeout settings for ODBC driver Policy Server settings