Change time zone in Oracle’s DBaaS

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.