1)transport tbs exmaple01 reverse teaching meterial.

1@@@@RHEL(64bit,O10g,tbs,AL32UTF8) => XP(32bit,O10g,tbs,ZHS16GBK) guide:   ~Transportable tablespaces are also useful to move data from the data    warehouse to a dependent data mart.   ~In oracle 10g, it is feasable to transport tbs between operating systems.    like Windows to Linux. step by step:   1. Create a new tablespace   2. Move the data you want to transfer into its own tablespace.   3. Alter the tablespace to read-only.   4. Use the export utility to unload the metadata describing the objects in the      tablespace.   5. If moving between different operating system, then convert the files using      RMAN(this conversion step can alternatively be permormed on the target platform      after step 6)   6. Copy the datafiles and export dump file containing the metadata to target      system.   7. Use the import utility to load the metadata descriptions into the target database.   8. Alter the tablespace to read/write.   9. Perform transformations  10. Move the data from the staging area to warehouse fact table. @@@ @@@<1>(oltp_db)create tablespace orders, and oltp user. @@@ SYS@ocp> ed   CREATE USER oltp IDENTIFIED BY oltp   DEFAULT TABLESPACE users   TEMPORARY TABLESPACE temp   QUOTA UNLIMITED ON users; SYS@ocp> / SYS@ocp> ed   CREATE TABLESPACE orders DATAFILE '/u01/app/oracle/oradata/OCP/datafile/orders.dbf' SIZE 5M   AUTOEXTEND ON  DEFAULT STORAGE   (INITIAL 64K PCTINCREASE 0 MAXEXTENTS UNLIMITED) SYS@ocp> / Tablespace created. @@@ @@@<2>(oltp_db)create table on oltp system. @@@ SYS@ocp> ed CREATE TABLE oltp.apr_orders TABLESPACE orders AS SELECT * FROM sh.sales WHERE time_id BETWEEN to_date('01-JAN-2001','dd-mon-yyyy')   AND to_date('01-JAN-2002','dd-mon-yyyy') / SYS@ocp> / Table created. SYS@ocp> select count(*) from  oltp.apr_orders;   COUNT(*) ----------     259418 @@@ @@@Note: the limit of transport tablespace.    Each tablespace must be self-contained and cannot reference anything outside  the tablespace. If there were a global index on the Apirl ORDERS table, it would  be not self-contained, and the index would have to be dropped before the tablespace  could be moved. @@@ @@@<3>(oltp_db)modify tbs to read only, create directory object. @@@ SYS@ocp> ALTER TABLESPACE orders READ ONLY; Tablespace altered. @@@ SYS@ocp> !mkdir -p /home/oracle/datafiles          SYS@ocp> !mkdir -p /home/oracle/logfiles SYS@ocp> CREATE OR REPLACE DIRECTORY data_file_dir AS '/home/oracle/datafiles'; Directory created. SYS@ocp> CREATE OR REPLACE DIRECTORY log_file_dir AS '/home/oracle/logfiles'; Directory created. SYS@ocp> GRANT READ, WRITE ON DIRECTORY data_file_dir TO oltp; Grant succeeded. SYS@ocp> GRANT READ, WRITE ON DIRECTORY log_file_dir TO oltp; @@@ @@@<4>(oltp_db)export the metadata @@@ [oracle@station78 ~]$ expdp \'sys/oracle as sysdba\' TRANSPORT_TABLESPACES=orders > DIRECTORY=data_file_dir DUMPFILE=expdat.dmp Export: Release 10.2.0.1.0 - 64bit Production on Friday, 24 August, 2012 19:57:08 Copyright (c) 2003, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  'sys/******** AS SYSDBA' TRANSPORT_TABLESPACES=orders DIRECTORY=data_file_dir DUMPFILE=expdat.dmp Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:   /home/oracle/datafiles/expdat.dmp Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 19:57:19 @@@ [oracle@station78 datafiles]$ pwd /home/oracle/datafiles [oracle@station78 datafiles]$ ls expdat.dmp export.log expdat.dmp  export.log @@@ @@@Hint:    When exporting and importing tbs, be sure to use an account that has been  granted the EXP_FULL_DATABASE role. @@@ @@@<5>(both db)ensure the endian format for both. @@@ SYS@ocp> ed SELECT d.platform_name, tp.endian_format FROM v$transportable_platform tp,      v$DATABASE d WHERE tp.platform_name = d.platform_name / SYS@ocp> / PLATFORM_NAME               ENDIAN_FORMAT ------------------------------ -------------- Linux 64-bit for AMD           Little @@@ @@@This is the target db. SYS@ocp> select PLATFORM_NAME, ENDIAN_FORMAT > from v$transportable_platform where PLATFORM_NAME like '%Windows%'; PLATFORM_NAME               ENDIAN_FORMAT ------------------------------ -------------- Microsoft Windows IA (32-bit)  Little Microsoft Windows IA (64-bit)  Little @@@ @@@<6>(oltp_db)convert and export the data using rman. @@@ [oracle@station78 ~]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 24 20:16:42 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved. connected to target database: OCP (DBID=2684153223) RMAN> CONVERT TABLESPACE orders TO PLATFORM 'Microsoft Windows IA (32-bit)' 2> FORMAT '/home/oracle/%N_%f';     @@@<==convert platform.   Starting backup at 24-AUG-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=146 devtype=DISK channel ORA_DISK_1: starting datafile conversion input datafile fno=00016 name=/u01/app/oracle/oradata/OCP/datafile/orders.dbf converted datafile=/home/oracle/ORDERS_16 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished backup at 24-AUG-12 @@@ [oracle@station78 ~]$ pwd /home/oracle [oracle@station78 ~]$ ls ORDERS_16 ORDERS_16 [oracle@station78 ~]$ du -sh ORDERS_16 14M    ORDERS_16 @@@ @@@Note: %N_%f @@@%N is the name of tablespace, %f is the file id of tbs SYS@ocp> select name from v$datafile where file#=16; NAME ---------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/OCP/datafile/orders.dbf @@@ @@@<7>*optional(dw_db xp) copy datafile to target system, then convert using rman. @@@ @@@generally, you could skip this step. use step 6 is more intelligant. RMAN> CONVERT DATAFILE 2> 'orders.dbf' 3> to platform='Microsoft Windows IA (32-bit)' 4> from platform='Linux IA (32-bit)'; @@@ @@@<8>(both) transport metadata and data to target operating system. @@@ ignore:   I use samba service to trunsport the two files. @@@ @@@<9>(xp dw)create directory object. @@@ c:>\sqlplus / as sysdba; Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create directory xp_dir as 'E:\download'; SQL> grant read,write on directory xp_dir to hr; @@@ @@@<10>(xp dw)import data. @@@ C:\>impdp 'sys/oracle@orcl as sysdba' transport_datafiles=E:\download\ORDERS_16 DIRECTORY=xp_dir DUMPFILE=expdat.dmp REMAP_SCHEMA=(oltp:hr) ... Import: Release 10.2.0.1.0 - Production on 星期六, 25 8月, 2012 14:33:55 Copyright (c) 2003, 2005, Oracle.  All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options 已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  'sys/********@orcl AS SYSDBA' transpo rt_datafiles=E:\download\ORDERS_16 DIRECTORY=xp_dir DUMPFILE=expdat.dmp 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK ORA-39123: 数据泵可传输的表空间作业中止 ORA-29345: 无法使用不兼容的字符集将表空间插入到数据库中 作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 14:35:25 停止 @@@ @@@<11>convert client characterset. @@@ SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK @@@Note:    characterset muse be big to small.    characterset convert is danger for data. @@@ @@@xp dw db characterset:  SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK @@@ @@@linux oltp db characterset: SYS@ocp> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.AL32UTF8 @@@ @@@server => AL32UTF8, client => ZHS16GBK , so make client convertion to AL32UTF8 SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP MOUNT EXCLUSIVE; SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION; SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0; SQL>ALTER DATABASE OPEN; SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8; SQL>SHUTDOWN immediate; SQL>startup; @@@ @@@<12>inspect characterset after conversion. @@@ @@@This is the linux server: SYS@ocp> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------------------------------------- AMERICAN_AMERICA.AL32UTF8 @@@ @@@This is the xp client after converting: SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.AL32UTF8 @@@ @@@<13>import data again. @@@ C:\>impdp 'sys/oracle@orcl as sysdba' transport_datafiles=E:\download\ORDERS_16 DIRECTORY=xp_dir DUMPFILE=expdat.dmp REMAP_SCHEMA=(oltp:hr) ... Import: Release 10.2.0.1.0 - Production on 星期六, 25 8月, 2012 15:00:36 Copyright (c) 2003, 2005, Oracle.  All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options 已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  'sys/********@orcl AS SYSDBA' transpo rt_datafiles=E:\download\ORDERS_16 DIRECTORY=xp_dir DUMPFILE=expdat.dmp REMAP_SC HEMA=(oltp:hr) 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK 处理对象类型 TRANSPORTABLE_EXPORT/TABLE 处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:00:39 成功完成 @@@ @@@<14>summary @@@     When we create the archiveture, we should think about issue about characterset first.    Any characterset conversion is danger for underlying data. Before you begin the conversion   the any data would be backuped.