溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

發(fā)布時(shí)間：2020-06-05 13:42:10 來源：網(wǎng)絡(luò) 閱讀：5510 作者：SEianG 欄目：關(guān)系型數(shù)據(jù)庫

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

實(shí)驗(yàn)環(huán)境：

1、導(dǎo)出環(huán)境：RedHat6.4+Oracle 11.2.0.4.0，利用數(shù)據(jù)庫自帶的scott示例用戶進(jìn)行試驗(yàn)測試。

Directory：wjq  à /tmp/seiang_wjq

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

2、導(dǎo)入環(huán)境：Centos7.1+Oracle 12.2.0.1.0   Oracle12c默認(rèn)沒有scott用戶

Directory：imp_wjq  à /tmp/imp_comsys

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移


一、導(dǎo)出數(shù)據(jù)：

特別注意：如果后續(xù)要導(dǎo)入的數(shù)據(jù)庫版本低，所有導(dǎo)出命令就需要在后面加一個(gè)version=指定版本。例如11g -> 10g，假設(shè)10g具體版本為10.2.0.1，那么就加一個(gè)版本的參數(shù)version=10.2.0.1。


1. 首先需要?jiǎng)?chuàng)建Directory

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

注意：目錄在系統(tǒng)上需要真實(shí)存在（mkdir /tmp/seiang_wjq），且有訪問的權(quán)限。

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移



2. 使用expdp導(dǎo)出用戶數(shù)據(jù)

2.1 只導(dǎo)出scott用戶的元數(shù)據(jù)，且不包含統(tǒng)計(jì)信息；

