Upgrade the TIMEZONE version on the 19.5 target DB from 32 (the default) to 33


Download Patch 28852325: RDBMS – DSTV33 UPDATE – TZDATA2018G:
https://updates.oracle.com/Orion/Services/download/p28852325_190000_Linux-x86-64.zip?aru=23061696&patch_file=p28852325_190000_Linux-x86-64.zip

Extract TIMEZONE V33 files under ORACLE_HOME by applying the patch:
[Can be applied while the DB is up and running, it will place the new TIMEZONE files under ORACLE_HOME]
# unzip p28852325_190000_Linux-x86-64.zip
# cd 28852325
# opatch apply
Do you want to proceed? [y|n]
y

Check the current timezone version:

SQL> select tz_version from registry$database;

TZ_VERSION
———-
32

Check the availability of the new (installed) timezone version on ORACLE_HOME:

SQL> select DBMS_DST.get_latest_timezone_version from dual;

GET_LATEST_TIMEZONE_VERSION
—————————
33

Before upgrade, perform the pre-upgrade stage steps which will let you know which tables will be impacted by this new TIMEZONE upgrade:

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

SQL> col property_name for a30
col property_value for 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      33
DST_UPGRADE_STATE                        PREPARE

View the impacted tables due to this TIMEZONE upgrade: [Which

SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;

SQL> EXEC DBMS_DST.find_affected_tables;

SQL> select * from sys.dst$affected_tables;
SQL> select * from sys.dst$error_table;

End the pre-upgrade stage:
SQL> EXEC DBMS_DST.end_prepare;

Now, the actual TIMEZONE upgrade: [Downtime required]

— Restart the DB in UPGRADE mode:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE

— Upgrade the TIMEZONE:
SQL> 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;
/

— Restart the DB in Normal mode:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

— Upgrade ZONE files: [Ideally will impact all tables having “TIMESTAMP WITH TIME ZONE” data type]
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;
/

Once it’s done, the TIMEZONE upgrade will be complete.

Post checks: Check the current TIMEZONE version:

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
——————–          ———-        ———-
timezlrg_33.dat            33                 0



Reference: 

https://www.br8dba.com/upgrade-timezone-version/

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more