* Oracle Applications 11.5.10 (aka E-Business Suite 11i) database is now supported on Solaris 10 for x86-64 architecture.
* The database must be at least Oracle 10g Release 2 (10gR2)
* KOREAN_LEXER lexer type has been discontinued with the Oracle 10gR2 release.
Scenario:
Customer X is trying to migrate the Oracle Apps 11i database instance from Oracle 10gR1/Solaris SPARC to Oracle 10gR2/Solaris x64. During the data import process, creation of some of the text indexes fail with the following error message:
ORA-20000: Oracle Text error:
DRG-10502: indexdoes not exist
DRG-13201: KOREAN_LEXER is desupported
Ignoring the above error message may lead to some dysfunctional modules. For example, creating a new service request (SR) fail with errors like:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE in Package
CS_ServiceRequest_PVT Procedure Create_ServiceRequest
The following query confirms that the issue is indeed with failed indexes.
% sqlplus / as sysdba
SQL> Select INDEX_NAME, TABLE_NAME, DOMIDX_STATUS, DOMIDX_OPSTATUS
2 from ALL_INDEXES where TABLE_NAME like 'CS%INCIDENTS%ALL%' and INDEX_TYPE='DOMAIN';
INDEX_NAME TABLE_NAME DOMIDX_STATUS DOMIDX_OPSTATUS
----------------------------------- ------------------------------ ----------------- -----------------
SUMMARY_CTX_INDEX CS_INCIDENTS_ALL_TL VALID FAILED
CS_INCIDENTS_ALL_TL_N1 CS_INCIDENTS_ALL_TL VALID FAILED
Refer to Oracle Metalink Note 306854.1 Create Service Request: Ora-29861: Domain Index Is Marked Loading/Failed/Unusable for more.
Similarly searching for a Catalog item under Sales & Marketing module fails with error message similar to:
ORA-20000: Oracle Text error: DRG-10599: column is not indexed
java.sql.SQLException: ORA-20000: Oracle Text error: DRG-10599: column is not indexed at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at
oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at
oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589) at
oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1972) at
oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1231) at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2607) at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2950) at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:656) at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:582) at
...
...
How to fix the above ORA-20000: Oracle Text errors?
Steps:
1. Execute the SQL script which was posted in Oracle Metalink Note 187905.1 bde_imt_index_status.sql - List all interMedia Text indexes by owner, to check the status of the Text indexes.
For your convenience, I'm reproducing the script here:
% cat textindexes.sql
ttitle -
center 'iMT Index Summary by Owner' skip 2
set pagesize 67
set linesize 95
column idx_owner heading 'Owner' format a8
column idx_name heading 'Index|Name' format a24;
column idx_table heading 'Table|Indexed' format a22
column idx_text_name heading 'Column|Indexed' format a21
column idx_docid_count heading 'Number|Rows|Indexed' format 99999
column idx_status heading 'Index|Status' format a8
break on idx_owner
spool bde_imt_index_status.lst
select idx_owner,idx_name,idx_table,idx_text_name,idx_docid_count,idx_status
from ctx_indexes
group by idx_owner,idx_name,idx_table,idx_text_name,idx_docid_count,idx_status
/
spool off;
Here's the sample output from a Oracle 10gR2/Solaris x64 system right after the data was imported with index errors:
% sqlplus ctxsys/ctxsys
SQL> @textindexes.sql
iMT Index Summary by Owner
Number
Index Table Column Rows Index
Owner Name Indexed Indexed Indexed Status
-------- ------------------------ ---------------------- --------------------- ------- --------
PA PA_PROJECT_CTX_SEARCH_C1 PA_PROJECT_CTX_SEARCH CTX_DESCRIPTION 159 INDEXED
HR IRC_SEARCH_CRITERIA_CTX IRC_SEARCH_CRITERIA KEYWORDS 0 INDEXED
JTF JTF_TASKS_TL_IM JTF_TASKS_TL TASK_NAME 4183 INDEXED
JTF_AMV_ITEMS_NAME_CTX JTF_AMV_ITEMS_TL ITEM_NAME 1394 INDEXED
CS CS_FORUM_MESSAGES_TL_N4 CS_FORUM_MESSAGES_TL COMPOSITE_ASSOC_COL 0 POPULATE
CS_KB_SETS_TL_N3 CS_KB_SETS_TL COMPOSITE_ASSOC_INDEX 0 POPULATE
JTF JTF_AMV_ITEMS_DESC_CTX JTF_AMV_ITEMS_TL DESCRIPTION 1394 INDEXED
HR PER_EMPDIR_PEOPLE_N1 PER_EMPDIR_PEOPLE PERSON_KEY 3258 INDEXED
CTXTEST QUICK_TEXT QUICK TEXT 0 NO_INDEX
HR PER_ADDRESSES_N4 PER_ADDRESSES DERIVED_LOCALE 0 INDEXED
CS SUMMARY_CTX_INDEX CS_INCIDENTS_ALL_TL SUMMARY 0 POPULATE
JTF JTF_AMV_ITEMS_URL_CTX JTF_AMV_ITEMS_B URL_STRING 0 INDEXED
JTF_AMV_ITEMS_TEXT_CTX JTF_AMV_ITEMS_TL TEXT_STRING 1394 INDEXED
ENI ENI_DEN_HRCHY_PAR_IM1 ENI_DENORM_HRCHY_PAREN CONCAT_CAT_PARENTAGE 143 INDEXED
TS
OKC OKC_ARTICLES_CTX OKC_ARTICLE_VERSIONS ARTICLE_TEXT 919 INDEXED
AR HZ_CUST_ACCT_SITES_ALL_T HZ_CUST_ACCT_SITES_ALL ADDRESS_TEXT 0 INDEXED
1
HR IRC_DOCUMENTS_CTX IRC_DOCUMENTS BINARY_DOC 13 INDEXED
AR HZ_STAGE_CONTACT_T1 HZ_STAGED_CONTACTS CONCAT_COL ###### INDEXED
CS CS_KB_SOLN_CAT_TL_N1 CS_KB_SOLN_CATEGORIES_ NAME 0 POPULATE
TL
HR IRC_POSTING_CON_TL_CTX IRC_POSTING_CONTENTS_T NAME 40 INDEXED
L
ICX ICX_CAT_ITEMS_CTX_DESC ICX_CAT_ITEMS_TLP CTX_DESC ###### INDEXED
IBC IBC_ATTRIBUTE_BUNDLES_CT IBC_ATTRIBUTE_BUNDLES ATTRIBUTE_BUNDLE_DATA 224 INDEXED
X
AMV AMV_C_CHANNELS_NAME_CTX AMV_C_CHANNELS_TL CHANNEL_NAME 56 INDEXED
ICX ICX_QUES_CTX ICX_QUESTIONS_TL QUESTION 1279 INDEXED
APPS IBE_CT_IMEDIA_SEARCH_IM IBE_CT_IMEDIA_SEARCH INDEXED_SEARCH 0 POPULATE
CTXSYS DEFAULT_POLICY_ORACONTAI DR$POLICY_TAB PLT_POLICY 0 NO_INDEX
NS
SYSTEM QUICK_TEXT QUICK TEXT 2 INDEXING
HR HR_LOCATIONS_N1 HR_LOCATIONS_ALL DERIVED_LOCALE 0 INDEXED
IRC_DOCUMENTS_CTX1 IRC_DOCUMENTS CHARACTER_DOC 13 INDEXED
AR HZ_STAGE_PARTIES_T1 HZ_STAGED_PARTIES CONCAT_COL ###### INDEXED
CS CS_KB_ELEMENTS_TL_N2 CS_KB_ELEMENTS_TL COMPOSITE_TEXT_INDEX 0 POPULATE
OKR OKR_IPS_TL_N1 OKR_IP_COMMON_TL KEYWORD_TEXT 7 INDEXED
OKC OKC_TERMS_TEMPLATES_CTX OKC_TERMS_TEMPLATES_AL TEMPLATE_NAME 70 INDEXED
L
APPLSYS FND_LOBS_CTX FND_LOBS FILE_DATA 0 POPULATE
AR HZ_CLASS_CODE_DENORM_T1 HZ_CLASS_CODE_DENORM CONCAT_CLASS_CODE_MEA 8154 INDEXED
NING
AMV AMV_C_CHANNELS_DESC_CTX AMV_C_CHANNELS_TL DESCRIPTION 56 INDEXED
CS CS_INCIDENTS_ALL_TL_N1 CS_INCIDENTS_ALL_TL TEXT_INDEX 0 POPULATE
AR HZ_STAGE_PARTY_SITES_T1 HZ_STAGED_PARTY_SITES CONCAT_COL ###### INDEXED
HZ_STAGE_CPT_T1 HZ_STAGED_CONTACT_POIN CONCAT_COL ###### INDEXED
TS
39 rows selected.
2. Note down the index names whose status is not 'INDEXED'.
From the output in step #1, the following are the offensive indexes.
APPLSYS.FND_LOBS_CTX
APPS.IBE_CT_IMEDIA_SEARCH_IM
CS.CS_FORUM_MESSAGES_TL_N4
CS.CS_INCIDENTS_ALL_TL_N1
CS.CS_KB_ELEMENTS_TL_N2
CS.CS_KB_SETS_TL_N3
CS.CS_KB_SOLN_CAT_TL_N1
CS.SUMMARY_CTX_INDEX
3. Depending on the indexes to be re-created, connect as CS or APPS user, drop all the indexes whose status is something other than 'INDEXED'.
Continuing with the example, drop the indexes shown in step #2 as follows:
% sqlplus apps/apps
SQL> drop index applsys.FND_LOBS_CTX;
Index dropped.
SQL> drop index IBE_CT_IMEDIA_SEARCH_IM;
Index dropped.
% sqlplus cs/cs
SQL> drop index CS_FORUM_MESSAGES_TL_N4;
Index dropped.
SQL> drop index CS_INCIDENTS_ALL_TL_N1;
Index dropped.
SQL> drop index CS_KB_ELEMENTS_TL_N2;
Index dropped.
SQL> drop index CS_KB_SETS_TL_N3;
Index dropped.
SQL> drop index CS_KB_SOLN_CAT_TL_N1;
Index dropped.
SQL> drop index SUMMARY_CTX_INDEX;
Index dropped.
4. Finally create the dropped indexes with the following syntax:
create index <index_name> on <table_name> (<column_name>) indextype is ctxsys.context;Continuing with the example, create the indexes shown in step #2 as follows:
% sqlplus apps/apps
SQL> create index applsys.FND_LOBS_CTX on applsys.FND_LOBS (FILE_DATA)
2 indextype is ctxsys.context;
Index created.
SQL> create index IBE_CT_IMEDIA_SEARCH_IM on IBE_CT_IMEDIA_SEARCH (INDEXED_SEARCH)
2 indextype is ctxsys.context;
Index created.
% sqlplus cs/cs
SQL> create index CS_FORUM_MESSAGES_TL_N4 on CS_FORUM_MESSAGES_TL (COMPOSITE_ASSOC_COL)
2 indextype is ctxsys.context;
Index created.
SQL> create index CS_INCIDENTS_ALL_TL_N1 on CS_INCIDENTS_ALL_TL (TEXT_INDEX)
2 indextype is ctxsys.context;
Index created.
SQL> create index CS_KB_ELEMENTS_TL_N2 on CS_KB_ELEMENTS_TL (COMPOSITE_TEXT_INDEX)
2 indextype is ctxsys.context;
Index created.
SQL> create index CS_KB_SETS_TL_N3 on CS_KB_SETS_TL (COMPOSITE_ASSOC_INDEX)
2 indextype is ctxsys.context;
Index created.
SQL> create index CS_KB_SOLN_CAT_TL_N1 on CS_KB_SOLN_CATEGORIES_TL (NAME)
2 indextype is ctxsys.context;
Index created.
SQL> create index SUMMARY_CTX_INDEX on CS_INCIDENTS_ALL_TL (SUMMARY)
2 indextype is ctxsys.context;
Index created.
5. Repeat step #1 to check the status of text indexes.
iMT Index Summary by Owner
Number
Index Table Column Rows Index
Owner Name Indexed Indexed Indexed Status
-------- ------------------------ ---------------------- --------------------- ------- --------
PA PA_PROJECT_CTX_SEARCH_C1 PA_PROJECT_CTX_SEARCH CTX_DESCRIPTION 159 INDEXED
HR IRC_SEARCH_CRITERIA_CTX IRC_SEARCH_CRITERIA KEYWORDS 0 INDEXED
CS CS_KB_SETS_TL_N3 CS_KB_SETS_TL COMPOSITE_ASSOC_INDEX 397 INDEXED
CS_FORUM_MESSAGES_TL_N4 CS_FORUM_MESSAGES_TL COMPOSITE_ASSOC_COL 10 INDEXED
JTF JTF_TASKS_TL_IM JTF_TASKS_TL TASK_NAME 4183 INDEXED
JTF_AMV_ITEMS_NAME_CTX JTF_AMV_ITEMS_TL ITEM_NAME 1394 INDEXED
JTF_AMV_ITEMS_DESC_CTX JTF_AMV_ITEMS_TL DESCRIPTION 1394 INDEXED
HR PER_EMPDIR_PEOPLE_N1 PER_EMPDIR_PEOPLE PERSON_KEY 3258 INDEXED
CTXTEST QUICK_TEXT QUICK TEXT 0 NO_INDEX
HR PER_ADDRESSES_N4 PER_ADDRESSES DERIVED_LOCALE 0 INDEXED
CS SUMMARY_CTX_INDEX CS_INCIDENTS_ALL_TL SUMMARY 3947 INDEXED
JTF JTF_AMV_ITEMS_URL_CTX JTF_AMV_ITEMS_B URL_STRING 0 INDEXED
JTF_AMV_ITEMS_TEXT_CTX JTF_AMV_ITEMS_TL TEXT_STRING 1394 INDEXED
ENI ENI_DEN_HRCHY_PAR_IM1 ENI_DENORM_HRCHY_PAREN CONCAT_CAT_PARENTAGE 143 INDEXED
TS
OKC OKC_ARTICLES_CTX OKC_ARTICLE_VERSIONS ARTICLE_TEXT 919 INDEXED
AR HZ_CUST_ACCT_SITES_ALL_T HZ_CUST_ACCT_SITES_ALL ADDRESS_TEXT 0 INDEXED
1
HR IRC_DOCUMENTS_CTX IRC_DOCUMENTS BINARY_DOC 13 INDEXED
AR HZ_STAGE_CONTACT_T1 HZ_STAGED_CONTACTS CONCAT_COL ###### INDEXED
HR IRC_POSTING_CON_TL_CTX IRC_POSTING_CONTENTS_T NAME 40 INDEXED
L
IBC IBC_ATTRIBUTE_BUNDLES_CT IBC_ATTRIBUTE_BUNDLES ATTRIBUTE_BUNDLE_DATA 224 INDEXED
X
ICX ICX_CAT_ITEMS_CTX_DESC ICX_CAT_ITEMS_TLP CTX_DESC ###### INDEXED
CS CS_KB_SOLN_CAT_TL_N1 CS_KB_SOLN_CATEGORIES_ NAME 60 INDEXED
TL
AMV AMV_C_CHANNELS_NAME_CTX AMV_C_CHANNELS_TL CHANNEL_NAME 56 INDEXED
ICX ICX_QUES_CTX ICX_QUESTIONS_TL QUESTION 1279 INDEXED
CS CS_KB_ELEMENTS_TL_N2 CS_KB_ELEMENTS_TL COMPOSITE_TEXT_INDEX 367 INDEXED
APPS IBE_CT_IMEDIA_SEARCH_IM IBE_CT_IMEDIA_SEARCH INDEXED_SEARCH ###### INDEXED
CTXSYS DEFAULT_POLICY_ORACONTAI DR$POLICY_TAB PLT_POLICY 0 NO_INDEX
NS
SYSTEM QUICK_TEXT QUICK TEXT 2 INDEXING
HR HR_LOCATIONS_N1 HR_LOCATIONS_ALL DERIVED_LOCALE 0 INDEXED
IRC_DOCUMENTS_CTX1 IRC_DOCUMENTS CHARACTER_DOC 13 INDEXED
AR HZ_STAGE_PARTIES_T1 HZ_STAGED_PARTIES CONCAT_COL ###### INDEXED
OKR OKR_IPS_TL_N1 OKR_IP_COMMON_TL KEYWORD_TEXT 7 INDEXED
OKC OKC_TERMS_TEMPLATES_CTX OKC_TERMS_TEMPLATES_AL TEMPLATE_NAME 70 INDEXED
L
APPLSYS FND_LOBS_CTX FND_LOBS FILE_DATA 61608 INDEXED
CS CS_INCIDENTS_ALL_TL_N1 CS_INCIDENTS_ALL_TL TEXT_INDEX 3947 INDEXED
AR HZ_CLASS_CODE_DENORM_T1 HZ_CLASS_CODE_DENORM CONCAT_CLASS_CODE_MEA 8154 INDEXED
NING
AMV AMV_C_CHANNELS_DESC_CTX AMV_C_CHANNELS_TL DESCRIPTION 56 INDEXED
AR HZ_STAGE_PARTY_SITES_T1 HZ_STAGED_PARTY_SITES CONCAT_COL ###### INDEXED
HZ_STAGE_CPT_T1 HZ_STAGED_CONTACT_POIN CONCAT_COL ###### INDEXED
TS
39 rows selected.
Read the Oracle Metalink Note 312640.1 Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes, if the steps mentioned in the previous paragraphs didn't help fixing the
Oracle Text errors.__________________
Technorati Tags:
Oracle | 
Database | 
E-Business Suite | 
Oracle Applications | 
Solaris
Thanks for posting this, Giri. I found it very helpful.
ReplyDelete