Changing Timezone Settings on Oracle Database Servers

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 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 

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

>srvctl getenv database -D DB1

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;

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

SQL> select dbms_scheduler.stime from dual;


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 Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s