如何解析datapunp传输表空间
这篇文章将为大家详细讲解有关如何解析data punp传输表空间,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。
站在用户的角度思考问题,与客户深入沟通,找到谯城网站设计与谯城网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:网站制作、成都做网站、企业官网、英文网站、手机端网站、网站推广、域名注册、雅安服务器托管、企业邮箱。业务覆盖谯城地区。
1,查看待传输表空间example是否违反了独立性规则
[oracle@snow ~]$ export ORACLE_SID=ora12c
[oracle@snow ~]$ sqlplus / as sysdba
SYS@ora12c >exec dbms_tts.transport_set_check('EXAMPLE',TRUE);
PL/SQL procedure successfully completed.
SYS@ora12c >select * from transport_set_violations;
no rows selected
2,将表空间example置为只读
SYS@ora12c >alter tablespace example read only;
Tablespace altered.
源端数据文件路径
SYS@ora12c >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora12c/system01.dbf
/u01/app/oracle/oradata/ora12c/example01.dbf
/u01/app/oracle/oradata/ora12c/sysaux01.dbf
/u01/app/oracle/oradata/ora12c/undotbs01.dbf
/u01/app/oracle/oradata/ora12c/users01.dbf
目标端数据文件路径
SYS@OCM12C >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_system_8xf29zsz_.dbf
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_sysaux_8xf1zgd7_.dbf
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_undotbs1_8xf2pgsg_.dbf
/12c/app/oracle/oradata/OCM12C/datafile/o1_mf_users_8xf2p505_.dbf
3,将源端的表空间数据文件scp到目标端数据文件路径
SYS@ora12c >!scp /u01/app/oracle/oradata/ora12c/example01.dbf 172.16.228.9:/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf
oracle@172.16.228.9's password:
example01.dbf 100% 323MB 32.3MB/s 00:10
SYS@ora12c >exit
4,使用数据泵导出表空间example的元数据scp到目标端的数据泵目录(和源端一样也是设置为dp_dir=/home/oracle)
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=trans.dmp transport_tablespaces=example
Export: Release 12.1.0.1.0 - Production on Mon Feb 9 12:45:28 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DP"."SYS_EXPORT_TRANSPORTABLE_01": dp/******** directory=dp_dir dumpfile=trans.dmp transport_tablespaces=example
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "DP"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DP.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/trans.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
/u01/app/oracle/oradata/ora12c/example01.dbf
Job "DP"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Feb 9 12:46:34 2015 elapsed 0 00:01:04
[oracle@snow ~]$ scp trans.dmp 172.16.228.9:/home/oracle
The authenticity of host '172.16.228.9 (172.16.228.9)' can't be established.
RSA key fingerprint is 70:7d:ec:8f:42:44:21:c9:24:d3:fc:23:1e:20:4b:ec.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.228.9' (RSA) to the list of known hosts.
oracle@172.16.228.9's password:
trans.dmp 100% 3172KB 3.1MB/s 00:00
6,将元数据导入目标端数据库
[oracle@test ~]$ impdp hr/hr directory=dp_dir dumpfile=trans.dmp transport_datafiles=/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf
Import: Release 12.1.0.1.0 - Production on Wed Feb 25 18:01:16 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "HR"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TRANSPORTABLE_01": hr/******** directory=dp_dir dumpfile=trans.dmp transport_datafiles=/12c/app/oracle/oradata/OCM12C/datafile/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user PM does not exist in the database
Job "HR"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fata
错误提示没有PM用户,创建该用户后重新执行impdp陆续提示SH,oe,ix用户不存在,逐个创建上述用户。
SYS@OCM12C >create user pm identified by pm;
SYS@OCM12C >create user sh identified by sh;
SYS@OCM12C >create user oe identified by oe;
SYS@OCM12C >create user ix identified by ix;
添加用户后再次执行impdp成功
7,分别将源端和目标端端将表空间修改为read write状态
SYS@OCM12C >alter tablespace example read write;
Tablespace altered.
SYS@OCM12C >select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SYS@ora12c >alter tablespace example read write;
Tablespace altered.
SYS@ora12c >select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
关于如何解析data punp传输表空间就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
当前文章:如何解析datapunp传输表空间
URL网址:http://myzitong.com/article/pppesd.html