oracle19c安装时区补丁(解决ORA-39405 TSTZ版本问题)

最新推荐文章于 2024-03-05 16:36:22 发布

狂人日志

最新推荐文章于 2024-03-05 16:36:22 发布

阅读量5.4k

点赞数

分类专栏： oracle 文章标签： oracle 数据库运维 rds

本文链接：https://blog.csdn.net/weixin_43885834/article/details/105745901

版权

oracle 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

背景

把aws rds 上托管的oracle数据导入到公司服务器oracle

亚马逊 rds oracle版本19.4

公司服务器oracle版本19.3

通过dump方式导入数据传到公司oracle服务器导入的时候报错

这个错误是因为数据库时区版本不一致造成的，并且是源数据时区版本高。目标数据库时区版本低。根据我的理解假如是源数据库时区版本低，目标数据库时区版本高应该是可以导入的。

通过命令 SELECT * FROM v$timezone_file;查询和浏览数据库相关文件发现公司服务器的oracle时区版本确实是32

这个错误除了升级数据库时区版本没有找到其他解决方法，如果找不到时区版本补丁，直接升级数据库版本也会顺带升级时区也能解决问题。

解决过程

数据库时区版本补丁和升级脚本可以去my oracle support社区下载，也可以在我的CSND中使用积分下载

补丁和脚本下载地址

https://download.csdn.net/download/weixin_43885834/12360971

https://download.csdn.net/download/weixin_43885834/12360984

检测环境

#oracle用户登陆服务器
[oracle@gp oracle]$ mkdir TSTZ_BUG
[oracle@gp oracle]$ cd TSTZ_BUG/
[oracle@gp TSTZ_BUG]$ cd 28852325/
[oracle@gp 28852325]$ ls
etc  files  README.txt
#查看补丁说明
[oracle@gp 28852325]$ cat README.txt 
[oracle@gp 28852325]$ cd $ORACLE_HOME/OPatch/
检查opatch版本
[oracle@gp OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.
检查当前oracle补丁情况
[oracle@gp OPatch]$ ./opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)

OPatch succeeded.
[oracle@gp OPatch]$ ./opatch lsinv
Oracle 临时补丁程序安装程序版本 12.2.0.1.17
版权所有 (c) 2020, Oracle Corporation。保留所有权利。


Oracle 主目录       ：/opt/oracle/product/19c/dbhome_1
主产品清单：/opt/oracle/oraInventory
   来自           ：/opt/oracle/product/19c/dbhome_1/oraInst.loc
OPatch 版本    ：12.2.0.1.17
OUI 版本       ：12.2.0.7.0
日志文件位置：/opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2020-04-24_18-41-46下午_1.log

Lsinventory Output file location : /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2020-04-24_18-41-46下午.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: gp
ARU platform id: 226
ARU platform description:: Linux x86-64

已安装的顶级产品 (1):

Oracle Database 19c                                                  19.0.0.0.0
此 Oracle 主目录中已安装 1 个产品。


临时补丁程序 (2) :

Patch  29585399     : applied on Thu Apr 18 15:22:15 CST 2019
Unique Patch ID:  22840393
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
   Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
   Bugs fixed:
     27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
     28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
     28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
     28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
     28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
     28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
     29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
     29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
     29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
     29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
     29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
     29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
     29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
     29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
     29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
     29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
     29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
     29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
     29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
     29413360, 29457319, 29465047

