Manage Time Zone File Updates on Autonomous Database

Autonomous Database provides several options to automatically update time zone files on an Autonomous Database database instance.

About Time Zone File Update Options
For time zone support Oracle Database uses time zone files that store the list of all time zones. The time zone files for Autonomous Database are periodically updated to reflect the latest time zone specific changes.
Use AUTO_DST_UPGRADE Time Zone File Option
Autonomous Database provides the AUTO_DST_UPGRADE option to automatically update time zone files on an Autonomous Database database instance.
Use AUTO_DST_UPGRADE_EXCL_DATA Time Zone File Option
Autonomous Database provides the AUTO_DST_UPGRADE_EXCL_DATA option to automatically update time zone files on an Autonomous Database database instance.

Parent topic: Manage the Service

About Time Zone File Update Options

For time zone support Oracle Database uses time zone files that store the list of all time zones. The time zone files for Autonomous Database are periodically updated to reflect the latest time zone specific changes.

Autonomous Database provides the following options for updating time zone files:

AUTO_DST_UPGRADE: Automatically upgrades the time zone files and automatically updates the data on your database to use the latest time zone data. This option requires that you restart or stop and then start your Autonomous Database instance.
AUTO_DST_UPGRADE_EXCL_DATA: Automatically upgrades the time zone files and does not automatically update the data on your database to use the latest time zone data. The time zone files are upgraded to the latest version when you enable this option and versions are kept up to date with the latest version. This option does not require that you restart your Autonomous Database instance.

Note

By default, both AUTO_DST_UPGRADE and AUTO_DST_UPGRADE_EXCL_DATA are disabled. You can enable one or the other of these options, but not both.

With every Daylight Saving Time (DST) version release, there are DST file changes introduced to make existing data comply with the latest DST rules. Applying a change to the database time zone files not only affects the way new data is handled, but potentially alters data stored in TIMESTAMP WITH TIME ZONE columns.

When a load or import operation results in the following time zone related error, this indicates that the version of your time zone files is out of date and you need to update to the latest version available for your database:

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version y 
into a target database with TSTZ version n.

You can enable the AUTO_DST_UPGRADE feature that automatically upgrades an instance to use the latest available version of the time zone files, and automatically updates the rows on your database to use the latest time zone data. The latest version of time zone files are applied and values in TIMESTAMP WITH TIME ZONE columns are updated at the next database restart. However, depending on your database usage, the required database restart might restrict you from using AUTO_DST_UPGRADE to upgrade to the latest time zone files.

You can enable AUTO_DST_UPGRADE_EXCL_DATA to update your time zone files. This option allows you to immediately update your database if you encounter import/export errors due to a version mismatch of time zone files (where you receive the ORA-3940 error).

Enabling AUTO_DST_UPGRADE_EXCL_DATA on your database can be beneficial in the following cases:

Data on the instance is in UTC and the database is not impacted by DST time zone file updates.
The instance does not have any data in TIMESTAMP WITH TIME ZONE columns.
Data in the instance uses dates that are not altered with new time zone or daylight saving time policies.

In this case, when your Autonomous Database instance does not contain rows that are adversely impacted by the new time zone rules and you encounter the ORA-3940 error, you can enable the AUTO_DST_UPGRADE_EXCL_DATA option to update to the latest version of the time zone files. The AUTO_DST_UPGRADE_EXCL_DATA option prioritizes the success of Oracle Data Pump jobs over the data consistency issues due to changing DST.

See Oracle Support Document 406410.1 to help you determine whether time zone changes will affect your database.

Note

Oracle recommends enabling the AUTO_DST_UPGRADE option when these limiting cases do not apply to your database.

In summary, the choice of enabling automatic time zone upgrades with AUTO_DST_UPGRADE or AUTO_DST_UPGRADE_EXCL_DATA involves the following considerations:

Possible Data Inconsistency: Oracle recommends that you maintain your database on the latest time zone file version by enabling AUTO_DST_UPGRADE. This option automatically updates the rows on your database to use the latest time zone data.

The alternative option, AUTO_DST_UPGRADE_EXCL_DATA allows you to maintain your database using the latest time zone version without requiring a database restart; however, data that might be affected by the updated time zone files is not updated, which could lead to possible data inconsistency.
Database Restart: Oracle recommends that you maintain your database on the latest time zone file version by enabling AUTO_DST_UPGRADE. This option requires a restart to upgrade to the latest time zone files version and during the restart updates the rows for existing data of TIMESTAMP WITH TIME ZONE data type to use the latest version. Enabling AUTO_DST_UPGRADE can affect database restart time. A restart can require extra time compared to a restart without this option enabled when there are new time zone files and there is data that needs to be updated when the database restarts.

When you enable AUTO_DST_UPGRADE_EXCL_DATA, at the time you enable this option the database is upgraded to the latest version the time zone files and during each maintenance window, if newer time zone files are available, Autonomous Database updates database to use the latest version. Enabling AUTO_DST_UPGRADE_EXCL_DATA does not require that you restart your Autonomous Database instance to keep up to date with the latest version of the time zone files.

See Datetime Data Types and Time Zone Support for more information.

Parent topic: Manage Time Zone File Updates on Autonomous Database

Use AUTO_DST_UPGRADE Time Zone File Option

Autonomous Database provides the AUTO_DST_UPGRADE option to automatically update time zone files on an Autonomous Database database instance.

The AUTO_DST_UPGRADE feature automatically upgrades the time zone files and automatically upgrades the rows on your database to use the latest time zone data.

Note

By default, both AUTO_DST_UPGRADE and AUTO_DST_UPGRADE_EXCL_DATA are disabled. You can enable one or the other of the automatic time zone file update options, but not both.

