impdp from 12c to 19c

[Roshan]

Hello Erman,

kindly advise why index count is lower on target when importing from 12c dump to 19c dump?
ppdsourcecount.txt
ppdtargetcount.txt

import parfile:

userid=system/888
dumpfile=X8_befmig_dump_full_01Dec22.dmp
directory=migg
full=y
logfile=imp_2Dec.elog
EXCLUDE=SCHEMA:"IN ('SYS'
,'SYSTEM'
,'DBSNMP'
,'APPQOSSYS'
,'GSMCATUSER'
,'XS$NULL'
,'MDDATA'
,'REMOTE_SCHEDULER_AGENT'
,'DBSFWUSER'
,'SYSBACKUP'
,'GGSYS'
,'ANONYMOUS'
,'SYSRAC'
,'CTXSYS'
,'OJVMSYS'
,'SI_INFORMTN_SCHEMA'
,'DVF'
,'PERFSTAT'
,'DVSYS'
,'AUDSYS'
,'GSMADMIN_INTERNAL'
,'DIP'
,'ORDPLUGINS'
,'MDSYS'
,'OLAPSYS'
,'LBACSYS'
,'ORDDATA'
,'SYSKM'
,'OUTLN'
,'ORACLE_OCM'
,'SYS$UMF'
,'XDB'
,'WMSYS'
,'ORDSYS'
,'SPATIAL_CSW_ADMIN_USR'
,'SYSDG'
,'GSMUSER'
)"


Regards,

Roshan

[ErmanArslansOracleBlog]

What are those index that differ from source to target? What are their types and their definitions/DDLs?
Any errors during expdp or impdp?

[Roshan]

I see these indexes are primary key indexes. As per doc below, this seems to be a normal behaviour. Please confirm.

ID: 951994.1

Will these missing indexes affect performance?

source.txt
target.txt

source ddl:

  CREATE TABLE "****"."****"
   (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 99
99999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
  NOKEEP  NOSCALE  NOT NULL ENABLE,
        "LOOKUP_TYPE" VARCHAR2(240),
        "DESCRIPTION" VARCHAR2(2000),
        "START_DATE" DATE,
        "END_DATE" DATE,
        "ACCESS_LEVEL" VARCHAR2(240),
        "CODE" VARCHAR2(240),
        "CODE_DESC" VARCHAR2(2000),
        "TAG" VARCHAR2(240),

        "CREATED_BY" VARCHAR2(240),
        "CREATED_ON" DATE DEFAULT sysdate,
        "UPDATED_ON" DATE DEFAULT sysdate,
        "UPDATED_BY" VARCHAR2(240),
        "ACTIVE" VARCHAR2(1),
         CONSTRAINT "LOOKUP_ID_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BPMDATAPROD_MU"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BPMDATAPROD_MU"

[Roshan]

I did the import again.. Now the indexes got created. However, I see some errors in log

Processing object type DATABASE_EXPORT/STATISTICS/MARKER
MWBPMUATMU.SYS_C00166826 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00166826: does not exist or insufficient privileges
CAMUNDAPREPRODMUWFT.SYS_C00109024 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00109024: does not exist or insufficient privileges
BPMINS_ENGPREPRODMU.SYS_C00188038 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00188038: does not exist or insufficient privileges
BPMPAY_ENGUATMU.SYS_C00177537 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00177537: does not exist or insufficient privileges
BPMENGUATMU.SYS_C00174953 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00174953: does not exist or insufficient privileges
BPMQWO_ENGUATMU.SYS_C00179890 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00179890: does not exist or insufficient privileges
BPMINS_ENGUATMU.SYS_C00187820 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00187820: does not exist or insufficient privileges
BPMTRS_ENGUATMU.SYS_C00187047 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00187047: does not exist or insufficient privileges
CAMUNDAPRODMUWFT.SYS_C009287 : sqlerrm = ORA-20000: Unable to set values for index SYS_C009287: does not exist or insufficient privileges
BPMQWO_ENGPREPRODMU.SYS_C00180108 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00180108: does not exist or insufficient privileges
BPMPAY_ENGPREPRODMU.SYS_C00177755 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00177755: does not exist or insufficient privileges
MWBPMPREPRODMU.SYS_C00158063 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00158063: does not exist or insufficient privileges
BPMTRS_ENGPREPRODMU.SYS_C00192812 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00192812: does not exist or insufficient privileges
BPMENGPREPRODMU.SYS_C00175573 : sqlerrm = ORA-20000: Unable to set values for index SYS_C00175573: does not exist or insufficient privileges
Importing statistics failed for 14 object(s);


I checked (Doc ID 2673528.1). Should I apply the patch and reimport again?

Regards,

Roshan

[ErmanArslansOracleBlog]

Good . your index issue is resolved than? What was the mistake that you made in the first try?

And for those ORA-20000 errors; yes solution or workaround given in the MOS note below, should fix it;

Datapump Import with Error ORA-20000: Unable to set values for index <index name> (Doc ID 2673528.1)

[Roshan]

I recreated the tablespaces and restarted import.

[ErmanArslansOracleBlog]

Solved automatically?