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: drop index FND_LOBS_CTX; 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: 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   Drop index FND_LOBS_CTX if exists:   drop index applsys.FND_LOBS_CTX;   Update all rows to IGNORE except FND_HELP rows:   update fnd_lobs set file_format = 'IGNORE' where nvl(program_name,'@') <> 'FND_HELP' ;   Execute aflobbld.sql from OS terminal:   sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;   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;   Check the search option.... it should work now. You are welcome to leave a comment . Aviad

How to execute TKPROF on trace files larger than 2GB ? --> Use pipe

Here is a nice trick to work with files larger than 2GB on Unix/Linux using pipe. First case - TKPROF When trying to execute TKPROF on a trace file larger than 2 GB I got this error: [udump]$ ll test_ora_21769.trc -rw-r-----  1 oratest dba 2736108204 Jun 23 11:04 test_ora_21769.trc [udump]$ tkprof test_ora_21769.trc test_ora_21769.out TKPROF: Release 9.2.0.6.0 - Production on Thu Jun 23 21:05:10 2008 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. could not open trace file test_ora_21769.trc In order to successfully execute TKPROF on this trace file you can use the mkfifo command to create named pipe as follow: Open a new unix/linux session (1st), change directory where the trace file exists and execute: [udump]$ mkfifo mytracepipe [udump]$ tkprof mytracepipe test_ora_21769.out TKPROF: Release 9.2.0.6.0 - Production on Thu Jun 23 21:07:35 2008 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Open another session (2nd), change directory where the trace file exists and execute: [udump]$ cat test_ora_21769.trc > mytracepipe This way you'll successfully get the output file.   Second case - spool Similar issue with spool to file larger than 2GB can be treat similarly. $ mkfifo myspoolpipe.out --> Create new named pipe called 'myspoolpipe.out' $ dd if=myspoolpipe.out of=aviad.out & --> What you read from 'myspoolpipe.out' write to 'aviad.out' $ sqlplus user/pwd@dbname SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jun 24 12:05:37 2008 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production SQL> spool myspoolpipe.out --> Spool to the pipe SQL> select ..... SQL> spool off SQL> 5225309+294082 records in 5367174+1 records out SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production [1]+  Done                    dd if=myspoolpipe.out of=aviad.out $ ls -ltr prw-r--r--  1 oratest dba          0 Jun 24 12:22 myspoolpipe.out -rw-r--r--  1 oratest dba 2747993487 Jun 24 12:22 aviad.out Related Notes: Note 62427.1 - 2Gb or Not 2Gb - File limits in Oracle Note 94486.1 - How to Create a SQL*Plus Spool File Larger Than 2 GB on UNIX Aviad

Mix of Old & New style buttons in OA Framework pages

After some heavy patches applied on our system we noticed that some buttons in OAF pages looks like the old style gray buttons while the others are fine new style yellow buttons. For example:   (The "Advanced" is the old style and all the others are the new style) Trying to clear cache ($COMMON_TOP/_pages) and bounce Apache didn't solve the problem. The solution is hiding within jserv.properties: Edit $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties Change the following to TRUE: wrapper.bin.parameters=-Djava.awt.headless=true (optional) Clear all content from $OA_HTML/cabo/images/cache (e.g rm -rf $OA_HTML/cabo/images/cache) (optional) Clear all content from $COMMON_TOP/_pages Bounce Apache And the problem will be resolved...   In order to make this change permanent, you should update the Application context file as follow, otherwise next run of AutoConfig will overwrite your change. Edit $APPL_TOP/admin/$CONTEXT_NAME.xml Change the following to: <java_awt_headless oa_var="s_java_awt_headless">true</java_awt_headless> Run AutoConfig on Apps Tier. Bounce Apache Related Note: 368188.1 - Buttons Are Not Rendering Correctly In Self Service Framework Pages. Aviad