November 19, 2020

How to Upgrade the database time zone using DBMS_DST Package in 19c

Posted by Shaik Ahmed Ali on November 19, 2020 in , , , | 2 comments

 Hi,  when I was upgrading the database from 12.1.0.2 to 19c after running the pre-upgrade it shows me in the post-upgrade, that you need to upgrade your database timezone so here in this article I am going to show you how to upgrade the time zone.

Let us start the blog

find out the current database time zone value using the below query 

Select * from v$timezone_file;


check the available timezone to which we can upgrade using the below query
Select DBMS_DST.get_latest_timezone_version from dual;


Let us prepare for the upgrade:

Before proceeding with the upgrade in this preparation phase we will check the impact of timezone after the upgrade, including the effect on the table after the upgrade it's optional but better to check. run the below command


Check the Secondary and Primary time zone version using the below query:

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


Truncate the table which holds the affected tables list and errors as below



Let do the upgrade now:

shutdown the database and start the database in upgrade mode and run the following DBMS_DST query as below
SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER; BEGIN
SELECT DBMS_DST.get_latest_timezone_version
INTO l_tz_version FROM dual;
DBMS_DST.begin_upgrade(l_tz_version);
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version); END;
/




restart the database and open in normal mode using 
shutdown immediate;
startup ;

now run the upgrade command as shown in the screenshot
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;
/


now check the new settings or timezone version


yes, we have done it upgrade the timezone to the latest version using the DBMS_DST package.

let's check if this also upgraded the PDB timezone version or not


So as per the above screenshot PDB timezone did not upgrade let's upgrade the timezone of PDB  
in the multitenant database, we can upgrade the timezone using "catcon.pl" Perl script 
 Create the scripts here I have created by name timezone.sql and add the following lines to a file

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

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

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

SHUTDOWN IMMEDIATE;
STARTUP;

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;
/

save the file with the above lines.


using the above script you can upgrade the PDB timezone version with the exclusion of  CDB 

$ORACLE_HOME/perl/bin/perl \
    -I$ORACLE_HOME/perl/lib \
    -I$ORACLE_HOME/rdbms/admin \
    $ORACLE_HOME/rdbms/admin/catcon.pl \
    -l /tmp/ \
    -b upgrade_tzf \
    -C 'CDB$ROOT' \
    /tmp/upgrade_tzf.sql

After executing the above on the PDB database, the timezone will be upgraded let's check now


yes, we have done it, so it's clear that the timezone upgrade method is different in NON-CDB and CDB databases.



Thank you everyone for reading, hope this article helps you to upgrade the timezone, happy learning.
submit your feedback.


















2 comments: