Oracle Cloud Infrastructure Documentation

DB System Time Zone

The Time Zone field in the Console and in the API allows you to launch a Base Database Service or Exadata Database Service on Dedicated Infrastructure instance with a time zone other than UTC (the default). Although UTC is the recommended time zone to use, having a common time zone for your database clients and application hosts can simplify management and troubleshooting for the database administrator.

The time zone that you specify when you create the database system applies to the host and to the Oracle Grid Infrastructure (if the system has Grid Infrastructure), and controls the time zone of the database log files. The time zone of the database itself is not affected, however, the database’s time zone affects only the timestamp datatype. You can change the database time zone manually but Oracle recommends that you keep it as UTC (the default) to avoid data conversion and improve performance when data is transferred among databases. This configuration is especially important for distributed databases, replication, and export and import operations.

Time Zone Options

Whether you use the Console or the API, the time zone options you can select from are represented in the named region format, for example, America/Los_Angeles. The Console allows you to select UTC, the time zone detected in your browser (if your browser supports time zone detection), or an alternate time zone.

To specify an alternate time zone (the Select another time zone option), you first select a value in the Region or country field to narrow the list of time zones to select from in the Time zone field. In the America/Los_Angeles example, America is the time region and Los_Angeles is the time zone. The options you see in these two fields roughly correlate with the time zones supported in both the Java.util.TimeZone class and on the Linux operating system. If you do not see the time zone you are looking for, try selecting "Miscellaneous" in the Region and country field.

Tip

If you are using the API and would like to see a list of supported time zones, you can examine the time zone options in the Console. These options appear on the Create DB System page when you show advanced options after you select a DB system shape.

Changing Time Zones After Provisioning

Follow these steps if you need to change the time zone of the system host, Oracle Grid Infrastructure, or database, after you launch the Base Database Service or Exadata Database Service on Dedicated Infrastructure system:

To change the time zone of the host on DB systems that use Grid Infrastructure
  1. Log on to the host system as root.

  2. Stop the CRS stack on all of the compute nodes.

    #Grid_Home/bin/crsctl stop crs

  3. Run the following commands to check the current time zone and to change it to the time zone you choose:

    
$ cat /etc/sysconfig/clock
ZONE="America/New_York"
$ cp -p /etc/sysconfig/clock /etc/sysconfig/clock.20160629

$ vi /etc/sysconfig/clock
ZONE="Europe/Berlin"

$ date
Wed Jun 29 10:35:17 EDT 2016
$ ln -sf /usr/share/zoneinfo/Europe/Berlin /etc/localtime
$ date
Wed Jun 29 16:35:27 CEST 2016

    In this example, the time zone was changed from America/New_York to Europe/Berlin.

    Tip

    To see a list of valid time zones on the host, you can run the ls -l /usr/share/zoneinfo command.
  4. (Optional) On an Exadata DB system, you can verify that /opt/oracle.cellos/cell.conf indicates the correct time zone. Using our example, the time zone entry in this file would be <Timezone>Europe/Berlin</Timezone>.

  5. Restart the CRS stack on all of the compute nodes.

    #Grid_Home/bin/crsctl start crs
To change the time zone of the host on DB systems that use Logical Volume Manager

Use this procedure for Fast Provisioned virtual machine DB systems, which use Logical Volume Manager instead of Grid Infrastructure for storage management.

  1. Log on to the host system as root.

  2. Stop the database and the listener on all of the compute nodes.

    #sqlplus / as sysdba
SQL> shutdown immediate
#lsnrctl stop
  3. Stop all other running processes from the Oracle Database Home.

  4. Run the following commands to check the current time zone and to change it to the time zone you choose:

    
$ cat /etc/sysconfig/clock
ZONE="America/New_York"
$ cp -p /etc/sysconfig/clock /etc/sysconfig/clock.20160629

$ vi /etc/sysconfig/clock
ZONE="Europe/Berlin"

$ date
Wed Jun 29 10:35:17 EDT 2016
$ ln -sf /usr/share/zoneinfo/Europe/Berlin /etc/localtime
$ date
Wed Jun 29 16:35:27 CEST 2016

    In this example, the time zone was changed from America/New_York to Europe/Berlin.

    Tip

    To see a list of valid time zones on the host, you can run the ls -l /usr/share/zoneinfo command.

  5. As Oracle, restart the listener and the database on all of the compute nodes.

    lsnrctl start
sqlplus / as sysdba
startup
To change the time zone of the Oracle Grid Infrastructure

The time zone of the Oracle Grid Infrastructure determines the time zone of the database log files. You can change this time zone by updating the TZ property in the GRID_HOME/crs/install/s_crsconfig_<node_name>_env.txt configuration file.

Note

This procedure does not apply to Fast Provisioned virtual machine DB systems, which use Logical Volume Manager instead of Grid Infrastructure for storage management.
  1. Ensure that you are logged onto the host as root and that the CRS stack is stopped on all of the compute nodes. See To change the time zone of the host on DB systems that use Grid Infrastructure.

  2. Inspect the current time zone value in the GRID_HOME/crs/install/s_crsconfig_<node_name>_env.txt file.

    $ cat /u01/app/19.0.0.0/grid/crs/install/s_crsconfig_node1_env.txt
#########################################################################
#This file can be used to set values for the NLS_LANG and TZ environment
#variables and to set resource limits for Oracle Clusterware and
#Database processes.
#1. The NLS_LANG environment variable determines the language and
#   characterset used for messages. For example, a new value can be
#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
#2. The Time zone setting can be changed by setting the TZ entry to
#   the appropriate time zone name. For example, TZ=America/New_York
#3. Resource limits for stack size, open files and number of processes
#   can be specified by modifying the appropriate entries.
#
#Do not modify this file except as documented above or under the
#direction of Oracle Support Services.
#########################################################################
TZ=UTC
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
CRS_LIMIT_STACK=2048
CRS_LIMIT_OPENFILE=65536
CRS_LIMIT_NPROC=16384
TNS_ADMIN=

    In this example, the time zone is set to UTC.

  3. Modify the time zone value, as applicable. Perform this task for all nodes in the cluster.

  4. Restart the CRS stack on all of the compute nodes.

    #Grid_Home/bin/crsctl start crs

For more information about changing the time zone of the Grid Infrastructure, see How To Change Timezone for Grid Infrastructure (Doc ID 1209444.1).

To change the time zone of a database

Use the ALTER DATABASE SET TIME_ZONE command to change the time zone of a database. This command takes either a named region such as America/Los_Angeles or an absolute offset from UTC.

This example sets the time zone to UTC:

ALTER DATABASE SET TIME_ZONE = '+00:00';

You must restart the database for the change to take effect. For more information, see Setting the Database Time Zone.