Wednesday, July 13, 2011

Rebuild Oracle Text indexes for Portal

The job portal.wwv_context.sync stopped working with the following error message:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2530
ORA-20000: Oracle Text error:
DRG-10502: index WWSBR_CORNER_CTX_INDX does not exist


We opened a Service Request at Oracle support but never figured out how the index disappeared, if that was the case and not a corruption somewhere. Support suggested that we executed the procedure given in Doc ID 340439.1; reinstall Oracle Text, then connect as the portal user from Portal server and execute the three scripts from middle tier home:


@?/portal/admin/plsql/wws/ctxdrind.sql
@?/portal/admin/plsql/wws/inctxgrn.sql
@?/portal/admin/plsql/wws/ctxcrind.sql


But the last script failed with:

SQL> @?/portal/admin/plsql/wws/ctxcrind.sql
Creating Context Preferences and Indexes...

PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2189
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2118
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
ORA-06512: at "PORTAL.WWV_CONTEXT", line 2077
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
ORA-06512: at "PORTAL.WWV_CONTEXT", line 1705
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtab.create_index_tables
ORA-00955: name is already used by an existing object
ORA-06512: at line 6


The Application Server Portal Configuration Guide section 8.3.4.1 shows that the last script executes wwv_context.createindex and the error was isolated first to this procedure. The same section shows that wwv_context.createindex is equivalent to:


wwv_context.drop_prefs; /* Drop all Oracle Text preferences for the indexes, except Lexer preferences */
wwv_context.drop_invalid_indexes; /* Drop all invalid indexes */
wwv_context.create_prefs; /* Create all Oracle Text preferences,except Lexer preferences */
wwv_context.create_missing_indexes(l_indexes); /* Create missing indexes and record them in l_indexes */
wwv_context.touch_index(l_indexes); /* Mark all rows for created indexes as requiring synchronization */
wwv_context.sync; /* Synchronize indexes */
wwv_context.optimize; /* Optimize indexes */


By executing each procedure in steps the error was isolated to wwv_context.create_missing_indexes. All of the indexes in question are listed in 8.3.4.2 and can be created one by one with procedure wwv_context.create_index.

To get around the error I executed the following procedure in stead of the script ctxcrind.sql, that is, after I executed the scripts inctxgrn.sql and ctxcrind.sql:


EXEC WWV_CONTEXT.CREATE_PREFS

EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.PAGE_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.DOC_TEXT_INDEX)
exec WWV_CONTEXT.CREATE_INDEX(wwv_context.PERSPECTIVE_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.ITEM_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.CATEGORY_TEXT_INDEX)
EXEC WWV_CONTEXT.CREATE_INDEX(WWV_CONTEXT.URL_TEXT_INDEX)

EXEC WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.PAGE_TEXT_INDEX)
EXEC WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.DOC_TEXT_INDEX)
EXEC WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.PERSPECTIVE_TEXT_INDEX)
EXEC WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.ITEM_TEXT_INDEX)
EXEC WWV_CONTEXT.TOUCH_INDEX(WWV_CONTEXT.CATEGORY_TEXT_INDEX)
exec wwv_context.touch_index(WWV_CONTEXT.URL_TEXT_INDEX)

exec wwv_context.sync;
exec wwv_context.optimize;



We did not verified if it was really necessary to reinstall Oracle Text, you may try the procedure above before reinstall Oracle Text if you encounter the same error with wwv_context.sync.

No comments :