Error
Error Code: ORA-30072

Oracle Error ORA-30072: Invalid Timezone

📦 Oracle Database
📋

Description

The ORA-30072 error indicates that Oracle Database encountered an invalid time zone value. This typically occurs when setting the time zone via the ALTER SESSION command, the ORA_SDTZ environment variable, or within a datetime function.
💬

Error Message

ORA-30072: invalid time zone value
🔍

Known Causes

4 known causes
⚠️
Invalid Time Zone Name
The specified time zone name is not recognized by the Oracle database. Time zone names must adhere to the Oracle-supported format.
⚠️
Typographical Error
A typo exists in the time zone string. Even a minor misspelling can cause the database to reject the value.
⚠️
Unsupported Time Zone
The time zone is not supported in the current Oracle database version. Some time zones might be deprecated or not included.
⚠️
Incorrect ORA_SDTZ Setting
The ORA_SDTZ environment variable is set to an invalid or non-existent time zone.
🛠️

Solutions

3 solutions available

1. Verify and Correct Timezone Setting in SPFILE easy

Ensures the Oracle database's timezone is correctly configured in the server parameter file.

1
Connect to the Oracle database as a user with DBA privileges (e.g., SYSDBA).
sqlplus sys as sysdba
2
Check the current value of the `TIMEZONE_REGION` parameter. If it's set to a specific region, verify its validity.
SHOW PARAMETER TIMEZONE_REGION;
3
If the `TIMEZONE_REGION` is invalid or needs to be set to a specific region, update it. For example, to set it to 'America/New_York':
ALTER SYSTEM SET TIMEZONE_REGION = 'America/New_York' SCOPE=SPFILE;
4
If you intended to use the operating system's timezone, ensure it's correctly set on the OS level and then restart the database.
ALTER SYSTEM SET TIMEZONE_REGION = '' SCOPE=SPFILE; -- Clears the setting to use OS timezone
5
Restart the Oracle database instance for the changes to take effect.
SHUTDOWN IMMEDIATE;
STARTUP;

2. Update Oracle Timezone Files medium

Applies the latest timezone data to the Oracle installation, resolving issues with outdated or corrupted timezone information.

1
Identify the Oracle home directory (ORACLE_HOME) for your database installation.
echo $ORACLE_HOME
2
Download the latest Oracle Time Zone Data files (tzdataXX.zi) from My Oracle Support (MOS) or the Oracle website. Ensure they are compatible with your Oracle Database version.
N/A (Download from MOS)
3
Stop all Oracle database instances and listeners associated with this ORACLE_HOME.
lsnrctl stop
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
EXIT;
4
Navigate to the `$ORACLE_HOME/oracore/ZONEINFO` directory.
cd $ORACLE_HOME/oracore/ZONEINFO
5
Back up the existing timezone files (optional but recommended).
mv * /path/to/backup/directory/
6
Unzip the downloaded timezone data files into the `$ORACLE_HOME/oracore/ZONEINFO` directory.
unzip /path/to/downloaded/tzdataXX.zi
7
Restart the Oracle database instances and listeners.
lsnrctl start
sqlplus / as sysdba
STARTUP;
EXIT;
8
Verify the timezone settings in the database.
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'timezone_version';

3. Set OS Timezone for Database easy

Configures the operating system's timezone to be used by the Oracle database when `TIMEZONE_REGION` is not explicitly set.

1
Determine the desired timezone for your server. For example, 'America/New_York'.
N/A
2
On Linux/Unix systems, use the `timedatectl` command (or `tzselect` and manual configuration).
sudo timedatectl set-timezone America/New_York
3
On Windows systems, configure the timezone through the Control Panel or PowerShell.
Set-TimeZone -Id "Eastern Standard Time"
4
Connect to the Oracle database as SYSDBA.
sqlplus sys as sysdba
5
Ensure that the `TIMEZONE_REGION` parameter is not explicitly set, or is set to an empty string, to allow the database to inherit the OS timezone.
SHOW PARAMETER TIMEZONE_REGION;
6
If `TIMEZONE_REGION` is set to a specific value, unset it to use the OS timezone.
ALTER SYSTEM SET TIMEZONE_REGION = '' SCOPE=SPFILE;
7
Restart the Oracle database instance.
SHUTDOWN IMMEDIATE;
STARTUP;