作者：david_zhang@sh 【转载时请以超链接形式标明文章】
链接：http://www.cnblogs.com/david-zhang-index/archive/2012/08/12/2634461.html
本文演示跨操作系统迁移表空间：
source os：
1 [oracle@Silent ~]$ more /etc/redhat-release 2 Red Hat Enterprise Linux Server release 5.3 (Tikanga) 64bit 3 4 SQL> select * from v$version; 5 6 BANNER 7 -------------------------------------------------------------------------------- 8 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 9 PL/SQL Release 11.2.0.1.0 - Production 10 CORE 11.2.0.1.0 Production 11 TNS for Linux: Version 11.2.0.1.0 - Production 12 NLSRTL Version 11.2.0.1.0 - Production
target os：
1 bash-3.2$ more /etc/release 2 Oracle Solaris 10 8/11 s10x_u10wos_17b X86 64bit 3 Copyright (c) 1983, 2011, Oracle and/or its affiliates. All rights reserved. 4 Assembled 23 August 2011 5 6 SQL> select * from v$version; 7 8 BANNER 9 -------------------------------------------------------------------------------- 10 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 11 PL/SQL Release 11.2.0.1.0 - Production 12 CORE 11.2.0.1.0 Production 13 TNS for Solaris: Version 11.2.0.1.0 - Production 14 NLSRTL Version 11.2.0.1.0 - Production
begin：
1.check character set at source and target os with under commands
1 SQL> select userenv('language') from dual; 2 3 USERENV('LANGUAGE') 4 ---------------------------------------------------- 5 AMERICAN_AMERICA.ZHS16GBK
2.check time zone at source and target os with under commands
1 SQL> SELECT version FROM v$timezone_file; 2 3 VERSION 4 ---------- 5 11
3.check transport tablespace access platforms and endianness at source and target os with under commands
1 SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp,V$DATABASE d WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME; 2 3 SQL> PLATFORM_NAME ENDIAN_FORMAT 4 5 ----------------------------- -------------- 6 Linux x86 64-bit Little
SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp,V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT ---------------------------------------- -------------- Solaris Operating System (x86-64) Little
4.创建测试表空及用户
source：
1 SQL> create tablespace ttbs1 datafile '/u01/app/oracle/oradata/silent/ttbs1.dbf' size 100m; 2 3 Tablespace created.
1 SQL> create user test1 identified by test default tablespace ttbs1; 2 3 User created. 4 5 SQL> grant connect,resource to test1; 6 7 Grant succeeded.
target：
1 SQL> create tablespace ttbs2 datafile '/u01/app/oracle/oradata/sun/ttbs2.dbf' size 100m; 2 3 Tablespace created.
SQL> create user test2 identified by test default tablespace ttbs2; User created. SQL> grant connect,resource to test2; Grant succeeded.
5.test1用户登录创建表及导入数据
1 SQL> create table ttbs as select * from all_objects; 2 3 Table created. 4 5 SQL> select * from tab; 6 7 TNAME TABTYPE CLUSTERID 8 ------------------------------ ------- ---------- 9 TTBS TABLE 10 11 SQL> select count(*) from ttbs; 12 13 COUNT(*) 14 ---------- 15 55624
6.check tablespace contain-self
1 SQL> exec dbms_tts.transport_set_check('ttbs1',true); 2 3 PL/SQL procedure successfully completed. 4 5 SQL> select * from transport_set_violations; 6 7 no rows selected
#如果不符合规定的，这里会显示的相关的信息。比如：
1 SQL> select * from transport_set_violations; 2 VIOLATIONS 3 ----------------------------------------------------------------------------- 4 ORA-39917: 可插入集内不允许使用 SYS 拥有的对象 TTBS1 (在表空间 TTBS1 中)
7.set tablespace ttbs1 read only
1 SQL> alter tablespace ttbs1 read only; 2 3 Tablespace altered.
8.平台转换
soruce转换：
1 RMAN> convert tablespace 'TTBS1'to platform="Solaris Operating System (x86-64)" db_file_name_convert='/u01/app/oracle/oradata/silent/ttbs1.dbf','/tmp/ttbs1.dbf'; 2 3 Starting conversion at source at 12-AUG-12 4 using channel ORA_DISK_1 5 channel ORA_DISK_1: starting datafile conversion 6 input datafile file number=00005 name=/u01/app/oracle/oradata/silent/ttbs1.dbf 7 converted datafile=/tmp/ttbs1.dbf 8 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16 9 Finished conversion at source at 12-AUG-12
如果在target端转换，方法如下:
1 RMAN> CONVERT DATAFILE '/u01/app/oracle/oradata/sun/ttbs1.dbf' TO PLATFORM="Solaris Operating System (x86-64)" FROM PLATFORM="Linux x86 64-bit" DB_FILE_NAME_CONVERT="/u01/app/oracle/oradata/sun/","/tmp/"; 2 3 Starting conversion at target at 12-AUG-12 4 using channel ORA_DISK_1 5 channel ORA_DISK_1: starting datafile conversion 6 input file name=/u01/app/oracle/oradata/sun/ttbs1.dbf 7 converted datafile=/tmp/ttbs1.dbf 8 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:14 9 Finished conversion at target at 12-AUG-12
9.将dump文件及生成的/tmp/ttbs1.dbf拷贝到target，将dump放在dpdir目录下，将数据文件放在target的数据文件目录下（注:数据文件可其他位置，只要oracle可以读取即可）
10.在target端导入
1 bash-3.2$ impdp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/sun/ttbs1.dbf' directory=dpdir dumpfile=ttbs.dmp remap_tablespace=ttbs1:ttbs2 remap_schema=test1:test2 logfile=ttbs1.log 2 3 Import: Release 11.2.0.1.0 - Production on Mon Aug 13 22:46:54 2012 4 5 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 6 7 Username: /as sysdba 8 9 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded 12 Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA TRANSPORT_DATAFILES=/u01/app/oracle/oradata/sun/ttbs1.dbf directory=dpdir dumpfile=ttbs.dmp remap_tablespace=ttbs1:ttbs2 remap_schema=test1:test2 logfile=ttbs1.log 13 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 14 Processing object type TRANSPORTABLE_EXPORT/TABLE 15 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 16 Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:47:16
备注：此步试验我重新做一下，原因是我想用到参数remap_tablespace=ttbs1:ttbs2，第一次失败的原因是我已经在target端建好ttbs2表空间，所以在导入的时候报错"ttbs2表空间已存在"，后来我删除了ttbs2表空间，重新导入成功，也就是说在导入的时候到自动创建新的表空间而无需事先建好。
如果不加remap_tablespace参数直接导入的话，结果是这样：
1 bash-3.2$ impdp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/sun/ttbs1.dbf' directory=dpdir dumpfile=ttbs.dmp remap_schema=test1:test2 logfile=ttbs1.log 2 Import: Release 11.2.0.1.0 - Production on Mon Aug 13 22:25:32 2012 3 4 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 5 6 Username: /as sysdba 7 8 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 9 With the Partitioning, OLAP, Data Mining and Real Application Testing options 10 Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded 11 Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA TRANSPORT_DATAFILES=/u01/app/oracle/oradata/sun/ttbs1.dbf directory=dpdir dumpfile=ttbs.dmp remap_schema=test1:test2 logfile=ttbs1.log 12 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 13 Processing object type TRANSPORTABLE_EXPORT/TABLE 14 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 15 Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:27:03 16 17 bash-3.2$ exit 18 exit 19 20 SQL> conn test2/test 21 Connected. 22 SQL> select table_name,tablespace_name from user_tables; 23 24 TABLE_NAME TABLESPACE_NAME 25 ------------------------------ ------------------------------ 26 TTBS TTBS1
11.验证操作正确性
1 SQL> conn test2/test 2 Connected. 3 SQL> select * from tab; 4 5 TNAME TABTYPE CLUSTERID 6 ------------------------------ ------- ---------- 7 TTBS TABLE 8 9 SQL> select count(*) from ttbs; 10 11 COUNT(*) 12 ---------- 13 55624 14 15 SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME='TEST2'; 16 17 USERNAME DEFAULT_TABLESPACE 18 ------------------------------ ------------------------------ 19 TEST2 TTBS2 20 21 SQL> select table_name,tablespace_name from user_tables; 22 23 TABLE_NAME TABLESPACE_NAME 24 ------------------------------ ------------------------------ 25 TTBS TTBS2
