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.
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 asAmerica/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.
- 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 asAmerica/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.