Scheduled Reports and Daylight Savings Time

[cspadola]

Good day, we have our OTM servers located in the Eastern Time Zone and the end users are in the EU - normally 6 hours time difference between the two locations.

We recently set up a number of scheduled reports to be sent out at various different times each week day. As the reports are set up on the OTM servers in the US and running on Eastern Time, we set up the reports to run at their scheduled intervals and using the times in Europe minus 6 hours. For example, a report that we need for 10:00CET we set up in OTM as 04:00EST.

This worked fine until this month when North America moved to DST at the beginning of March - March 7/8 - and the EU did not move to DST until March 29. During this period, the reports were received one hour earlier than expected. This does not work for us as there is data in the 1 hour that needs to be on the report. Seeing how North America will be using different days than the EU to move to and from DST each year, this can become a serious issue for the 3-4 weeks that the time difference is 5 hours instead of 6 hours.

Besides altering the schedule for each report (we have about 60 reports) twice a year, is there any way in OTM to get around this?

[chrisplough]

Re: Scheduled Reports and Daylight Savings Time

None that I'm aware of, but this sounds like a great Enhancement Request for Oracle!

--Chris

[acuartero]

Re: Scheduled Reports and Daylight Savings Time

Chris,

We resolved the issue by changing the report server to use the local time zone for the region (in our case Europe/Luxembourg). This has the advantage of allowing the users to schedule reports in their local time without having to do the conversion to the time zone where the physical server is located and without having to be concerned for the different dates that the US and Europe observe daylight savings time.

This change is done by both changing the time zone for the OS user account that the reports services runs under and changing the reports server time zone config. You do not need to change the time zone globally on the server. But if you need to do maintenance and login as the report services user you will need to be aware of the time difference when looking at file time stamps, etc.

First shutdown the Oracle reports server using the following commands.

opmnctl stopall
emctl stop iasconsole

In our case the OS user for the reports services is oracle. To change the user time zone you need to add the following line to the users .profile file.

export TZ=Europe/Luxembourg

Then you need to log out and back in so your .profile will execute or manually run the export command above to set the environment TZ variable so it will be in effect for the steps that follow.

Next the report server configuration needs to be updated to reflect the current user's time zone. The easiest way to do this is with the following command.

emctl resetTZ agent

All this command does is to modify the agentTZRegion property in the $ORACLE_HOME/sysman/config/emd.properties file. In our case it was changed to read

agentTZRegion=Europe/Luxembourg

However you will notice additional instructions in the emctl resetTZ agent command output telling you to run "exec mgmt_target.set_agent_tzrgn" you can safely ignore this.

-bash-3.00$ emctl resetTZ agent

Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Updating /opt/app/oas10g/product/10.1.2/sysman/config/emd.properties...
Successfully updated /opt/app/oas10g/product/10.1.2/sysman/config/emd.properties.
Login as the em repository user and run the script:
exec mgmt_target.set_agent_tzrgn('your_report_host.comp any.com:1157','Europe/Luxembourg')
This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('your_report_host.comp any.com:1157','Europe/Luxembourg')

Next bring the reports server back up and log into OTM to see the change. When you view scheduled report jobs the time shown on the screen should now be in the correct local time for your user's region.

Thanks,
Alan