Upgrade Database timezone file in oracle 19c upgrade

We may need to upgrade database timezone file during database upgrade from 11g to 19c. Below recommend action is from preupgrade.jar information tool.

Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 14 and the target 19 release ships with time zone file version 32.

Oracle recommends upgrading to the desired (latest) version of the time zone file.  For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19 Oracle Database Globalization Support Guide.

Below are the steps to Upgrade the database timezone file

Connect to sqlplus and start the database in upgrade mode

sqlplus / as sysdba
shutdown immediate;
startup upgrade;

Check the current timezone file version

SELECT * FROM v$timezone_file;

--output:
SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 	     14 	 0

Start the upgrade window

SET SERVEROUTPUT 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_upgrade(l_tz_version);
END;

--output
SQL> SET SERVEROUTPUT 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_upgrade(l_tz_version);
END;
/SQL>   2    3    4    5    6    7    8
l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Start the database in normal mode

shutdown immediate;
startup;

Do the upgrade

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);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;


--output
SQL> 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);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL>   2    3    4    5    6    7    8    9  
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
.
.
<output truncated>
.
.
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL> 

Check the new timezone settings

SELECT * FROM v$timezone_file;

--output
SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 	     32 	 0

Timezone file has been upgraded to version 32.