000029596 - IMG: Changing Oracle Timezone File to Resolve Stuck Workflows and Change Request Timing Issues

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000029596
Applies ToRSA IMG 6.9
Red Hat Linux
SUSE Linux 
Appliance and Soft Appliance  (jboss)
Local and remote Oracle database
IssueWe have seen this issue manifest itself in the following ways:
1. Change Requests (CR) show a Completion Date recorded at a time that has not yet occurred. For example, you create a CR at 9:00am, complete the CR at 9:30am, look at the CR at 11:00am and it shows as completed at 4pm, a time in the future.
2. Another symptom is stuck Workflows (WF). A WF does not progress beyond the Approval phase even though there are no approvals to perform. If the Approval nodes are removed from the WF, the WF then does not progress beyond the Fulfillment phase. 
Note: There could be other symptoms that have not been observed.
CauseStuck workflows and incorrect change request completion dates can be caused by a mismatch between the timezone in Oracle's oracle grid's timezone file, the system clock on Linux where RSA IMG is installed, and the timezone as stored in the Oracle database.
There are three places where the timezone value is defined. The value for timezone must match in all three places:
1. The system clock on Linux
2. The timezone defined in the Oracle database.  This query is run as 'avuser'.
SQL> select dbtimezone from dual;
3. Oracle's timezone file on Linux:
where $HOSTNAME is the node name where the Oracle database is installed
ResolutionThis issue has been reported to engineering and will be fixed in a future release.
WorkaroundThe workaround is to modify the value of TZ in $ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt from TZ=GMT+<some number> to TZ=<timezone> where 'timezone' is the value of TIMEZONE defined in /etc/sysconfig/clock and the value returned from the query 'select dbtimezone from dual' 

Determine the value of TIMEZONE in the linux system clock
a) Log into the linux server where RSA IMG is installed.
b) cat /etc/sysconfig/clock
Sample output:

Determine the value of the timezone as stored in the Oracle database
a) using a tool such as sqlplus, sql developer or squirrel connect to the Oracle database
b) issue this SQL command:
select dbtimezone from dual;
Sample output:
This should presumably report same output as Step 1. If it does not you can alter it to reflect the same timezone.

Determine the value of TZ in the Oracle grid timezone file on the server where the Oracle database is installed:
a) Login to the linux server where oracle is installed
b) cat  $ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt 
(substitute your node name for $HOSTNAME)
Sample output:

Backup $ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt and modify the value of TZ in $ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt to match the values from Steps 1 and 2.
a) Backup $ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt replacing $HOSTNAME with the node name on which the Oracle database resides:
$ mv  
$ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt  $ORACLE_GRID_HOME/crs/install/s_crsconfig_$HOSTNAME_env.txt_backup
b) Modify the value of TZ
TZ=GMT+<some number>
TZ=<timezone value from 1 and 2>
Example, modify:


c) Restart IMG
$ acm restart

d) Reboot Linux server to have the changes effected.
As root user run below: