【故障處理】如何避免在執行impdp後出現ORA-00001錯誤

問題還原

1.  在源庫建立一個帶有主鍵的表

SQL> create user tc identified by oracle default tablespace users;

SQL> grant resource ,connect to tc;

SQL> conn tc/oracle

SQL> create sequence seq1 increment by 1;

Sequence created.

SQL> create table tc_table (nr number primary key,txt varchar2(10));

SQL> conn / as sysdba

2.  使用序列給該表插入資料

SQL> insert into tc.tc_table values (tc.seq1.nextval,'Line 1');

SQL> insert into tc.tc_table values (tc.seq1.nextval,'Line 2');

commit;

3.  使用expdp 匯出 tc schema ，在匯出的時候要繼續插入資料

insert into tc.tc_table values (tc.seq1.nextval,'Line 3');

commit;

insert into tc.tc_table values (tc.seq1.nextval,'Line 4');

commit;

·

·

insert into tc.tc_table values (tc.seq1.nextval,'Line 24');

commit;

4.  執行expdp 匯出

$ expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_tc.dmp logfile=expdp_tc.log schemas=tc

Export: Release 11.2.0.4.0 - Production on Sat Nov 28 01:07:33 2020

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

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=DATA_PUMP_DIR dumpfile=expdp_tc.dmp logfile=expdp_tc.log schemas=tc

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

. . exported "TC"."TC_TABLE"                             5.429 KB        24  rows

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

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

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/orcl/dpdump/expdp_tc.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Nov 28 01:07:40 2020 elapsed 0 00:00:07

5.  在目標庫執行匯入操作

$ impdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_tc.dmp logfile=impdp_tc.log schemas=tc

Import: Release 11.2.0.4.0 - Production on Sat Nov 28 01:10:26 2020

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

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

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

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=expdp_tc.dmp logfile=impdp_tc.log schemas=tc

Processing object type SCHEMA_EXPORT/USER

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

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TC"."TC_TABLE"                             5.429 KB       24 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Nov 28 01:10:27 2020 elapsed 0 00:00:01

6.  成功匯入後，向tc.tc_table 表插入資料會報以下錯誤

insert into tc.tc_table values (tc.seq1.nextval,'Line 25');

*

ERROR at line 1:

ORA-00001: unique constraint (TC.SYS_C0013274) violated

解決方法

出現上述問題的原因是，在我們執行expdp 匯出的操作時，應用繼續往表中插入資料。 Data Pump 首先會匯出序列，之後匯出表資料， 因此在匯出表資料時，可能已經使用儲存在dumpfile 中的序列的 nextval 將資料插入到表中。

解決方法如下:

方式一：

1.  以一致性模式進行匯出，意味著整個匯出的時間與給定的時間一致

$   expdp system/oracle directory=DATA_PUMP_DIR dumpfile=expdp_tc.dmp logfile=expdp_tc.log schemas=tc flashback_time=systimestamp

2.  為了避免ORA-01555(snapshot too old) 錯誤，確保引數 undo_retention 有足夠的空間去執行 expdp 匯出工作。

如果expdp 需要花費 3.5 小時，設定 undo_retention 為 4 個小時

SQL> alter system set undo_retention=14400 scope=spfile sid='*';

同時也要確保UNDO 表空間在源庫中有足夠的空間儲存 4 個小時的 UNDO 資料。

方式二：

避免在執行expdp 的過程中應用在使用

方式三：

在目標庫中，透過重新創序列，提高序列的next value

SQL> select max(nr) from tc.<TABLE_NAME>;

MAX(NR)

------------

24

SQL> select tc.<SEQUENCE_NAME>.nextval from dual;

NEXTVAL

------------

22

SQL> select dbms_ metadata.get_ddl('SEQUENCE',' SEQ1 ','TC') "DDL" from dual;

DDL

---------------------------------------------------------

CREATE SEQUENCE "TC"." SEQ1 " MINVALUE 1 MAXVALUE 9999999999999999 INCREMENT BY 1 START WITH 41

 

SQL> drop sequence tc.<SEQUENCE_NAME>;

SQL> create sequence tc.<SEQUENCE_NAME> start with 25 increment by 1

 

---- end ----