Changing Timezone Settings on Oracle Database Servers

by

Here’s the info on how to check and modify the timezone settings in Linux OS and Oracle DB.
Following settings were valid for Oracle DB 11.2.0.2+ on Redhat/Oracle Linux 7+.

OS Side Setting:
check /etc/localtime to see the timezone setting for the server. The
setting should be set to an entry in the /usr/share/zoneinfo directory/subdirectory.

# ls -lhF /etc/localtime
lrwxrwxrwx 1 root root 45 Jul  8 07:22 /etc/localtime -> /usr/share/zoneinfo/Asia/Tehran

Oracle Grid Timezone Setting:

Grid Home Config File:
The TZ entry in $GRID_HOME/crs/install/s_crsconfig__env.txt the timezone entry for the Grid Home; hence, the ASM and listener.

# grep TZ $GRID_HOME/crs/install/cat s_crsconfig_db1_env.txt 
TZ=Asia/Tehran

Grid Infrastructure Settings:
If TZ is set in via the cluster or srvctl, it will take precedence over the s_crsconfig__env.txt setting.

> srvctl getenv listener -l listener
LISTENER:
TZ=Asia/Tehran
 
> srvctl getenv database -D DBTEST1
DBTEST1:
TZ=Asia/Tehran

The TZ setting can be set by the following command:

> srvctl setenv listener -l listener -t 'TZ=Asia/Tehran
 
> srvctl setenv database -d DBTEST1 -t 'TZ=Asia/Tehran'

Database Scheduler:
The database scheduler works under the timezone setting at startup time. The timezone can be checked and modified with the following commands.

SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-JUL-22 14.33.52.384017000 AM IRST
 
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','Asia/Tehran');
 
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-JUL-22 14.34.49.222015000 AM IRST

Oracle References :
How To Change Timezone for 11gR2 Grid Infrastructure (Doc ID 1209444.1)
Dates & Calendars – Frequently Asked Questions ( Doc ID 227334.1 )
Incorrect SYSDATE shown when connected via Listener in RAC ( Doc ID 1390015.1 )
Timestamps & time zones – Frequently Asked Questions (Doc ID 340512.1)

Leave a Comment