Upgrade the timezone in Multitenant database in Oracle.

It has two phase: 1. Prepare Window 2. Upgrade Window.

Prepare Window: we verified the effect of upgrading the time zone in the database. Is any table column or row is affected due to upgrading the time zone.

1. Check the current time zone.

SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat              18          0 

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

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         18                     
DST_SECONDARY_TT_VERSION       0                       
DST_UPGRADE_STATE              NONE                                             

2. Check on which version we are going to upgrade according to Oracle binaries.

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION                   
---------------------------                        
                         32  

3. PREPARE WINDOW started with begin procedure.

SQL> exec DBMS_DST.begin_prepare(32);
PL/SQL procedure successfully completed.

SQL> 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         18                                               
DST_SECONDARY_TT_VERSION       32                                               
DST_UPGRADE_STATE              PREPARE            

4. Check any table is affected due to timezone upgradation. If zero rows then everything seems good.

SQL> EXEC DBMS_DST.find_affected_tables;
PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected

SQL> SELECT * FROM sys.dst$error_table;
no rows selected

5. End the PREPARE WINDOW with end procedure.

SQL> EXEC DBMS_DST.end_prepare;

PL/SQL procedure successfully completed.

UPGRADE WINDOW: In this we actually going to upgrade the time zone to latest version according to Oracle binaries. In Multitenant we have many DB instead of one by one we do it by script and using catcon.pl command.

1. Create a folder and a script as below: I am using window server as example: create the folder D:\script and save the following commands as info.sql file.

shutdown immediate;
startup upgrade;

SET SERVEROUTPUT ON
DECLARE
  v_tz_version PLS_INTEGER;
BEGIN
  SELECT DBMS_DST.get_latest_timezone_version
  INTO   v_tz_version
  FROM   dual;

  DBMS_OUTPUT.put_line('v_tz_version=' || v_tz_version);
  DBMS_DST.begin_upgrade(v_tz_version);
END;
/

SHUTDOWN IMMEDIATE;
STARTUP;

SET SERVEROUTPUT ON
DECLARE
  v_failures  PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(v_failures);
  DBMS_OUTPUT.put_line('Upgrade Process Failures=' || v_failures);
  DBMS_DST.end_upgrade(v_failures);
  DBMS_OUTPUT.put_line('End Process Failures=' || v_failures);
END;
/

2. The upper command will update the timezone on which CDB or PDB you run with following catcon.pl command:

--Run on all CDB or PDBS database
%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output info.sql


--If you want to do it one by one
---Run only at CDB$ROOT
%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output -c 'CDB$ROOT' info.sql
---Run only at pdb$seed
%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output -c 'PDB$SEED' info.sql
--Run only at PDB1
%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output -c 'PDB1' info.sql

Note: 
-d : directory for log creation.
-c : on which database you want to execute the upgrade info.sql script.
-b : prefix for logs files.
-C : for exclude any Database from multitenant list.

3. For reference log is attached for file PDB1 database timezone upgrade. I do it one by one because i have only one PDB database, if you have multiple then do it with first command.

4. Verify the time zone.

Select * from v$timezone_file;

Select TZ_VERSION from registry$database;

col property_name for a26
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

Note: Script with both Prepare Window plus Upgrade window. Simple save this script and run with catcon.pl command: