Test transportable tablespace (3) - failed due to incompatible character set

The source database use WE8MSWIN1252 (default when choose "general purpose" template from dbca), and the target database use UTF8.


1.1 use RMAN to convert the endian format from Little to Big platform.



CONVERT DATAFILE
'/software/oraods/ops/exp/tts1_01.dbf',
'/software/oraods/ops/exp/tts1_02.dbf'
DB_FILE_NAME_CONVERT
  '/software/oraods/ops/exp','/ods001/oradata/ODST'
FROM PLATFORM 'Linux x86 64-bit';



Starting conversion at target at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_01.dbf
converted datafile=/ods001/oradata/ODST/tts1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_02.dbf
converted datafile=/ods001/oradata/ODST/tts1_02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 20-MAR-13

RMAN> exit


Recovery Manager complete.
odsdev01:ODST:/software/oraods/ops/exp> ls -l /ods001/oradata/ODST/tts*
-rw-r-----   1 oraods     dba        20979712 Mar 20 13:46 /ods001/oradata/ODST/tts1_01.dbf
-rw-r-----   1 oraods     dba        20979712 Mar 20 13:46 /ods001/oradata/ODST/tts1_02.dbf

odsdev01:ODST:/software/oraods/ops/exp> ls -l /software/oraods/ops/exp/tts*.dbf
-rw-r-----   1 oraods     dba        20979712 Mar 20 13:36 /software/oraods/ops/exp/tts1_01.dbf
-rw-r-----   1 oraods     dba        20979712 Mar 20 13:36 /software/oraods/ops/exp/tts1_02.dbf

odsdev01:ODST:/software/oraods/ops/exp> date
Wed Mar 20 13:47:23 SST 2013


1.2 The impdp error

odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par

Import: Release 11.2.0.3.0 - Production on Wed Mar 20 13:48:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 13:48:34

2. When I tried to change character set in source database with CS scanner. The report shows me there are unconvertible dictionary tables listed below.

NOTHANDLEDDATADICTCOLUMNS
---------------------------------------------------------------------------------------------------------------------------
SYS.HISTGRM$(EPVALUE) - VARCHAR2 - CONVERTIBLE
SYS.REG$(SESSION_KEY) - VARCHAR2 - CONVERTIBLE
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY(EPVALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_IPW_INFO(IPW) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_NC_CRED_COLUMNS(CRED_ATTR_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.EM_TARGET_CRED_PROPERTIES(PROPERTY_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_AGENT_SEC_INFO(AGENT_KEY) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_CCR_TARGET_SOURCES(DESCRIPTION) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_CNTR_CREDS(CREDS) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_PARAMETERS(PARAMETER_VALUE) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_SEC_INFO(CA_PWD) - VARCHAR2 - CONVERTIBLE
SYSMAN.MGMT_VIEW_USER_CREDENTIALS(VIEW_PASSWORD) - VARCHAR2 - CONVERTIBLE

12 rows selected.

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                    33,862,781              106                0                0
CHAR                            51,168                0                0                0
LONG                           301,547                0                0                0
CLOB                         1,363,901           63,402                0                0
VARRAY                          56,889                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                       35,636,286           63,508                0                0

3. If I ignore the warning,  there is failure when running csalter.plb.

orarac2poc:RCAT:/home/oracle/ops/exp> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 16:00:52 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SYS@RCAT> @?/rdbms/admin/csalter.plb

0 rows created.


Function created.


Function created.


Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old   6:     if (UPPER('&conf') <> 'Y') then
new   6:     if (UPPER('y') <> 'Y') then
Checking data validity...
Sorry only one session is allowed to run this script

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.


0 rows deleted.


Function dropped.


Function dropped.


Procedure dropped.

SYS@RCAT> exit




To save my time, I assume I am not able to convert WE8MSWIN1252 to UTF8. Will play csscan later.

References:

Running Csalter.Plb Gives Unrecognized Convertible Data Found In Scanner Result [ID 335147.1]
Changing the NLS_CHARACTERSET to AL32UTF8 _ UTF8 (Unicode) [ID 260192.1]