Oracle故障案例之-19C时区补丁DSTV38更新

文章目录

• 前言
• • 📣 1.故障案例
  • 📣 2.补丁下载
  • 📣 3.升级Opatch版本
  • 📣 4.检查Patch 是否冲突
  • 📣 5.应用补丁34006614
  • 📣 6.确认升级
  • 📣 7.升级TZ
  • 📣 8.查看升级结果

前言

Oracle19C通过dump方式从TSTZ version 38 导入到低版本TSTZ version 32时报ORA-39405，本博客做了详细的处理过程说明。

---

📣 1.故障案例

--目标库
SQL> SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

--源库
SQL> SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_38.dat              38          0

SELECT DBMS_DST.get_latest_timezone_version FROM dual;

📣 2.补丁下载

📣 3.升级Opatch版本

--备份原来的OPatch
[root@dbhost ~]# su - oracle
Last login: Sun Aug 20 13:09:38 CST 2023 on pts/0
[oracle@dbhost ~]$ cd $ORACLE_HOME/
[oracle@dbhost dbhome_1]$ mv OPatch OPatch_bak

--应用新的补丁
unzip /opt/p6880880_190000_Linux-x86-64.zip -d $ORACLE_HOME
[oracle@dbhost ~]$ cd $ORACLE_HOME/OPatch
[oracle@dbhost OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.28
OPatch succeeded.

📣 4.检查Patch 是否冲突

📣 5.应用补丁34006614

Oracle Interim Patch Installer version 12.2.0.1.28
Copyright (c) 2023, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.3.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.3.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.28
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2023-08-20_14-02-53PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   34006614  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '34006614' to OH '/u01/app/oracle/product/19.3.0/dbhome_1'

Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 34006614 successfully applied.
Log file location: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2023-08-20_14-02-53PM_1.log

OPatch succeeded.

📣 6.确认升级

--查看当前 TZ 版本
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

SQL> col PROPERTY_NAME for a30
SQL> set line 200           
SQL> 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

--查看下载的TZ版本
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         38

[oracle@dbhost ~]$ cd $ORACLE_HOME/oracore/zoneinfo
[oracle@dbhost zoneinfo]$ ls
big              timezlrg_17.dat  timezlrg_28.dat  timezlrg_8.dat   timezone_1.dat   timezone_30.dat
little           timezlrg_18.dat  timezlrg_29.dat  timezlrg_9.dat   timezone_20.dat  timezone_31.dat
readme_38.txt    timezlrg_19.dat  timezlrg_2.dat   timezone_10.dat  timezone_21.dat  timezone_32.dat
readme.txt       timezlrg_1.dat   timezlrg_30.dat  timezone_11.dat  timezone_22.dat  timezone_38.dat
timezdif.csv     timezlrg_20.dat  timezlrg_31.dat  timezone_12.dat  timezone_23.dat  timezone_3.dat
timezlrg_10.dat  timezlrg_21.dat  timezlrg_32.dat  timezone_13.dat  timezone_24.dat  timezone_4.dat
timezlrg_11.dat  timezlrg_22.dat  timezlrg_38.dat  timezone_14.dat  timezone_25.dat  timezone_5.dat
timezlrg_12.dat  timezlrg_23.dat  timezlrg_3.dat   timezone_15.dat  timezone_26.dat  timezone_6.dat
timezlrg_13.dat  timezlrg_24.dat  timezlrg_4.dat   timezone_16.dat  timezone_27.dat  timezone_7.dat
timezlrg_14.dat  timezlrg_25.dat  timezlrg_5.dat   timezone_17.dat  timezone_28.dat  timezone_8.dat
timezlrg_15.dat  timezlrg_26.dat  timezlrg_6.dat   timezone_18.dat  timezone_29.dat  timezone_9.dat
timezlrg_16.dat  timezlrg_27.dat  timezlrg_7.dat   timezone_19.dat  timezone_2.dat

📣 7.升级TZ

✨ 7.1 Upgrade mode下启动数据库

SQL> select name, open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE
SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN MIGRATE

✨ 7.2 升级前准备

SQL> COLUMN property_name FORMAT A30

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       38
DST_UPGRADE_STATE              PREPARE

✨ 7.3 查找受影响的表

SQL> EXEC DBMS_DST.find_affected_tables;

PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dst$affected_tables;

  COUNT(*)
----------
         0
结束升级前准备
SQL> EXEC DBMS_DST.end_prepare;
PL/SQL procedure successfully completed.

✨ 7.4 开始升级TZ

SQL> shutdown immediate;
SQL> startup upgrade;

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

✨ 7.5 关闭升级模式

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

📣 8.查看升级结果

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         38
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE


[oracle@dbhost ~]$ cd $ORACLE_HOME/OPatch
[oracle@dbhost OPatch]$ ./opatch lspatches
34006614;RDBMS - DSTV38 UPDATE - TZDATA2022A
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)