# 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 RDS for Oracle DB instance.
**Topics**
+ [
# Overview of Oracle time zone files
](Appendix.Oracle.Options.Timezone-file-autoupgrade.tz-overview.md)
+ [
# Strategies for updating your time zone file
](Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.md)
+ [
# Downtime during the time zone file update
](Appendix.Oracle.Options.Timezone-file-autoupgrade.considerations.md)
+ [
# Preparing to update the time zone file
](Appendix.Oracle.Options.Timezone-file-autoupgrade.preparing.md)
+ [
# Adding the time zone file autoupgrade option
](Appendix.Oracle.Options.Timezone-file-autoupgrade.adding.md)
+ [
# Checking your data after the update of the time zone file
](Appendix.Oracle.Options.Timezone-file-autoupgrade.checking.md)
# 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html#GUID-805AB986-DE12-4FEA-AF56-5AABCD2132DF) 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 Release Updates (RUs) and Release Update Revisions (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 DSTv*version*, 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, existing `TIMESTAMP WITH TIME ZONE` 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 import data from a source database with a higher time zone file version than the target database, the database issues 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 AWS DMS or logical replication to migrate your data.
## Automatic updates using the TIMEZONE\$1FILE\$1AUTOUPGRADE option
When the option group attached to your RDS for Oracle DB instance includes the `TIMEZONE_FILE_AUTOUPGRADE` option, RDS updates your time zone files automatically. By ensuring that your Oracle 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 `TIMEZONE_FILE_AUTOUPGRADE` option to your option group, you can choose whether to add the option immediately or during the maintenance window. After your DB instance applies the new option, RDS checks whether it can install a newer DSTv*version* file. The target DSTv*version* 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, your current time zone file version might be DSTv33. When RDS applies the update to your option group, it might determine that DSTv34 is currently available on your DB instance file system. RDS will then update your time zone file to DSTv34 automatically.
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](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/Welcome.html). For example, [version 19.0.0.0.ru-2022-10.rur-2022-10.r1](https://docs.aws.amazon.com/AmazonRDS/latest/OracleReleaseNotes/oracle-version-19-0.html#oracle-version-RU-RUR.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
Upgrading your DB engine and adding the `TIMEZONE_FILE_AUTOUPGRADE` option to an option group are separate operations. Adding the `TIMEZONE_FILE_AUTOUPGRADE` option initiates the update of your time zone file if a more current one is available. You run the following commands (only relevant options are shown) either immediately or at the next maintenance window:
+ Upgrade your DB engine only using the following RDS CLI command:
```
modify-db-instance --engine-version name ...
```
+ Add the `TIMEZONE_FILE_AUTOUPGRADE` option only using the following CLI command:
```
add-option-to-option-group --option-group-name name --options OptionName=TIMEZONE_FILE_AUTOUPGRADE ...
```
+ Upgrade your DB engine and add a new option group to your instance using the following CLI command:
```
modify-db-instance --engine-version name --option-group-name name ...
```
Your update strategy depends on whether you want to upgrade your database and time zone file together or perform just one of these operations. Keep in mind that if you update your option group and then upgrade your DB engine in separate API operations, it's possible for a time zone file update to be currently in progress when you upgrade your DB engine.
The examples in this section assume the following:
+ You have not yet added `TIMEZONE_FILE_AUTOUPGRADE` to the option group currently associated with 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.
**Topics**
+ [
## Update the time zone file without upgrading the engine
](#Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.no-upgrade)
+ [
## Upgrade the time zone file and DB engine version
](#Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.upgrade)
+ [
## Upgrade your DB engine version without updating the time zone file
](#Appendix.Oracle.Options.Timezone-file-autoupgrade.strategies.upgrade-only)
## 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 an `add-option-to-option-group` command, 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. After applying the `TIMEZONE_FILE_AUTOUPGRADE` option, RDS does the following:
1. Checks for a new DST version.
1. Determines that DSTv34 is available on the file system.
1. Updates the time zone file immediately.
## 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.
To upgrade the engine and time zone file together, run `modify-db-instance` with the `--option-group-name` and `--engine-version` options. You can run the command immediately or defer it to maintenance window. `In --option-group-name`, specify an option group that includes the `TIMEZONE_FILE_AUTOUPGRADE` option. For example:
```
aws rds modify-db-instance
--db-instance-identifier my-instance \
--engine-version new-version \
----option-group-name og-with-timezone-file-autoupgrade \
--apply-immediately
```
RDS begins upgrading your engine to 19.0.0.0.ru-2022-10.rur-2022-10.r1. After applying the `TIMEZONE_FILE_AUTOUPGRADE` option, 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.
To upgrade your engine immediately and then upgrade your a timezone file, perform the operations in sequence:
1. Upgrade your DB engine only using the following CLI command:
```
aws rds modify-db-instance \
--db-instance-identifier my-instance \
--engine-version new-version \
--apply-immediately
```
1. Add the `TIMEZONE_FILE_AUTOUPGRADE` option to the option group attached to your instance using the following CLI command:
```
aws rds add-option-to-option-group \
--option-group-name og-in-use-by-your-instance \
--options OptionName=TIMEZONE_FILE_AUTOUPGRADE \
--apply-immediately
```
## 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`. In your `modify-db-instance` command, don't specify a new option group so that RDS doesn't update your time zone file.
+ Your DB instance is currently associated with an option group that includes `TIMEZONE_FILE_AUTOUPGRADE`. Within a single `modify-db-instance` command, associate your DB instance with an option group that doesn't include `TIMEZONE_FILE_AUTOUPGRADE` and 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html#GUID-B0ACDB2E-4B49-4EB4-B4CC-9260DAE1567A) in the Oracle Database documentation.
**To prepare to update the time zone file**
1. Connect to your Oracle database using a SQL client.
1. Determine the current timezone file version used.
```
SELECT * FROM V$TIMEZONE_FILE;
```
1. Determine the latest timezone file version available on your DB instance.
```
SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION FROM DUAL;
```
1. 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');
```
1. 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;
```
1. 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\$1AFFECTED\$1TABLE Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html#GUID-C53BAABA-914A-404C-9CD5-823257BE0B00) in the Oracle Database documentation.
+ This procedure `CREATE_ERROR_TABLE` creates a table to log errors. For more information, see [CREATE\$1ERROR\$1TABLE Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html#GUID-6A7EA024-B02D-4486-B1D6-EF6ABF5DE507) 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;
```
1. 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 AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Option groups**.
1. 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.
1. For **Major engine version** choose the version of your DB instance.
For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create).
1. Choose the option group that you want to modify, and then choose **Add option**.
1. In the **Add option** window, do the following:
1. Choose **TIMEZONE\$1FILE\$1AUTOUPGRADE**.
1. 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.
1. When the settings are as you want them, choose **Add option**.
## AWS CLI
The following example uses the AWS CLI [add-option-to-option-group](https://docs.aws.amazon.com/cli/latest/reference/rds/add-option-to-option-group.html) 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\$1AFFECTED\$1TABLES Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html#GUID-1F977505-671C-4D5B-8570-86956F136199) in the Oracle documentation.