By Brian Fitzgerald
Summary
Oracle databases require consistent timezone file versions between the source and target databases during imports. If the source database has a higher timezone file version than the target, the target’s version must be upgraded. In Oracle on-premises environments, this requires direct OS access, which is not available in AWS RDS.
AWS provides the TIMEZONE_FILE_AUTOUPGRADE option to manage this automatically, but it can cause unexpected disruptions due to undocumented behaviors. Understanding these behaviors and managing them carefully is necessary to prevent unexpected DB instance reboots.
Background
Timezone rules change in response to societal preferences and political changes. These changes can include the timezone offset from UTC, daylight saving time (DST) start and end dates, adoption or repeal of DST, timezone names and abbreviations, and more.
Oracle Database uses the timezone file to interpret the timezone stored with timestamps in the timestamp with time zone data type. For example, in the following SQL command:
SELECT TO_TIMESTAMP_TZ('July 19, 1969 9:32 AM EDT',tzd’) liftoff FROM dual;
'Month dd, yyyy hh:mi AM
Oracle determines that Eastern Daylight Time was 4 hours behind UTC in July 1969.
Ittoqqortoormiit, Greenland
When timezone rules change, the timezone file is updated to reflect the change and its effective date. For example, on March 31, 2024, Ittoqqortoormiit, Greenland, changed its timezone rules. Timestamps before this date are interpreted differently than those after it.
Oracle includes timezone file updates with quarterly patch release updates (RUs) if an updated file is available. Some RUs have no timezone file update. However, in Q1 2023, Oracle issued two RUs, each with a new timezone file update.
To upgrade the timezone file on-premises, download and run the MOS script upg_tzv_apply.sql, which starts your database in upgrade mode and runs the dbms_dst package.
The active timezone file version can be found by running:
select version
from v$timezone_file
AWS does not permit customers to directly start an RDS Oracle database in upgrade mode, so it offers the TIMEZONE_FILE_AUTOUPGRADE option. You can enable this option for one or more databases by adding it to the databases’ option group, or by switching a database to an option group with this option. Note that an option group applies to a single account, region, engine, and Virtual Private Cloud (VPC).
AWS states, “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.” This statement hides undocumented behavior that first-time users may find counterintuitive.
Behavior
Once the TIMEZONE_FILE_AUTOUPGRADE option is enabled for a database, two conditions must be understood.
Whether the timezone file will be upgraded
You can determine if a timezone file will be upgraded by comparing the active version from v$timezone_file to the version found in the Amazon RDS for Oracle Release Notes. For instance, if your timezone file version is 42 and your engine version is 19.0.0.0.ru-2024-07.rur-2024-07.r1, RU 2024-07.ru did not change the timezone file version. However, the previous version, 19.0.0.0.ru-2024-04.rur-2024-04.r1, included “DSTV43”.
Conclusion: If TIMEZONE_FILE_AUTOUPGRADE is enabled, your DB engine version is 19.0.0.0.ru-2024-07.rur-2024-07.r1, and your timezone file version is 42, the file will be automatically upgraded. Identifying the highest available version requires reviewing the documentation.
When the timezone file will be upgraded
If the timezone file will be upgraded. the upgrade occurs:
- During the next maintenance window, or
- Immediately, if any immediate change is made to the DB instance.
The immediate upgrade is undocumented and can cause unexpected behavior. The database will reboot and upgrade the timezone file if you make any immediate changes, such as changing:
- Maintenance window
- Backup window
- Deletion protection
- IOPS or storage throughput
- CA Certificate identifier
- Auto minor version upgrade
- Master user password
Example scenario
Suppose you had created an Oracle EE RDS DB instance in 2022 with the following settings:
- DB Engine version:19.0.0.0.ru-2022-01.rur-2022-01.r1
- Timezone file version: 37
- Preferred maintenance window: Saturday 06:00 to 07:00
- Auto minor version upgrade: Enabled
By 2024, the DB Engine version is 19.0.0.0.ru-2024-04.rur-2024-04.r1 and timezone file version 43 is available.
If, say, on June 5, 2024, you add the TIMEZONE_FILE_AUTOUPGRADE option, no “pending” changes appear in the AWS console. However, if on June 17, you change storage throughput, the DB instance will reboot and apply the timezone file version change.
Immediate upgrade behavior only happens if the active timezone file version is less than the available version.
Discussion
Most RDS DB instance attributes can be checked and modified by running describe-db-instances and modify-db-instance. Other modifications can be made using option groups, parameter groups, or maintenance actions. However, controlling the timezone file version differs.
To check for a pending timezone file upgrade, examine v$timezone_file and your DB engine version documentation. There is no direct indication of an upgrade pending.
You cannot issue a command that directly upgrades a timezone file version. The TIMEZONE_FILE_AUTOUPGRADE and database upgrades must be implemented carefully, as many unrelated commands can inadvertently trigger an immediate upgrade and reboot.
Internal Process
If TIMEZONE_FILE_AUTOUPGRADE is enabled, any immediate or scheduled DB instance change prompts the hypervisor to connect via cloud-init and compare active and available timezone file versions. If a newer version is available, cloud-init restarts the database in upgrade mode and runs dbms_dst to upgrade the file version.
Documentation
The apparent spontaneous reboot behavior is not documented.
Workaround
One workaround is to not use the TIMEZONE_FILE_AUTOUPGRADE option. However, this choice limits flexibility across imports. A better option is to keep the option enabled, ensuring timezone files are updated with DB instance upgrades.
If implementing TIMEZONE_FILE_AUTOUPGRADEon an existing DB instance, do so just before the next version upgrade and avoid further modifications until the upgrade is complete.
“Master Arm is On”
One minute before liftoff from the Moon in the Apollo 11 Lunar Module ascent stage, Neil Armstrong activated the Master Arm switch. When the countdown clock reached zero, a complex sequence of events unfolded: pyrotechnics separated the ascent stage electrically and mechanically from the descent stage and opened explosive helium pressurization valves to prepare for ascent stage engine ignition. The purpose of the “arming switch” is, therefore, to enable an intentional, automatic, sequenced operation of circuits, actuators, and firing of pyrotechnics in the ascent stage launch sequence. 
Apollo 11 Lunar Module ascent stage
The TIMEZONE_FILE_AUTOUPGRADE option functions like an arming switch. Once set, the timezone file upgrade is “armed”. The normal actuating trigger is a DB instance engine upgrade. However, any DB instance change will also trigger the timezone file upgrade.
Our DBA team enabled TIMEZONE_FILE_AUTOUPGRADE on a Friday morning, with a minor version upgrade scheduled for the following maintenance window. We adopted the custom of announcing, “Master Arm is On,” indicating team members should refrain from ad-hoc, immediate changes until after the engine and timezone file upgrade.
During the maintenance window, both the DB engine and timezone file versions were upgraded. The TIMEZONE_FILE_AUTOUPGRADE option remained enabled for future updates.
We experienced no issues or surprises in production, development, or QA environments. All abnormal behavior was identified during testing, and implementation was coordinated to avoid adverse effects.
Conclusion
Maintaining synchronization of the timezone file version is crucial for import flexibility. The TIMEZONE_FILE_AUTOUPGRADE option automates upgrades but requires careful handling to avoid unexpected outages.