Manage Time Zone

The Time Zone field in the Console and in the API allows you to launch DB System resources with a time zone other than UTC (the default).

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.

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.

Note

Time zones are largely used for display purposes or to handle user input. Changing time zone does not change the time on the system clock.

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.

View the Current Time Zone

Perform the following steps to view the time zone information.

  1. Log on to the host as the grid user.

    Example:
    [opc@rc ~]$ sudo su - grid
  2. Use the following command to view the time zone.
    timedatectl
    Example:
    [grid@rc ~]$ timedatectl 
            Local time: Thu 2023-03-16 08:00:25 UTC 
        Universal time: Thu 2023-03-16 08:00:25 UTC 
              RTC time: Thu 2023-03-16 08:00:21 
             Time zone: UTC (UTC, +0000) 
           NTP enabled: yes 
      NTP synchronized: no 
       RTC in local TZ: no 
            DST active: n/a

    Notice that the system is currently in the UTC time zone.

  3. Log on to the database.

    Example:
    [grid@rc ~]$ sqlplus / as sysdba 
    
    SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 16 08:00:33 2023 
    Version 21.9.0.0.0 
    
    Copyright (c) 1982, 2022, Oracle.  All rights reserved. 
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 -Production 
    Version 21.9.0.0.0
  4. You can view the current database time using the following command.
    sysdate
    Example:
    SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual; 
    
    TO_CHAR(SYSDATE,'DD-
    --------------------
    16-MAR-2023 08:00:40
  5. Exit.

Change the Time Zone of the DB System

Perform the following steps to set the time zone of the DB System.

  1. Log on to the host as the root user.

    Example:
    [opc@rc ~]$ sudo su -
  2. Identify the correct time zone (TZ) value to be set using the following command. For example, if you want to identify the correct TZ value for the US eastern time zone (EST/EDT), you can use the tzselect command and choose Americas/United States/Eastern.
    tzselect
    Example:
    [root@rc ~]# tzselect 
    Please identify a location so that time zone rules can be set correctly. 
    Please select a continent or ocean. 
     1) Africa 
     2) Americas 
     3) Antarctica 
     4) Arctic Ocean 
     5) Asia 
     6) Atlantic Ocean 
     7) Australia 
     8) Europe 
     9) Indian Ocean 
    10) Pacific Ocean 
    11) none - I want to specify the time zone using the Posix TZ format. 
    #? 2 
    Please select a country. 
     1) Anguilla              19) Dominican Republic    37) Peru 
     2) Antigua & Barbuda     20) Ecuador               38) Puerto Rico 
     3) Argentina             21) El Salvador           39) St Barthelemy 
     4) Aruba                 22) French Guiana         40) St Kitts & Nevis 
     5) Bahamas               23) Greenland             41) St Lucia 
     6) Barbados              24) Grenada               42) St Maarten (Dutch) 
     7) Belize                25) Guadeloupe            43) St Martin (French) 
     8) Bolivia               26) Guatemala             44) St Pierre & Miquelon 
     9) Brazil                27) Guyana                45) St Vincent 
    10) Canada                28) Haiti                 46) Suriname 
    11) Caribbean NL          29) Honduras              47) Trinidad & Tobago 
    12) Cayman Islands        30) Jamaica               48) Turks & Caicos Is 
    13) Chile                 31) Martinique            49) United States 
    14) Colombia              32) Mexico                50) Uruguay 
    15) Costa Rica            33) Montserrat            51) Venezuela 
    16) Cuba                  34) Nicaragua             52) Virgin Islands (UK) 
    17) Curaçao               35) Panama                53) Virgin Islands (US) 
    18) Dominica              36) Paraguay 
    #? 49 
    Please select one of the following time zone regions.
     1) Eastern (most areas)              16) Central - ND (Morton rural) 
     2) Eastern - MI (most areas)         17) Central - ND (Mercer) 
     3) Eastern - KY (Louisville area)    18) Mountain (most areas) 
     4) Eastern - KY (Wayne)              19) Mountain - ID (south); OR (east) 
     5) Eastern - IN (most areas)         20) MST - Arizona (except Navajo) 
     6) Eastern - IN (Da, Du, K, Mn)      21) Pacific 
     7) Eastern - IN (Pulaski)            22) Alaska (most areas) 
     8) Eastern - IN (Crawford)           23) Alaska - Juneau area 
     9) Eastern - IN (Pike)               24) Alaska - Sitka area 
    10) Eastern - IN (Switzerland)        25) Alaska - Annette Island 
    11) Central (most areas)              26) Alaska - Yakutat 
    12) Central - IN (Perry)              27) Alaska (west) 
    13) Central - IN (Starke)             28) Aleutian Islands 
    14) Central - MI (Wisconsin border)   29) Hawaii 
    15) Central - ND (Oliver) 
    #? 1 
    The following information has been given:
            United States 
            Eastern (most areas) 
    Therefore TZ='America/New_York' will be used. 
        Local time is now:    Thu Mar 16 04:01:39 EDT 2023. 
    Universal Time is now:    Thu Mar 16 08:01:39 UTC 2023. 
    Is the above information OK? 
    1) Yes 
    2) No 
    #? 1 
    You can make this change permanent for yourself by appending the line 
            TZ='America/New_York'; export TZ 
    to the file '.profile' in your home directory; then log out and log in again. 
    Here is that TZ value again, this time on standard output so that you 
    can use the /bin/tzselect command in shell scripts: 
            America/New_York
    [root@rc ~]#

    From the tzselect command, we have identified the TZ value as America/New_York for the EDT time zone.

  3. Update the following command to set the new time zone.
    timedatectl set-timezone <new_time_zone>
    Example:
    [root@rc ~]# timedatectl set-timezone America/New_York
  4. Verify if the time zone has been updated using the following command.
    timedatectl
    Example:
    [root@rc ~]# timedatectl 
             Local time: Thu 2023-03-16 04:02:15 EDT 
         Universal time: Thu 2023-03-16 08:02:15 UTC 
               RTC time: Thu 2023-03-16 08:02:10 
              Time zone: America/New_York (EDT, -0400) 
            NTP enabled: yes 
       NTP synchronized: no 
        RTC in local TZ: no 
             DST active: yes 
        Last DST change: DST began at 
                         Sun 2023-03-12 01:59:59 EST 
                         Sun 2023-03-12 03:00:00 EDT 
        Next DST change: DST ends (the clock jumps one hour backwards) at 
                         Sun 2023-11-05 01:59:59 EDT 
                         Sun 2023-11-05 01:00:00 EST

    Notice that the system is now in the America/New_York (EDT) time zone.

  5. Exit.

