Oracle TTS ORA-39322: 表空間傳輸
一.問題描述
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
測試環境是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
CORE11.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
CORE11.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 Database9iincludes version 1 of the time zone files, and Oracle Database10gincludes 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.
對應的解決方法有兩種:
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=
- 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:
2.3 MOS 上的升級time zone 說明
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]
官網上提到的方法是對於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 無法成功進行。
-------------------------------------------------------------------------------------------------------
版權所有,文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任!
Skype: tianlesoftware
Email:
Blog:
Weibo:
Twitter:
Facebook:
Linkedin:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/196700/viewspace-763538/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle傳輸表空間(TTS)OracleTTS
- oracle可傳輸表空間TTS小結OracleTTS
- 資料泵 TTS(傳輸表空間技術)TTS
- 【TTS】傳輸表空間Linux asm -> AIX asmTTSLinuxASMAI
- 【TTS】傳輸表空間AIX asm -> linux asmTTSAIASMLinux
- Oracle 表空間傳輸Oracle
- oracle表空間傳輸Oracle
- Oracle傳輸表空間Oracle
- 【TTS】傳輸表空間Linux ->AIX 基於rmanTTSLinuxAI
- 【TTS】傳輸表空間AIX->linux基於rmanTTSAILinux
- 傳輸表空間操作-OracleOracle
- Oracle 傳輸表空間-RmanOracle
- Oracle傳輸表空間學習Oracle
- Oracle 傳輸表空間-EXPDP/IMPDPOracle
- Oracle 傳輸表空間-EXP/IMPOracle
- Oracle表空間傳輸詳解Oracle
- oracle 傳輸表空間一例Oracle
- Oracle可傳輸表空間測試Oracle
- MySQL 傳輸表空間MySql
- MySQL表空間傳輸MySql
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- oracle表空間傳輸的限制條件Oracle
- 傳輸表空間(TTS) - 不同位元組序平臺 RMAN Convert - 1TTS
- 傳輸表空間(TTS) - 不同位元組序平臺 RMAN Convert - 2TTS
- mysql之 表空間傳輸MySql
- 總結-表空間傳輸
- 聊聊Oracle可傳輸表空間(Transportable Tablespace)(上)Oracle
- 聊聊Oracle可傳輸表空間(Transportable Tablespace)(中)Oracle
- 聊聊Oracle可傳輸表空間(Transportable Tablespace)(下)Oracle
- 跨平臺表空間遷移(傳輸表空間)
- 使用Oracle可傳輸表空間的特性複製資料(7)實戰RMAN備份傳輸表空間Oracle
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- 基於redhat 6.5 oracle 11.2.0.4測試tts傳輸表空間之資料庫遷移之一RedhatOracleTTS資料庫
- 基於可傳輸表空間的表空間遷移
- 傳輸表空間自包含理解
- 關於oracle可傳輸表空間的總結Oracle
- oracle 10g 傳輸表空間的測試Oracle 10g