Oracle传输表空间

2025-12-26 13:32:46

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

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

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)

----------

自此，传输并将算是完成

声明：本网站引用、摘录或转载内容仅供网站访问者交流或参考，不代表本站立场，如存在版权或非法内容，请联系站长删除，联系邮箱：site.kefu@qq.com。