Oracle TTSORA-39322: Cannot use transportable tabl

一.問(wèn)題描述
Oracle 11.2.0.3 做TTS 測(cè)試，在impdp時(shí)報(bào)錯(cuò)，信息如下：

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.

測(cè)試環(huán)境是windows 到 Oracle Linux：

在windows 上查看timezone:

SQL> select * from v$version;

BANNER

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

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 – Production

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 上的說(shuō)明：

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

導(dǎo)致這個(gè)問(wèn)題是source 和target 端timezones的不兼容，比如我們這里target 端的timezone 是14，高于source端的11. 當(dāng)Data pump 檢查dump 文件中timezones是否改變時(shí)，就會(huì)失敗。

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.

       默認(rèn)情況下，11.2.0.1 的time zone 是11.

       11.2.0.2的time zone 是14

       11.2.0.3的time zone 是14.

對(duì)應(yīng)的解決方法有兩種：

2.1 解決方法一：創(chuàng)建一個(gè)新db 與 source 庫(kù) 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>

- 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 默認(rèn)是14.

2.2 解決方法二：升級(jí)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 上的升級(jí)time zone 說(shuō)明
TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) dataand DST updates [ID 756454.1]

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]

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]

官網(wǎng)上提到的方法是對(duì)于Oracle 8i,9i,10g的time zone 升級(jí)到11g的方法，基本是運(yùn)行utltzver.sql腳本，或?qū)τ?1.2.0.1到11.2.0.3則是直接升級(jí)DB.

我這里也直接升級(jí)DB，不采用其他的操作了。

這個(gè)問(wèn)題引出的表空間傳輸?shù)淖⒁馐马?xiàng)：

TTS 要求source 和 Target 數(shù)據(jù)庫(kù)版本一致，否則就出出現(xiàn)Time zone 的問(wèn)題，導(dǎo)致impdp 無(wú)法成功進(jìn)行。

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]