Tuesday, July 29, 2008

How to enable FND_HELP search by creating an index on FND_LOBS efficiently

Have you ever tried to use the search option of Online Help in Oracle Applications?
Our users did... and they got "The page cannot be found" message...

I checked it on firefox, hope to get more accurate message, and I got this:
"Not Found. The requested URL /pls/DEV/fnd_help.search was not found on this server".


I checked fnd_help package and it's compiled and looks fine.

These errors appeared in error_log_pls:

[Mon Jul 28 10:34:54 2008] [warn] mod_plsql: Stale Connection due to Oracle error 20000
[Mon Jul 28 10:34:54 2008] [error] mod_plsql: /pls/DEV/fnd_help.search ORA-20000
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "APPS.FND_HELP", line 1043
ORA-06512: at "APPS.FND_HELP", line 873
ORA-06512: at line 20

I found note 306239.1 - "Cannot Search Online Help After Fresh Install of 11.5.10" which suggest that Applications interMedia Text indexes are corrupt. In my instance it even wasn't exist...

I followed this note which instructs to:

  1. drop index FND_LOBS_CTX;
  2. Rebuild the index using aflobbld.sql

But aflobbld.sql had been running for more than 10 hours and the size of DR$FND_LOBS_CTX$I table has reached to 35 GB !
I had been wondering how it can be that fnd_lobs table is less than 1GB and the index on it is 35 GB and counting.... ?!

Note 396803.1 - "FND_LOBS_CTX is having huge size, how to reduce the sizeof the index?" suggests it's a bug, indexing all documents in FND_LOB table, also the binary files, while using wrong filter.

So how can we make aflobbld.sql to index only FND_HELP documents?

For each row in FND_LOBS table the file_format column is populated with one of the following values: IGNORE, BINARY, TEXT.
aflobbld.sql will index only rows that have this column set to BINARY or TEXT.
If we set all rows to IGNORE except FND_HELP rows, we could index them only.

Note 397757.1 - "How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data" suggests the steps to do it.

These are the steps:

  1. Backup the fnd_lobs table before updating it, we will use it later:
    create table fnd_lobs_bk as select * from fnd_lobs;
    ** you can create a backup of this table and omit the file_date column to make this backup faster
  2. Drop index FND_LOBS_CTX if exists:
    drop index applsys.FND_LOBS_CTX;
  3. Update all rows to IGNORE except FND_HELP rows:
    update fnd_lobs
    set file_format = 'IGNORE'
    where nvl(program_name,'@') <> 'FND_HELP' ;
  4. Execute aflobbld.sql from OS terminal:
    sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;
  5. Since I'm not sure about the impact of leaving the FND_LOBS rows as IGNORE, I updated them back to the previous state:
    create unique index fnd_lobs_bk_u1 on fnd_lobs_bk (file_id);
    update (select fl.file_format ffo,flb.file_format ffb
            from fnd_lobs fl
                ,fnd_lobs_bk flb
            where fl.file_id = flb.file_id)
    set ffo=ffb;
    drop table fnd_lobs_bk;
  6. Check the search option.... it should work now.

You are welcome to leave a comment .