My Oracle Support Banner

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

Last updated on OCTOBER 30, 2018

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Purpose

If the only goal is to update the RDBMS DST version to the default RDBMS DST version included in the 12.1.0.x version after an Oracle RDBMS version upgrade to 12.1.0.x
then simply run the scripts found in <note 1585343.1> Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database.
Those scripts do all the steps in this note.

This document can be used only till release 12.2.0.1. For the subsequent releases, the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory. Refer to Database Globalization Support Guide in "Upgrading the Time Zone File and Timestamp with Time Zone Data" Section. 

This note provides an overview of

 

This note does not cover OJVM DST updates for the simple reason that OJVM DST updates do not need any action on stored data. They can be simply applied. See the readme of the OJVM patches for installation instructions.
The RDBMS and OJVM DST versions are NOT technically related so they do not NEED to be the same.

Scope

 

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 1) What is changed in 12cR1 about RDBMS DST updates (when compared to 11gR1 and lower):
 2) When do I need to apply RDBMS DST patches to the 12.1.0.x ORACLE_HOME before using DBMS_DST (= before going to step 3) in this note)?
 3) Checks before doing the actual update of the RDBMS DST version using DBMS_DST in an 12.1.0.x database:
 3a) check current RDBMS DST version and "DST UPGRADE STATUS" in your 12.1.0.x database:
 3b) Check UPFRONT using DBMS_DST if there is affected data that cannot be resolved automatically in your 12.1.0.x database:
 4) Do the actual RDBMS DST version update of the database using DBMS_DST in your 12.1.0.x database:
 5) How long will DBMS_DST take?
 5.a) Things one can check upfront:
 5.b) why does DBMS_DST.FIND_AFFECTED_TABLES sometimes takes longer than the actual upgrade?
 5.c) Selects useful if DBMS_DST "hangs" or takes a long time:
 6) Known Issues
 * if DBMS_DST.FIND_AFFECTED_TABLES fails with ORA-00904: "T"."SYS_C00001_-random number here-": invalid identifier
 * EXEC DBMS_DST.BEGIN_PREPARE (or any other DBMS_DST call) fails with 'PLS-00201: identifier 'DBMS_DST.-insert name here-' must be declared':
 * if DBMS_DST.BEGIN_PREPARE fail with ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
 * if DBMS_DST.BEGIN_PREPARE fails with ORA-56922: Starting a prepare window failed
 7) Can the DST version be updated in 12.1.0.x without downtime? Or in a "rolling" fashion on RAC?
 8) CDB /PDB (Multitenant) database and DST updates.
 9) Upgrading Timezone file on Oracle Releases post 12.2.0.1 (or 18.1 onwards)
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.