A-A+

【传输表空间】使用 EXPDP/IMPDP工具的传输表空间完成数据迁移

2016年08月10日 Linux 数据库 暂无评论 阅读 863 次

本文通过实践演示,完整模拟一下使用EXPDP/IMPDP工具实现传输表空间的过程,供参考。
任务描述:将secdb1实例上的sec用户表空间tbs_sec的数据传输到secdb2实例上secooler用户下。

1.secdb1实例环境准备
1)创建待操作的表空间
[email protected]> create tablespace tbs_sec datafile '/u01/app/Oracle/oradata/secdb1/dfile/tbs_sec_01.dbf' size 5 m autoextend on;

Tablespace created.

2)重新创建sec用户并授权
[email protected]> drop user sec cascade;

User dropped.

[email protected]> create user sec identified by sec default tablespace tbs_sec;

User created.

[email protected]> grant connect,resource to sec;

Grant succeeded.

3)在sec用户下简单创建一张表并初始化一条记录
[email protected]> conn sec/sec
Connected.
[email protected]> create table t(x int);

Table created.

[email protected]> insert into t values (1);

1 row created.

[email protected]> commit;

Commit complete.

[email protected]> select * from t;

        X
----------
        1

2.检tbs_sec表空间是否“自包含”
[email protected]> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_sec',true);

PL/SQL procedure successfully completed.

[email protected]> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

OK,没有问题。

3.将表空间调整到只读状态
[email protected]> ALTER TABLESPACE tbs_sec read only;

Tablespace altered.

4.使用EXPDP工具完成表空间元素据导出
[email protected]> create directory dir_home as '/home/oracle';

Directory created.

[email protected]> grant read,write on directory dir_home to public;

Grant succeeded.

[[email protected] ~]$ expdp system/oracle1 directory=dir_home dumpfile=tbs_sec.dmp transport_tablespaces=tbs_sec transport_full_check=y

Export: Release 10.2.0.1.0 - Production on Saturday, 23 July, 2010 22:23:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dir_home dumpfile=tbs_sec.dmp transport_tablespaces=tbs_sec transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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/tbs_sec.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:23:39

5.将tbs_sec表空间对应的数据文件拷贝到secdb2实例对应的目录
[[email protected] ~]$ cp /u01/app/oracle/oradata/secdb1/dfile/tbs_sec_01.dbf /u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf

6.在secdb2实例上使用IMPDP工具完成表空间的导入
[email protected]> create user secooler identified by secooler;

User created.

[email protected]> grant connect,resource to secooler;

Grant succeeded.

[email protected]> create directory dir_home as '/home/oracle';

Directory created.

[email protected]> grant read,write on directory dir_home to public;

Grant succeeded.

[[email protected] ~]$ impdp system/oracle1 DUMPFILE=tbs_sec.dmp DIRECTORY=dir_home TRANSPORT_DATAFILES=/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA=(sec:secooler)

Import: Release 10.2.0.1.0 - Production on Saturday, 23 July, 2010 22:34:48

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=tbs_sec.dmp DIRECTORY=dir_home TRANSPORT_DATAFILES=/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA=(sec:secooler)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:34:52

7.在secdb2实例上验证数据导入效果
[email protected]> conn secooler/secooler
Connected.
[email protected]> select * from cat;

TABLE_NAME                    TABLE_TYPE
------------------------------ -----------
T                              TABLE

[email protected]> select * from t;

        X
----------
        1

[email protected]> select owner,tablespace_name from dba_tables where wner='SECOOLER' and table_name='T';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SECOOLER                      TBS_SEC

OK,使命完成。

8.最后记得将secdb1实例中tbs_sec表空间置为可读写模式
[email protected]> ALTER TABLESPACE tbs_sec read write;

Tablespace altered.

9.小结
使用EXPDP/IMPDP传输表空间功能可以比较便捷高效的完成数据迁移,善用之。
有关使用EXP/IMP工具完成传输表空间的操作方法,请参考如下文章,大同小异。
《【实验】利用可传输表空间技术实现数据的高效迁移》( 2016-08/133898.htm

更多Oracle相关信息见Oracle 专题页面 http:///?tid=12

标签:

给我留言

Copyright © SEARU.ORG 保留所有权利.   Theme  Ality 网站地图 360网站安全检测平台

用户登录

分享到: