Export(源数据库)
创建存放dmp的文件夹
[root@mycplmdb01 u01]# mkdir export [root@mycplmdb01 u01]# chown -R oracle:oinstall export [root@mycplmdb01 u01]#
使用 sys 用户在数据库创建文件夹对象,并授权
[oracle@mycplmdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 17 10:11:41 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -- 创建文件夹 SQL> create directory erp as '/u01/export'; Directory created. -- 授权 SQL> grant read, write on directory erp to public; Grant succeeded. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@mycplmdb01 ~]$
开始导出指定用户(erp), 将自动导出到文件夹对象对应的路径(使用拥有dba角色的用户导出)
[oracle@mycplmdb01 ~]$ expdp erp/123456 directory=erp dumpfile=expdp_20201117.dmp logfile=expdp_log_20201117.log schemas=erp Export: Release 12.1.0.2.0 - Production on Tue Nov 17 10:17:39 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "ERP"."SYS_EXPORT_SCHEMA_01": erp/******** directory=erp dumpfile=expdp_20201117.dmp logfile=expdp_log_20201117.log schemas=erp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 122.5 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/JOB . . exported "ERP"."F573002L" 23.70 MB 45326 rows . . exported "ERP"."F3002Z1" 22.77 MB 43116 rows . . exported "ERP"."SOURCE_BOM" 16.28 MB 71814 rows . . exported "ERP"."F3003Z1" 11.49 MB 21804 rows . . exported "ERP"."SOURCE_ITEM" 6.346 MB 10885 rows . . exported "ERP"."BUILD_BOM" 5.868 MB 57630 rows . . exported "ERP"."SOURCE_BOM_REDLINE_ACTION" 4.602 MB 62638 rows . . exported "ERP"."F573002M" 3.959 MB 50020 rows . . exported "ERP"."F0005" 1.757 MB 7075 rows . . exported "ERP"."F4101Z1" 1.729 MB 1510 rows . . exported "ERP"."TBL_ERP_SYS_LOG" 1.204 MB 11028 rows . . exported "ERP"."SOURCE_AFFECTED_ITEMS" 475.0 KB 2401 rows . . exported "ERP"."F4105Z1" 309.3 KB 956 rows . . exported "ERP"."F554102T" 252.8 KB 1104 rows . . exported "ERP"."F4101Z1_BAK" 167.0 KB 41 rows . . exported "ERP"."F554102A" 81.17 KB 1121 rows . . exported "ERP"."SOURCE_CHANGE" 162.8 KB 119 rows . . exported "ERP"."DEFAULT_ATTRIBUTE_MAPPING" 26.47 KB 499 rows . . exported "ERP"."F554102E" 21.78 KB 116 rows . . exported "ERP"."ITEM_MASTER_TYPE_MAPPING" 10.64 KB 68 rows . . exported "ERP"."JDE_ATTRIBUTE_MAPPING" 34.57 KB 467 rows . . exported "ERP"."META_XML_DB" 19.51 KB 241 rows . . exported "ERP"."UDC_ATTRIBUTE_MAPPING" 6.765 KB 54 rows Master table "ERP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ERP.SYS_EXPORT_SCHEMA_01 is: /u01/export/expdp_20201117.dmp Job "ERP"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 17 10:18:17 2020 elapsed 0 00:00:37
查看当前schema 的表空间和临时表空间(用于表空间、临时表空间映射使用)
[oracle@mycplmdb01 ~]$ sqlplus erp/123456@agile9 SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 17 11:05:50 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Nov 17 2020 11:02:49 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select username,default_tablespace,temporary_tablespace from user_users; USERNAME -------------------------------------------------------------------------------- DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ERP SYSTEM TEMP SQL>
Import(目标数据库)
创建存放dmp的文件夹,并放入expdp_20201117.dmp
[oracle@PLMDB u01]$ clear [oracle@PLMDB u01]$ pwd /u01 [oracle@PLMDB u01]$ mkdir import [oracle@PLMDB u01]$ chown -R oracle:oinstall import [oracle@PLMDB u01]$
使用 sys 用户在数据库创建文件夹对象,并授权
[oracle@PLMDB u01]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 17 11:29:26 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create directory erp as '/u01/import'; Directory created. SQL> grant read, write on directory erp to public; Grant succeeded. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@PLMDB u01]$
查看对应目标Schema的表空间和临时表空间
[oracle@PLMDB u01]$ sqlplus agiletl/Mflex2020@agile9 SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 17 11:36:27 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Oct 24 2020 21:34:30 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select username,default_tablespace,temporary_tablespace from user_users; USERNAME -------------------------------------------------------------------------------- DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ AGILETL ADAPTOR_DATA_TBS ADAPTOR_TMP_TBS SQL>
用system用户导入指定文件(用拥有dba角色的用户导入)
[oracle@PLMDB ~]$ impdp system/manager directory=erp dumpfile=expdp_20201117.dmp logfile=impdp_log_20201117.log remap_schema=erp:agiletl remap_tablespace=SYSTEM:ADAPTOR_DATA_TBS,TEMP:ADAPTOR_TMP_TBS Import: Release 12.2.0.1.0 - Production on Tue Nov 17 13:07:11 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=erp dumpfile=expdp_20201117.dmp logfile=impdp_log_20201117.log remap_schema=erp:agiletl remap_tablespace=SYSTEM:ADAPTOR_DATA_TBS,TEMP:ADAPTOR_TMP_TBS Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"AGILETL" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "AGILETL"."F573002L" 23.70 MB 45326 rows . . imported "AGILETL"."F3002Z1" 22.77 MB 43116 rows . . imported "AGILETL"."SOURCE_BOM" 16.28 MB 71814 rows . . imported "AGILETL"."F3003Z1" 11.49 MB 21804 rows . . imported "AGILETL"."SOURCE_ITEM" 6.346 MB 10885 rows . . imported "AGILETL"."BUILD_BOM" 5.868 MB 57630 rows . . imported "AGILETL"."SOURCE_BOM_REDLINE_ACTION" 4.602 MB 62638 rows . . imported "AGILETL"."F573002M" 3.959 MB 50020 rows . . imported "AGILETL"."F0005" 1.757 MB 7075 rows . . imported "AGILETL"."F4101Z1" 1.729 MB 1510 rows . . imported "AGILETL"."TBL_ERP_SYS_LOG" 1.204 MB 11028 rows . . imported "AGILETL"."SOURCE_AFFECTED_ITEMS" 475.0 KB 2401 rows . . imported "AGILETL"."F4105Z1" 309.3 KB 956 rows . . imported "AGILETL"."F554102T" 252.8 KB 1104 rows . . imported "AGILETL"."F4101Z1_BAK" 167.0 KB 41 rows . . imported "AGILETL"."F554102A" 81.17 KB 1121 rows . . imported "AGILETL"."SOURCE_CHANGE" 162.8 KB 119 rows . . imported "AGILETL"."DEFAULT_ATTRIBUTE_MAPPING" 26.47 KB 499 rows . . imported "AGILETL"."F554102E" 21.78 KB 116 rows . . imported "AGILETL"."ITEM_MASTER_TYPE_MAPPING" 10.64 KB 68 rows . . imported "AGILETL"."JDE_ATTRIBUTE_MAPPING" 34.57 KB 467 rows . . imported "AGILETL"."META_XML_DB" 19.51 KB 241 rows . . imported "AGILETL"."UDC_ATTRIBUTE_MAPPING" 6.765 KB 54 rows Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/JOB Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Nov 17 13:07:48 2020 elapsed 0 00:00:36
- 存在一个错误,用户对象已经存在,可以忽略该错误, ORA-31684: Object type USER:“AGILETL” already exists .
备注:将dmp文件解析成sql语句
当需要查看数据泵导出的dmp文件内的sql语句,可以使用sqlfile,参考如下命令,该命令只转换成export.sql,不执行导入的操作。
D:\app\Administrator\admin\agile9\create\agile>impdp system/manager directory=erp dumpfile=AGILE9_20210720EXPDP.DMP sqlfile=export.sql Import: Release 12.1.0.2.0 - Production on 星期二 7月 20 19:28:47 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Produc tion With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions 已成功加载/卸载了主表 "SYSTEM"."SYS_SQL_FILE_FULL_01" 启动 "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** directory=exp_dir_agile d umpfile=AGILE9_20210720EXPDP.DMP sqlfile=zhonghuan.sql 处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 处理对象类型 SCHEMA_EXPORT/SYNONYM/SYNONYM 处理对象类型 SCHEMA_EXPORT/TYPE/TYPE_SPEC 处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE 处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT 处理对象类型 SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN 处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC 处理对象类型 SCHEMA_EXPORT/FUNCTION/FUNCTION 处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE 处理对象类型 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SP EC 处理对象类型 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION 处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 处理对象类型 SCHEMA_EXPORT/VIEW/VIEW 处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATIST ICS 处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 处理对象类型 SCHEMA_EXPORT/TABLE/TRIGGER 处理对象类型 SCHEMA_EXPORT/EVENT/TRIGGER 处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 处理对象类型 SCHEMA_EXPORT/STATISTICS/MARKER 处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX 作业 "SYSTEM"."SYS_SQL_FILE_FULL_01" 已于 星期二 7月 20 19:28:59 2021 elapsed 0 00:00:10 成功完成
FAQ
问题1: impdp或expdp 时报错,报错信息如下.
症状
ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation
解决方式
1.先删除原先创建的备份目录
SQL> drop directory exp
2.重新创建新的备份目录
SQL> create or replace directory erp as '/u01/import '; Directory created. SQL> grant read,write on directory kmdata_exp to public ;
3.给文件夹授权
chown -R oracle:oinstall /u01/import
4.重新测试
问题2: 数据库存在版本差异
症状
当从高版本导入到低版本时是没有影响的. 但是当低版本导入高版本时会出现错误信息
ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 5.1 in dump file "/u01/import/expdp_20201117.dmp"
解决方式
- 指定版本
expdp导出时,需要指定目标数据库的版本.如下方式
expdp erp/123456 directory=erp dumpfile=expdp_20201117.dmp logfile=expdp_log_20201117.log schemas=erp version=12.2.0.1.0
- 重新导入即可
关注公众号:熊猫Jay字节之旅,了解更多 AI 技巧 ~