Gunther Oracle APEX und Datenbank Wiki

Aufgabe:

In einer Umgebung auf einem festen Patch Stand (es kann nicht der aktuelle 19c Patch eingespielt werden), muss eine Datapump Export aus einer neueren 19c Patch Version (und damit ein neuerer Zeitzonen File) eingespielt werden.

Daher wird nur die Zeitzonen Definition auf die Version 43 erneuert.

Ablauf:

• Zeitzonen Datei mit der Version 43 hinzufügen über einen Patch 36260493: RDBMS - DSTV43 UPDATE - TZDATA2024A
• Datenbank auf Objekte prüfen die vom Patch betroffen sind
• Datenbank Schema upgraden auf neuen Zeitzonen Definition

was ist aktiv:

SELECT version FROM v$timezone_file;
 
 
    VERSION
-----------
         32
 
 
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
PROPERTY_NAME VALUE
--------------------------___------
DST_PRIMARY_TT_VERSION    32
 
DST_SECONDARY_TT_VERSION  0
 
DST_UPGRADE_STATE         NONE

Ab der Version 18

• Patch laden 18.0/19.0 Patch 36260493: RDBMS - DSTV43 UPDATE - TZDATA2024A
• Unzip RDBMS DSTv43 Patch 36260493
• Apply RDBMS DSTv43 Patch 36260493mit Opatch.

(44 war im März 2024 nicht für MS Windows verfügbar!)

Für diesen Schritte ist keine Downtime notwendig.

In Dos ausgeführt:

e:
 
cd E:\temp\p36260493_190000_MSWIN-x86-64_43\36260493_19
 
set ORACLE_HOME=E:\Oracle\products\19.5.0.0\dbhome_1
 
E:\Oracle\products\19.5.0.0\dbhome_1\OPatch\opatch apply
 
Oracle Interim Patch-Installationsprogramm Version 12.2.0.1.21
Copyright (c) 2025, Oracle Corporation.  All rights reserved. Alle Rechte vorbehalten.
 
Oracle Home: E:\Oracle\products\19.5.0.0\dbhome_1
Zentrales Bestandsverzeichnis: C:\Program Files\Oracle\Inventory
   von:
OPatch-Version: 12.2.0.1.21
OUI-Version : 12.2.0.7.0
Speicherort der Logdatei: E:\Oracle\products\19.5.0.0\dbhome_1\cfgtoollogs\opatch\opatch2025-03-13_17-43-23PM_1.log
 
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   36260493
 
M÷chten Sie fortfahren? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Interimpatch "36260493" wird in OH "E:\Oracle\products\19.5.0.0\dbhome_1" eingespielt
 
Patching von Komponente oracle.oracore.rsf, 19.0.0.0.0...
Patch 36260493 successfully applied.
Log file location: E:\Oracle\products\19.5.0.0\dbhome_1\cfgtoollogs\opatch\opatch2025-03-13_17-43-23PM_1.log
 
OPatch succeeded.

Nach dem Upate der Zonen Definition muss die Datenbank auch auf die Zeitzone umgestellt werden. Dazu müssen zuvor die Vorausetzungen/Auswirkungen geprüft werden und dann wird in eine Downtime die Zeitzonen Definition umgestellt.

siehe auch Support Node : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1) für die neuesten TimeZone Updates bei Bedarf, hier die mit der 19c dabei sind eingespielt und Upgrading DST using scripts - 12.2 and above - (With Example Test Case - 19.11) (Doc ID 2794427.1)

Überprüfen - Wer verwendet Timezone Abhängige Datenbank Spalten ? :

cd $ENV:ORACLE_HOME/rdbms/admin
 
 
sqlplus / AS sysdba
 
-- Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info.
 
@?/rdbms/admin/utltz_countstats.sql
 
..
Amount OF TSTZ DATA USING num_rows stats info IN DBA_TABLES.
..
 
--=> prüfen ob Spalten von der Anwendung verwendet werden
 
----------------------------------------------------------
 
-- Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each -- -- table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of -- 
-- utlz_upg_check.sql and utlz_upg_apply.sql scripts.
 
 
@?/rdbms/admin/utltz_countstar.sql
 
..
 Estimating amount OF TSTZ DATA USING COUNT(*).
..
 SYS.XS$PRIN.START_DATE - 15
 Total COUNT * OF SYS TSTZ COLUMNS IS : 180747
 There are IN total 162 SYS TSTZ COLUMNS.
 .
 FOR non-SYS TABLES ...
 Note: empty TABLES are NOT listed.
...
 
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
 Total COUNT * OF non-SYS TSTZ COLUMNS IS :  18936
 There are IN total 60 non-SYS TSTZ COLUMNS.
 Total Minutes elapsed : 0
 
