OracleTTSORA-39322: Cannotusetransportabletablespacewit

OracleTTSORA-39322: cannotusetransportablespacewithdifferenttimezoneversion description

Oracle TTSORA-39322: Cannot use transportable tablespace with different timezone version description

I. Problem Description

Oracle 11.2.0.3 performs a TTS test. An error is reported in impdp. The information is as follows:

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,201 1, Oracle and/or itsaffiliates. All rights reserved.

Username:/as sysdba

Connected to: Oracle Database 11 gEnterprise 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 transportablespace with timestamp with timezone columns and different timezone version.

The test environment is windows to Oracle Linux:

View timezone on windows:

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 value $

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

DST_PRIMARY_TT_VERSION 11

View timezone on Linux:

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 value $

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

DST_PRIMARY_TT_VERSION 14

Rac1:/home/oracle> oerr ora 39322

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

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

// Target database and there were tables in the dumpfile

// Timestamp with timezone columns.

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

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

Ii. solution:

Description on MOS:

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

This problem is caused by the incompatibility between source and target timezones. For example, here, the timezone of target is 14, which is higher than the timezone of source. when Data pump checks whether timezones in the dump file has changed, it will fail.

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

The time zone file version of Oracle 9i is 1, 10g is 2, to 11gR2, time zone files can be from 1 to 14.

By default, the time zone of 11.2.0.1 is 11.

The time zone of 11.2.0.2 is 14.

The time zone of 11.2.0.3 is 14.

There are two solutions:

2.1 solution 1: Create a new database which is the same as the source database 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 ..

-- Note that the default timezone of 11.2.0.2 is 14.

2.2 solution 2: Upgrade the Timezone of source db

Upgrade the thesource database Time Zone File and Timestamp with Time Zone Data (tching) 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, 11 gRelease 2 (11.2)" steps under "Upgrading the Time Zone File andTimestamp with Time Zone Data" here:

Upgrade time zone on MOS 2.3

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]

The method mentioned on the official website is to upgrade the time zone of oracle 8i, 9i, and 10g to 11g, basically running utltzver. SQL script, or for 11.2.0.1 to 11.2.0.3, directly upgrade the database.

I am also upgrading the database directly here, so I will not use other operations.

Precautions for tablespace transmission caused by this problem:

TTS requires that the source and Target database versions be the same. Otherwise, the Time zone issue occurs, leading to impdp failure.