当前位置：Oracle研究中心 > 运维DBA >
【学习笔记】Oracle传输表空间 OTTS跨平台传输表空间的详细步骤
时间:2016-12-24 21:33 来源:Oracle研究中心 作者:网络 点击: 次
天萃荷净
Oracle研究中心学习笔记：分享一篇关于OTTS Oracle传输表空间的使用笔记，详细记录了linux系统下的Oracle表空间传输到solaris系统的详细步骤。
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.检查数据库字符集
1 SQL> select userenv('language') from dual;
2
3 USERENV('LANGUAGE')
4 ----------------------------------------------------
5 AMERICAN_AMERICA.ZHS16GBK
2.检查ZONE时区
1 SQL> SELECT version FROM v$timezone_file;
2
3 VERSION
4 ----------
5 11
3.检查数据库endian
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
Oracle研究中心
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.检查数据库字符集
1 SQL> select userenv('language') from dual;
2
3 USERENV('LANGUAGE')
4 ----------------------------------------------------
5 AMERICAN_AMERICA.ZHS16GBK
2.检查ZONE时区
1 SQL> SELECT version FROM v$timezone_file;
2
3 VERSION
4 ----------
5 11
3.检查数据库endian
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
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle传输表空间 OTTS跨平台传输表空间的详细步骤
本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/1433.html
