Manage Time Zone File Version on Autonomous Database

Autonomous Database lets you choose to automatically update time zone files when you restart, or stop and then start an Autonomous Database instance.

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. Because the time zone file upgrade process can have an impact on performance, automatically upgrading an instance to use the latest time zone files is an option that you can enable or disable.

By default, automatic time zone file updates are disabled. Run DBMS_CLOUD_ADMIN.ENABLE_FEATURE to specify that an 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 timezone 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:

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

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.

If you do not want to automatically upgrade to the latest time zone file, disable AUTO_DST_UPGRADE.

1. Disable the AUTO_DST_UPGRADE feature:

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

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