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 


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:
| | | |


Sunday, June 10, 2007
 
Java Web Console on Sun Solaris

Have you ever noticed a java process running under noaccess user id on your Solaris 10 and later systems? It looks something similar to:
# prstat -u noaccess
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
7905 noaccess 110M 91M run 59 0 0:00:46 0.3% java/25

It is the Java Web Console whose responsibility is to provide a common location for users to access web-based system management applications. The Java Web Console is a web page where you can find the Sun system management web-based applications that are installed and registered on your system. Any compliant J2EETM web application can register with the web console to make itself available to authenticated and authorized users. Registration is automatically a part of an application's installation process.

The Java Web Console provides the following:

Check the 3. Working With the Sun Java Web Console (Tasks) section in Solaris 10 System Administration Guide: Basic Administration for the detailed steps to work with Java Web Console.

If you do not need the web console, it can be disabled by running the following command:
# svcadm -v disable webconsole
svc:/system/webconsole:console disabled.

To remove the web console software completely, run the following command:

Solaris 10 3/05, Solaris 10 1/06, Solaris 10 6/06:
# cd / ; /usr/lib/webconsole/setup -u

Solaris 10 11/06 and later releases:
# cd / ; /usr/share/webconsole/bin/setup -u

* Thanks to Prameet Chhabra for sharing the Java Web Console tip.
______________
Technorati tags:
|



2004-2014 

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