Mandalika's scratchpad [ Work blog @Oracle | Stock Market Notes | My Music Compositions ]

Old Posts: 09.04  10.04  11.04  12.04  01.05  02.05  03.05  04.05  05.05  06.05  07.05  08.05  09.05  10.05  11.05  12.05  01.06  02.06  03.06  04.06  05.06  06.06  07.06  08.06  09.06  10.06  11.06  12.06  01.07  02.07  03.07  04.07  05.07  06.07  08.07  09.07  10.07  11.07  12.07  01.08  02.08  03.08  04.08  05.08  06.08  07.08  08.08  09.08  10.08  11.08  12.08  01.09  02.09  03.09  04.09  05.09  06.09  07.09  08.09  09.09  10.09  11.09  12.09  01.10  02.10  03.10  04.10  05.10  06.10  07.10  08.10  09.10  10.10  11.10  12.10  01.11  02.11  03.11  04.11  05.11  07.11  08.11  09.11  10.11  11.11  12.11  01.12  02.12  03.12  04.12  05.12  06.12  07.12  08.12  09.12  10.12  11.12  12.12  01.13  02.13  03.13  04.13  05.13  06.13  07.13  08.13  09.13  10.13  11.13  12.13  01.14  02.14  03.14  04.14  05.14  06.14  07.14  09.14  10.14  11.14  12.14  01.15  02.15  03.15  04.15  06.15  09.15  12.15  01.16  03.16  04.16  05.16  06.16  07.16  08.16  09.16  12.16  01.17  02.17  03.17  04.17  06.17  07.17  08.17  09.17 


Wednesday, June 13, 2007
 
Oracle 10gR2/Solaris x64: Fixing ORA-20000: Oracle Text errors

First, some facts:

* 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: index does 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:
| | | |


Comments:
Thanks for posting this, Giri. I found it very helpful.
 
Post a Comment

Links to this post:

Create a Link



<< Home


2004-2017 

This page is powered by Blogger. Isn't yours?