How to use multiple timezones with one Oracle RAC database (Doc ID 1531653.1)

Last updated on SEPTEMBER 10, 2023

Applies to:

Purpose

This document explains a generic approach on how to use multiple timezones with one Oracle RAC database, assuming different dynamic database services can be used to connect to the database.

A typical use case for such a configuration is consolidation in a way that an application or a group of clients from different timezones connect to the same database while those clients should get the impression that the application or clients are in "their timezone" which might be different from the database timezone.

For a Single Instance Oracle Database, this requirement can be addressed by using multiple listeners per database in combination with static registration as per My Oracle Support <note 399448.1>. In principle, the same approach can be used for an Oracle RAC database. However, static registration requires connections using the Oracle SID to connect to a database instance are generally discouraged in Oracle RAC environments.

For Oracle RAC versions before Oracle RAC 11g Release 2, setting the timezone (TZ) variable on a per listener basis as described in My Oracle Support <Note 227334.1>, section "How do I see & set the TZ environment variable in a RAC environment" is another way of implementing this requirement. This workaround will not work for Oracle RAC 11g Release 2 or higher databases anymore.

Note that the use case as described above is different from the use case, in which you have multiple databases hosted on a shared Oracle Grid Infrastructure environment, and in which you want the databases to use different timezones. The major difference between these two use cases is that clients in the latter use case do not share a database. The setup of the "multiple database" use case is described in My Oracle Support <note 1390015.1>. As soon as more than one set of clients access the same database requiring to use their own timezone, follow the setup described in this note.

Scope

After implementing the steps outlined below, an application should be able to connect to the same Oracle RAC Database using two different services (MyAppInNY or MyAppInEUR).

Depending on which service is chosen to connect to the database, the session timezone will be adjusted using a logon trigger to reflect the timezone for which the service was created (here: New York and Europe for example). This will let the connected client assume that it is operating in its own timezone, although the database timezone may differ. If further timezones are required, more services need to be created and the logon trigger needs to be updated accordingly.

The approach assumes that an application or a group of clients can connect to the same database using different dynamic database services, which should only lead to an updated connection string on the application side. No further changes are required on application side. If the application was using the multiple listener approach described for an Oracle Single Instance database on My Oracle Support <note 399448.1>, the different connect strings already used by the application need to be updated.

Note that setting the timezone in the described way will not affect queries on "sysdate" or "systimestamp". The adapted timezone will only affect queries on "CURRENT_DATE", which returns the current date and time in the session timezone as a value of data type DATE. This change will also affect queries on "LOCAL_TIMESTAMP", which returns the current date and time in the session timezone as a value of data type TIMESTAMP. Last but not least, this setup will affect queries on "CURRENT_TIMESTAMP" (as shown below) which returns the current date and time in the session timezone as a value of data type TIMESTAMP WITH TIME ZONE.

Examples in this document use a standard Oracle RAC 11g Release 2 (11.2.0.2) implementation on a two node cluster (node names "rac1" and "rac2") on Oracle Linux 5.8 and require a general understanding of how to connect to an Oracle RAC database (database name "orcl"). The examples should work generically. For simplification purposes, only one oracle software owner for the Oracle Grid Infrastructure (GRID) and the Oracle RAC database home (RAC) is used (OS user "oracle"). The SCAN configuration on the cluster uses a simplified configuration with only one IP / listener as described in the SCAN paper, which is without impact for the configuration described in this document.

Details