Oracle time zone file autoupgrade - Amazon Relational Database Service
OverviewDST update strategiesDowntime during the updatePreparing to updateAdding the optionChecking your data
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle time zone file autoupgrade

With the TIMEZONE_FILE_AUTOUPGRADE option, you can upgrade the current time zone file to the latest version on your DB instance.

Overview of Oracle time zone files

An Oracle Database time zone file stores the following information:

  • Offset from Coordinated Universal Time (UTC)

  • Transition times for Daylight Saving Time (DST)

  • Abbreviations for standard time and DST

Oracle Database supplies multiple versions of time zone files. When you create an Oracle database in an on-premises environment, you choose the time zone file version. For more information , see Choosing a Time Zone File in the Oracle Database Globalization Support Guide.

If the rules change for DST, Oracle publishes new time zone files. Oracle releases these new time zone files independently of the schedule for quarterly RUs and RURs. The time zone files reside on the database host in the directory $ORACLE_HOME/oracore/zoneinfo/. The time zone file names use the format DSTvversion, as in DSTv35.

How the time zone file affects data transfer

In Oracle Database, the TIMESTAMP WITH TIME ZONE data type stores time stamp and time zone data. Data with the TIMESTAMP WITH TIME ZONE data type uses the rules in the associated time zone file version. Thus, the existing data is affected when you update the time zone file.

Problems can occur when you transfer data between databases that use different versions of the time zone file. For example, if you try to import data from a source database with a higher time zone file version than the target database, you receive the ORA-39405 error. Previously, you had to work around this error by using either of the following techniques:

  • Create an RDS for Oracle DB instance with the desired time zone file, export data from your source database, and then import it into the new database.

  • Use Amazon DMS or logical replication to migrate your data.

Automatic updates using the TIMEZONE_FILE_AUTOUPGRADE option

When you add the TIMEZONE_FILE_AUTOUPGRADE option in RDS for Oracle, RDS for Oracle updates your time zone files automatically. By ensuring that your databases use the same time zone file version, you avoid time-consuming manual techniques when you move data between different environments. The TIMEZONE_FILE_AUTOUPGRADE option is supported for both container databases (CDBs) and non-CDBs.

When you add the TIMESTAMP WITH TIME ZONE option to your option group, you can choose whether to add the option immediately or during the maintenance window. After your DB instance uses the new option, RDS checks whether it can install a newer DSTvversion file. The target DSTvversion depends on the following:

  • The minor engine version that your DB instance is currently running

  • The minor engine version to which you want to upgrade your DB instance

For example, if your current time zone file version is DSTv33, RDS might determine that DSTv34 is currently available on your DB instance file system. In this case, when you add the TIMESTAMP WITH TIME ZONE option, RDS immediately updates your time zone file to DSTv34.

To find the available DST versions in the supported RDS release updates, look at the patches in Release notes for Amazon Relational Database Service (Amazon RDS) for Oracle. For example, version 19.0.0.0.ru-2022-10.rur-2022-10.r1 lists patch 34533061: RDBMS - DSTV39 UPDATE - TZDATA2022C.

Strategies for updating your time zone file

You can upgrade your DB engine and update your time zone file independently. Thus, you must choose among different update strategies, depending on whether you want to upgrade your database and time zone file at the same time.

The examples in this section assume the following:

  • You have not yet added TIMEZONE_FILE_AUTOUPGRADE to the option group used by your DB instance.

  • Your DB instance uses database version 19.0.0.0.ru-2019-07.rur-2019-07.r1 and time zone file DSTv33.

  • Your DB instance file system includes file DSTv34.

  • Release update 19.0.0.0.ru-2022-10.rur-2022-10.r1 includes DSTv35.

To update your time zone file, you can use the following strategies.

Update the time zone file without upgrading the engine

In this scenario, your database is using DSTv33, but DSTv34 is available on your DB instance file system. You want to update the time zone file used by your DB instance from DSTv33 to DSTv34, but you don't want to upgrade your engine to a new minor version, which includes DSTv35. In your modify DB instance operation, do the following:

  • Add TIMEZONE_FILE_AUTOUPGRADE to the option group used by your DB instance. Specify whether to add the option immediately or defer it to the maintenance window.

  • Don’t change your engine version.

After the TIMEZONE_FILE_AUTOUPGRADE option is applied, RDS checks for a new DST version, sees that DSTv34 is available on the file system, and immediately starts the update.