Patch  29517242     : applied on Thu Apr 18 15:21:56 CST 2019
Unique Patch ID:  22862832
Patch description:  "Database Release Update : 19.3.0.0.190416 (29517242)"
   Created on 17 Apr 2019, 23:27:10 hrs PST8PDT
   Bugs fixed:
     29515240, 14735102, 19697993, 20313356, 21965541, 25806201, 25883179
     25986062, 26476244, 26611353, 26872233, 27369515, 27423500, 27666312
     27710072, 27846298, 27957203, 28064977, 28072567, 28129791, 28181021
     28210681, 28279456, 28313275, 28350595, 28371123, 28379065, 28431445
     28463226, 28489419, 28502773, 28513333, 28534475, 28561704, 28569897
     28572533, 28572544, 28587723, 28593682, 28594086, 28597221, 28601957
     28605066, 28606598, 28625862, 28627033, 28636532, 28643718, 28644549
     28645570, 28646200, 28646939, 28649388, 28655209, 28663782, 28673945
     28692275, 28694872, 28696373, 28705231, 28710385, 28710734, 28714461
     28718469, 28730079, 28740708, 28760206, 28772390, 28774416, 28777214
     28789531, 28791852, 28795551, 28802734, 28804517, 28810381, 28811560
     28815123, 28815355, 28819640, 28824482, 28833912, 28835937, 28862532
     28863432, 28873575, 28876253, 28876639, 28884931, 28888327, 28892794
     28897512, 28899663, 28901126, 28905457, 28907196, 28912691, 28915561
     28917080, 28918429, 28919145, 28922227, 28922532, 28922608, 28925634
     28925880, 28933158, 28936114, 28937717, 28938698, 28940179, 28940281
     28941901, 28942455, 28945421, 28945994, 28951533, 28952168, 28954762
     28955606, 28957292, 28957723, 28962775, 28965231, 28966444, 28974083
     28977322, 28983095, 28983486, 28986326, 28986481, 28988482, 28989306
     28993295, 28994307, 28996376, 29000000, 29001888, 29002927, 29003738
     29006318, 29006621, 29007321, 29007775, 29008035, 29011936, 29013475
     29014076, 29015118, 29017265, 29018655, 29019121, 29021063, 29021352
     29024552, 29026582, 29026606, 29027456, 29027694, 29027940, 29031575
     29031600, 29032234, 29032457, 29032607, 29033052, 29033145, 29033200
     29033280, 29034587, 29037290, 29038528, 29039089, 29039510, 29043554
     29043651, 29043725, 29044763, 29044954, 29047850, 29048289, 29048498
     29048605, 29050560, 29050765, 29051702, 29052726, 29053783, 29056024
     29056270, 29056560, 29059011, 29061959, 29062692, 29062848, 29062860
     29062868, 29110526, 29110783, 29110790, 29110797, 29110802, 29110805
     29111598, 29113282, 29113305, 29117526, 29117642, 29119077, 29120223
     29122224, 29122254, 29123297, 29123432, 29123482, 29124368, 29125380
     29126345, 29127957, 29128935, 29129450, 29129497, 29129712, 29130219
     29131539, 29132938, 29134447, 29136111, 29138641, 29141316, 29141341
     29141685, 29142667, 29144995, 29145214, 29145730, 29149829, 29150338
     29151520, 29152357, 29155099, 29157389, 29158680, 29158899, 29159909
     29159936, 29160174, 29162095, 29163415, 29163437, 29163524, 29163567
     29167111, 29167342, 29167374, 29167940, 29168219, 29168433, 29169073
     29169215, 29171942, 29172618, 29172826, 29173140, 29173373, 29173817
     29174004, 29176318, 29177466, 29177543, 29177886, 29178385, 29180313
     29180455, 29180559, 29180893, 29181153, 29181231, 29181620, 29181743
     29181923, 29182019, 29183912, 29184297, 29184666, 29185193, 29186456
     29189302, 29189307, 29189889, 29190235, 29190474, 29190740, 29191541
     29192419, 29192468, 29192685, 29193207, 29194205, 29194367, 29194493
     29194827, 29195279, 29195337, 29195758, 29196725, 29199635, 29199733
     29200316, 29200700, 29201494, 29201539, 29202104, 29202850, 29203122
     29203166, 29203425, 29203443, 29203604, 29205281, 29205323, 29205419
     29205463, 29205767, 29205918, 29206109, 29206605, 29207073, 29208260
     29208732, 29211457, 29211724, 29212012, 29212433, 29212611, 29213351
     29213775, 29213850, 29213879, 29214561, 29214960, 29216746, 29216984
     29217294, 29217472, 29217828, 29217848, 29218570, 29219205, 29219273
     29220079, 29221248, 29221891, 29222031, 29222784, 29223833, 29223859
     29223967, 29224065, 29224605, 29225076, 29227602, 29228869, 29229164
     29229754, 29229844, 29229955, 29230252, 29230565, 29231133, 29232117
     29232154, 29234123, 29237538, 29240307, 29241345, 29242017, 29242884
     29243958, 29245137, 29245160, 29246163, 29247415, 29247712, 29247906
     29248552, 29248835, 29248858, 29249991, 29250059, 29251259, 29253184
     29253871, 29254031, 29254930, 29255178, 29255273, 29255431, 29255435
     29256426, 29259119, 29259320, 29261906, 29262512, 29262887, 29265448
     29266248, 29266899, 29267292, 29268412, 29269171, 29270585, 29273539
     29273735, 29273847, 29274428, 29274564, 29274627, 29275461, 29278218
     29279658, 29279751, 29279854, 29282898, 29285503, 29285788, 29285956
     29286037, 29287130, 29287705, 29292837, 29293072, 29297863, 29297915
     29298220, 29299049, 29299082, 29299844, 29301566, 29302963, 29303918
     29304781, 29311588, 29312310, 29312734, 29312753, 29313347, 29313417
     29313525, 29314539, 29318410, 29319441, 29321489, 29323946, 29324568
     29325087, 29325105, 29325257, 29325993, 29327044, 29329087, 29331209
     29331380, 29331493, 29332771, 29333500, 29337310, 29338315, 29338453
     29338780, 29338913, 29339155, 29341209, 29343086, 29346943, 29347620
     29348176, 29350052, 29351386, 29351716, 29351749, 29352867, 29353271
     29355654, 29356547, 29356752, 29358828, 29360672, 29361801, 29363151
     29364171, 29364177, 29367019, 29367561, 29368253, 29373418, 29373588
     29374179, 29377986, 29378029, 29379978, 29382784, 29385652, 29386502
     29386635, 29386660, 29387274, 29388094, 29388524, 29388830, 29389889
     29390435, 29394140, 29394749, 29397954, 29398863, 29399100, 29399121
     29405012, 29405651, 29412066, 29412269, 29417884, 29423826, 29429264
     29429566, 29431485, 29435652, 29438736, 29439522, 29450421, 29451386
     29452576, 29452953, 29457807, 29460252, 29462957, 29486181, 29507616
     29521748, 29530812, 29531654, 29557336, 29558975, 29601461



