Applying the RDBMS DSTv36 patch 32327201 on the server side in Oracle RDBMS 12c and above
After a DST patch is installed on 12c $ORACLE_HOME there are steps who need to be done to change an existing database to use this newer DST version. Simply applying the RDBMS DST patch and restarting the database will NOT enable the new applied RDBMS DST version patch (like it did in pre-12cR1 versions).
For 18c and higher versions:
- Unzip the RDBMS DSTv36 Patch 32327201
- Apply the RDBMS DSTv36 Patch 32327201 using Opatch.
Note: in 11.2 and up there is NO Need to shut down or stop the database or other processes seen you are simply adding new files, not replacing used ones.
Start Upgrade
Unzip the patch p32327201_190000_Linux-x86-64.zip and Check the Prerequisite:
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Apply the Patch
Check the Timezone File Version
col PROPERTY_NAME format a40
col VALUE format a10;
SELECT version FROM v$timezone_file;
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;
Note: Version FROM v$timezone_file should match the DST_PRIMARY_TT_VERSION value found.
exec dbms_scheduler.purge_log;
— check the number of rows in the tables
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
— check the data retention period of the stats — the default value is 31
select systimestamp – dbms_stats.get_stats_history_availability from dual;
— disable stats retention
exec dbms_stats.alter_stats_history_retention(0);
— remove all the stats
exec DBMS_STATS.PURGE_STATS(systimestamp);
— check the result of the purge operation
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
spool utltz_upg_check.log @/oracle/product/19c/dbhome_1/rdbms/admin/utltz_upg_check.sql spool off
spool utltz_upg_apply.log
@utltz_upg_apply.sql
spool off
Set the data retention period back to its original value using the following command once the time zone data upgrade is completed:
exec dbms_stats.alter_stats_history_retention(31); col PROPERTY_NAME format a40; col VALUE format a10; SELECT version FROM v$timezone_file; SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
References:
- Applying the DSTv36 update for the Oracle Database (Doc ID 2767770.1)
For Your Further Reading:
