一、传输表空间概述
在Oracle环境中,有很多方法可以对数据进行备份和传输,像是exp/imp、expdp/impdp和rman等。而可传输表空间(Transportable Tablespace)也是方法之一。 传输表空间技术在oracle9i开始出现, 由于传输表空间只是复制包含实际数据的数据文件到目标数据库的指定位置,而使用数据泵则是传输表空间对象的元数据到目标数据库。所以传输表空间的速度一般较快。
二、传输表空间实验
1.传输前的检查
检查平台
若是平台不同,需要对导出的文件进行转换再导入。这里的两个平台都是Linux IA (32-bit) 。
SQL> select d.platform_name, tp.endian_format
from v$transportable_platform tp, v$database d
where tp.platform_name = d.platform_name; 2 3
PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------- --------------
Linux IA (32-bit) Little
可以通过下面语句查看oracle支持那些平台的传输表空间(可以不同平台之间进行传输)
SQL> col PLATFORM_NAME format a30
SQL> select * from v$transportable_platform order by platform_name;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
6 AIX-Based Systems (64-bit) Big
16 Apple Mac OS Big
21 Apple Mac OS (x86-64) Little
19 HP IA Open VMS Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
18 IBM Power Based Linux Big
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86- Little
64)
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
20 rows selected.
检查表空间是否自包含
SQL> exec dbms_tts.transport_set_check('RCAT', incl_constraints=>true, full_check=>true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
这里的表没有数据,可见是该表空间是自包含的。
2、将表空间的状态改为只读
SQL> alter tablespace rcat read only;
Tablespace altered.
3、进行表空间导出
[oracle@LINFAN2~]$ expdp system directory=DATA_PUMP_DIR dumpfile=cs_rcat.dmp logfile=tts_rcat.log transport_tablespaces=rcat transport_full_check=y
Export: Release 11.2.0.3.0 - Production on Thu Feb 23 16:13:18 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password: oracle
。。。。。。。。。。。
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:14:12
导出成功
4、将导出的文件传送到另一个平台上
[oracle@LINFAN2 ~]$ scp /u01/app/oracle/admin/TOMATO/dpdump/cs_rcat.dmp odd:/u01/app/oracle/admin/POTATO/dpdump/
oracle@linfan's password:
tts_rcat.dmp 100% 1864KB 1.8MB/s 00:00
[oracle@EVEN ~]$ scp /u01/app/oracle/oradata/TOMATO/rcat.dbf odd:/u01/app/oracle/POTATO/oradata
oracle@linfan's password:
rcat.dbf 100% 113MB 22.6MB/s 00:05
导出完就可以把表空间改为读写模式了
SQL> alter tablespace rcat read write;
Tablespace altered.
5、建立rman用户,并赋予权限
SQL> create user rman identified by rman;
User created.
SQL> grant connect, resource to rman;
Grant succeeded.
6、开始传输
oracle@odd-LINFAN ~]$ impdp system directory=data_pump_dir dumpfile=tts_rcat.dmp logfile=cs_import.log transport_datafiles=/u01/app/oracle/oradata/POTATO/rcat.dbf
Import: Release 11.2.0.3.0 - Production on Thu Feb 23 16:38:52 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
。。。。。。。。
ORA-39082: Object type TRIGGER:"RMAN"."SCRL_TRIGGER" created with compilation warnings
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 4 error(s) at 16:39:13
完成导入,这里报的几个waring是因为触发器没有出送过来
7、检查数据是否传送过来了
SQL> select count(1) from rman.bp;
COUNT(1)
----------
11
自此,传输并将算是完成了
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31386161/viewspace-2134437/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31386161/viewspace-2134437/