Upgrade the time zone file and DB engine version

In this scenario, your database is using DSTv33, but DSTv34 is available on your DB instance file system. You want to upgrade your DB engine to minor version 19.0.0.0.ru-2022-10.rur-2022-10.r1, which includes DSTv35, and update your time zone file to DSTv35 during the engine upgrade. Thus, your goal is to skip DSTv34 and update your time zone files directly to DSTv35. In your modify DB instance operation, do the following:

  • Add TIMEZONE_FILE_AUTOUPGRADE to the option group used by your DB instance. Specify whether to add the option immediately or defer it to the maintenance window.

  • Change your minor engine version.

After the TIMEZONE_FILE_AUTOUPGRADE option is applied, RDS checks for a new DST version, sees that DSTv35 is available in 19.0.0.0.ru-2022-10.rur-2022-10.r1, and immediately starts the update to DSTv35. At the same time, RDS upgrades your DB engine to version 19.0.0.0.ru-2022-10.rur-2022-10.r1.

Upgrade your DB engine version without updating the time zone file

In this scenario, your database is using DSTv33, but DSTv34 is available on your DB instance file system. You want to upgrade your DB engine to version 19.0.0.0.ru-2022-10.rur-2022-10.r1, which includes DSTv35, but retain time zone file DSTv33. You might choose this strategy for the following reasons:

  • Your data doesn't use the TIMESTAMP WITH TIME ZONE data type.

  • Your data uses the TIMESTAMP WITH TIME ZONE data type, but your data is not affected by the time zone changes.

  • You want to postpone updating the time zone file because you can't tolerate the extra downtime.

Your strategy depends on which of the following possibilities are true:

  • Your DB instance isn't associated with an option group that includes TIMEZONE_FILE_AUTOUPGRADE. Leave your option group as it is so that RDS doesn't update your time zone file.

  • Your DB instance is associated with an option group that includes TIMEZONE_FILE_AUTOUPGRADE. Associate your DB instance with an option group that doesn't include TIMEZONE_FILE_AUTOUPGRADE, and then upgrade your DB engine to 19.0.0.0.ru-2022-10.rur-2022-10.r1.

Downtime during the time zone file update

When RDS updates your time zone file, existing data that uses TIMESTAMP WITH TIME ZONE might change. In this case, your primary consideration is downtime.

Warning

If you add the TIMEZONE_FILE_AUTOUPGRADE option, your engine upgrade might have prolonged downtime. Updating time zone data for a large database might take hours or even days.

The length of the time zone file update depends on factors such as the following:

  • The amount of TIMESTAMP WITH TIME ZONE data in your database

  • The DB instance configuration

  • The DB instance class

  • The storage configuration

  • The database configuration

  • The database parameter settings

Additional downtime can occur when you do the following:

  • Add the option to the option group when the DB instance uses an outdated time zone file

  • Upgrade the Oracle database engine when the new engine version contains a new version of the time zone file

Note

During the time zone file update, RDS for Oracle calls PURGE DBA_RECYCLEBIN.

Preparing to update the time zone file

A time zone file upgrade has two separate phases: prepare and upgrade. While not required, we strongly recommend that you perform the prepare step. In this step, you find out which data will be affected by running the PL/SQL procedure DBMS_DST.FIND_AFFECTED_TABLES. For more information about the prepare window, see Upgrading the Time Zone File and Timestamp with Time Zone Data in the Oracle Database documentation.

To prepare to update the time zone file

  1. Connect to your Oracle database using a SQL client.

  2. Determine the current timezone file version used.

    SELECT * FROM V$TIMEZONE_FILE;

  3. Determine the latest timezone file version available on your DB instance. This step is only applicable if you use Oracle Database 12c Release 2 (12.2) or higher.

    SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION FROM DUAL;

  4. Determine the total size of tables that have columns of type TIMESTAMP WITH LOCAL TIME ZONE or TIMESTAMP WITH TIME ZONE.

    SELECT SUM(BYTES)/1024/1024/1024 "Total_size_w_TSTZ_columns_GB"
