TimeZone File, Updating

Summary

What follows are the standard steps to update a TimeZone file. Starting with Oracle Database 19.18.0 DBRU (Jan 2023), all available DST patches are installed with the RU and deployed into the Oracle_home/oracore/zoneinfo directory.

Assumptions

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

Upgrade the Database DST

This example updates 19c database to version 44.

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

 -- Shows Related Stats
 @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql

 -- Approximate how much TIMESTAMP WITH TIME ZONE
 @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql

 -- Time zone upgrade check script
 @$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

 -- QC
 SELECT * FROM v$timezone_file;
   44

  Note: OJVM already update via last RU.
  -- QC (44)
  SELECT * FROM v$timezone_file;

  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 it is present, 
 bounce and run @?/javavm/admin/fixTZb.sql—but on 19.28 it’s usually already fixed.

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

You've stamped PRI at v44. If you have a physical SB, the change is in redo and will flow; just make sure the SB Oracle Home is also on a RU that includes DSTv44 files (19.18+ does) and that apply is running.

Bottom line: Your database is at DST v44 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