Prepare Oracle Database 19c,26ai for new time zones of British Columbia , Alberta and Manitoba

Patch 27605010: DST UPGRADE SLOW (BEGIN_UPGRADE AND UPGRADE_DATABASE) EVEN WITH PATCH 19704999 (Only for 19c Homes)

Patch 40063528: RDBMS - DSTV47 UPDATE - TZDATA2026D & TZDATA2026E

Patch 40063583: DSTV47 UPDATE - TZDATA2026D & TZDATA2026E - NEED OJVM FIX

#Pro Tip: Backup your oracle home and database prior to the activity
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
$ORACLE_HOME/OPatch/opatch apply

cd 40063583
opatch apply 
* connect as SYS for non-CDB and for CDB connect to root container CDB$ROOT
SQL>startup
SQL>@?/javavm/admin/fixTZa.sql  
...
...
########################################################              
Bug is in fact present, so this patch is needed              
Proceed by restarting the database and running script              
fixTZb              
########################################################

# Only if you see above message follow below steps #

SQL>shut immediate
SQL>startup upgrade
SQL>@?/javavm/admin/fixTZb.sql    
...
...
########################################################              
Bug is no longer present. Patch succeeded.              
######################################################## 
SQL>shut immediate
SQL>startup

-- Check the current timezone version
SQL> SELECT * FROM v$timezone_file;
FILENAME                          VERSION               CON_ID
-------------------- -------------------- --------------------
timezlrg_32.dat                        32                    0

SELECT tz_version FROM registry$database;
          TZ_VERSION
--------------------
                  32

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

-- Check the latest timezone version available from the patch , this must show as 47
SQL>SELECT DBMS_DST.get_latest_timezone_version FROM   dual;  
GET_LATEST_TIMEZONE_VERSION
---------------------------
                         47

-- Start a prepare window
SQL>set timing on;
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;
  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_prepare(l_tz_version);
END;
/  
PL/SQL procedure successfully completed.


-- This must show the secondary timezone version as 47 , primary should be lesser than this
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       47
DST_UPGRADE_STATE              PREPARE


-- Count(*) for both the tables should be '0' if not truncate them both
select count(*) from sys.dst$affected_tables;
select count(*) from  sys.dst$error_table;


-- Find the tables which will be affected by the new timezone date
SQL>EXEC DBMS_DST.find_affected_tables;

-- Check which tables are affected , if there is any data in sys.dst$error_table check with oracle support
SQL>select * from sys.dst$affected_tables;
SQL>select * from  sys.dst$error_table;

--End the prepare window
SQL>EXEC DBMS_DST.end_prepare;

-- After ending the upgrade state should reflect back to none
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;  
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>shut immediate 

SQL>startup upgrade

-- For homes above 21c 
SQL> ALTER SYSTEM SET TIMEZONE_VERSION_UPGRADE_ONLINE = TRUE containers=ALL;

-- This means you donot have to restart database in upgrade mode

SQL> set timing on
SET SERVEROUTPUT ON
DECLARE
l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
SQL>   DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/

...
...
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

-- It should show the new timezone version
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         47
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

-- This should show the upgrade_in_progress column as "No" for all the tables , if any table is still in upgrade state contact oracle support
SQL> COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT con_id,
       owner,
       table_name,
       upgrade_in_progress
FROM   cdb_tstz_tables
ORDER BY 1,2,3; 

SQL> SELECT * FROM v$timezone_file;

-- compile all the packages
SQL> @?/rdbms/admin/utlrp.sql

-- Check if all the registry components are valid
SQL> SET LINES 1000
COLUMN COMPONENT FORMAT A40
SELECT comp_name AS component, version, status
FROM dba_registry;