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.
Topics
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
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.
Topics
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 includeTIMEZONE_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
To prepare to update the time zone file
-
Connect to your Oracle database using a SQL client.
-
Determine the current timezone file version used.
SELECT * FROM V$TIMEZONE_FILE;
-
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;
-
Determine the total size of tables that have columns of type
TIMESTAMP WITH LOCAL TIME ZONE
orTIMESTAMP 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');
-
Determine the names and sizes of segments that have columns of type
TIMESTAMP WITH LOCAL TIME ZONE
orTIMESTAMP 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;
-
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 theDBMS_DST.FIND_AFFECTED_TABLES
procedure. For more information, see CREATE_AFFECTED_TABLE Procedurein the Oracle Database documentation. -
This procedure
CREATE_ERROR_TABLE
creates a table to log errors. For more information, see CREATE_ERROR_TABLE Procedurein 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 * FROMmy_affected_table
; SELECT * FROMmy_error_table
; -
-
Query the affected and error tables.
SELECT * FROM
my_affected_table
; SELECT * FROMmy_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
Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/
. -
In the navigation pane, choose Option groups.
-
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:
-
For Engine choose the Oracle Database edition for your DB instance.
-
For Major engine version choose the version of your DB instance.
For more information, see Creating an option group.
-
-
Choose the option group that you want to modify, and then choose Add option.
-
In the Add option window, do the following:
-
Choose TIMEZONE_FILE_AUTOUPGRADE.
-
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.
-
-
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