TimeZone File, Updating

Summary

Below are the standard steps for updating a TimeZone file. Beginning with Oracle Database 19.18.0 DBRU (January 2023), all available DST patches are included with the Release Update (RU) and installed into the Oracle_home/oracore/zoneinfo directory.

This example updates 19c database to version 44.

Data Guard

After the PRI is stamped at 44 on the physical standby, the redo changes will flow to the standby. Just ensure the standby Oracle Home is fully patched and redo apply is active.

Assumptions

• You have applied the latest Oracle RU\[OJVM] (see Doc ID 2940808.1).

Upgrade the Database DST

 -- confirm Current Version
 SELECT * FROM v$timezone_file;
   nn

 -- Reports counts of objects, columns, and rows potentially affected by a TZ file upgrade.
 @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

 -- Estimates the total number of TIMESTAMP WITH TIME ZONE values stored in user data.
 @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql

 -- Performs pre-upgrade check to identify issues that may block\impact upgrade.
 @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql

 -- If you have any standby databases pause apply (run on SB).
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 -- Time zone apply script.
 Warning: This script will restart the database and adjust time zone data.
 @?/rdbms/admin/utltz_upg_apply.sql

 Reminder: OJVM already updated via last RU.

 -- QC TZ File (44)
 SELECT * FROM v$timezone_file;
   44

 -- QC DST_PRIMARY_TT_VERSION (44)
 SELECT property_name, property_value
 FROM   database_properties
 WHERE  property_name LIKE 'DST_%'
 ORDER  BY property_name;  

 -- Optional Oracle-sanctioned OJVM TZ check (harmless).
 -- Run on the PRIMARY only.
 @?/javavm/admin/fixTZa.sql

 ########################################################
 Bug is not present.  Patch not needed.
 ########################################################

 If it reports the bug is not present, you’re done. 

 -- If it says the bug is present, bounce and run:
 -- @?/javavm/admin/fixTZb.sql

 -- If you have any standby database resume apply (run on SB).
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Data Guard

• SB QC Checks

Once PRI stamped at 44. If you have a physical SB, the change in redo and will flow to SB; just make sure the SB Oracle Home patches are up-to-date and that apply is running.

With a database at DST 44 and complete. OJVM patching isn’t needed on 19.28; verifying via dba_registry_sqlpatch and/or fixTZa.sql is optional peace of mind.

APPENDIX

SB QC

-- 1) Is managed recovery running?
SELECT process, status, client_process, thread#, sequence#
FROM   v$managed_standby
WHERE  process LIKE 'MRP%';
-- Expect: MRP0  APPLYING_LOG  (client_process often LGWR if real-time)

-- 2) Are we receiving and writing logs from the primary?
SELECT process, status, client_process, thread#, sequence#
FROM   v$managed_standby
WHERE  process = 'RFS';
-- client_process = LGWR implies real-time shipping

-- 3) What’s the transport/apply lag?
SELECT name, value, unit
FROM   v$dataguard_stats
WHERE  name IN ('transport lag','apply lag');

-- 4) What’s the last log applied here?
SELECT MAX(sequence#) AS last_applied_seq,
       TO_CHAR(MAX(next_time),'YYYY-MM-DD HH24:MI:SS') AS last_applied_time
FROM   v$archived_log
WHERE  applied = 'YES';

-- 5) Sanity: role/open mode
SELECT database_role, open_mode, switchover_status
FROM   v$database;

If you want a one-liner to sanity-check SB apply:

SELECT CASE
         WHEN EXISTS (
           SELECT 1 FROM v$managed_standby
           WHERE process LIKE 'MRP%' AND status LIKE 'APPLYING%')
         THEN 'APPLYING'
         ELSE 'NOT APPLYING'
       END AS apply_status
FROM dual;

Returns APPLYING when MRP is running.

MOS Notes

Notes here are dervided from Doc ID 412160.1.

Also:

• Doc ID 412160.1 — Primary Note on DST Patches for RDBMS and OJVM (always updated with the current DST version, patch numbers, and links).
• Doc ID 3071901.1 — Applying the DSTv44 Update for the Oracle Database (step-by-step for both RDBMS and OJVM DSTv44).

Manaul file prep:

  mkdir /u01/orasw/patches/tz
  cd /u01/orasw/patches/tz/
  DL RDBMS and OJVJ tz file to dir.
  unzip each