How do I resolve an "ORA-39405" or "ORA-39002" error in my Amazon RDS for Oracle DB instance?

Short description

You might get an error that's similar to one of the following:

• "ORA-39002: invalid operation"
• "ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 33"

You receive one of the preceding errors when you try to transfer data between a source and target database that have different daylight saving time (DST) versions. In new versions, Oracle releases new DST files and minor updates for rule changes in various time zones.

Resolution

Important: Before you change the production RDS instance, it's a best practice to test time zone-related changes in a nonproduction environment. Also, restore the snapshot of your DB instance before you change the production environment.

Upgrade the DST time zone version of the target database to a version that's equal to or later than the source database version.

Check the current time zone for your DB instance

To check the current time zone version for your DB instance, run the following query:

SQL> SELECT * FROM V$TIMEZONE_FILE;

To check the latest available time zone version, run the following query:

SQL> SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION FROM DUAL;

Add the TIMEZONE_FILE_AUTOUPGRADE option

To automatically upgrade your DB instance's time zone file to the latest version, add the TIMEZONE_FILE_AUTOUPGRADE option to a new or existing instance option group.

Note: When you add the option to an existing option group that's attached to an instance, you experience downtime during the time zone file update.

After you apply the TIMEZONE_FILE_AUTOUPGRADE option, Amazon RDS runs the following query to check for a new DST version and immediately starts the update:

SQL> SELECT * FROM V$TIMEZONE_FILE;

Check your data after you update the time zone

After you update the time zone file on your DB instance, it's a best practice to check the data. Amazon RDS for Oracle automatically creates the following tables:

• The rdsadmin.rds_dst_affected_tables lists the tables that contain data affected by the update.
• The rdsadmin.rds_dst_error_table lists the errors that are generated during the update.

To see the results of the update, run the following commands to query the tables:

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 on the Oracle website.

Related information

Amazon RDS for Oracle Database 19c (19.0.0.0)

Option groups overview

How do I change the time zone of my Amazon RDS for Oracle instance?