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.

Log on to the host as the grid user.

Example:
```
[opc@rc ~]$ sudo su - grid
```

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.

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

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

Exit.

Change the Time Zone of the DB System

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

Log on to the host as the root user.

Example:
```
[opc@rc ~]$ sudo su -
```

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.

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

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.

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.

Log on to the host as the grid user.

Example:
```
[opc@rc ~]$ sudo su - grid
```

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.

Run the following command to get the host name.
```
hostname
```
Example:
```
[grid@rc ~]$ hostname
rc
```

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

Log out as a grid user and sign in as a root user.

Example:
```
[grid@rc ~]$ exit
logout
[opc@rc ~]$ sudo su -
```

Navigate to the following directory.

cd $GRID_HOME/crs/install

Example:

[root@rc ~]# cd /u01/app/21.0.0.0/grid/crs/install/

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
```

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]#

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.

Navigate to the following directory.

cd $GRID_HOME/bin

Example:

[root@rc install]# cd /u01/app/21.0.0.0/grid/bin/

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"

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

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]#

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]#

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.