Upgrade fails with ORA-20000: General Failure " convert TZ error

Products

CA Automic Applications Manager (AM)

Issue/Introduction

When upgrading to Applications Manager 9.2.2 or above the following error message can be seen in the $AW_HOME/install/awinstall.log

ERROR at line 1:
ORA-20001: Submit_chains job_seq=34693 ORA-20001: Submit_chains job_seq=34693
[ORA-20000: General Failure " convert TZ error in [aw_timezones.submit_chains 1 [ORA-20000: General Failure " convert TZ error in [aw_timezones.submit_chains 1
master TZ seq=108 sched TZ seq=452] from TZ [US/Central] 12/31/2040 05:00:00 to
TZ [IST]
[ORA-20000: General Failure "To time zone I [ORA-20000: General Failure "To time zone I
ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2069] ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2069]
ORA-06512: at "AUTOMIC1.AWAPI2", line 488 ORA-06512: at "AUTOMIC1.AWAPI2", line 488
ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2084 ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2084
ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2292 ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2292
ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2436 ORA-06512: at "AUTOMIC1.AW_TIMEZONES", line 2436
ORA-06512: at "AUTOMIC1.AWAPI3", line 2828 ORA-06512: at "AUTOMIC1.AWAPI3", line 2828
ORA-06512: at "AUTOMIC1.SCHED_MIGRATION", line 748 ORA-06512: at "AUTOMIC1.SCHED_MIGRATION", line 748
ORA-06512: at "AUTOMIC1.SCHED_MIGRATION", line 917 ORA-06512: at "AUTOMIC1.SCHED_MIGRATION", line 917
ORA-06512: at line 20 ORA-06512: at line 20

Fatal Error occurred - check the log /Automic1/app/automic93/install/aw_install.log

     ==================
   Requested processes "all" not running
   stopso done
Installation exited with errors

Cause

Applications Manager 9.2.2 + has changed from using Java based timezone functions to now using Oracle timezone functions. The timezone definitions between the two methods are not exact, thus when upgrading if there is a miss match between available timezones in Java vs Oracle the upgrade will error. This can be resolved by setting the timezone to a valid Oracle timezone prior to running the upgrade.

Resolution

The following sql can be run against the Applications Manager database to identify where the invalid time zone is set. Commonly this is either in the master's time zone definition or on a specific job schedule's time zone option.

SQL>select 'Master '||a.so_oper_name ||' has invalid timezone '||b.aw_tz_name message
from so_operators a, aw_timezone_list b where a.so_master_flag='Y'
and a.so_time_zone = b.aw_tz_seq
and b.aw_tz_name NOT IN (select tzname from v$timezone_names)
union
select 'Schedule '||a.aw_sch_name||' under job '||c.so_module|| ' has invalid timezone '||b.aw_tz_name message
from aw_module_sched a, aw_timezone_list b , so_job_table c
where
a.aw_job_seq = c.so_job_seq
and a.aw_time_zone is not null
and a.aw_time_zone = b.aw_tz_seq
and b.aw_tz_name NOT IN (select tzname from v$timezone_names

For example when the sql was run during a failed time zone upgrade the following details where returned from the query.

MESSAGE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schedule NIGHTRUN under job AW_APPWORX has invalid time zone IST

This indicates that on the job AW_APPWORX the schedule NIGHTRUN has an invalid time zone of IST.

To resolve this the schedule mentioned above can be edited and set it to a time zone which has a similar GMT offset of IST and is supported by Oracle Real-Time Collaboration.

The timezone can be changed by reverting back the upgrade and accessing the Applications Manager client gui. Once the time zone has been updated the upgrade can be rerun and the time zone error should be resolved.

Additional Information

If access to the gui is not possible during the upgrade please contact Automic Support for additional steps.