4.2  查看检查结果

SQL> col violations for a70

SQL> select * from sys.transport_set_violations;

 

VIOLATIONS

--------------------------------------------------------------------------------

ORA-39907: 索引 IDXTBS 中SCOTT.APP2_TAB

(在表空间 )。

 

 

SQL>

 

结论： 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的SYS.APP2_TAB表，所以这里选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查

 

SQL> execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from transport_set_violations;

 

no rows selected

 

SQL>

 

结论： 此时这个表空间集已经不在违背自包含的条件，可以确定为一个可传输表空间集

 

 

5  产生可传输表空间集

5.1  使自包含的表空间集中的所有表空间变为只读状态

SQL> alter tablespace app1tbs read only;

 

Tablespace altered.

 

SQL> alter tablespace app2tbs read only;

 

Tablespace altered.

 

SQL> alter tablespace idxtbs read only;

 

Tablespace altered.

 

 

5.2  使用数据泵导出工具，导出要传输的各个表空间的元数据

5.2.1  确定导出目录

SQL> set line 9999

SQL> col directory_name for a25

SQL> col directory_path for a100

SQL> select directory_name,directory_path from dba_directories;

 

 

DIRECTORY_NAME            DIRECTORY_PATH

------------------------- --------------------------------------------

XMLDIR                    c:\ade\aime_dadvfm0254\oracle\rdbms\xml

ASMSRC                    F:\app\oracle\oradata\orcl

DATA_PUMP_DIR             F:\app\oracle\admin\orcl\dpdump\

ORACLE_OCM_CONFIG_DIR     F:\app\oracle\product\ccr\state

 

SQL>

 

 

 

SQL>

 

5.2.2  开始导出

C:\Users\Administrator>expdp system/lhr@orclxp dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log

 

Export: Release 11.2.0.1.0 - Production on 星期一 5 19:29:29 2015

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

连接到"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********@orclxp dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log

处理对象类型 TRANSPORTABLE_EXPORT/TABLE

处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS

处理对象类型 /卸载了主表 :

  F:\APP\ORACLE\ADMIN\ORCL\DPDUMP\EXPDAT.DMP

******************************************************************************

可传输表空间 APP1TBS 所需的数据文件:

  F:\APP\ORACLE\ORADATA\ORCL\APP1TBS.DBF

可传输表空间 APP2TBS 所需的数据文件:

  F:\APP\ORACLE\ORADATA\ORCL\APP2TBS.DBF

可传输表空间 IDXTBS 所需的数据文件:

  F:\APP\ORACLE\ORADATA\ORCL\IDXTBS.DBF

作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 19:30:07 成功完成

 

 

C:\Users\Administrator>

 

查看文件：

wps2A2D.tmp 

 

5.3  将数据文件拷贝到dpdump目录下

wps2A3D.tmp 

 

 

6  还原源库中的表空间为读/写模式

SQL> alter tablespace app1tbs read write;

 

Tablespace altered.

 

SQL> alter tablespace app2tbs read write;

 

Tablespace altered.

 

SQL> alter tablespace idxtbs read write;

 

Tablespace altered.

 

SQL>

 

至此，已和源库没有任何关系。

 

7  传输文件

这里需要传输转储元文件和数据文件到目标库

 

7.1  利用ftp工具传输转储元文件到目标库

 

[root@rhel6_lhr share-2]# cd dpdump/

[root@rhel6_lhr dpdump]# ll

total 30850

-rwxrwxrwx 1 root root 10493952 Jan  5 19:27 APP1TBS.DBF

-rwxrwxrwx 1 root root 10493952 Jan  5 19:27 APP2TBS.DBF

-rwxrwxrwx 1 root root   106496 Jan  5 19:30 EXPDAT.DMP

-rwxrwxrwx 1 root root 10493952 Jan  5 19:27 IDXTBS.DBF

-rwxrwxrwx 1 root root     1373 Jan  5 19:30 tts_export.log

[root@rhel6_lhr dpdump]#

 

7.2  查看目标库数据文件位置和导入目录

[oracle@rhel6 ~]$ env | grep ORACLE

ORACLE_UNQNAME=orcl

ORACLE_SID=orcl

ORACLE_BASE=/u01/app/oracle

ORACLE_HOSTNAME=192.168.59.129

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@rhel6 ~]$ export ORACLE_SID=orclasm

[oracle@rhel6 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 6 09:50:44 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> select name from v$datafile;

 

NAME

--------------------------------------------------------------------------------

+DATA/orclasm/datafile/system.256.868235071

+DATA/orclasm/datafile/sysaux.257.868235073

+DATA/orclasm/datafile/undotbs1.258.868235073

+DATA/orclasm/datafile/users.259.868235073

13 rows selected.

SQL> select directory_name,directory_path from dba_directories;

 

DIRECTORY_NAME   DIRECTORY_PATH

------------------------- ----------------------------------------------------------------------------------------------------

XMLDIR        /ade/b/2125410156/oracle/rdbms/xml

DATA_PUMP_DIR

ORACLE_OCM_CONFIG_DIR        /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

3 rows selected.

 

SQL>

 

7.3  拷贝文件到目标库相应位置并修改文件权限

 

[root@rhel6_lhr dpdump]# rm -rf /u01/app/oracle/admin/orclasm/dpdump/*

[root@rhel6_lhr dpdump]#

[root@rhel6_lhr dpdump]#

[root@rhel6_lhr dpdump]#

[root@rhel6_lhr dpdump]# mv * /u01/app/oracle/admin/orclasm/dpdump/

[root@rhel6_lhr dpdump]# ll /u01/app/oracle/admin/orclasm/dpdump/

total 30852

-rwxrwxrwx 1 root root 10493952 Jan  5 19:27 APP1TBS.DBF

-rwxrwxrwx 1 root root 10493952 Jan  5 19:27 APP2TBS.DBF

-rwxrwxrwx 1 root root   106496 Jan  5 19:30 EXPDAT.DMP

-rwxrwxrwx 1 root root 10493952 Jan  5 19:27 IDXTBS.DBF

-rwxrwxrwx 1 root root     1373 Jan  5 19:30 tts_export.log

[root@rhel6 dpdump]# chown oracle:oinstall APP1TBS.DBF

[root@rhel6 dpdump]# chown oracle:oinstall APP2TBS.DBF

[root@rhel6 dpdump]# chown oracle:oinstall IDXTBS.DBF

[root@rhel6 dpdump]# ll

total 30860

-rwxr-xr-x. 1 oracle oinstall 10493952 Jan  6 00:46 APP1TBS.DBF

-rwxr-xr-x. 1 oracle oinstall 10493952 Jan  6 00:46 APP2TBS.DBF

-rwxr-xr-x. 1 root   root       106496 Jan  6 00:46 EXPDAT.DMP

-rwxr-xr-x. 1 oracle oinstall 10493952 Jan  6 00:46 IDXTBS.DBF

-rw-r--r--. 1 oracle oinstall      236 Jan  6 00:52 par.f

-rwxr-xr-x. 1 root   root         1373 Jan  6 00:46 tts_export.log

-rw-r--r--. 1 oracle asmadmin      917 Jan  6 00:52 tts_import.log

[root@rhel6 dpdump]#