How to solve Oracle 9i to 11g migration KOREAN_LEXER problems?

Question

I currently work at migrating an Oracle 9i database (*.dmp file) to an Oracle 11g one. To achieve this I use the exp and imp Oracle utilities command lines:

exp USERID=<user>/<password>@<database> FILE=<path> OWNER=<owner>

Then I create a skeleton.sql file which will create tables, index tables and finally indexes.

imp <user>/<password>@<database> FILE=<path> INDEXFILE="<path>\skeleton.sql" FROMUSER=<fromuser> TOUSER=<touser>

During this migration I am able to import most of the data correctly, and of course tablespaces are kept the same from one database to the other to avoid any conflicts.

But here comes the problem. In Oracle 11g, KOREAN_LEXER is no longer supported, instead you have to use the KOREAN_MORPH_LEXER. To do so I execute the following SQL commands:

call ctx_ddl.create_preference('korean_lexer','korean_morph_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','korean','korean_lexer',null);

Then I import the skeleton.sql file in order to inject the data needed before the import:

sqlplus <user>/<password>@<database> @<path>\skeleton.sql

The creation of tables and index tables go smoothly until I get the following error for each of the 150+ indexes I created:

CREATE INDEX "<schema>"."WORKORDER_NDX16" ON "WORKORDER"
ERROR at line 1 :
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10502: WORKORDER_NDX16 index does not exist
DRG-13201: KOREAN_LEXER is no longer supported
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

Indexes are still created, this message is just a warning. I try to rebuild any of the broken indexes:

ALTER index WORKORDER_NDX16 REBUILD;

Which give me the following error again:

SQL Error : ORA-29874: warning in the execution of ODCIINDEXCREATE routine
ORA-29960: ligne 1, 
DRG-10595: failure on ALTER INDEX WORKORDER_NDX16
DRG-50857: oracle error in drixmd.PurgeKGL
ORA-20000: Oracle Text error:
DRG-13201: KOREAN_LEXER is no longer supported
ORA-30576: ConText Option dictionary loading error
DRG-50610: internal error: kglpurge []
29874. 00000 -  "warning in the execution of ODCIINDEXALTER routine"
*Cause:    A waring was returned from the ODCIIndexAlter routine.
*Action:   Check to see if the routine has been coded correctly
           Check the user defined warning log tables for greater details.

In my skeleton.sql file, under the creation of each indexes, I have the following lines for each language:

ctxsys.driimp.set_object('LEXER','MULTI_LEXER',12);
...
ctxsys.driimp.set_sub_value('SUB_LEXER','8', NULL, NULL,'KO:KOREAN_LEXER:');
...

So far I am lost on what to do, this seems to be a simple issue to solve but my dba skills are too low to do this on my own.

If anyone could help me on this I would greatly appreciate it !

Thank you.

Answer 1

This looks like your database is using Oracle Text indexes, which are not the same as ordinary indexes. Have you followed completely Oracle Note 300172.1 (Obsolescence of KOREAN_LEXER Lexer Type)? It mentions this code below, which could help.

ALTER INDEX <[schema.]index> REBUILD 
PARAMETERS('REPLACE LEXER ko_morph_lexer [MEMORY <size>]');

If all else fails, maybe consider trying to migrate your data into an Oracle 10g database and complete the korean_morph_lexer in 10g. If that works, it would be an easy Data Pump task to move it from 10g to 11g (or 12c).