--------------------------------------------------------------------------------

OPatch succeeded.
检查冲突
[oracle@gp OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -ph /home/oracle/TSTZ_BUG/28852325/
Oracle 临时补丁程序安装程序版本 12.2.0.1.17
版权所有 (c) 2020, Oracle Corporation。保留所有权利。

PREREQ session

Oracle 主目录       ：/opt/oracle/product/19c/dbhome_1
主产品清单：/opt/oracle/oraInventory
   来自           ：/opt/oracle/product/19c/dbhome_1/oraInst.loc
OPatch 版本    ：12.2.0.1.17
OUI 版本       ：12.2.0.7.0
日志文件位置：/opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2020-04-24_18-43-55下午_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

开始打补丁

关闭数据库实例和监听
[oracle@gp OPatch]$ sqlplus / as sysdba
SQL> shutdown immediate

[oracle@gp OPatch]$ lsnrctl stop

[oracle@gp OPatch]$ cd /home/oracle/TSTZ_BUG/28852325/
[oracle@gp 28852325]$ ls
etc  files  README.txt
[oracle@gp 28852325]$ /opt/oracle/product/19c/dbhome_1/OPatch/opatch apply

安装完成后，再次通过./opatch lsinv 命令 检查补丁的列表，可以看到28852325补丁已经安装好：
打开数据库
[oracle@gp 28852325]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 24 19:09:19 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3.1474E+10 bytes
Fixed Size                 26857400 bytes
Variable Size            1.4026E+10 bytes
Database Buffers         1.7381E+10 bytes
Redo Buffers               40251392 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       MOUNTED
         4 RDSPDB                         MOUNTED
         5 BIZ                            MOUNTED
SQL> alter pluggable database ORCLPDB1 open;

Pluggable database altered.

SQL> alter pluggable database RDSPDB open;

Pluggable database altered.

SQL> alter pluggable database BIZ open;

Pluggable database altered.
SQL> SELECT * FROM v$timezone_file;

FILENAME                                                        VERSION
------------------------------------------------------------ ----------
    CON_ID
----------
timezlrg_32.dat                                                      32
         0
将修改后的SQL文件加载到数据库中
[oracle@gp OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@gp OPatch]$ ./datapatch -verbose
等待安装完成

此时检测时区版本还是32，但是数据库时区目录里已存在33版本的数据
[oracle@gp ~]$ cd $ORACLE_HOME/oracore/zoneinfo
[oracle@gp zoneinfo]$ ls
big              timezlrg_13.dat  timezlrg_20.dat  timezlrg_28.dat  timezlrg_4.dat   timezone_12.dat  timezone_1.dat   timezone_27.dat  timezone_3.dat
little           timezlrg_14.dat  timezlrg_21.dat  timezlrg_29.dat  timezlrg_5.dat   timezone_13.dat  timezone_20.dat  timezone_28.dat  timezone_4.dat
readme_33.txt    timezlrg_15.dat  timezlrg_22.dat  timezlrg_2.dat   timezlrg_6.dat   timezone_14.dat  timezone_21.dat  timezone_29.dat  timezone_5.dat
readme.txt       timezlrg_16.dat  timezlrg_23.dat  timezlrg_30.dat  timezlrg_7.dat   timezone_15.dat  timezone_22.dat  timezone_2.dat   timezone_6.dat
timezdif.csv     timezlrg_17.dat  timezlrg_24.dat  timezlrg_31.dat  timezlrg_8.dat   timezone_16.dat  timezone_23.dat  timezone_30.dat  timezone_7.dat
timezlrg_10.dat  timezlrg_18.dat  timezlrg_25.dat  timezlrg_32.dat  timezlrg_9.dat   timezone_17.dat  timezone_24.dat  timezone_31.dat  timezone_8.dat
timezlrg_11.dat  timezlrg_19.dat  timezlrg_26.dat  timezlrg_33.dat  timezone_10.dat  timezone_18.dat  timezone_25.dat  timezone_32.dat  timezone_9.dat
timezlrg_12.dat  timezlrg_1.dat   timezlrg_27.dat  timezlrg_3.dat   timezone_11.dat  timezone_19.dat  timezone_26.dat  timezone_33.dat

调整数据库时区版本

通过sql脚本，直接把数据库时区调整为最新的版本即33版本

PDB和CDB的时区版本都可以单独升级，我这里只需要把我要导入数据的RDSPDB时区版本升级为33，没有升级CDB，当然你也可以去升级CDB，操作完全一样。

#切换为rdspdb
SQL> alter session set container=RDSPDB;

Session altered.

SQL> SELECT * FROM v$timezone_file;

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

#允许环境检测脚本
SQL> @/home/oracle/TSTZ_BUG/DBMS_DST_scriptsV1.9/upg_tzv_check.sql
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 DSTv33 .
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 upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

#运行调整脚本
SQL> @/home/oracle/TSTZ_BUG/DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv33 .
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.
Pluggable Database closed.
Pluggable 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.
Pluggable Database closed.
Pluggable 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_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
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 DSTv33 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.

#查看时区版本，已升级成功
SQL> SELECT * FROM v$timezone_file;

FILENAME                                                        VERSION
------------------------------------------------------------ ----------
    CON_ID
----------
timezlrg_33.dat                                                      33
         0


1 row selected.

再次通过命令导入备份时执行成功。

狂人日志

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
2
评论
oracle19c安装时区补丁(解决ORA-39405 TSTZ版本问题)

背景把aws rds 上托管的oracle数据导入到公司服务器oracle亚马逊 rds oracle版本19.4公司服务器oracle版本19.3通过dump方式导入数据传到公司oracle服务器导入的时候报错这个错误是因为数据库时区版本不一致造成的，并且是源数据时区版本高。目标数据库时区版本低。根据我的理解假如是源数据库时区版本低，目标数据库时区版本高应该是可以导...
复制链接

扫一扫