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.

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.

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.

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.

1. Enable the AUTO_DST_UPGRADE feature.

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

2. 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                                   

3. 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';

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.

1. Enable the AUTO_DST_UPGRADE_EXCL_DATA feature.

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

2. 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                                   

3. 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';