--=> prüfen ob Spalten von der Anwendung verwendet werden
 
----------------------------------------------------------

Theoretisch muss man sich nun Gedanken machen, ob eine Änderungen Auswirkungen auf die Applikation hat.

Überprüfen ob ein Upgrade möglich mit @?/rdbms/admin/utltz_upg_check.sql

--Time zone upgrade check script
 
@?/rdbms/admin/utltz_upg_check.sql
 
SESSION altered.
 
 
INFO: Starting WITH RDBMS DST UPDATE preparation.
INFO: NO actual RDBMS DST UPDATE will be done BY this script.
INFO: IF an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks FOR known issues ...
INFO: DATABASE version IS 19.0.0.0 .
INFO: DATABASE RDBMS DST version IS DSTv32 .
INFO: No known issues detected.
INFO: Now detecting NEW RDBMS DST version.
A PREPARE window has been successfully started.
INFO: Newest RDBMS DST version detected IS DSTv43 .
INFO: NEXT step IS checking ALL TSTZ DATA.
INFO: It might take a while BEFORE any further output IS seen ...
A PREPARE window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used IS found.
INFO: Note that NO DST UPDATE was yet done.
INFO: Now run utltz_upg_apply.sql TO do the actual RDBMS DST UPDATE.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the DATABASE 2 times WITHOUT any confirmation OR prompt.

Upgrade der Zeitzonen Definition mit @?/rdbms/admin/utltz_upg_apply.sql:

cd $ENV:ORACLE_HOME/rdbms/admin
 
sqlplus / AS sysdba
 
 
--Time zone apply script. Warning: This script will restart the database and adjust time zone data.
 
 
@?/rdbms/admin/utltz_upg_apply.sql
 
SESSION altered.
 
INFO: IF an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The DATABASE RDBMS DST version will be updated TO DSTv43 .
WARNING: This script will restart the DATABASE 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW IF this IS NOT intended.
INFO: Restarting the DATABASE IN UPGRADE mode TO START the DST upgrade.
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
ORACLE instance started.
 
Total System Global Area   5049940912 bytes
Fixed SIZE                    9038768 bytes
Variable SIZE              3137339392 bytes
DATABASE Buffers           1895825408 bytes
Redo Buffers                  7737344 bytes
DATABASE mounted.
DATABASE opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading ALL SYS owned TSTZ DATA.
INFO: It might take TIME BEFORE any further output IS seen ...
An upgrade window has been successfully started.
INFO: Restarting the DATABASE IN NORMAL mode TO upgrade non-SYS TSTZ DATA.
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
ORACLE instance started.
 
Total System Global Area   5049940912 bytes
Fixed SIZE                    9038768 bytes
Variable SIZE              3137339392 bytes
DATABASE Buffers           1895825408 bytes
Redo Buffers                  7737344 bytes
DATABASE mounted.
DATABASE opened.
INFO: Upgrading ALL non-SYS TSTZ DATA.
INFO: It might take TIME BEFORE any further output IS seen ...
INFO: Do NOT START any application yet that uses TSTZ DATA!
INFO: NEXT IS a list OF ALL upgraded TABLES:
TABLE list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
NUMBER OF failures: 0
TABLE list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
NUMBER OF failures: 0
......
NUMBER OF failures: 0
INFO: Total failures during UPDATE OF TSTZ DATA: 0 .
An upgrade window has been successfully ended.
INFO: Your NEW Server RDBMS DST version IS DSTv43 .
INFO: The RDBMS DST UPDATE IS successfully finished.
INFO: Make sure TO exit this SQL*Plus SESSION.
INFO: Do NOT USE it FOR timezone related selects.
 
SESSION altered.
 
 
 
1 ROW selected.

Version prüfen:

SELECT * FROM v$timezone_file;
 
 
FILENAME                                                          VERSION       CON_ID
------------------------------------------------------------ ------------ ------------
timezlrg_43.dat                                                        43            0

Auf Platte liege die Zeitzonen Definition hier:

cd $ENV:ORACLE_HOME\dbhome_1\oracore\zoneinfo
 
cat readme.txt
 
Current Structure version: 3
Current Content Version  :43
 
Content Version 43
------------------
Timezones updated:
DSTVERSION TIME_ZONE_NAME FROM_YEAR TO_YEAR
43, Africa/Bissau, 1912,

Support:

• Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)
• Applying the DSTv44 update for the Oracle Database (Doc ID 3071901.1)
• Patch 36260493: RDBMS - DSTV43 UPDATE - TZDATA2024A