Change TZ on Oracle Restart and Oracle RAC

If you need to change the timezone on a server running an Oracle DB be careful to change it properly for Oracle Restart and Oracle RAC, otherwise you will end up getting incorrect values when connecting via Listener.

To change the TZ on OEL 7 to MST TZ:

timedatectl set-timezone America/Phoenix

Date command on OS will return the correct date:

[oracle@dbserver]$ date
Thu Jun 25 18:56:48 MST 2020

To configure TZ for Oracle Restart/RAC:

srvctl setenv database -d rspaws -t 'TZ=America/Phoenix'
srvctl setenv listener -l LISTENER -t 'TZ=America/Phoenix'

Check new values:

srvctl getenv database -d rspaws
srvctl getenv listener

Then restart the services:

srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER

srvctl stop database -d rspaws
srvctl start database -d rspaws

Connect to the db using using the listener:

sqlplus system@rspaws

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

!date

select sysdate from dual;

Date output should match between OS and Oracle.

If you don’t use Oracle Restart/RAC then changing the TZ at OS level is enough to make Oracle aware of the new date.

I’ve tested this procedure on Oracle 12.1, Oracle 12.2 and Oracle 18c with GI 18c.

Enjoy!

Author: reguchi

Oracle ACE, OCP 12c, OCE 12c MAA, OCE Exadata, RHCE, AWS CSAA, OCI CSAA. Technology geek, homebrewer, dad.

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s