Users Intermittently Running Into 'Your session has expired' when using RAC database

APPLIES TO: Oracle Applications Technology Stack - Version 11.5.10.0 to 12.1.3 [Release 11.5 to 12.1] Information in this document applies to any platform. SYMPTOMS Users working with Oracle Applications are intermittently redirected to Login page which shows the following error: Error Your session has expired. This error is raised while actively performing actions and happens before period set for profile ICX: Session Timeout. CHANGES Problem may start after implementation or addition of RAC node(s) for the Applications database. CAUSE Summary: The error is raised when there is a difference in OS (date) time between hosts running the RAC nodes and the difference is larger than profile 'ICX: Session timeout'. Explanation: All user actions update the session record in table ICX_SESSIONS with the new 'status'. Th column LAST_CONNECT indicates the moment of last activity. For a new activity the LAST_CONNECT is used to check if the session was idle longer than 'ICX: Session timeout'. If so the user must log in again to re-validate the session. If there is a significant difference in OS time between RAC nodes used this will introduce a problem best explained in a simple example. The following scenario illustrates why the 'Your session has been expired.' is raised right after user provided the credentials to log in on a Release 12 environment. Assumptions: Profile ICX: Session Timeout = 30 minutes Time RAC Node 1 = 02:02:48 Time RAC Node 2 = 02:46:43 Remark: Note that the time difference between the RAC hosts is 44 minutes. This is longer than the Session timeout. Sequence of events 1. The user requests the Login page. This starts the session and uses the MainLoginPG application module. The MainLoginPG uses a JDBC connection from the pool which connects to the database via RAC Node 1 A new record is created in ICX_SESSIONS table The LAST_CONNECT column is based on the SYSDATE  giving 02:02:48 (derived from OS time of RAC Node 1) 2. After 30 seconds the user submits the username/password. If validation succeeds the ICX_SESSION record is updated USER_ID changes from 6 (GUEST) to ID from the user logging in LAST_CONNECT is updated with the time stamp of the login action 3. After the validation the session continues to render the HOMEPAGE. This uses the HomePG application module   The JDBC connection is borrowed by instance of an application module With the change of application module from MainLoginPG to HomePG also the JDBC connection changes This JDBC connection connects to the database via RAC Node 2 being 44 minutes ahead of RAC Node 1 4. As part of the page rendering processing a validation is done to check is the last activity in the session was longer ago than the session time-out. If so the user has to loging again to re-establish the session. For the validation it's expected that LAST_CONNECT 02:02:48 is validated against a SYSDATE around 02:03:18 (OS time on RAC Node 1) However the JDBC connection now connects via RAC Node 2 it's validated against the SYSDATE 02:47:13 (OS time on RAC Node 2) The difference between 02:02:48 and 02:47:13 = 44 minutes Since 44 minutes > 30 minutes timeout the validation fails and returns the error 'Your session has expired' !!! The above is a very simply cases. In reality it happens that a number of actions keep using JDBC connection via RAC Node1 or start via RAC Node 2 and work as expected. However as soon as JDBC connection changes from RAC Node 1 to RAC Node 2 this triggers the error. This explains the intermittent nature where in some cases the error is raised immediately, while in other cases it takes some navigation steps. The error is prevented when all the RAC nodes used have the OS time synchronized. In this case the LAST_CONNECT is validated against the 'same' SYSDATE independent of RAC Node connected to.  SOLUTION Synchronize the time on all the RAC Nodes. Follow the following sequence of actions: Stop all Applications Middle Tier Services Stop all RAC Clusterware Services Synchronize the time between all RAC nodes (for example using NTP (*)) Restart the RAC Clusterware Services Restart the Applications Middle Tier Services Test to confirm the error is not raised anymore (*) Synchronizing the time between nodes is an OS administrative task. Please check with the OS Administrator for assistance.