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 0Start 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 0Timezone file has been upgraded to version 32.
Visit Today : 351
Total Visit : 376446
Total Hits : 781425
Who's Online : 1