The below errors occurred, which are not “Show Stoppers” but need to be considered from a Siebel + Oracle Database Upgrade Perspective.
Issue -1
Description
ORA-30556: either functional or bitmap join index is defined on the column to be modified on Table “S_CONTACT” & Column: "EMAIL_ADDR"
Root Cause
The below Functional Indexes were causing the upgrade to error out & then quit. So, I have dropped these indexes.
Below are the SQLs to recreate the Indexes after the upgrade.
SIEBEL.S_CONTACT_M15_C1_X
SIEBEL.S_CONTACT_M20_C1_X
Conclusion
The above Indexes must be dropped before the upgrade in Siebel + Oracle CRM 8.X/CRM 23.X (CRMDB Database) and recreated once the upgrade is done (Through DDLSYNC).
Issue -2
Description
Index S_CONTACT_F1
create index S_CONTACT_F1 on S_CONTACT
("LAST_UPD") parallel nologging
tablespace SIEBINDEX
2014-07-09 20:55:13 [tp][ODBC Oracle driver][Oracle]ORA-01408: such column list already indexed
Index SCT_CONTACT_LASTUPD_X
create index SCT_CONTACT_LASTUPD_X on S_CONTACT
("LAST_UPD") parallel nologging
tablespace SIEBINDEX
2014-07-09 20:56:10 [tp][ODBC Oracle driver][Oracle]ORA-01408: such column list already indexed
Index S_ORG_EXT_F1
create index S_ORG_EXT_F1 on S_ORG_EXT
("LAST_UPD") parallel nologging
tablespace SIEBINDEX
2014-07-09 23:05:58 [tp][ODBC Oracle driver][Oracle]ORA-01408: such column list already indexed
Index SCT_ORGEXT_LASTUPD_X
create index SCT_ORGEXT_LASTUPD_X on S_ORG_EXT
("LAST_UPD") parallel nologging
tablespace SIEBINDEX
2014-07-09 23:06:11 [tp][ODBC Oracle driver][Oracle]ORA-01408: such column list already indexed
Root Cause
The above Indexes are getting created again on the Column “LAST_UPD” on the S_CONTACT Table, which is already indexed.
&
"LAST_UPD" Column on S_ORG_EXT Table.
Conclusion
I have modified the schema.ddl file by deleting the above Indexes so that these indexes won’t appear during the upgrade. But, The above Indexes can constantly be recreated once the upgrade is completed successfully using DDLSYNC.
NOTE: These Indexes need to be carefully analyzed.
ISSUEalter table S_CONTACT modify "EMAIL_ADDR" varchar2(350 char)
2014-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
2014-07-09 18:15:12
2014-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
2014-07-09 18:15:12
2014-07-09 18:15:12 alter table S_CONTACT modify
2014-07-09 18:15:12 "EMAIL_ADDR" varchar2(350 char)
2014-07-09 18:15:12
2014-07-09 18:15:12 ;
2014-07-09 18:15:12 writeExecDDL error (UTLOdbcExecDirectDDL pDDLSql).
2014-07-09 18:15:12 writeExecDDL error (UTLDbDdlColModify).
2014-07-09 18:15:12 Error in MainFunction (UTLDbDdlDbMerge).
2014-07-09 18:15:12 Error in Main function...
2014-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 ); |
Comments