How can I troubleshoot issues with the timezone option in my Amazon RDS for Oracle DB instance?

Short description

You can use the timezone option to change the system time zone that's used by your RDS for Oracle DB instance. For example, you might change the time zone of a DB instance to make it compatible with an on-premises environment or a legacy application.

The timezone option changes the time zone at the host level. If you change the time zone of your instance, then the option affects all date columns and values. This includes SYSDATE and SYSTIMESTAMP.

Resolution

Considerations and limitations

Before you use the timezone option, review the following to understand the limitations of the option:

• When you create an RDS for Oracle DB instance, the database automatically sets the database time zone. The database time zone is also known as the daylight savings (DST) time zone. The database time zone is distinct from the system time zone.
• Between Oracle Database releases, patch sets or individual patches often include new DST versions. These patches reflect the changes in transition rules for various time zone Regions. Changes to DST rules might affect existing data of the TIMESTAMP WITH TIME ZONE data type.
• If you upgrade an RDS for Oracle DB instance, then Amazon RDS doesn't upgrade the database time zone file automatically. To upgrade the time zone file automatically, include the TIMEZONE_FILE_AUTOUPGRADE option in the instance's option group during or after the engine version upgrade.

Recover your DB instance to a previous time zone setting

Note: The timezone option is a permanent and persistent option. This means that you can't remove the option from an option group after you add the option. Similarly, you can't remove the option group from your instance after you add it. You also can't modify the time zone setting of the option to a different time zone. For more information, see Persistent and permanent options.

If you set the time zone incorrectly, then you must recover your DB instance to its previous time zone setting.

If your DB instance uses the default option group, then follow these steps:

1. Take a snapshot of your DB instance. For more information, see Creating a DB snapshot for a Single-AZ DB instance.
2. Add the timezone option to your DB instance.

If your DB instance currently uses a non-default option group, then follow these steps:

1. Take a snapshot of your DB instance.
2. Create a new option group with the timezone option .
3. Add the option group to your DB instance.

Note: When you add the timezone option , problems with tables that use system date to add dates or time can occur. It's a best practice to test the timezone option on a test DB instance before you add it to a production DB instance. Analyze your data and applications to assess the impact of changing the time zone.

Add the timezone option to your DB instance

The Timezone option changes the time zone at the host level and affects all date columns and values, such as SYSDATE. Use these steps to add the timezone option to a DB instance:

1. Create a new option group, or copy or modify an existing option group.
2. Add the timezone option to the option group.
3. Associate the option group with the DB instance.

Note: When you add the timezone option, your DB instance is automatically restarted and a brief outage occurs.

Example:

To verify a column's value before and after the TIMEZONE option modification, run a command similar to the following:

SYS.ORCL> select sysdate,systimestamp from dual;

Use the DB (database) Timezone Option

Use the alter_db_time_zone procedure to change the time zone for certain data types. This doesn't change SYSDATE. After you run alter_db_time_zone, reboot your DB instance for the change to take effect.

The following example changes the time zone to UTC plus 10 hours:

EXEC rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => '+10:00');

The following example changes the time zone to the Australia/Sydney time zone:

EXEC rdsadmin.rdsadmin_util.alter_db_time_zone(p_new_tz => 'Australia/Sydney');

The following example verifies the DB TIMEZONE setting after the instance reboot completes.

select (select name from v$database) dbname,dbtimezone from dual;

Troubleshoot issues related to timezone and timezone upgrade file changes

API call error after you modify the time zone setting to a different time zone

The timezone option that you requested in the new option group is different from the timezone option of the existing option group. After you set the time zone of a DB instance, you can't change to a different time zone.

To resolve this issue, create a new instance with the desired timezone. Then use the native Oracle utility export/import to migrate the data from the existing instance to the new one.

Issues occur after you transfer data between databases that use different versions of the time zone file

After you try to transfer data between databases that use different time zone file versions, you get an error similar to the following:

"ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 39."

To resolve this, connect to your Oracle database with a SQL client and check the current timezone file version that's used. Run a command similar to the following:

SELECT * FROM V$TIMEZONE_FILE;

Determine the latest timezone file version available on your DB instance. This step is applicable only if you use Oracle Database 12c Release 2 (12.2) or higher. Run a command similar to the following:

SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION FROM DUAL;

You can upgrade your DB engine and update your time zone file independently. Your upgrade strategy varies based on whether you upgrade your database and time zone files at the same time or one at a time.

• You have not yet added TIMEZONE_FILE_AUTOUPGRADEto the option group used by your DB instance.
• Your DB instance version 19.0.0.0.ru-2022-10.rur-2022-10.r1 uses database time zone file DSTv39.
• Release update 19.0.0.0.ru-2023-01.rur-2023-01.r2 includes DSTv41.

To update your time zone file, use one of the following strategies.

• Update the time zone file without upgrading the engine.
• Upgrade the time zone file and DB engine version.
• Upgrade your DB engine version without updating the time zone file.

Related information

Setting the database time zone

Adding the time zone file autoupgrade option