The Source DB:
- DB Name: TTS
- Platform: Linux x86_64
- Endian Format: Little
- Character Set: ASC7II
- Default block size: 8k
The Target DB:
- DB Name: ODST
- Platform: HP-UX ia64 v3
- Endian Format: Big
- Character Set: UTF8
- Default block size: 32k
1. setup in source db
SYS@TTS> set pages 1000
SYS@TTS> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0
20 rows selected.
column platform_name format a30
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit Little
SYS@TTS> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/TTS/system01.dbf
/u02/oradata/TTS/sysaux01.dbf
/u02/oradata/TTS/undotbs01.dbf
/u02/oradata/TTS/users01.dbf
SYS@TTS> create tablespace tts1 datafile '/u02/oradata/TTS/tts1_01.dbf' size 20m;
Tablespace created.
SYS@TTS> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/TTS/system01.dbf
/u02/oradata/TTS/sysaux01.dbf
/u02/oradata/TTS/undotbs01.dbf
/u02/oradata/TTS/users01.dbf
/u02/oradata/TTS/tts1_01.dbf
SYS@TTS> create user tranp identified by oracle123 default tablespace tts1;
User created.
SYS@TTS> grant create session, resource to tranp;
Grant succeeded.
SYS@TTS> create table tranp.t01 as select * from sys.dba_objects;
Table created.
SYS@TTS> conn tranp/oracle123
Connected.
TRANP@TTS> select count(*) from t01;
COUNT(*)
----------
65949
SYS@TTS> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS1',TRUE);
PL/SQL procedure successfully completed.
SYS@TTS> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
SYS@TTS> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@TTS> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@TTS> alter tablespace tts1 read only;
Tablespace altered.
2.1 Create the problem if tablespace is not put into read-only mode.
SYS@TTS> alter tablespace tts1 read write;
Tablespace altered.
orarac2poc:TTS:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac2poc:TTS:/home/oracle/ops/exp> expdp parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Thu Mar 21 10:24:17 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA parfile=expdp_trans_df.par
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is
ORA-29335: tablespace 'TTS1' is not read only
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:24:36
2.2 Fix the problem in 2.1
SYS@TTS> alter tablespace tts1 read only;
Tablespace altered.
orarac2poc:TTS:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Thu Mar 21 10:26:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u02/oradata/TTS/tts1_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:27:53
3. Copy the dump file and data file to destination server.
odsdev01:ODST:/software/oraods/ops/exp> scp oracle@10.139.90.42:/u02/oradata/TTS/tts1_01.dbf ./
oracle@10.139.90.42's password:
tts1_01.dbf 100% 20MB 10.0MB/s 11.2MB/s 00:02
odsdev01:ODST:/software/oraods/ops/exp> scp oracle@10.139.90.42:~/ops/exp/tts*.dmp ./
oracle@10.139.90.42's password:
tts.dmp 100% 88KB 88.0KB/s 88.0KB/s 00:00
odsdev01:ODST:/software/oraods/ops/exp> ls -lrt
total 41232
-rw-r--r-- 1 oraods dba 93 Mar 21 10:58 impdp_trans_df.par
-rw-r----- 1 oraods dba 20979712 Mar 21 11:01 tts1_01.dbf
-rw-r----- 1 oraods dba 90112 Mar 21 11:02 tts.dmp
4. Preparation in destination database.
uname -a
HP-UX odsdev01 B.11.31 U ia64 2376076186 unlimited-user license
SQL> create directory tranp_dir as '/software/oraods/ops/exp';
Directory created.
SQL> grant read, write on directory tranp_dir to system;
Grant succeeded.
SQL> create user tranp identified by oracle123;
User created.
SQL> grant create session, resource to tranp;
Grant succeeded.
SQL> alter user system identified by oracle123;
User altered.
column platform_name format a30
SELECT d.PLATFORM_NAME, tp.ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
HP-UX IA (64-bit) Big
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
(select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
(select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
--------------------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
5. Conver the data file.
Datafile conversion necessitates that you choose a technique for naming the output files. You must use the
FORMAT or DB_FILE_NAME_CONVERT arguments to CONVERT to control the naming of output files.If the source and destination platforms differ, then you must specify the
FROM PLATFORM parameter. View platform names by querying V$TRANSPORTABLE_PLATFORM. The FROM PLATFORM value must match the format of the datafiles to be converted to avoid an error.CONVERT DATAFILE
'/software/oraods/ops/exp/tts1_01.dbf'
DB_FILE_NAME_CONVERT
'/software/oraods/ops/exp','/ods010/oradata/ODST'
FROM PLATFORM 'Linux x86 64-bit';
Starting conversion at target at 21-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=247 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/software/oraods/ops/exp/tts1_01.dbf
converted datafile=/ods010/oradata/ODST/tts1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 21-MAR-13
RMAN> exit
Recovery Manager complete.
odsdev01:ODST:/software/oraods/ops/exp> ls -l /ods010/oradata/ODST/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 21 11:08 /ods010/oradata/ODST/tts1_01.dbf
odsdev01:ODST:/software/oraods/ops/exp> ls -l /software/oraods/ops/exp/tts1_01.dbf
-rw-r----- 1 oraods dba 20979712 Mar 21 11:01 /software/oraods/ops/exp/tts1_01.dbf
6.1 import but tablespace block size 8192 does not match
odsdev01:ODST:/software/oraods/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_DATAFILES='/ods010/oradata/ODST/tts1_01.dbf'
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Thu Mar 21 11:09:23 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-29339: tablespace block size 8192 does not match configured block sizes
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:09:34
6.2 Fix the problem of 6.1
odsdev01:ODST:/software/oraods/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 11:09:50 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 32768
SQL> exit
odsdev01:ODST:/software/oraods/ops/exp> oerr ora 29339
29339, 00000, "tablespace block size %s does not match configured block sizes"
// *Cause: The block size of the tablespace to be plugged in or
// created does not match the block sizes configured in the
// database.
// *Action:Configure the appropriate cache for the block size of this
// tablespace using one of the various (db_2k_cache_size,
// db_4k_cache_size, db_8k_cache_size, db_16k_cache_size,
// db_32K_cache_size) parameters.
while the block size in source database is 8k shown below.
SYS@TTS> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
So in destination DB,
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> alter system set db_8k_cache_size=6m;
System altered.
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 16M
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SQL> exit
odsdev01:ODST:/software/oraods/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.3.0 - Production on Thu Mar 21 11:16:28 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
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:16:36
7. verification.
--verification
odsdev01:ODST:/software/oraods/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 11:18:52 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name='TTS1';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TTS1
/ods010/oradata/ODST/tts1_01.dbf
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TTS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS1 READ ONLY
SQL> conn tranp/oracle123
Connected.
SQL> select count(*) from t01;
COUNT(*)
----------
65949
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tts1 read write;
Tablespace altered.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TTS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TTS1 ONLINE