Change the Time Zone of the Host on DB Systems that Use Grid Infrastructure

The time zone of the Oracle Grid Infrastructure determines the time zone of the database log files.

Perform the following steps to update the time zone information of the host of the DB systems that use grid infrastructure for storage management.

Note

  • Steps 1 through 10 should be performed on all the nodes in a RAC cluster.
  • Steps 11 through 15 can be performed on any node in a RAC cluster.
  • This procedure does not apply to fast provisioned DB systems, which use Logical Volume Manager instead of Grid Infrastructure for storage management.
  1. Log on to the host as the grid user.

    Example:
    [opc@rc ~]$ sudo su - grid
  2. Identify the correct time zone (TZ) value to be set using the following command. For example, if you want to identify the correct TZ value for the US Pacific time zone (PST/PDT), you can use the tzselect command and choose Americas/United States/Pacific.
    tzselect
    Example:
    [grid@rc ~]$ tzselect 
    Please identify a location so that time zone rules can be set correctly. 
    Please select a continent or ocean. 
     1) Africa 
     2) Americas 
     3) Antarctica 
     4) Arctic Ocean 
     5) Asia 
     6) Atlantic Ocean 
     7) Australia 
     8) Europe 
     9) Indian Ocean 
    10) Pacific Ocean 
    11) none - I want to specify the time zone using the Posix TZ format. 
    #? 2 
    Please select a country. 
     1) Anguilla              19) Dominican Republic    37) Peru 
     2) Antigua & Barbuda     20) Ecuador               38) Puerto Rico 
     3) Argentina             21) El Salvador           39) St Barthelemy 
     4) Aruba                 22) French Guiana         40) St Kitts & Nevis 
     5) Bahamas               23) Greenland             41) St Lucia 
     6) Barbados              24) Grenada               42) St Maarten (Dutch) 
     7) Belize                25) Guadeloupe            43) St Martin (French) 
     8) Bolivia               26) Guatemala             44) St Pierre & Miquelon 
     9) Brazil                27) Guyana                45) St Vincent 
    10) Canada                28) Haiti                 46) Suriname 
    11) Caribbean NL          29) Honduras              47) Trinidad & Tobago 
    12) Cayman Islands        30) Jamaica               48) Turks & Caicos Is 
    13) Chile                 31) Martinique            49) United States 
    14) Colombia              32) Mexico                50) Uruguay 
    15) Costa Rica            33) Montserrat            51) Venezuela 
    16) Cuba                  34) Nicaragua             52) Virgin Islands (UK) 
    17) Curaçao               35) Panama                53) Virgin Islands (US) 
    18) Dominica              36) Paraguay 
    #? 49 
    Please select one of the following time zone regions. 
     1) Eastern (most areas)              16) Central - ND (Morton rural) 
     2) Eastern - MI (most areas)         17) Central - ND (Mercer) 
     3) Eastern - KY (Louisville area)    18) Mountain (most areas) 
     4) Eastern - KY (Wayne)              19) Mountain - ID (south); OR (east) 
     5) Eastern - IN (most areas)         20) MST - Arizona (except Navajo) 
     6) Eastern - IN (Da, Du, K, Mn)      21) Pacific 
     7) Eastern - IN (Pulaski)            22) Alaska (most areas) 
     8) Eastern - IN (Crawford)           23) Alaska - Juneau area 
     9) Eastern - IN (Pike)               24) Alaska - Sitka area 
    10) Eastern - IN (Switzerland)        25) Alaska - Annette Island 
    11) Central (most areas)              26) Alaska - Yakutat 
    12) Central - IN (Perry)              27) Alaska (west) 
    13) Central - IN (Starke)             28) Aleutian Islands 
    14) Central - MI (Wisconsin border)   29) Hawaii 
    15) Central - ND (Oliver) 
    #? 21 
    The following information has been given: 
            United States 
            Pacific 
    Therefore TZ='America/Los_Angeles' will be used. 
        Local time is now:    Thu Mar 16 01:08:57 PDT 2023. 
    Universal Time is now:    Thu Mar 16 08:08:57 UTC 2023. 
    Is the above information OK? 
    1) Yes 
    2) No 
    #? 1 
    You can make this change permanent for yourself by appending the line 
            TZ='America/Los_Angeles'; export TZ 
    to the file '.profile' in your home directory; then log out and log in again. 
    Here is that TZ value again, this time on standard output so that you 
    can use the /bin/tzselect command in shell scripts: 
            America/Los_Angeles 
    [grid@rc ~]$

    From the tzselect command, we have identified the TZ value as America/Los_Angeles for the PDT time zone.

  3. Run the following command to get the host name.
    hostname
    Example:
    [grid@rc ~]$ hostname 
    rc
  4. Run the following command to get the database name.
    srvctl config database -v
    Example:
    [grid@rc ~]$ srvctl config database -v 
    SMDB0316_iad1cx    /u01/app/oracle/product/21.0.0.0/dbhome_1    21.0.0.0.0
  5. Log out as a grid user and sign in as a root user.

    Example:
    [grid@rc ~]$ exit 
    logout 
    [opc@rc ~]$ sudo su -
  6. Navigate to the following directory.
    cd $GRID_HOME/crs/install
    Example:
    [root@rc ~]# cd /u01/app/21.0.0.0/grid/crs/install/
  7. Edit the TZ content of the following file. Save and close the file after editing it.
    vim s_crsconfig_<hostname>_env.txt
    Example:
    [root@rc install]# vim s_crsconfig_rc_env.txt
  8. Verify if the TZ entry in the file is updated.
    cat s_crsconfig_<hostname>_env.txt
    Example:
    [root@rc install]# cat s_crsconfig_rc_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=America/Los_Angeles 
    NLS_LANG=AMERICAN_AMERICA.AL32UTF8 
    CRS_LIMIT_STACK=2048 
    CRS_LIMIT_OPENFILE=65536 
    CRS_LIMIT_NPROC=65536 
    TNS_ADMIN= 
    [root@rc install]#
  9. Set the new time zone using the following command.
    timedatectl set-timezone <new_time_zone>
    Example:
    [root@rc install]# timedatectl set-timezone America/Los_Angeles
    Note

    • Steps 1 through 9 should be performed on all the nodes in a RAC cluster.
    • Steps 10 through 15 can be performed on any node in a RAC cluster.
  10. Navigate to the following directory.
    cd $GRID_HOME/bin
    Example:
    [root@rc install]# cd /u01/app/21.0.0.0/grid/bin/
  11. Change the TZ value at the database level to the desired time zone using the following command.
    srvctl setenv database -d <database_name> -t "TZ=<new_time_zone>"
    Example:
    [root@rc bin]# ./srvctl setenv database -d SMDB0316_iad1cx -t "TZ=America/Los_Angeles"
  12. Verify the TZ value set using the following command.
    srvctl getenv database -d <database_name>
    Example:
    [root@rc bin]# ./srvctl getenv database -d SMDB0316_iad1cx 
    SMDB0316_iad1cx: 
    TZ=America/Los_Angeles
  13. Stop the CRS stack on all of the compute nodes.
    crsctl stop cluster
    Example:
    [root@rc bin]# ./crsctl stop cluster 
    CRS-2673: Attempting to stop 'ora.crsd' on 'rc' 
    . 
    . 
    . 
    CRS-2673: Attempting to stop 'ora.cssd' on 'rc' 
    CRS-2677: Stop of 'ora.cssd' on 'rc' succeeded 
    [root@rc bin]#
  14. Start the CRS stack on all of the compute nodes.
    crsctl start cluster
    Example:
    [root@rc bin]# ./crsctl start cluster 
    CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rc' 
    . 
    . 
    . 
    CRS-2672: Attempting to start 'ora.crsd' on 'rc' 
    CRS-2676: Start of 'ora.crsd' on 'rc' succeeded 
    [root@rcbin]#
  15. Verify if the time zone has been updated using the following command.
    timedatectl
    Example:
    [root@rc bin]# timedatectl 
             Local time: Thu 2023-03-16 01:19:15 PDT 
         Universal time: Thu 2023-03-16 08:19:15 UTC 
               RTC time: Thu 2023-03-16 08:19:10 
              Time zone: America/Los_Angeles (PDT, -0700) 
            NTP enabled: yes 
       NTP synchronized: no 
        RTC in local TZ: no 
             DST active: yes 
        Last DST change: DST began at 
                         Sun 2023-03-12 01:59:59 PST 
                         Sun 2023-03-12 03:00:00 PDT 
        Next DST change: DST ends (the clock jumps one hour backwards) at 
                         Sun 2023-11-05 01:59:59 PDT 
                         Sun 2023-11-05 01:00:00 PST

    Notice that the system is now in the America/Los_Angeles (PDT) time zone.