Missing Oracle Text Supplied Knowledge Base after 10gR2 database installation

After applying the 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4 Patchset and attempting to use theme functionality this will fail with errors like the following:

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11422: linguistic initialization failed
DRG-11446: supplied knowledge base file
/u1/app/oracle/product/10.2.0/ctx/data/enlx/droldUS.dat not installed
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
Or while running RepCA you receive a warning message that says:
"Oracle Text Knowlege Base is not installed."

Solution
1) You will need to install Oracle Text knowledge base from the 10.2.0.1.0 Companion CD, please refer to the Companion CD Installation Guide specific to your platform for specific details.
2) Afterwards you must re-apply 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4.0 Patchset. Start up the runInstaller once again and select the 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4.0 products.xml file once again in the "Specify File Locations" screen. The OUI will see the new components installed and will only install the 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4.0 patchsets associated to the new products.
3) Click on 'Install' on the Summary Screen (On 10.2.0.3.0 there should be roughly 13 "New Installations" items that are being installed)

4) After this then re-run catupgrd.sql script once again and this will correct the situation.

Same steps are applicable when Ultra Search product is required and was not installed with Oracle Database 10g release 2 (10.2.0.1.0) software.


Cheers!!!

Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade

Gather Schema Statistics" program reported following errors in request log files :

Error #1: ERROR: While GATHER_TABLE_STATS: 
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt*** 
Error #2: ERROR: While GATHER_TABLE_STATS: 
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt*** 
Error #3: ERROR: While GATHER_TABLE_STATS: 
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #4: ERROR: While GATHER_TABLE_STATS:  object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

There are two reasons for that error message:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

Following SQL should have returned one row , not two.

SQL> select a.column_name, nvl(a.hsize,254) hsize
from FND_HISTOGRAM_COLS a
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

Solution:
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

-- identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;


-- Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;


-- Use following SQL to delete obsoleted rows
delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in 
  (
   select hc.table_name, hc.column_name
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
   where hc.table_name  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );


Cheers!!!

Deploying a new forms EAR file in R12

Sometimes forms patch may contain changes to the Forms listener servlet code. If so, the patch will contain a new formsapp.ear file to update the J2EE Forms module. To complete the patching process, this new formsapp.ear file must be deployed within the Forms OC4J container used with Oracle Applications.

To do the process, basic steps are
Set Applications environment and stop instance processes

From the Applications instance $APPL_TOP, set the environment by running the APPS[CONTEXT_NAME].env script, then stop all application tier processes for the instance by running the script $ADMIN_SCRIPTS_HOME/adstpall.sh.

Run deployment script

txkCfgOC4JApp.pl is a perl script that installs the .ear file in the proper location and configures the Forms J2EE group correctly.

Note: For successful completion of the script, ensure that no Java processes for the instance are still running.

The script is executed by running the txkrun.pl script, as shown in the example below.

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp
Enter Application name for re-deployment ? forms
Enter Oc4j Instance password for re-deployment ? (current password)
Run Autoconfig [Yes/No] ? No

post-deployment tasks

Run AutoConfig on the instance by running the command:
$ADMIN_SCRIPTS_HOME/adautocfg.sh

Start the appropriate services on the instance by running the command:
$ADMIN_SCRIPTS_HOME/adstrtal.sh [apps user/apps password]


Cheers!!!