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.

ORA-32012

You may get error ORA-32012 if you are on 11g, but have the compatible parameter set to pre-11g in the database you are cloning from, and the spfile for the source database is stored in ASM when you do an RMAN duplicate from active database.

To get around this error, I have found two workarounds:

1) Set the value for compatible to at least 11.1
2) Skip transferring of the spfile during the clone process.

Option 1 is a big change for the database (it affects the CBO among other things), but say you need a clone and are going to change this parameter anyway it is the simplest one since you don't have to create the parameterfile manually. Option 2 means you create the spfile before the cloning starts and remove the clause with spfile from the duplicate command.

An example of duplicate for option 1 is:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to FOOTEST
from active database
spfile
parameter_value_convert='FOOPROD','FOOTEST'
set compatible='11.1.0.0'
set diagnostic_dest='/u01/app/oracle'
;
}

An example for option 2:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to WINTIDST
from active database;
}

It looks as the transfer of spfile from ASM to normal file system is perceived as a downgrade, though there is no change when compatible is 10.2.3 on both databases. The format of the spfile in ASM seems to be different when stored in ASM as explained in Database Administrator's Guide 11g. The change from HARD-enabled storage to normal is not supported then if you are below 11g. Makes sense when one thinks about it, but I did a few tests back and forth to understand the connection, and it looks as if this new format was introduced in 11g. Exactly what format is used for the spfile in ASM is not clear. The ASM installation in this case is on version 11.2.0.2 and the database is on 11.1.0.7 (with the compatible left on 10.2.0.3 for some unknown reason). Maybe the new format is used when the database is on version 11g even though the compatible parameter is pre-11g, if so it looks like a bug when RMAN duplicate looks only at the compatible parameter and not on version of Oracle.

When RMAN creates the spfile it will be created in $ORACLE_HOME/dbs, I could not find any way of having it created in ASM directly.

Anyway, probably not a frequent error, but decided to write it up since I didn't find much when googling.