Maybe you have realized that when you deploy a compute instance in your favourite cloud environment that the “hardware” clock is set to UTC - which is the coordinated universal time. And that of course totally makes sense, since your cloud provider is operating a global business. That also isn’t a problem if you handle your times and dates in your Oracle database application properly with a timezone component. For this you can save time related data in the TIMESTAMP WITH TIME ZONE datatype. So you can easily at any time present the value in the desired timezone value.

But for some reason nowadays there are still developers that just use the timestamp or date without timezone information and expect the system to have their local time correctly set. There’s of course nothing wrong with that at the first place, but in this case you have to be careful with your setup and presenting the time in a users local timezone that sit’s on the other side of the globe might get a bit tricky.

Things are a bit different on the system side also when you’re in the cloud. There’s always some sort of hardware clock in a computer which is also shared among the virtual machines. It is a good adivce to leave these clocks, sometimes referred as RTC, at UTC. This way any VM running on the same machine can have it’s own local timezone independently. But when you deploy a system and forget to select the right timezone when configuring you might end up with the wrong timezone for your database when using SYSTIMESTAMP which is defined to output the current date and time with the timezone of the operating system.

When deploying an Oracle Base Database Service it is easy to miss the timezone configuration. So you should be aware of the hidden setting where you can select the timezone for your operating system. It is also crucial to select it at this configuration time to avoid unnecessary trouble. For a self manaaged installation please make sure that you configure the timezone of your operating system before you install the Oracle software, especially when using the Oracle Grid Infrastructure.

If you already have missed that or installed a VM in some other cloud or virtual environment without selecting a timezone other than UTC, you may easily set the timezone of your operating system with a little command as follows. You can use the same command without any parameters to identify current settings.

% sudo timedatectl set-timezone Europe/Berlin

To get the timezone of your database or your session you can use the following SQL statements.

SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;

When you issue the statement after you fixed the timezone of your system you may notice that your database still tells you that its dbtimezone still is UTC. You can change that with the following SQL statement.

ALTER DATABASE SET TIME_ZONE='Europe/Berlin';

But even after fixing the timezone of your controlfile you may notice differences of the time when using the systimestamp function. You can find out a misconfigured system by issuing the the following statement from your system using shared memory access (aka sql / as sysdba) and connected via SQL*Net and the listener of the system. You may notice a time difference that is the same as the difference between UTC and your desired timezone.

SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') FROM DUAL;

This difference may occur mostly on systems that have the Oracle Grid Infrastructure installed and where the database is started by it (which I always recommend). For some reason a GI installation saves the timezone during installation time and always uses this configuration when it is started. In turn this setting gets passed to any child processes including the database. There are two methods to fix that afterwards. My preferred method is to fix the installation as the grid user in the following way. Make sure to check this on all hosts when you’re using RAC on multiple nodes.

% cfg=$ORACLE_HOME/crs/install/s_crsconfig_$(hostname -s)_env.txt
% cp ${cfg} ${cfg}.bak
% sed -i 's/^TZ.*/TZ=Europe\/Berlin/' ${cfg}
% reboot

The other variant is to change the environment settings just for the database using srvctl without touching the configuration of the whole GI installation.

% srvctl setenv database -db $ORACLE_UNQNAME -env TZ="Europe/Berlin"
% srvctl stop database -db $ORACLE_UNQNAME
% srvctl start database -db $ORACLE_UNQNAME

To sum it up: set your timezone correct at the first hand as you later need a restart of your system or database. Of course there’s some extensive support note about that from oracle (1627439.1) but I thought it might help to sum this up a little.