Changing Timezone Settings on Oracle Database Servers

Leave a reply

Twice now in the past month, I have run across some clients that are wanting to change the timezone of their database due to a server relocation. Both cases ended up being a bit similar, one a single instance and the other on Exadata. Since I had to lookup how to do it the seconds time, I thought I would blog about it. Here’s the info on how to view the timezone settings and how to change them.

These settings shown were valid for DB 11.2.0.2 and above on Redhat/Oracle Linux 6.

OS Settings

Look at /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 35 May  9 17:54 /etc/localtime -> /usr/share/zoneinfo/America/Chicago

Grid Timezone Setting

Config File in Grid Home

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=America/Chicago

Grid Infrastructure Settings

If TZ is set via in the cluster via srvctl, it will take precedence over the s_crsconfig__env.txt setting.

 
>srvctl getenv listener -l listener
LISTENER:
TZ=America/Chicago

>srvctl getenv database -D DB1
DB1:
TZ=America/Chicago

The TZ setting can be set via the following srvctl command:

srvctl setenv listener -l listener -t 'TZ=America/Chicago'

srvctl setenv database -d DB1 -t 'TZ=America/Chicago'

Database Scheduler

The database scheduler works under the timezone setting at statup time. The timezone can be seen and modified with the following statements.

 
SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-SEP-14 11.57.49.394023000 AM EST5EDT

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Central');

SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-SEP-14 11.59.49.313001000 AM US/CENTRAL

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 )

Leave a comment