When you enable AUTO_DST_UPGRADE your Autonomous Database instance automatically applies updates for time zone files, depending on the state of the instance:

Stopped: At the next start operation the update is automatically applied.
Available: After a restart, or a stop and then start, the update is automatically applied.

When a load or import operation results in the following time zone related error, this indicates that your time zone files are out of date and you need to update to the latest version available for your database:

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version n+1 
into a target database with TSTZ version n.

To enable automatic time zone file updates with AUTO_DST_UPGRADE:

Enable the AUTO_DST_UPGRADE feature.

BEGIN 
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'AUTO_DST_UPGRADE');
END;
/

Query dba_cloud_config to verify that AUTO_DST_UPGRADE is enabled.

SELECT param_name, param_value FROM dba_cloud_config WHERE
       LOWER(param_name) = 'auto_dst_upgrade';

PARAM_NAME              PARAM_VALUE                              
----------------------- ---------------------------------------- 
auto_dst_upgrade        enable

Query dba_cloud_config to check the time zone version.

SELECT param_name, param_value FROM dba_cloud_config 
    WHERE LOWER(param_name) = 'latest_timezone_version';

PARAM_NAME              PARAM_VALUE 
----------------------- ----------- 
latest_timezone_version 38

You can query the DB_NOTIFICATIONS view to see if the time zone version is the latest version:

SELECT type, time, description, expected_start_date FROM db_notifications 
   WHERE TYPE='TIMEZONE VERSION';

When AUTO DST UPGRADE is enabled, Autonomous Database upgrades to the latest version of the time zone files (when you next restart, or stop and then start the database). The columns in your database with the datatype TIMESTAMP WITH TIME ZONE are converted to the new time zone version during the restart.

To disable AUTO_DST_UPGRADE:

Disable the AUTO_DST_UPGRADE feature:

BEGIN 
   DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
        feature_name => 'AUTO_DST_UPGRADE');
END;
/

Query dba_cloud_config to verify that AUTO_DST_UPGRADE is disabled.

SELECT param_name, param_value FROM dba_cloud_config WHERE
       LOWER(param_name) = 'auto_dst_upgrade';

0 rows selected.

See ENABLE_FEATURE Procedure and DISABLE_FEATURE Procedure for more information.

See TIMESTAMP WITH TIME ZONE Data Type for more information.

Parent topic: Manage Time Zone File Updates on Autonomous Database

Use AUTO_DST_UPGRADE_EXCL_DATA Time Zone File Option

Autonomous Database provides the AUTO_DST_UPGRADE_EXCL_DATA option to automatically update time zone files on an Autonomous Database database instance.

The AUTO_DST_UPGRADE_EXCL_DATA automatically upgrades the time zone files and does not automatically upgrade the rows on your database to use the latest time zone data. When this option is enabled the database upgrades to the latest version of the time zone files and subsequently upgrades the database to use new versions of the time zone files during the Autonomous Database maintenance window (whenever a new version is available). This option does not require that you restart your Autonomous Database instance.

Note

By default, both AUTO_DST_UPGRADE and AUTO_DST_UPGRADE_EXCL_DATA are disabled. You can enable one or the other of the automatic time zone file update options, but not both.

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version n+1 
into a target database with TSTZ version n.

To enable automatic time zone file updates with AUTO_DST_UPGRADE_EXCL_DATA:

Enable the AUTO_DST_UPGRADE_EXCL_DATA feature.

BEGIN 
   DBMS_CLOUD_ADMIN.ENABLE_FEATURE(
        feature_name => 'AUTO_DST_UPGRADE_EXCL_DATA');
END;
/

Query dba_cloud_config to verify that AUTO_DST_UPGRADE_EXCL_DATA is enabled.

SELECT param_name, param_value FROM dba_cloud_config WHERE
       LOWER(param_name) = 'auto_dst_upgrade_excl_data';

PARAM_NAME                   PARAM_VALUE                              
---------------------------- ---------------------------------------- 
auto_dst_upgrade_excl_data    enabled

Query dba_cloud_config to check the time zone version.

SELECT param_name, param_value FROM dba_cloud_config 
    WHERE LOWER(param_name) = 'latest_timezone_version';

PARAM_NAME              PARAM_VALUE 
----------------------- ----------- 
latest_timezone_version 38

You can query the DB_NOTIFICATIONS view to see if the time zone version is the latest version:

SELECT type, time, description, expected_start_date FROM db_notifications 
   WHERE TYPE='TIMEZONE VERSION';

When AUTO_DST_UPGRADE_EXCL_DATA is enabled, Autonomous Database upgrades to the latest version of the time zone files and checks and updates to new time zone file versions during each scheduled maintenance window. With AUTO_DST_UPGRADE_EXCL_DATA enabled, the columns in your database with the datatype TIMESTAMP WITH TIME ZONE are not converted to the new time zone version.

To disable AUTO_DST_UPGRADE_EXCL_DATA:

Disable the AUTO_DST_UPGRADE_EXCL_DATA feature:

BEGIN 
   DBMS_CLOUD_ADMIN.DISABLE_FEATURE(
        feature_name => 'AUTO_DST_UPGRADE_EXCL_DATA');
END;
/

Query dba_cloud_config to verify that AUTO_DST_UPGRADE_EXCL_DATA is disabled.

SELECT param_name, param_value FROM dba_cloud_config WHERE
       LOWER(param_name) = 'auto_dst_upgrade_excl_data';

0 rows selected.

See ENABLE_FEATURE Procedure and DISABLE_FEATURE Procedure for more information.

See TIMESTAMP WITH TIME ZONE Data Type for more information.

Parent topic: Manage Time Zone File Updates on Autonomous Database

Oracle Cloud Infrastructure Documentation