签到成功

知道了

CNDBA社区CNDBA社区

Oracle TTS ORA-39322: Cannot use transportable tablespace with different timezone version 说明

2016-11-25 15:43 789 0 原创 Oracle 故障处理
作者: Dave

 

一.问题描述

 

Oracle 11.2.0.3 做TTS 测试,在impdp时报错,信息如下:

rac1:/> impdp directory=backupdumpfile=ANQING.DMPtransport_datafiles=/u02/app/oracle/oradata/anqing/ANQING01.DBFremap_schema=anqing:dave logfile=anqing.log         

 

Import: Release 11.2.0.3.0 - Production onMon Feb 20 22:22:17 2012

 

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

 

Username: / as sysdba

 

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORA-39002: invalid operation

ORA-39322: Cannot use transportabletablespace with timestamp with timezone columns and different timezone version.

 

Oracle 传输表空间的相关说明,参考:

Oracle Transportable TableSpace(TTS) 传输表空间 说明

http://blog.csdn.net/tianlesoftware/article/details/7267582

 https://www.cndba.cn/Dave/article/476

测试环境是windows 到 Oracle Linux:

 

在windows 上查看timezone:

SQL> select * from v$version;

 

BANNERhttps://www.cndba.cn/Dave/article/476

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE   11.2.0.1.0      Production

TNS for 64-bit Windows: Version 11.2.0.1.0- Production

NLSRTL Version 11.2.0.1.0 – Productionhttps://www.cndba.cn/Dave/article/476

 

SQL> SELECT NAME,VALUE$ FROM PROPS$WHERE NAME='DST_PRIMARY_TT_VERSION';

 

NAME                           VALUE$

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

DST_PRIMARY_TT_VERSION         11

 

在Linux 上查看timezone:

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE   11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 -Production

NLSRTL Version 11.2.0.3.0 – Production

 

SQL> SELECT NAME,VALUE$ FROM PROPS$WHERE NAME='DST_PRIMARY_TT_VERSION';

 

NAME                           VALUE$

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

DST_PRIMARY_TT_VERSION         14

 

 

rac1:/home/oracle> oerr ora 39322

39322, 00000, "Cannot usetransportable tablespace with timestamp with timezone columns and differenttimezone version."

// *Cause:  The source database was at a different timesonze version than the

//          target database and there were tables in the dumpfile with

//          timestamp with timezone columns.

// *Action: Convert the target database to the same timezone version as the

//          source database or use Data Pump without transportable tablespace.

 

 

二.解决方法:

MOS 上的说明:

Data Pump TTS Import Fails With ORA-39002And ORA-39322 Due To TIMEZONE Conflict [ID 1275433.1]

 

导致这个问题是source 和target 端timezones的不兼容,比如我们这里target 端的timezone 是14,高于source端的11. 当Data pump 检查dump 文件中timezones是否改变时,就会失败。

 

Oracle Database9i includes version 1 of the time zone files, and Oracle Database10g includes version 2. For Oracle Database 11g, release 2, all time zonefiles from versions 1 to 14 are included. Various patches and patch sets, whichare released separately for these releases, may update the time zone fileversion as well.

       Oracle 9i 的time zone 文件version是1,10g 是2,到了11gR2,time zone files 可以从1到14.

       默认情况下,11.2.0.1 的time zone 是11.

       11.2.0.2的time zone 是14

       11.2.0.3的time zone 是14.

 https://www.cndba.cn/Dave/article/476

 

对应的解决方法有两种:https://www.cndba.cn/Dave/article/476

 

2.1 解决方法一:创建一个新db 与 source 库 timezone相同

Create a newdatabase with the same timezone as the source database and use that to convertthe tablespace :

Before creatingthe new database set the environment variable, ORA_TZFILE, to match the sourcedatabase timezone version by setting it to the appropriate value, for example:-

$ export ORA_TZFILE=$ORACLE_HOME/oracore/zoneinfo/timezlrg_13.dat


- Import the source table space into the newly created database, for example:

$ impdp userid=/"/ as sysdba/"directory=DATA_PUMP_EXADATA dumpfile=<SOURCE_DATA.dmp>https://www.cndba.cn/Dave/article/476


- Use the 'dbms_dst' package to upgrade the new database timezone to version 14( in this example). 
- Note: The default timezone version for 11.2.0.2 is14.. 

--注意11.2.0.2 的timezone 默认是14.

 

2.2 解决方法二:升级source db的Timezone

Upgrade the thesource database Time Zone File and Timestamp with Time Zone Data (TSTZ) to thesame version as the target database version and redo the export.

 

For the steps todo this upgrade see the "Oracle Database Globalization Support Guide,11gRelease 2 (11.2)" steps under "Upgrading the Time Zone File andTimestamp with Time Zone Data" here:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch4datetime.htm#CACFFHCJ

 

2.3 MOS 上的升级time zone 说明

 

TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) dataand DST updates [ID 756454.1]

https://www.cndba.cn/Dave/article/476

How To Upgrade The Timezone File Older ThanVersion 11 Using DBMS_DST Package [ID 944122.1]

Updating the RDBMS DST version in 11gR2(11.2.0.1 and up) using DBMS_DST [ID 977512.1]

Actions For DST Updates When Upgrading ToOr Applying The 11.2.0.3 Patchset [ID 1358166.1]

https://www.cndba.cn/Dave/article/476

Actions For DST Updates When Upgrading ToOr Applying The 11.2.0.2 Patchset [ID 1201253.1]

Actions For DST Updates When Upgrading To11.2.0.1 Base Release [ID 815679.1]

 

 

官网上提到的方法是对于oracle 8i,9i,10g的time zone 升级到11g的方法,基本是运行utltzver.sql脚本,或对于11.2.0.1到11.2.0.3则是直接升级DB.

        我这里也直接升级DB,不采用其他的操作了。

 

这个问题引出的表空间传输的注意事项:

TTS 要求source 和 Target 数据库版本一致,否则就出出现Time zone 的问题,导致impdp 无法成功进行。

 

 https://www.cndba.cn/Dave/article/476

 

 

 

 

-------------------------------------------------------------------------------------------------------https://www.cndba.cn/Dave/article/476

版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

Skype: tianlesoftware

Email:   tianlesoftware@gmail.com

Blog:     http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

-------加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请----

DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群:83829929   DBA5群: 142216823

DBA6 群:158654907    DBA7 群:172855474   DBA总群:104207940

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1835
    原创
  • 2
    翻译
  • 423
    转载
  • 123
    评论
  • 访问:3272444次
  • 积分:2903
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群