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:
Oracle | Database | E-Business Suite | Oracle Applications | Solaris