20230207-Oracle impdp迁移数据后主键丢失故障处理

问题现象：

通过IMPDP将B库数据导出A库时，提示：

ORA-31684: Object type CONSTRAINT:"CJCTEST"."SYS_C0022260" already exists
ORA-31684: Object type CONSTRAINT:"CJCTEST"."SYS_C0021924" already exists

通过提示知道导入时两个约束已经存在了，是否由影响需要继续检查。

检查A,B库对象类型

B库：

SQL> select object_type from dba_objects where object_name='SYS_C0022260';
OBJECT_TYPE
--------------------------------------
INDEX

A库：

SQL> select object_type from dba_objects where object_name='SYS_C0022260';
OBJECT_TYPE
-------------------
INDEX

检查A,B库SYS_C0022260索引创建语句

SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('INDEX','SYS_C0022260','CJCTEST') from dual;
###
CREATE UNIQUE INDEX "CJCTEST"."SYS_C0022260" ON "CJCTEST"."CJC_MEMORY_MONITOR" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "CJC_INDEX01"

检查A,B库约束类型
原库B库，显示为主键约束

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_CONSTRAINTs where OWNER='CJCTEST' and CONSTRAINT_NAME='SYS_C0022260';
CONSTRAINT_NAME CO
------------------------------------------------------------ --
SYS_C0022260 P

目标库A库，显示为检查约束

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_CONSTRAINTs where OWNER='CJCTEST' and CONSTRAINT_NAME='SYS_C0022260';
CONSTRAINT_NAME C
------------------------------ -
SYS_C0022260 C

检查表定义

select dbms_metadata.get_ddl('TABLE','CJC_MEMORY_MONITOR','CJCTEST') from dual;

A库：没有CONSTRAINT “SYS_C0022260” PRIMARY KEY (“ID”)

CREATE TABLE "CJCTEST"."CJC_MEMORY_MONITOR"
( "ANA_ID" VARCHAR2(255),
"CREATED_BY" VARCHAR2(255),
"CREATED_DATE" TIMESTAMP (6),
"ID" VARCHAR2(36) NOT NULL ENABLE,
"LAST_MODIFIED_BY" VARCHAR2(255),
"LAST_MODIFIED_DATE" TIMESTAMP (6),
"MAX_AVA_MEMORY" NUMBER(38,0) NOT NULL ENABLE,
"NANO" NUMBER(38,0),
"STACKTRACE" CLOB,
"SYS_TENANT_ID" VARCHAR2(255),
"TENANT_ID" VARCHAR2(255) NOT NULL ENABLE,
"TS" TIMESTAMP (6) DEFAULT cast(sysdate as timestamp),
"USED_MEMORY" NUMBER(38,0) NOT NULL ENABLE,
"USER_ID" VARCHAR2(255) NOT NULL ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "CJC_DATA01"
LOB ("STACKTRACE") STORE AS BASICFILE (TABLESPACE "CJC_DATA01" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )

B库：有CONSTRAINT “SYS_C0022260” PRIMARY KEY (“ID”)

CREATE TABLE "CJCTEST"."CJC_MEMORY_MONITOR"
( "ANA_ID" VARCHAR2(255),
"CREATED_BY" VARCHAR2(255),
"CREATED_DATE" TIMESTAMP (6),
"ID" VARCHAR2(36) NOT NULL ENABLE,
"LAST_MODIFIED_BY" VARCHAR2(255),
"LAST_MODIFIED_DATE" TIMESTAMP (6),
"MAX_AVA_MEMORY" NUMBER(38,0) NOT NULL ENABLE,
"NANO" NUMBER(38,0),
"STACKTRACE" CLOB,
"SYS_TENANT_ID" VARCHAR2(255),
"TENANT_ID" VARCHAR2(255) NOT NULL ENABLE,
"TS" TIMESTAMP (6) DEFAULT cast(sysdate as timestamp),
"USED_MEMORY" NUMBER(38,0) NOT NULL ENABLE,
"USER_ID" VARCHAR2(255) NOT NULL ENABLE,
CONSTRAINT "SYS_C0022260" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "CJC_INDEX01" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "CJC_DATA01"
LOB ("STACKTRACE") STORE AS BASICFILE (
TABLESPACE "CJC_DATA01" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )
ALTER TABLE CJC_MEMORY_MONITOR

检查约束定义

select dbms_metadata.get_ddl('CONSTRAINT','SYS_C0022260','CJCTEST') from dual;

B库：

ALTER TABLE "CJCTEST"."CJC_MEMORY_MONITOR" ADD CONSTRAINT "SYS_C0022260" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "CJC_INDEX01" ENABLE

A库：

ALTER TABLE "CJCTEST"."SCAPTO_MATTERLISTREPORT_MID" MODIFY ("LIST_ID" NOT NULL ENABLE)

问题原因：

约束对应的表名不一致，初步怀疑在导入数据库时约束名称是自动生成的，
由于导入表先后顺序的不同，导致SYS_C0022260名称被占用。

解决方案：

A库需要手动添加CJC_MEMORY_MONITOR表ID列主键

ALTER TABLE CJC_MEMORY_MONITOR ADD CONSTRAINT PK_CJC_MEMORY_MONITOR_ID PRIMARY KEY(ID);

同理处理SYS_C0021924对象

select dbms_metadata.get_ddl('CONSTRAINT','SYS_C0021924','CJCTEST') from dual;
ALTER TABLE "CJCTEST"."WB_DATA_CJC" ADD CONSTRAINT "SYS_C0021924" PRIMARY KEY ("ID")
ALTER TABLE "CJCTEST"."HI_CJC_AGREEMENT" MODIFY ("PK_AGREEMENT" NOT NULL ENABLE)

A库手动添加主键

ALTER TABLE WB_DATA_CJC ADD CONSTRAINT PK_WB_DATA_CJC_ID PRIMARY KEY(ID);

欢迎关注我的公众号《IT小Chen》