TTS实现数据库迁移，具有速度快、支持跨平台和跨版本等优点。本文记录了用TTS从10g single迁移到11g RAC的过程。 Source数据库版本和字符集设置： SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> select * from nls_database_parameters where parameter in (‘NLS_CHARACTERSET‘,‘NLS_NCHAR_CHARACTERSET‘); PARAMETER VALUE ------------------------------ ------------------------------ NLS_CHARACTERSET UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 Target数据库版本和字符集设置： SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 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 * from nls_database_parameters where parameter in (‘NLS_CHARACTERSET‘,‘NLS_NCHAR_CHARACTERSET‘); PARAMETER VALUE ------------------------------ -------------------- NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 1、检测平台差异 source： SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------- -------------- Linux x86 64-bit Little target： SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d 3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------- ------------------------------------------ Linux x86 64-bit Little 两者并无差异，无需进行平台格式转换。 2、Tablespace Self-Contained 检测 进行TTS的表空间必须满足自包含条件，如果不满足，则需要进行调整直至满足才可以进行TTS。 source： SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘tbs_postmall_data,tbs_postmall_idx‘, TRUE); PL/SQL procedure successfully completed. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected 出现0行，说明满足自包含条件。 3、Generate a Transportable Tablespace Set 生成TTS set之前，需要设置相关的表空间为read only模式。 SQL> alter tablespace tbs_postmall_data read only; Tablespace altered. SQL> alter tablespace tbs_postmall_idx read only; Tablespace altered. 生成相关表空间的metadata dumpfile oracle@wls112:/data/oracle/dump> expdp system dumpfile=postmall_metadata.dmp directory=dump transport_tablespaces=tbs_postmall_data,tbs_postmall_idx logfile=tts_export.log; Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 14 March, 2013 10:57:34 Copyright (c) 2003, 2007, Oracle. All rights reserved. Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=postmall_metadata.dmp directory=dump transport_tablespaces=tbs_postmall_data,tbs_postmall_idx logfile=tts_export.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/COMMENT Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS 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: /data/oracle/dump/postmall_metadata.dmp Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:57:51 4、Transport the Tablespace Set 把第三步生成的metadata dumpfile和相关表空间的datafile传输到target端。其中dumpfile传输到目标端的一个directory中，datafile则传送到一个临时目录(target为ASM存储)或者目标数据库的datafile目录(target为文件系统存储)。 oracle@wls112:/data/oracle/dump> scp postmall_metadata.dmp 172.24.138.101:/data/oracle/dump/ Password: postmall_metadata.dmp 100% 260KB 260.0KB/s 00:00 oracle@wls112:/data/oracle/dump> cd ../oradata/postmall/ oracle@wls112:/data/oracle/oradata/postmall> scp tbs_postmall_* 172.24.138.101:/data/oracle/dump/ Password: tbs_postmall_data01.dbf 100% 5120MB 33.0MB/s 02:35 tbs_postmall_data02.dbf 100% 5120MB 28.1MB/s 03:02 tbs_postmall_idx01.dbf 100% 5120MB 25.9MB/s 03:18 5、在目标端将datafile从文件系统传输至ASM中 不能直接用asm的cp命令来进行简单的复制，而需要用RMAN中的convert datafile功能来传输datafile oracle@postmallrac1:/data/oracle/dump> ls -l total 15744801 -rw-r--r-- 1 oracle asmadmin 1216 2013-03-14 09:53 import.log -rw-r----- 1 oracle oinstall 266240 2013-03-14 11:11 postmall_metadata.dmp -rw-r----- 1 oracle oinstall 520192 2013-03-13 12:06 postmall_user.dmp -rw-r----- 1 oracle oinstall 5368717312 2013-03-14 11:14 tbs_postmall_data01.dbf -rw-r----- 1 oracle oinstall 5368717312 2013-03-14 11:17 tbs_postmall_data02.dbf -rw-r----- 1 oracle oinstall 5368717312 2013-03-14 11:21 tbs_postmall_idx01.dbf -rw-r--r-- 1 oracle asmadmin 1582 2013-03-14 09:51 tts_import.log 用RMAN进行convert datafile oracle@postmallrac1:/data/oracle/dump> rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 14 11:26:51 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: POSTMALL (DBID=3816065011) RMAN> convert DATAFILE ‘/data/oracle/dump/tbs_postmall_data01.dbf‘ 2> db_file_name_convert="/data/oracle/dump/tbs_postmall_data01.dbf","+DATA"; Starting conversion at target at 2013-03-14 11:27:22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1155 instance=postmall1 device type=DISK channel ORA_DISK_1: starting datafile conversion input file name=/data/oracle/dump/tbs_postmall_data01.dbf converted datafile=+DATA/postmall/datafile/tbs_postmall_data.274.810041245 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:25 Finished conversion at target at 2013-03-14 11:29:50 RMAN> convert DATAFILE ‘/data/oracle/dump/tbs_postmall_data02.dbf‘ 2> db_file_name_convert="/data/oracle/dump/tbs_postmall_data02.dbf","+DATA"; Starting conversion at target at 2013-03-14 11:30:21 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=/data/oracle/dump/tbs_postmall_data02.dbf converted datafile=+DATA/postmall/datafile/tbs_postmall_data.275.810041421 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:45 Finished conversion at target at 2013-03-14 11:33:06 RMAN> convert DATAFILE ‘/data/oracle/dump/tbs_postmall_idx01.dbf‘ 2> db_file_name_convert="/data/oracle/dump/tbs_postmall_idx01.dbf","+DATA"; Starting conversion at target at 2013-03-14 12:19:58 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=/data/oracle/dump/tbs_postmall_idx01.dbf converted datafile=+DATA/postmall/datafile/tbs_postmall_idx.273.810044399 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:35 Finished conversion at target at 2013-03-14 12:22:33 在asmcmd里可以看到添加进去的datafile ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE FEB 25 15:00:00 Y EXAMPLE.264.807814653 DATAFILE UNPROT COARSE MAR 13 21:00:00 Y SYSAUX.257.807814555 DATAFILE UNPROT COARSE FEB 25 15:00:00 Y SYSTEM.256.807814555 DATAFILE UNPROT COARSE FEB 25 15:00:00 Y TBS_OGG.272.808070095 DATAFILE UNPROT COARSE MAR 14 11:00:00 Y TBS_POSTMALL_DATA.274.810041245 DATAFILE UNPROT COARSE MAR 14 11:00:00 Y TBS_POSTMALL_DATA.275.810041421 DATAFILE UNPROT COARSE MAR 14 12:00:00 Y TBS_POSTMALL_IDX.273.810044399 DATAFILE UNPROT COARSE FEB 25 15:00:00 Y TBS_ULECARD_DATA.270.807903909 DATAFILE UNPROT COARSE FEB 25 15:00:00 Y TBS_ULECARD_IDX.271.807903973 DATAFILE UNPROT COARSE FEB 25 15:00:00 Y UNDOTBS1.258.807814557 DATAFILE UNPROT COARSE MAR 13 22:00:00 Y UNDOTBS2.265.807814821 DATAFILE UNPROT COARSE FEB 25 15:00:00 Y USERS.259.807814557 6、Import the Tablespace Set 导入表空间之前，先把用户创建好。 SQL> create user postmall_user identified by postmall_pwd account unlock; SQL> grant connect,resource,dba to postmall_user; SQL> exit oracle@postmallrac1:/data/oracle/dump> impdp system dumpfile=postmall_metadata.dmp directory=dump logfile=tts_import.log; Import: Release 11.2.0.3.0 - Production on Thu Mar 14 13:23:07 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=postmall_metadata.dmp directory=dump transport_datafiles=+data/postmall/datafile/TBS_POSTMALL_DATA.274.810041245,+data/postmall/datafile/TBS_POSTMALL_DATA.275.810041421,+data/postmall/datafile/TBS_POSTMALL_IDX.273.810044399 logfile=tts_import.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/COMMENT Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:23:43 7、导入用户的其他object 因为system表空无法进行TTS，所以postmall_user在system表空间的object，比如sequences、PL/SQL packages等就没法通过TTS来迁移。所以到了最后，还需要用Data Pump把其他object给迁移过来。 source端： oracle@wls112:/data/oracle/dump> expdp system directory=dump schemas=postmall_user dumpfile=postmall_user.dmp; Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 14 March, 2013 13:34:16 Copyright (c) 2003, 2007, Oracle. All rights reserved. Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump schemas=postmall_user exclude=table,index dumpfile=postmall_user.dmp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /data/oracle/dump/postmall_user.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:34:35 传送到target端，在target端导入： oracle@postmallrac1:/data/oracle/dump> impdp system dumpfile=postmall_user.dmp directory=dump logfile=user_import.log Import: Release 11.2.0.3.0 - Production on Thu Mar 14 13:36:46 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=postmall_user.dmp directory=dump logfile=user_import.log Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"POSTMALL_USER" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39082: Object type ALTER_PROCEDURE:"POSTMALL_USER"."UPDATE_0124" created with compilation warnings Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 13:36:57 导入的时候，出现了两个错误，第一个"ORA-31684"是因为用户已经存在了，无需再导入。第二个错误"ORA-39082"是因为有个procedure编译告警，这个procedure已经废弃，无需理会。 用户postmall_user虽然已经创建，但是还需要手动修改该用户的default tablespace为新导入的TBS_POSTMALL_DATA SQL> alter user postmall_user default tablespace tbs_postmall_data; User altered. 8、检验数据 source端 SQL> show user USER is "POSTMALL_USER" SQL> select object_name,object_type,status from user_objects; OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- -------------------- ------- ORDER_LOTTERY TABLE VALID PRIZE_RECORD TABLE VALID IDX_OL_LOTTERY_TERM_END INDEX VALID IDX_ORDER_LOTTERY_STATUS INDEX VALID IDX_OL_LOTTERY_ONLYID_END INDEX VALID IDX_LOTTERY_NO INDEX VALID IDX_OL_ESCORDERID INDEX VALID IDX_OL_USR_PHONE_TYPE INDEX VALID PK_PRIZE_RECORD INDEX VALID IDX_PR_MOBILE INDEX VALID IDX_PR_UCNO INDEX VALID IDX_PR_CREATE_DESC INDEX VALID PK_T1 INDEX VALID T1 TABLE VALID UPDATE_0124 PROCEDURE INVALID PK_ORDER_LOTTERY INDEX VALID EMPLOYEES TABLE VALID PK_EMPLOYEES INDEX VALID IDX_EMPLOYEES_EMAIL INDEX VALID CREATE_INDEX_CCLR PROCEDURE VALID 20 rows selected. SQL> select count(*) from ORDER_LOTTERY; COUNT(*) ---------- 33330240 SQL> select count(*) from PRIZE_RECORD; COUNT(*) ---------- 111660 SQL> select count(*) from T1; COUNT(*) ---------- 2549760 SQL> select count(*) from employees; COUNT(*) ---------- 123 SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL FROM EMPLOYEES WHERE EMPLOYEE_ID<=10; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL ----------- -------------------- ------------------------- ------------------------- 1 杰森 伯恩 124@gmail.com 2 勒布朗 詹姆斯 124@gmail.com 3 科比 布莱恩特 124@gmail.com 4 沙克 奥尼尔 124@gmail.com 5 蒂姆 邓肯 124@gmail.com 6 迈克尔 乔丹 124@gmail.com 7 摩西 马龙 124@gmail.com 8 乔治 布什 124@gmail.com 9 hello baby 124@gmail.com 10 jay 刘杰 124@gmail.com 10 rows selected. target端： SQL> show user USER is "POSTMALL_USER" SQL> select object_name,object_type,status from user_objects; OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- -------------------- --------------------- UPDATE_0124 PROCEDURE INVALID CREATE_INDEX_CCLR PROCEDURE VALID IDX_PR_CREATE_DESC INDEX VALID PK_T1 INDEX VALID IDX_EMPLOYEES_EMAIL INDEX VALID PK_EMPLOYEES INDEX VALID IDX_PR_UCNO INDEX VALID IDX_PR_MOBILE INDEX VALID PK_PRIZE_RECORD INDEX VALID IDX_OL_USR_PHONE_TYPE INDEX VALID IDX_OL_ESCORDERID INDEX VALID IDX_LOTTERY_NO INDEX VALID IDX_OL_LOTTERY_ONLYID_END INDEX VALID IDX_ORDER_LOTTERY_STATUS INDEX VALID IDX_OL_LOTTERY_TERM_END INDEX VALID PK_ORDER_LOTTERY INDEX VALID T1 TABLE VALID EMPLOYEES TABLE VALID PRIZE_RECORD TABLE VALID ORDER_LOTTERY TABLE VALID 20 rows selected. SQL> select count(*) from ORDER_LOTTERY; COUNT(*) ---------- 33330240 SQL> select count(*) from PRIZE_RECORD; COUNT(*) ---------- 111660 SQL> select count(*) from T1; COUNT(*) ---------- 2549760 SQL> select count(*) from employees; COUNT(*) ---------- 123 SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL FROM EMPLOYEES WHERE EMPLOYEE_ID<=10; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL ----------- ---------- ---------- --------------- 1 杰森 伯恩 124@gmail.com 2 勒布朗 詹姆斯 124@gmail.com 3 科比 布莱恩特 124@gmail.com 4 沙克 奥尼尔 124@gmail.com 5 蒂姆 邓肯 124@gmail.com 6 迈克尔 乔丹 124@gmail.com 7 摩西 马龙 124@gmail.com 8 乔治 布什 124@gmail.com 9 hello baby 124@gmail.com 10 jay 刘杰 124@gmail.com 10 rows selected. 数据均一致，中文也没有出现乱码。数据库迁移成功。 小结： 一、两边数据库的字符集(包括NLS_CHARACTERSET和NLS_NCHAR_CHARACTERSET)最好完全一样，如果不一样，也要保证source端的字符集是target端的字符集的真子集，还要加上一些限制，具体可以参考官方文档的说明。 二、在source端生成和传送TTS Set之前必须要设置相关表空间为read only模式。 三、如果从filesystem→filesystem,直接把datafile传输到目标数据库的datafile路径即可。如果从filesystem→asm，则需要多一RMAN的convert datafile操作。如果是asm→asm则还没进行测试。 --------------------- 作者：jasonliu_2012 来源：CSDN 原文：https://blog.csdn.net/jasonliu_2012/article/details/8688209 版权声明：本文为博主原创文章，转载请附上博文链接！
原文：https://www.cnblogs.com/chendian0/p/10536254.html