[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 只導(dǎo)出scott用戶的數(shù)據(jù)；

[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 只導(dǎo)出scott用戶下的emp，dept表及數(shù)據(jù)；


[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用戶導(dǎo)出，需要注意scott用戶對于directory的權(quán)限問題：需要dba用戶賦予scott用戶read,write目錄的權(quán)限。

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

[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 只導(dǎo)出scott用戶下的emp，dept表結(jié)構(gòu)；

[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 導(dǎo)出scott用戶下所有的內(nèi)容；

[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 并行導(dǎo)出scott用戶下所有的內(nèi)容；

[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. 查詢當(dāng)前用戶用到的表空間

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

 

二、導(dǎo)入數(shù)據(jù)

導(dǎo)入準(zhǔn)備：將剛才從11g數(shù)據(jù)庫導(dǎo)出的內(nèi)容通過scp發(fā)送給12c



Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

 

1. 首先需要?jiǎng)?chuàng)建Directory


Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

 

2. 使用impdp導(dǎo)入用戶數(shù)據(jù)

2.1 導(dǎo)入scott用戶的元數(shù)據(jù)，且不包含統(tǒng)計(jì)信息；

[oracle@seiang ~]$ impdp system directory=imp_wjq dumpfile=scott_meta.dmplogfile=imp_scott_meta.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:26:30 2017

 

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

Password:

 

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=imp_wjqdumpfile=scott_meta.dmp logfile=imp_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

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfullycompleted at Mon Apr 24 15:27:15 2017 elapsed 0 00:00:20

 

2.2 導(dǎo)入scott用戶的數(shù)據(jù)；

只有在2.1導(dǎo)入元數(shù)據(jù)后才可以導(dǎo)入數(shù)據(jù)。

[oracle@seiang ~]$ impdp system directory=imp_wjq dumpfile=scott_data.dmplogfile=imp_scott_data.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:29:27 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=imp_wjqdumpfile=scott_data.dmp logfile=imp_scott_data.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

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

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

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

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfullycompleted at Mon Apr 24 15:29:44 2017 elapsed 0 00:00:12

 

 

2.3 只導(dǎo)入scott用戶下的emp表及數(shù)據(jù)；

[oracle@seiang ~]$ impdp scott directory=imp_wjq tables=empdumpfile=scott_emp_dept.dmp logfile=imp_scott_emp.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:40:56 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

ORA-39002:invalid operation

ORA-39070:Unable to open the log file.

ORA-39087:directory name IMP_WJQ is invalid

 

因?yàn)樵趯?dǎo)入的時(shí)候沒有給imp_wjq目錄賦予read和write的權(quán)限，所以會出現(xiàn)上面的錯(cuò)誤，下面就給imp_wjq目錄授權(quán)：

 

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

 

[oracle@seiang ~]$ impdp scott directory=imp_wjq tables=emp dumpfile=scott_emp_dept.dmplogfile=imp_scott_emp.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:45:03 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0- 64bit Production

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

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=imp_wjq tables=empdumpfile=scott_emp_dept.dmp logfile=imp_scott_emp.log

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39151:Table "SCOTT"."EMP" exists. All dependent metadata and datawill be skipped due to table_exists_action of skip

 

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

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

Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with2 error(s) at Mon Apr 24 15:45:13 2017 elapsed 0 00:00:04

 

2.4 只導(dǎo)入scott用戶下的emp,dept表結(jié)構(gòu)；

由于之前2.1、2.2、2.3導(dǎo)入的執(zhí)行，所有的表都已成功導(dǎo)入，為了接下來的實(shí)驗(yàn)，我們把scott用戶下存在的表都刪掉；


Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

[oracle@seiang ~]$ impdp scott directory=imp_wjq tables=emp,dept dumpfile=scott_emp_dept_meta.dmplogfile=imp_scott_emp_dept_meta.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:59:16 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=imp_wjqtables=emp,dept dumpfile=scott_emp_dept_meta.dmplogfile=imp_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

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfullycompleted at Mon Apr 24 15:59:22 2017 elapsed 0 00:00:02

 

由于導(dǎo)出就是emp,dept兩張表，所以也可以不指定tables，以下兩種寫法在這里都是可以的：

[oracle@seiang ~]$ impdp scott directory=imp_wjqdumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log

或者

[oracle@seiang ~]$ impdp scott directory=imp_wjqdumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log full=y

 

 

2.5 導(dǎo)入scott用戶下所有的內(nèi)容；

如果是在2.4基礎(chǔ)上直接導(dǎo)入，會因?yàn)閑mp,dept表已經(jīng)存在導(dǎo)致導(dǎo)入過程中會由于table_exists_action參數(shù)的默認(rèn)選項(xiàng)是skip，從而跳過emp，dept表數(shù)據(jù)的導(dǎo)入，如下：

 

[oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scottdumpfile=scott_all.dmp logfile=imp_scott_all.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:06:28 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=imp_wjqschemas=scott dumpfile=scott_all.dmp logfile=imp_scott_all.log

Processing object type SCHEMA_EXPORT/USER

ORA-31684:Object type USER:"SCOTT" 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/TABLE/TABLE

ORA-39151:Table "SCOTT"."EMP" exists. All dependent metadata and datawill be skipped due to table_exists_action of skip

 

ORA-39151:Table "SCOTT"."DEPT" exists. All dependent metadata anddata will be skipped due to table_exists_action of skip

 

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

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

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

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

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 3 error(s) at Mon Apr 24 16:06:38 2017 elapsed 0 00:00:05

所以這時(shí)我們想導(dǎo)入這些數(shù)據(jù)，可以加參數(shù) table_exists_action，指定想要的選項(xiàng)。
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE,[SKIP] and TRUNCATE.

這里選擇truncate，即如果表存在，那么處理方式是truncate此表后導(dǎo)入文件中包含的數(shù)據(jù)。

[oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scott table_exists_action=truncatedumpfile=scott_all.dmp logfile=imp_scott_all.log

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:17:44 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=imp_wjqschemas=scott table_exists_action=truncate dumpfile=scott_all.dmplogfile=imp_scott_all.log

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTT" 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/TABLE/TABLE

ORA-39120:Table "SCOTT"."DEPT" can't be truncated, data will beskipped. Failing error is:

ORA-02266:unique/primary keys in table referenced by enabled foreign keys

ORA-00955:name is already used by an existing object

 

Table"SCOTT"."SALGRADE" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped dueto table_exists_action of truncate

Table"SCOTT"."BONUS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped dueto table_exists_action of truncate

Table"SCOTT"."EMP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped dueto table_exists_action of truncate

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "SCOTT"."EMP"failed to load/unload and is being skipped due to error:

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parentkey not found

 

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

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

ORA-39112: Dependent object typeINDEX:"SCOTT"."PK_DEPT" skipped, base object typeTABLE:"SCOTT"."DEPT" creation failed

 

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-39112: Dependent object typeCONSTRAINT:"SCOTT"."PK_DEPT" skipped, base object typeTABLE:"SCOTT"."DEPT" creation failed

 

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 5 error(s) at Mon Apr 24 16:18:00 2017 elapsed 0 00:00:

注意：如果這里選用append選項(xiàng)，那么如果原表有數(shù)據(jù)，且沒有合理的約束條件，則可能導(dǎo)致數(shù)據(jù)的重復(fù)導(dǎo)入，所以，在生產(chǎn)環(huán)境實(shí)際導(dǎo)入過程中一定要弄清楚數(shù)據(jù)的實(shí)際情況才能準(zhǔn)確決定如何選用此參數(shù)的選項(xiàng)。

 

2.6 并行導(dǎo)入scott用戶下所有的內(nèi)容；

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

[oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scott table_exists_action=replacedumpfile=scott_all%U.dmplogfile=imp_scott_all_U.log parallel=2

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:26:42 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=imp_wjqschemas=scott table_exists_action=replace dumpfile=scott_all%U.dmplogfile=imp_scott_all_U.log parallel=2

Processing object type SCHEMA_EXPORT/USER

ORA-31684:Object type USER:"SCOTT" 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/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

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

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

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

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

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

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 1 error(s) at Mon Apr 24 16:26:52 2017 elapsed 0 00:00:06

 

3. 特殊需求

特殊需求環(huán)境準(zhǔn)備：
（1）創(chuàng)建表空間user2：
SYS@ORCL>create tablespace user2 datafile ''/u01/app/oracle/oradata/orcl/user02.dbf'size 20M autoextend on maxsize 5G;
（2）創(chuàng)建用戶scott2：
SYS@ORCL>create user scott2 identified by tiger defaulttablespace user2;
（3）賦權(quán)用戶scott2：
SYS@ORCL>grant connect, resource to scott2;

Oracle使用數(shù)據(jù)泵 (expdp/impdp)實(shí)施遷移

 

3.1 如果導(dǎo)入環(huán)境的用戶不同；

需求：將原scott用戶的數(shù)據(jù)導(dǎo)入到現(xiàn)在的scott2用戶。

[oracle@seiang orcl]$ impdp system directory=imp_wjq schemas=scott remap_schema=scott:scott2table_exists_action=replacedumpfile=scott_all%U.dmp logfile=imp_scott2_all.log parallel=2

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:46:13 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=imp_wjqschemas=scott remap_schema=scott:scott2 table_exists_action=replacedumpfile=scott_all%U.dmp logfile=imp_scott2_all.log parallel=2

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTT2" 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/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT2"."DEPT"                             5.929 KB       4 rows

. . imported "SCOTT2"."EMP"                              8.562 KB      14 rows

. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows

. . imported "SCOTT2"."BONUS"                                0 KB       0 rows

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

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 1 error(s) at Mon Apr 24 16:46:24 2017 elapsed 0 00:00:06

 

 

3.2 如果導(dǎo)入環(huán)境的表空間也不同；

需求：將原users表空間的對象重定向到users2表空間。

[oracle@seiang orcl]$ impdp system directory=imp_wjq schemas=scottremap_schema=scott:scott2 remap_tablespace=users:user2table_exists_action=replace dumpfile=scott_all%U.dmp logfile=imp_scott2_all.logparallel=2

 

Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:47:59 2017

 

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

Password:

 

Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=imp_wjqschemas=scott remap_schema=scott:scott2 remap_tablespace=users:user2table_exists_action=replace dumpfile=scott_all%U.dmp logfile=imp_scott2_all.logparallel=2

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTT2" 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/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT2"."DEPT"                             5.929 KB       4 rows

. . imported "SCOTT2"."EMP"                              8.562 KB      14 rows

. . imported "SCOTT2"."SALGRADE"                         5.859 KB       5 rows

. . imported "SCOTT2"."BONUS"                                0 KB       0 rows

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

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 1 error(s) at Mon Apr 24 16:48:10 2017 elapsed 0 00:00:06

 

根據(jù)結(jié)果，可以發(fā)現(xiàn)導(dǎo)入的日志最后都提示有一個(gè)錯(cuò)誤，往上查發(fā)現(xiàn)是報(bào)錯(cuò)ORA-31684用戶已存在，這是因?yàn)槲覀兞?xí)慣在導(dǎo)入前建立好對應(yīng)的用戶，避免一些其他的權(quán)限錯(cuò)誤，所以這個(gè)錯(cuò)誤是可以忽略的。當(dāng)然其實(shí)如果我們已經(jīng)建立了對應(yīng)的表空間，用戶也是可以不事先建立的，在導(dǎo)入的時(shí)候，如果用戶不存在，會自動(dòng)創(chuàng)建用戶；


 

作者：SEian.G（苦練七十二變，笑對八十一難）

◇本文在ITPUB：http://blog.itpub.net/31015730/

       51CTO：http://seiang.blog.51cto.com/上有同步更新

◇本文itpub地址：http://blog.itpub.net/31015730/viewspace-2137909/

◇本文51CTO地址：http://seiang.blog.51cto.com/10819863/1919003

 

 



向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手