FROM   DBA_SEGMENTS
WHERE  SEGMENT_TYPE LIKE 'TABLE%'
AND    (OWNER, SEGMENT_NAME) IN
         (SELECT OWNER, TABLE_NAME
          FROM   DBA_TAB_COLUMNS
          WHERE  DATA_TYPE LIKE 'TIMESTAMP%TIME ZONE');

  5. Determine the names and sizes of segments that have columns of type TIMESTAMP WITH LOCAL TIME ZONE or TIMESTAMP WITH TIME ZONE.

    SELECT OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 "SEGMENT_SIZE_W_TSTZ_COLUMNS_GB"
FROM   DBA_SEGMENTS
WHERE  SEGMENT_TYPE LIKE 'TABLE%'
AND    (OWNER, SEGMENT_NAME) IN
         (SELECT OWNER, TABLE_NAME
          FROM   DBA_TAB_COLUMNS
          WHERE  DATA_TYPE LIKE 'TIMESTAMP%TIME ZONE')
GROUP BY OWNER, SEGMENT_NAME;

  6. Run the prepare step.

    • The procedure DBMS_DST.CREATE_AFFECTED_TABLE creates a table to store any affected data. You pass the name of this table to the DBMS_DST.FIND_AFFECTED_TABLES procedure. For more information, see CREATE_AFFECTED_TABLE Procedure in the Oracle Database documentation.

    • This procedure CREATE_ERROR_TABLE creates a table to log errors. For more information, see CREATE_ERROR_TABLE Procedure in the Oracle Database documentation.

    The following example creates the affected data and error tables, and finds all affected tables.

    EXEC DBMS_DST.CREATE_ERROR_TABLE('my_error_table')
EXEC DBMS_DST.CREATE_AFFECTED_TABLE('my_affected_table')

EXEC DBMS_DST.BEGIN_PREPARE(new_version);
EXEC DBMS_DST.FIND_AFFECTED_TABLES('my_affected_table', TRUE, 'my_error_table');
EXEC DBMS_DST.END_PREPARE;

SELECT * FROM my_affected_table;
SELECT * FROM my_error_table;

  7. Query the affected and error tables.

    SELECT * FROM my_affected_table;
SELECT * FROM my_error_table;

Adding the time zone file autoupgrade option

When you add the option to an option group, the option group is in one of the following states:

  • An existing option group is currently attached to at least one DB instance. When you add the option, all DB instances that use this option group automatically restart. This causes a brief outage.

  • An existing option group is not attached to any DB instance. You plan to add the option and then associate the existing option group with existing DB instances or with a new DB instance.

  • You create a new option group and add the option. You plan to associate the new option group with existing DB instances or with a new DB instance.

Console

To add the time zone file autoupgrade option to a DB instance

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Option groups.

  3. Determine the option group you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings:

    1. For Engine choose the Oracle Database edition for your DB instance.

    2. For Major engine version choose the version of your DB instance.

    For more information, see Creating an option group.

  4. Choose the option group that you want to modify, and then choose Add option.

  5. In the Add option window, do the following:

    1. Choose TIMEZONE_FILE_AUTOUPGRADE.

    2. To enable the option on all associated DB instances as soon as you add it, for Apply Immediately, choose Yes. If you choose No (the default), the option is enabled for each associated DB instance during its next maintenance window.

  6. When the settings are as you want them, choose Add option.

Amazon CLI

The following example uses the Amazon CLI add-option-to-option-group command to add the TIMEZONE_FILE_AUTOUPGRADE option to an option group called myoptiongroup.

For Linux, macOS, or Unix:

aws rds add-option-to-option-group \
    --option-group-name "myoptiongroup" \
    --options "OptionName=TIMEZONE_FILE_AUTOUPGRADE" \
    --apply-immediately

For Windows:

aws rds add-option-to-option-group ^
    --option-group-name "myoptiongroup" ^
    --options "OptionName=TIMEZONE_FILE_AUTOUPGRADE" ^
    --apply-immediately

Checking your data after the update of the time zone file

We recommend that you check your data after you update the time zone file. During the prepare step, RDS for Oracle automatically creates the following tables:

  • rdsadmin.rds_dst_affected_tables – Lists the tables that contain data affected by the update

  • rdsadmin.rds_dst_error_table – Lists the errors generated during the update

These tables are independent of any tables that you create in the prepare window. To see the results of the update, query the tables as follows.

SELECT * FROM rdsadmin.rds_dst_affected_tables;
SELECT * FROM rdsadmin.rds_dst_error_table;

For more information about the schema for the affected data and error tables, see FIND_AFFECTED_TABLES Procedure in the Oracle documentation.