本来没想过发布这个文章，只是周边有一朋友工作中遇到合并数据库的情况，他是通过expdp提取出五个库对象，然后impdp到新库里面。我觉得这种方法特别耗时，尤其在数据量比较大的时候。这种时候我觉得采用表空间传输方法效率更高点，当然我们还可以使用RMAN DUPLICATE方式。下面就把TRANSPORT TABLESPACE一测试案例贴出来分享。
传输表空间有三个基本要求：
1）要传输的表空间是自包含的；
2）要传输的表空间是只读的；
3）源数据库与目标数据库的字符集相同。
跨平台表空间传输还需要考虑字节顺序和平台。
引用eygle的原文：
数据文件之所以不能跨平台，主要是由于不同平台的字节顺序不同。
在各种计算机体系结构中，由于对于字、字节等的存储机制有所不同，通信双方交流的信息单元(比特、字节、字、双字等)应该以什么样的顺序进行传送就成了一个问题，如果不达成一致的规则，通信双方将无法进行正确的编译码从而导致通信失败。
查看当前平台信息方法：
Col platform_name for a40
Set line 120
SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id;
我们也可以通过数据库查询oracle支持的平台转换：
sys@LUOCS11G> Col platform_name for a40
sys@LUOCS11G> Select * from v$transportable_platform order by 3;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
3 HP-UX (64-bit) Big
6 AIX-Based Systems (64-bit) Big
18 IBM Power Based Linux Big
2 Solaris[tm] OE (64-bit) Big
4 HP-UX IA (64-bit) Big
16 Apple Mac OS Big
1 Solaris[tm] OE (32-bit) Big
9 IBM zSeries Based Linux Big
17 Solaris Operating System (x86) Little
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
8 Microsoft Windows IA (64-bit) Little
21 Apple Mac OS (x86-64) Little
11 Linux IA (64-bit) Little
5 HP Tru64 UNIX Little
10 Linux IA (32-bit) Little
7 Microsoft Windows IA (32-bit) Little
15 HP Open VMS Little
20 rows selected.
====== TEST STARTING ======
Source database:
Windows 7 64 bit – 11.2.0.1 db version
col platform_name for a40
set line 120
SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ---------------------------
12 Microsoft Windows x86 64-bit Little
Destination Database:
Linux 32 bit – 11.2.0.1 db version
col platform_name for a40
set line 120
SELECT A.platform_id, A.platform_name, B.endian_format
FROM v$database A, v$transportable_platform B
WHERE B.platform_id (+) = A.platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
10 Linux IA (32-bit) Little
Setup:
SQL> create tablespace L datafile 'E:\APP\LUOCS\ORADATA\LUOCS\l01.dbf' size 10M;
表空间已创建。
SQL> grant resource, connect to l identified by oracle;
授权成功。
SQL> alter user l default tablespace l;
用户已更改。
SQL> conn l/oracle
已连接。
SQL> create table t1 (
2 id number,
3 name varchar2(50)
4 );
表已创建。
SQL> conn / as sysdba
已连接。
SQL> insert into l.t1
2 select object_id,
3 object_name
4 from dba_objects
5 where rownum <= 10000
6 /
已创建10000行。
SQL> select count(*) from l.t1;
COUNT(*)
----------
10000
SQL> commit;
提交完成。
SQL> conn l/oracle
已连接。
SQL> create index idx_id on t1(id);
索引已创建。
Source database:
SQL> conn / as sysdba
已连接。
SQL> exec dbms_tts.transport_set_check(ts_list => 'L', incl_constraints => TRUE,
full_check => FALSE);
PL/SQL 过程已成功完成。
SQL> SELECT * FROM SYS.transport_set_violations;
未选定行
-- 满足自包含原则。
SQL> alter tablespace l read only;
表空间已更改。
导出要传输的表空间：
C:\>exp '/ as sysdba' tablespaces=l transport_tablespace=y file=d:\l_exp.dmp
Export: Release 11.2.0.1.0 - Production on 星期二 9月 25 13:33:32 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 AL32UTF8 字符集 (可能的字符集转换)
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 L...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表 T1
EXP-00091: 正在导出有问题的统计信息。
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
导出成功终止, 但出现警告。
SQL> alter tablespace l read write;
表空间已更改。
使用rman的convert命令转换文件格式：
C:\>rman target /
恢复管理器: Release 11.2.0.1.0 - Production on 星期二 9月 25 13:42:49 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到目标数据库: LUOCS (DBID=578752031)
RMAN> convert tablespace l to platform 'Linux IA (32-bit)' format 'd:\%N%f';
启动 conversion at source 于 25-9月 -12
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=136 设备类型=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00005 名称=E:\APP\LUOCS\ORADATA\LUOCS\L01.DBF
已转换的数据文件 = D:\L5
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at source 于 25-9月 -12
拷贝L5和l_exp.dmp文件到目标数据库服务器上。略！
Destination Database:
[oracle@localhost ~]$ ll L5 l_exp.dmp
-rw-r--r-- 1 oracle oinstall 10493952 09-25 15:31 L5
-rw-r--r-- 1 oracle oinstall 4096 09-25 15:31 l_exp.dmp
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 15:36:12 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: LUOCS11G (DBID=1468663667)
RMAN> convert datafile '/home/oracle/L5' db_file_name_convert '/home/oracle/L5','/u01/app/oracle/oradata/luocs11g/L01.dbf';
Starting conversion at target at 25-SEP-2012 15:38:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/L5
converted datafile=/u01/app/oracle/oradata/luocs11g/L01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 25-SEP-2012 15:38:09
创建相应的用户：
SQL> create user l identified by oracle;
User created.
SQL> grant create session, create table to l;
Grant succeeded.
执行导入：
先确保客户端字符集：
[oracle@localhost ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@localhost ~]$ imp \'/ as sysdba\' tablespaces=l transport_tablespace=y file=/home/oracle/l_exp.dmp datafiles=/u01/app/oracle/oradata/luocs11g/L01.dbf
Import: Release 11.2.0.1.0 - Production on Tue Sep 25 15:42:21 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing L's objects into L
. . importing table "T1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
这时候表空间已经成功传输到目标数据库中：
SQL> select name from v$datafile where name like '%L%';
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/luocs11g/L01.dbf
再查数据：
SQL> select count(*) from l.t1;
COUNT(*)
----------
10000
这时候导入的表空间为read only状态，确认无误可以改为读写模式：
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'L';
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
L READ ONLY
SQL> alter tablespace L read write;
Tablespace altered.
====== THE END ======
后语：本测试案例中，我使用了exp/imp方式，当然我们完全可以使用expdp/impdp 数据泵来实现。
另外，如果不是跨平台跨字节顺序，那么传输Transport Tablespace操作简单了很多，所以选择此方法做数据迁移，值得推荐。
下面是不跨平台的迁移测试案例：
一、创建测试表空间
SQL> create tablespace testdata datafile '/oradata/testdata01.dbf' size 10M;
Tablespace created.
SQL> create tablespace testindex datafile '/oradata/testindex01.dbf' size 5M;
Tablespace created.
SQL> conn jscn/jscn
Connected.
SQL> create table test (id number) tablespace testdata;
Table created.
SQL> create index ind_id on test(id) tablespace testindex;
Index created.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
二、测试开始
1、检查支持的平台
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
20 Solaris Operating System (x86-64 Little
)
19 HP IA Open VMS Little
19 rows selected.
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
--------------
Linux x86 64-bit
Little
2、Pick a Self-Contained Set of Tablespaces
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('testdata,testindex', TRUE);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
3、Generate a Transportable Tablespace Set
3.1 Make all tablespaces in the set you are copying read-only.
SQL> ALTER TABLESPACE testdata READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE testindex READ ONLY;
Tablespace altered.
3.2Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/home/oracle/dpump_dir';
Directory created.
[oracle@SHOW-100-12 ~]$ expdp system/sys DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = testdata,testindex TRANSPORT_FULL_CHECK=Y
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 15 May, 2012 15:12:01
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
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=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = testdata,testindex TRANSPORT_FULL_CHECK=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
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/dpump_dir/expdat0515.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:12:20
3.3 在目标中创建directory，拷贝文件到目标数据库文件
[oracle@SHOW-100-12 oradata]$ scp test* 192.168.8.201:/oradata/
Address 192.168.8.201 maps to bogon, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.8.201''s password:
testdata01.dbf 100% 10MB 10.0MB/s 00:01
testindex01.dbf 100% 5128KB 5.0MB/s 00:00
[oracle@IM-8-201 ~]$ mkdir /home/oracle/dpump_dir
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/home/oracle/dpump_dir';
3.4 导入生成表空间
[oracle@SHOW-100-12 oradata]$ impdp system/sys@8.201 DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=/oradata/testdata01.dbf,/oradata/testindex01.dbf REMAP_SCHEMA=jscn:security
Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 15 May, 2012 16:44:51
Copyright (c) 2003, 2007, Oracle. All rights reserved.
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
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********/@8.201 DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=/oradata/testdata01.dbf,/oradata/testindex01.dbf REMAP_SCHEMA=jscn:security
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:21:47
注意：这里要求目标数据库和源数据库字符集必须相同
如何修改字符集，参考如下
http://blog.csdn.net/rulev5/article/details/7020134
3.5 验证数据
[oracle@IM-8-201 dpump_dir]$ sqlplus / as sysdba
SQL> conn security/security
SQL> select * from test;
ID
----------
1
2
转载于:https://www.cnblogs.com/Clark-cloud-database/p/7813702.html
146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
