ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 36

丫无名

已于 2022-04-21 14:33:52 修改

阅读量3k

点赞数 2

分类专栏： ORA-39405 TSTZ version 36 文章标签： dba 数据库开发

于 2022-04-21 14:14:04 首次发布

本文链接：https://blog.csdn.net/weixin_36782521/article/details/124320439

版权

ORA-39405 同时被 2 个专栏收录

1 篇文章 1 订阅

订阅专栏

TSTZ version 36

1 篇文章 0 订阅

订阅专栏

导入时 DST TZ 版本不匹配

ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ 
version 36 into a target database with TSTZ version 32.

1、环境检查

[root@localhost ~]#
[root@localhost ~]# su - oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 21 12:09:02 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT * FROM v$timezone_file;

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

SQL>
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
32
SQL>

2、下载最新的 OPatch 和 DSTV36 更新补丁

Opatch Version Patch:
https://updates.oracle.com/download/6880880.html
DST V36 Patch:
https://updates.oracle.com/download/32327201.html
已经下好的软件地址

3、升级Opatch版本

[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ cd $ORACLE_HOME/
[oracle@localhost dbhome_1]$
[oracle@localhost dbhome_1]$ cd OPatch/
[oracle@localhost OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.
[oracle@localhost OPatch]$
[oracle@localhost OPatch]$
[oracle@localhost OPatch]$ cd $ORACLE_HOME/
[oracle@localhost dbhome_1]$
[oracle@localhost dbhome_1]$ ls -ld OPatch
drwxr-xr-x. 14 oracle oinstall 4096 Apr  7 14:53 OPatch
[oracle@localhost dbhome_1]$
[oracle@localhost dbhome_1]$ cd /opt/orcl_bug/
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ ls -ltr *.zip
-rwxr-xr-x. 1 oracle oinstall 124109254 Apr 21 12:08 p6880880_190000_Linux-x86-64.zip
-rwxr-xr-x. 1 oracle oinstall    405701 Apr 21 12:08 p32327201_190000_Linux-x86-64.zip
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ unzip p6880880_190000_Linux-x86-64.zip
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ ls
OPatch  p32327201_190000_Linux-x86-64.zip  p6880880_190000_Linux-x86-64.zip
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ cd $ORACLE_HOME/
[oracle@localhost dbhome_1]$
[oracle@localhost dbhome_1]$ mv OPatch OPatch_bak
[oracle@localhost dbhome_1]$
[oracle@localhost dbhome_1]$ mv /opt/orcl_bug/OPatch $ORACLE_HOME/OPatch
[oracle@localhost dbhome_1]$
[oracle@localhost dbhome_1]$ cd OPatch
[oracle@localhost OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.30

OPatch succeeded.
[oracle@localhost OPatch]$

4、将 DSTV36 更新补丁应用到 ORACLE HOME

[oracle@localhost OPatch]$
[oracle@localhost OPatch]$ cd /opt/orcl_bug/
[oracle@localhost orcl_bug]$ ls
p32327201_190000_Linux-x86-64.zip  p6880880_190000_Linux-x86-64.zip
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ unzip p32327201_190000_Linux-x86-64.zip
Archive:  p32327201_190000_Linux-x86-64.zip
   creating: 32327201/
   creating: 32327201/files/
   creating: 32327201/files/oracore/
   creating: 32327201/files/oracore/zoneinfo/
  inflating: 32327201/files/oracore/zoneinfo/timezone_36.dat
   creating: 32327201/files/oracore/zoneinfo/big/
  inflating: 32327201/files/oracore/zoneinfo/big/timezone_36.dat
  inflating: 32327201/files/oracore/zoneinfo/big/timezlrg_36.dat
   creating: 32327201/files/oracore/zoneinfo/little/
  inflating: 32327201/files/oracore/zoneinfo/little/timezlrg_36.dat
  inflating: 32327201/files/oracore/zoneinfo/little/timezone_36.dat
  inflating: 32327201/files/oracore/zoneinfo/timezlrg_36.dat
  inflating: 32327201/files/oracore/zoneinfo/readme_36.txt
  inflating: 32327201/README.txt
   creating: 32327201/etc/
   creating: 32327201/etc/config/
  inflating: 32327201/etc/config/actions.xml
  inflating: 32327201/etc/config/inventory.xml
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ /opt/oracle/product/19.3.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /opt/orcl_bug/32327201
Oracle Interim Patch Installer version 12.2.0.1.30
Copyright (c) 2022, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /opt/oracle/product/19.3.0/dbhome_1
Central Inventory : /opt/oraInventory
   from           : /opt/oracle/product/19.3.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.30
OUI version       : 12.2.0.7.0
Log file location : /opt/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-04-21_12-32-05PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$
[oracle@localhost orcl_bug]$ cd /opt/orcl_bug/32327201
[oracle@localhost 32327201]$
[oracle@localhost 32327201]$ /opt/oracle/product/19.3.0/dbhome_1/OPatch/opatch apply -oh /opt/oracle/product/19.3.0/dbhome_1/
Oracle Interim Patch Installer version 12.2.0.1.30
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/product/19.3.0/dbhome_1
Central Inventory : /opt/oraInventory
   from           : /opt/oracle/product/19.3.0/dbhome_1//oraInst.loc
OPatch version    : 12.2.0.1.30
OUI version       : 12.2.0.7.0
Log file location : /opt/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-04-21_12-34-41PM_1.log

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

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

Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 32327201 successfully applied.
Log file location: /opt/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2022-04-21_12-34-41PM_1.log

OPatch succeeded.
[oracle@localhost 32327201]$
[oracle@localhost 32327201]$
[oracle@localhost 32327201]$ /opt/oracle/product/19.3.0/dbhome_1/OPatch/opatch lsinv | grep -i "DSTV36 UPDATE"
Patch description:  "RDBMS - DSTV36 UPDATE - TZDATA2020E"
[oracle@localhost 32327201]$

5、查看当前 TZ 版本

[oracle@localhost OPatch]$
[oracle@localhost OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 21 12:39:08 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL> SELECT * FROM v$timezone_file;

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

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 4999608360 bytes
Fixed Size                  8906792 bytes
Variable Size             939524096 bytes
Database Buffers         4043309056 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL>
SQL> SELECT * FROM v$timezone_file;

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

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


SQL>

6、查看下载的TZ版本

SQL>
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
36

SQL>

7、Upgrade mode下启动数据库

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 4999608360 bytes
Fixed Size                  8906792 bytes
Variable Size             939524096 bytes
Database Buffers         4043309056 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL>
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
OPTICAL   READ WRITE

SQL>
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
OPTICAL          OPEN MIGRATE

SQL>

8、升级前准备

SQL>
SQL> DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_prepare(l_tz_version);
END;  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;SQL> SQL> SQL>   2    3    4

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       36
DST_UPGRADE_STATE              PREPARE

SQL>

9、查找受影响的表

SQL>
SQL> EXEC DBMS_DST.find_affected_tables;

PL/SQL procedure successfully completed.

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

  COUNT(*)
----------
0

SQL>
SQL> select * from sys.dst$error_table;

no rows selected

SQL>

10、结束升级前准备

SQL>
SQL> EXEC DBMS_DST.end_prepare;

PL/SQL procedure successfully completed.

SQL>

11、开始升级TZ

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4999608360 bytes
Fixed Size                  8906792 bytes
Variable Size             939524096 bytes
Database Buffers         4043309056 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL>
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;SQL>   2    3    4    5    6    7    8    9   10
 11  /
l_tz_version=36
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SQL>

12、关闭升级模式，正常启动数据库

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 4999608360 bytes
Fixed Size                  8906792 bytes
Variable Size             939524096 bytes
Database Buffers         4043309056 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL>

13、结束升级

SQL>
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;
/SQL>   2    3    4    5    6    7    8    9
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
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
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

SQL>

14、查看升级结果

SQL>
SQL> COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;SQL> SQL> SQL>   2    3    4

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

SQL>

丫无名

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 36

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 36 into a target database with TSTZ version 32.
复制链接

扫一扫