Oracle使用数据泵 (expdp/impdp)实施迁移

1、导出环境:RedHat6.4+Oracle 11.2.0.4.0,利用数据库自带的scott示例用户进行试验测试。

Directory:wjq  à /tmp/seiang_wjq

Oracle使用数据泵 (expdp/impdp)实施迁移

2、导入环境:CentOS7.1+Oracle 12.2.0.1.0   Oracle12c默认没有scott用户

Directory:imp_wjq  à /tmp/imp_comsys

Oracle使用数据泵 (expdp/impdp)实施迁移

一、导出数据:

特别注意:如果后续要导入的数据库版本低,所有导出命令就需要在后面加一个version=指定版本。例如11g -> 10g,假设10g具体版本为10.2.0.1,那么就加一个版本的参数version=10.2.0.1。

1. 首先需要创建Directory

注意:目录在系统上需要真实存在(mkdir /tmp/seiang_wjq),且有访问的权限。

Oracle使用数据泵 (expdp/impdp)实施迁移

2. 使用expdp导出用户数据

2.1 只导出scott用户的元数据,且不包含统计信息;

[oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott content=metadata_onlyexclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:17:16 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=wjq schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log

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/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/tmp/seiang_wjq/scott_meta.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 24 14:17:48 2017 elapsed 0 00:00:21

2.2 只导出scott用户的数据;

[oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:22:36 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=wjq schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/tmp/seiang_wjq/scott_data.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 24 14:22:47 2017 elapsed 0 00:00:06

2.3 只导出scott用户下的emp,dept表及数据;

[oracle@seiangwjq ~]$ expdp scott directory=wjq tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:25:37 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name WJQ is invalid

这里如果用scott用户导出,需要注意scott用户对于directory的权限问题:需要dba用户赋予scott用户read,write目录的权限。

Oracle使用数据泵 (expdp/impdp)实施迁移

[oracle@seiangwjq ~]$ expdp scott directory=wjq tables=emp,dept dumpfile=scott_emp_dept.dmplogfile=scott_emp_dept.log

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:28:18 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=wjq tables=emp,deptdumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."DEPT"                              5.929 KB      4 rows

. . exported "SCOTT"."EMP"                              8.562 KB      14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01"successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/tmp/seiang_wjq/scott_emp_dept.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfullycompleted at Mon Apr 24 14:28:35 2017 elapsed 0 00:00:09

2.4 只导出scott用户下的emp,dept表结构;

[oracle@seiangwjq ~]$ expdp scott directory=wjq tables=emp,dept content=metadata_onlydumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:34:07 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=wjq tables=emp,deptcontent=metadata_only dumpfile=scott_emp_dept_meta.dmplogfile=scott_emp_dept_meta.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Master table "SCOTT"."SYS_EXPORT_TABLE_01"successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/tmp/seiang_wjq/scott_emp_dept_meta.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfullycompleted at Mon Apr 24 14:34:21 2017 elapsed 0 00:00:08

2.5 导出scott用户下所有的内容;

[oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott dumpfile=scott_all.dmplogfile=scott_all.log

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:38:10 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=wjq schemas=scottdumpfile=scott_all.dmp logfile=scott_all.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

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/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."DEPT"                             5.929 KB      4 rows

. . exported "SCOTT"."EMP"                              8.562 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.859 KB      5 rows

. . exported "SCOTT"."BONUS"                                0 KB      0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01"successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/tmp/seiang_wjq/scott_all.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfullycompleted at Mon Apr 24 14:38:30 2017 elapsed 0 00:00:16

2.6 并行导出scott用户下所有的内容;

[oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott dumpfile=scott_all%U.dmplogfile=scott_all.log parallel=2 

Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:44:04 2017

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=wjq schemas=scottdumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER

. . exported "SCOTT"."DEPT"                              5.929 KB      4 rows

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

. . exported "SCOTT"."SALGRADE"                          5.859 KB      5 rows

. . exported "SCOTT"."BONUS"                                 0 KB      0 rows

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01"successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /tmp/seiang_wjq/scott_all01.dmp

  /tmp/seiang_wjq/scott_all02.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfullycompleted at Mon Apr 24 14:44:27 2017 elapsed 0 00:00:15

3. 查询当前用户用到的表空间

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/8983cccc7c56dad87dc269c258d193c3.html