Recently I assisted in a project that is utilizing Oracle Cloud Infrastructure (OCI) to provide application servers and Oracle databases. The customer is located in New Zealand, so they obviously wanted to work with dates and times in their local time zone. But when it came to the Database Scheduler, we found, that is was using UTC rather than the local time zone. This makes totally sense in the first place, but it’s not what we wanted to have.
Here is, what it looked like initially.
[oracle@ecotes-dbt ~]$ date Thu Nov 14 07:05:29 UTC 2019 SQL> select sysdate from dual; SYSDATE ---------------- 14.11.2019 07:06
So at first we checked and changed the time zone for the underlying server. This is done by pointing /etc/localtime to another file.
[opc@ecotes-dbt ~]# sudo su - [root@ecotes-dbt ~]# ll /etc/localtime lrwxrwxrwx 1 root root 23 Oct 7 06:51 /etc/localtime -> /usr/share/zoneinfo/UTC [root@ecotes-dbt ~]# ll /usr/share/zoneinfo/NZ -rw-r--r-- 4 root root 2434 Jan 29 2018 /usr/share/zoneinfo/NZ [root@ecotes-dbt ~]# ll /usr/share/zoneinfo/Pacific/Auckland -rw-r--r-- 4 root root 2434 Jan 29 2018 /usr/share/zoneinfo/Pacific/Auckland [root@ecotes-dbt ~]# rm -f /etc/localtime [root@ecotes-dbt ~]# ln -s /usr/share/zoneinfo/Pacific/Auckland /etc/localtime [root@ecotes-dbt ~]# ll /etc/localtime lrwxrwxrwx 1 root root 36 Nov 14 20:10 /etc/localtime -> /usr/share/zoneinfo/Pacific/Auckland [oracle@ecotes-dbt ~]$ date Thu Nov 14 20:11:31 NZDT 2019
Now the database is picking up the new settings from the OS.
SQL> select sysdate from dual; SYSDATE ---------------- 14.11.2019 20:11
Unfortunately, the database scheduler has it’s own settings. So we needed to change that too.
SQL> SELECT * 2 FROM dba_scheduler_global_attribute 3 WHERE attribute_name = 'DEFAULT_TIMEZONE'; ATTRIBUTE_NAME VALUE ---------------------- ----------- DEFAULT_TIMEZONE Etc/UTC SQL> select dbms_scheduler.stime from dual; STIME --------------------------------------------------------------------------- 14-NOV-19 08.26.59.556872000 AM ETC/UTC
But we can simply change this default with a single call to DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE.
SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone', 'Pacific/Auckland');
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM dba_scheduler_global_attribute
3 WHERE attribute_name = 'DEFAULT_TIMEZONE';
ATTRIBUTE_NAME VALUE
---------------------- -----------
DEFAULT_TIMEZONE Pacific/Auckland
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
14-NOV-19 09.31.26.365519000 PM PACIFIC/AUCKLAND
So that’s it. Now we have our DBaaS running with the time zone specific to New Zealand.
Update 2020-01-06
In the first version of my post, I did not mention the relevance of the server side time zone settings. Because a database session inherits its NLS settings from the listener that forks the process for a session. You can find a complete checklist in MOS Note 1627439.1.
Basically, you set the timezone in /etc/sysconfig/clock. The possible values can be found in /usr/share/zoneinfo.
$ cat /etc/sysconfig/clock ZONE="Pacific/Auckland" UTC=true ARC=false
Before doing so, you should check if the times for UTC and your desired time zone are accurate.
$ export TZ=UTC $ date Fri Dec 20 11:53:48 UTC 2019 $ export TZ=Pacific/Auckland $ date Sat Dec 21 00:53:52 NZDT 2019
The settings for the Clusterware may also need a change. The neccessary steps can be found in part D) of the note mentioned above. Ideally you set the time zone only in $GRID_HOME/crs/install/s_crsconfig__env.txt:
$ grep ^TZ $GRID_HOME/crs/install/s_crsconfig_$(hostname -s)_env.txt TZ=Pacific/Auckland
All other ressource specific settings shall be removed using “srvctl unsetenv [database | asm | listener] -envs TZ”, that will make those ressources use the settings defined for the clusterware.
If all those changes have been made, the Clusterware stack should be bounced to bring all the changes into effect.e geänderten Einstellungen zu aktivieren.
Marco's DBA Blog 