Errors in SIEBEL CRM DB Upgrade (Oracle DB), ddlsync, upgphys

Photo by BoliviaInteligente on Unsplash.

Issue

Cannot alter the S_CONTACT Table during the upgrade process.

Root Cause

The below Indexes are created again on the Column “LAST_UPD” on S_CONTACT  Table, which is already indexed during the upgrade process.

[tp][ODBC Oracle driver][Oracle]ORA-01408: such column list already indexed.

Errors

alter table S_CONTACT modify
"EMAIL_ADDR"                     varchar2(350 char)
 
20XX-07-09 18:15:12        [tp][ODBC Oracle driver][Oracle]ORA-30556: either functional or bitmap join index is defined on the column to be modified
 
 
20XX-07-09 18:15:12       
 
20XX-07-09 18:15:12        S1000: [tp][ODBC Oracle driver][Oracle]ORA-30556: either functional or bitmap join index is defined on the column to be modified
 
20XX-07-09 18:15:12       
 
20XX-07-09 18:15:12        alter table S_CONTACT modify
 
20XX-07-09 18:15:12        "EMAIL_ADDR"                     varchar2(350 char)
 
20XX-07-09 18:15:12       
 
20XX-07-09 18:15:12        ;
 
20XX-07-09 18:15:12        writeExecDDL error (UTLOdbcExecDirectDDL pDDLSql).
 
20XX-07-09 18:15:12        writeExecDDL error (UTLDbDdlColModify).
 
20XX-07-09 18:15:12        Error in MainFunction (UTLDbDdlDbMerge).
 
20XX-07-09 18:15:12        Error in Main function...
 
20XX-07-09 18:15:12        (logapi.cpp (184) err=1 sys=0) SBL-GEN-00001: (logapi.cpp: 184) error code = 1, system error = 0, msg1 = (null), msg2 = (null), msg3 = (null), msg4 = (null)
 

Resolution

DROP INDEX SIEBEL.S_CONTACT_M15_C1_X;

CREATE INDEX SIEBEL.S_CONTACT_M15_C1_X ON SIEBEL.S_CONTACT
(NLS_UPPER("EMAIL_ADDR",'nls_sort=''GENERIC_BASELETTER'''))
LOGGING
TABLESPACE SIEBINDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

DROP INDEX SIEBEL.S_CONTACT_M20_C1_X;

CREATE INDEX SIEBEL.S_CONTACT_M20_C1_X ON SIEBEL.S_CONTACT
(BU_ID, NLS_UPPER("EMAIL_ADDR",'nls_sort=''GENERIC_BASELETTER'''))
LOGGING
TABLESPACE SIEBINDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );

Conclusion

Carefully Modify the schema.ddl file by deleting the above Indexes so that these indexes won’t appear during the upgrade. But, The above Indexes can always be recreated once the upgrade is completed successfully via ddlsync.