Java.Lang.Noclassdeffounderror: Javax/Jms/Connection When Starting Workflow Mailer

We got this error while starting Workflow Mailer Service in the logs:

Java.Lang.Noclassdeffounderror: Javax/Jms/Connection 

Metalink Note 342700.1 describes the cause of this problem:

The error occurs because JMS jar files ($IAS_OH/rdbms/jlib/jmscommon.jar and

$IAS_OH/rdbms/jlib/aqapi.jar) cannot be found. Normally these are included in

$JAVA_TOP/appsborg2.zip which is not included in the AF_CLASSPATH.

Ensuring that appsborg2.zip is present in your AF_CLASSPATH resolves this problem

ORA-01578 block corrupted in jserv.log

Today we had a major issue in Production, and the DBAs ended up restoring everything from RMAN backup.  After the Database was restored, we could not access the AppsLogin page.  After turning on the debug mode, we saw these errors in the jserv.log:

ORA-01578: ORACLE data block corrupted (file # 377, block # 4205) 

ORA-01110: data file 377: '/dev/rac_data2/rapplsysd03.dbf' 

ORA-26040: Data block was loaded using the NOLOGGING option

On seeing this error, Akhilesh said that it is a known issue and ran the WF_LOCAL_SYNCH procedure:

SQL> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',

2 P_PARALLEL_PROCESSES=>2,

3 P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,

4 P_TEMPTABLESPACE=>'APPS_TS_TX_DATA');

5 END;

6 /

After running the above procedure, we were able to see the AppsLogin page and login to the instance.

Metalink Notes 433280.1 and 435523.1 describe this problem in detail and provide this cause:

During its execution, the Synchronize WF LOCAL tables concurrent program 

executes the following statements: 

ALTER TABLE WF_LOCAL_ROLES 

EXCHANGE PARTITION  

WITH TABLE WF_LOCAL_ROLES_STAGE INCLUDING INDEXES WITHOUT VALIDATION; 

ALTER TABLE WF_LOCAL_USER_ROLES 

EXCHANGE PARTITION  

WITH TABLE WF_LOCAL_USER_ROLES_STAGE INCLUDING INDEXES WITHOUT VALIDATION;

ALTER TABLE WF_USER_ROLE_ASSIGNMENTS 

EXCHANGE PARTITION

WITH TABLE WF_UR_ASSIGNMENTS_STAGE INCLUDING INDEXES WITHOUT VALIDATION; 

  

When an alter table exchange partition takes place, all the segment attributes 

of the two objects (including tablespace and 

logging) are also exchanged. 

So if one of the stage tables is set to logging = 'NO' before the Synchronize WF 

LOCAL tables 

concurrent program is launched, after its completion a partition of 

the corresponding table will be set to LOGGING = 'NO'.           

    

    

    

Example: 

1. WF_LOCAL_ROLES_STAGE are set to LOGGING = 'NO' 

all partitions of WF_LOCAL_ROLES are set to LOGGING = 'YES' 

2. ran 

Synchronize WF LOCAL tables concurrent program for originating system = ENG_LIST. 

3. After completion, 

WF_LOCAL_ROLES_STAGE is set to LOGGING = 'NO' 

ENG_LIST partition of WF_LOCAL_ROLES is set to LOGGING = 'YES'

    

This problem is described in Bug 5942254 RUNNING SYNCHRONIZE WF LOCAL TABLES CHANGES PARTITION TO NOLOGGING. 

I also found this solution on astijf wiki here.

oracle.apps.fnd.sso.SessionMgr.setLang

Abhishek reported this error:

Internal Server Error

The Server encountered an internal error or misconfiguration and was unable to complete your request

Please contact the server administrator, applmgr@justanexample.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

apache access_log showed HTTP-503 error:

192.168.1.32 - - [28/Jan/2009:09:23:46 -0500] "GET /oa_servlets/AppsSSOServlet/?urlc=v1.2~64B471D0DE31B9ABC28F471BB5DCF74F6583C912573EFBE43AE8001F4F33C31CE4
C01658BEF2F5DE748E13B748BA0CD3EC9B272181AC9A939DB5D75D6C9DA03435445B79EC48BC0B1CCD0764201C93309A2B3C77B1870912D4220B82FF987AF091E9576ABDF0FEA035EC8572556A19B
DB78035965C12BBB63B58E420C3FD37BB86BE2101B5C74879BBF26BEB55C1317BF9FFF6ADB234E480AF57B7156ED45E161D19D36C3D7435BD577E67F96866026C43B3D7AAAD980FDFD9D68576F99A
230B030B435000F61003EEC92225B7FD8DFCB8C34835A41E3CC6EDC7BFD09A9BAF563D7E284A75AD0785AA189D1E21F7BD0A99266AE3DD97EC4FED32AE6A867F81B25F93E5564561A76E348836B54
8F6B06184CBB963A67A28D6 HTTP/1.1" 500 543 0


mod_jserv.log:

[28/01/2009 09:39:24:897] (ERROR) an error returned handling request via protocol "ajpv12"
[28/01/2009 09:39:24:897] (ERROR) balance: 1775 internal servlet error in server dev14.justanexample.com:16220
[28/01/2009 09:39:24:897] (ERROR) an error returned handling request via protocol "balance"
[28/01/2009 09:52:12:600] (ERROR) ajp12: Servlet Error: java.lang.NoSuchMethodError: oracle.apps.fnd.sso.SessionMgr.setLang(Loracle/apps/fnd/common/WebAppsContext;Ljavax/servlet/http/HttpServletRequest;Ljava/lang/String;)V: oracle.apps.fnd.sso.SessionMgr.setLang(Loracle/apps/fnd/common/WebAppsContext;Ljavax/servlet/http/HttpServletRequest;Ljava/lang/String;)V

$IAS_CONFIG_HOME/Apache/Jserv/logs/jvm/OACoreGroup.0.stderr
java.lang.NoSuchMethodError: oracle.apps.fnd.sso.SessionMgr.setLang(Loracle/apps/fnd/common/WebAppsContext;Ljavax/servlet/http/HttpServletRequest;Ljava/lang/String;)V
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:462)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)

Cause: Un-synchronized data in APPLSYS.WF_LOCAL_USER_ROLES:HZ_PARTY

Solution:

For non-OATM instances:

sql>
begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
P_PARALLEL_PROCESSES=>2,
P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,
P_TEMPTABLESPACE=>'APPLSYSX');
END;
/

For OATM instances:
sql>
begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
P_PARALLEL_PROCESSES=>2,
P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,
P_TEMPTABLESPACE=>'APPS_TS_TX_DATA'); END; /

Since we were on an OATM instance, we ran this pl/sql block after logging in as apps:

SQL> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
2 P_PARALLEL_PROCESSES=>2,
3 P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,
4 P_TEMPTABLESPACE=>'APPS_TS_TX_DATA');
5 END;
6 /

PL/SQL procedure successfully completed.

SQL>

There was no need to bounce Apache. After running the above query, we were able to login without issues.

usdsop cannot redirect standard output

Akhilesh reported this error to me today where concurrent managers refused to start with this error in the *.mgr logs:

Cause: usdsop encountered an error redirecting standard output for a child process.

Metalink Note 733901.1 describes this problem and gives the cause as:

The cause of this problem has been identified in Bug 7000874. It is caused by a code bug which causes memory corruption on an internal FILENAME variable. As a result of that the FND Service Manager fails to spawn the service manager processes such as the Output Post Processor and many others.

Solution

Apply a patch that includes the fixed file version of afpsms.oc:

• Oracle E-Business Suite 11i
  Patch 7244628 1OFF:7235678:11.5.10.2:11.5.10.2:GSM Services Not Running
  --> Controlled release patch on top of patch 4676589 11i.ATG_PF.H.RUP4.
• Oracle E-Business Suite Release 12
  Patch 7000874 GSM Services Not Running
  --> Controlled release patch on top of the 12.0.3 code level.

Note: Both patches are released under the terms of 'By Support' which implies that the patch is queryable on Metalink, but downloads of these patches requires a password. Please contact Global Customer Support to receive a password to download the patch.

Workaround

In case the issue is encountered on a lower code level and thus a patch is not available, one may consider to implement the following workaround:

1. Stop the concurrent managers.
2. Decreased the sequence value of FND_CONCURRENT_PROCESSES_S to 500000 (decrease from 8 to 6 digits).
   
   
   • Determine the current sequence value:
     
     SELECT applsys.fnd_concurrent_processes_s.NEXTVAL
FROM DUAL;
     
     
   • Set the INCREMENT BY value of the sequence to the value of (fnd_concurrent_processes_s.NEXTVAL - 500000) * -1). For example, if the current value is 10019473 then the INCREMENT value will be -9519473 (= (10019473 - 500000) * -1).
     
     ALTER SEQUENCE applsys.fnd_concurrent_processes_s INCREMENT BY -9519473;
     
     
   • Decrease the current sequence value by requesting a next value from the sequence:
     
     SELECT fnd_concurrent_processes_s.NEXTVAL
FROM DUAL;
     
     This will return a value of 500000.
     
     
   • Reset the INCREMENT BY value to 1:
     
     ALTER SEQUENCE fnd_concurrent_processes_s INCREMENT BY 1;
     
     
3. Determine the concurrent manager log files that will need to be removed manually from the Concurrent Processing tier. The easiest way is to spool the output to a spool file so that it can be execute afterwards from the command line (or to be archived if needed).
   
   SELECT 'rm ' || logfile_name
FROM fnd_concurrent_processes
WHERE concurrent_process_id >= 500000;
   
   
4. Delete the rows from the FND_CONCURRENT_PROCESSES table:
   
   DELETE FROM fnd_concurrent_processes
WHERE concurrent_process_id >= 500000;
   
   
5. Delete the rows from the FND_ENV_CONTEXT table:
   
   DELETE FROM fnd_env_context
WHERE concurrent_process_id >= 500000;
   
   
6. Start the concurrent managers.

Once these actions are completed, the service managers such as the Workflow related ones as well as the Output Post Processor will start up successfully.

Note: The impact of the workaround affects only the Concurrent Processing log files. If preferred, these can be kept for a temporary time in case it may be of interest. The existing concurrent requests which ran in the past are not affected meaning that report data will not be lost by completing this workaround.

ASCP plan uses lot of temp tablespace after OATM

After converting to OATM, we got complaints of ASCP Plan not completing and erroing out with errors of unable to extend TEMP tablespace.

This was traced to materialized view: INV.MTL_SYS_ITEMS_SN

SELECT /*+ OPAQUE_TRANSFORM */
"INVENTORY_ITEM_ID", "ORGANIZATION_ID", "USING_ORGANIZATION_ID",
"ASL_ID", "PROCESSING_LEAD_TIME", "MINIMUM_ORDER_QUANTITY",
"FIXED_LOT_MULTIPLE", "DELIVERY_CALENDAR_CODE",
"PURCHASING_UNIT_OF_MEASURE", "VENDOR_ID", "VENDOR_SITE_ID",
"ENABLE_VMI_FLAG", "VMI_MIN_QTY", "VMI_MAX_QTY",
"ENABLE_VMI_AUTO_REPLENISH_FLAG", "VMI_REPLENISHMENT_APPROVAL",
"ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3", "ATTRIBUTE4", "ATTRIBUTE5",
"ATTRIBUTE6", "ATTRIBUTE7", "ATTRIBUTE8", "ATTRIBUTE9", "ATTRIBUTE10",
"ATTRIBUTE11", "ATTRIBUTE12", "ATTRIBUTE13", "ATTRIBUTE14",
"ATTRIBUTE15", "ITEM_PRICE", "PRIMARY_VENDOR_ITEM",
"REPLENISHMENT_METHOD", "MIN_MINMAX_DAYS", "MAX_MINMAX_DAYS",
"FORECAST_HORIZON", "FIXED_ORDER_QUANTITY"
FROM "MRP_AP_PO_SUPPLIERS_V" "X"
WHERE "ORGANIZATION_ID" = 1234
OR "ORGANIZATION_ID" = 2341
OR "ORGANIZATION_ID" = 2929
OR "ORGANIZATION_ID" = 2382

The query when traced showed a lot of wait events on 'direct path write temp' and filled up the temp tablespace.

WAIT #35: nam='direct path write temp' ela= 5078 file number=4017 first dba=18341
WAIT #35: nam='direct path write temp' ela= 2611 file number=4017 first dba=18354
WAIT #35: nam='direct path write temp' ela= 2617 file number=4017 first dba=18353
WAIT #35: nam='direct path write temp' ela= 1880 file number=4017 first dba=18360
WAIT #35: nam='direct path write temp' ela= 26 file number=4017 first dba=183641
WAIT #35: nam='direct path write temp' ela= 1721 file number=4017 first dba=18363
WAIT #35: nam='direct path write temp' ela= 24 file number=4017 first dba=183721
WAIT #35: nam='direct path write temp' ela= 1922 file number=4017 first dba=18378
WAIT #35: nam='direct path write temp' ela= 6247 file number=4017 first dba=18377
WAIT #35: nam='direct path write temp' ela= 1887 file number=4017 first dba=18383
WAIT #35: nam='direct path write temp' ela= 5088 file number=4017 first dba=18383
WAIT #35: nam='direct path write temp' ela= 4958 file number=4017 first dba=18396


/8: lwp_park(0x00000000, 0) = 0
/8: pwrite(423, "\bA2\0\0\f03 M YA9 b P _".., 122880, 0x69AB2000) = 122880
/1: kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL
/1: write(5, " W A I T # 3 5 : n a".., 127) = 127
/1: write(5, "\n", 1) = 1
/1: times(0xFFFFFFFF7FFF8F70) = 1994720337
/1: semctl(469762093, 12, SETVAL, 1) = 0
/1: semtimedop(570425393, 0xFFFFFFFF7FFF7E04, 1, 0xFFFFFFFF7FFF7DF0) = 0
/1: write(5, " W A I T # 3 5 : n a".., 99) = 99
/1: write(5, "\n", 1) = 1
/1: semctl(469762093, 12, SETVAL, 1) = 0
/1: semtimedop(570425393, 0xFFFFFFFF7FFF7E04, 1, 0xFFFFFFFF7FFF7DF0) = 0
/1: write(5, " W A I T # 3 5 : n a".., 99) = 99
/1: write(5, "\n", 1) = 1
/1: semctl(469762093, 12, SETVAL, 1) = 0
/1: semtimedop(570425393, 0xFFFFFFFF7FFF7E04, 1, 0xFFFFFFFF7FFF7DF0) = 0
/1: write(5, " W A I T # 3 5 : n a".., 99) = 99
/1: write(5, "\n", 1) = 1
/1: times(0xFFFFFFFF7FFF8D50) = 1994720339
/1: lwp_unpark(9) = 0
/9: lwp_park(0x00000000, 0) = 0
/1: kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL
/9: pwrite(422, "\bA2\0\0\vC3E3D9A9 b P _".., 122880, 0x7C7B2000) = 122880
/9: kaio(AIONOTIFY, 0) = 0
/1: kaio(AIOWAIT, 0xFFFFFFFF7FFF8E30) = 1
/1: write(5, " W A I T # 3 5 : n a".., 127) = 127
/1: write(5, "\n", 1) = 1
/1: times(0xFFFFFFFF7FFF8F70) = 1994720340
/1: times(0xFFFFFFFF7FFF8D50) = 1994720340
/1: lwp_unpark(10) = 0
/10: lwp_park(0x00000000, 0) = 0
/1: kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL
/10: pwrite(423, "\bA2\0\0\f03 M iA9 b P _".., 122880, 0x69AD2000) = 122880
/10: kaio(AIONOTIFY, 0) = 0
/1: kaio(AIOWAIT, 0xFFFFFFFF7FFF8E30) = 1
/1: write(5, " W A I T # 3 5 : n a".., 128) = 128
/1: write(5, "\n", 1) = 1
/1: times(0xFFFFFFFF7FFF8F70) = 1994720342
/1: semctl(469762093, 12, SETVAL, 1) = 0
/1: semtimedop(570425393, 0xFFFFFFFF7FFF7E04, 1, 0xFFFFFFFF7FFF7DF0) = 0
/1: write(5, " W A I T # 3 5 : n a".., 99) = 99
/1: write(5, "\n", 1) = 1
/1: semctl(469762093, 12, SETVAL, 1) = 0
/1: semtimedop(570425393, 0xFFFFFFFF7FFF7E04, 1, 0xFFFFFFFF7FFF7DF0) = 0
/1: write(5, " W A I T # 3 5 : n a".., 99) = 99

Solved by following these steps:

execute dbms_stats.gather_fixed_objects_stats;
execute dbms_stats.gather_dictionary_stats();
execute dbms_stats.gather_schema_stats('APPS',cascade=>TRUE,degree=>48);
execute dbms_stats.gather_schema_stats('APPLSYS',cascade=>TRUE,degree=>48);
execute dbms_stats.gather_schema_stats('INV',cascade=>TRUE,degree=>48);
execute dbms_stats.gather_schema_stats('PO',cascade=>TRUE,degree=>48);
execute dbms_stats.gather_schema_stats('MSC',cascade=>TRUE,degree=>48);
execute dbms_stats.gather_schema_stats('ASO',cascade=>TRUE,degree=>48);

Query to check temp tablespace usage

This query is courtesy www.dbspecialists.com from their excellent article here.

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

ORA-24813: cannot send or receive an unsupported LOB

I used dbms_metadata package to get the definition of a view while logged in from 806 ORACLE_HOME

  1  select dbms_metadata.get_ddl('VIEW','MRP_AP_PROCESS_EFFECTIVITY_V','APPS')

  2* FROM DUAL

SQL> /

FROM DUAL

     *

ERROR at line 2:

ORA-24813: cannot send or receive an unsupported LOB

Error: ORA-24813 Text: cannot send or receive an unsupported LOB  --------------------------------------------------------------------------- Cause: An attempt was made to send a LOB across the network, but either the   server does not support the LOB sent by the client, or the client does   not support the LOB sent by the server. This error usually occurs when   the client and server are running different versions of Oracle.  Action: Use a version of the Oracle that supports the LOB on both the client   and the server.

ORA-24813: cannot send or receive an unsupported LOB

Cause: An attempt was made to send a LOB across the network, but either the server does not support the LOB sent by the client, or the client does not support the LOB sent by the server. This error usually occurs when the client and server are running different versions of Oracle.

Action: Use a version of the Oracle that supports the LOB on both the client and the server.

Connect with 10g client and the command works.

SQL> select dbms_metadata.get_ddl('VIEW','MRP_AP_PROCESS_EFFECTIVITY_V','APPS')

FROM DUAL  2

  3  /

DBMS_METADATA.GET_DDL('VIEW','MRP_AP_PROCESS_EFFECTIVITY_V','APPS')

--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "APPS"."MRP_AP_PROCESS_EFFECTIVITY_V" ("INVENT

OR

SQL> set long2000

SQL> /

DBMS_METADATA.GET_DDL('VIEW','MRP_AP_PROCESS_EFFECTIVITY_V','APPS')

--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "APPS"."MRP_AP_PROCESS_EFFECTIVITY_V" ("INVENT

ORY_ITEM_ID", "ORGANIZATION_ID", "BILL_SEQUENCE_ID",

 "ALTERNATE_BOM_DESIGNATOR", "ALTERNATE_ROUTING_DESI

GNATOR", "ROUTING_SEQUENCE_ID", "LINE_ID", "PREFEREN

CE", "PRIMARY_LINE_FLAG", "PRODUCTION_LINE_RATE", "L

OAD_DISTRIBUTION_PRIORITY", "EFFECTIVITY_DATE", "ATT

RIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3", "ATTRIBUTE4",

"ATTRIBUTE5", "ATTRIBUTE6", "ATTRIBUTE7", "ATTRIBUTE

8", "ATTRIBUTE9", "ATTRIBUTE10", "ATTRIBUTE11", "ATT

RIBUTE12", "ATTRIBUTE13", "ATTRIBUTE14", "ATTRIBUTE15", "RN4", "RN3", "RN2", "RN1") AS

  select items.inventory_item_id,

       items.organization_id,

       b.bill_sequence_id * 2 bill_sequence_id,

       b.alternate_bom_designator,

       r.alternate_routing_designator,

       r.routing_sequence_id * 2 routing_sequence_id,

       TO_NUMBER(NULL) LINE_ID,

       nvl(r.priority, DECODE( b.alternate_bom_designator,

               NULL, DECODE( r.alternate_routing_designator,

                             NULL, 1,

                             2),

               2)) PREFERENCE,

       TO_NUMBER(NULL) PRIMARY_LINE_FLAG,

       TO_NUMBER(NULL) PRODUCTION_LINE_RATE,

       TO_NUMBER(NULL) LOAD_DISTRIBUTION_PRIORITY,

       SYSDATE EFFECTIVITY_DATE,

       b.attribute1,

       b.attribute2,

       b.attribute3,

       b.attribute4,

       b.attribute5,

       b.attribute6,

       b.attribute7,

       b.attribute8,

       b.attribute9,

       b.attribute10,

       b.attribute11,

       b.attribute12,

       b.attribute13,

       b.attribute14,

       b.attribute15,

       NVL(r.RN,0)     RN4,

       NVL(b.RN,0)     RN3,

       items.RN        RN2,

       0           RN1

from MRP_SN_BOMS b,

     MRP_SN_OPR_RTNS r,

     MRP_SN_SYS_ITEMS items

where items.inventory_item_id = b.assembly_item_id(+)

and items.organization_id = b.organization_id(+)

and items.inventory_item_id = r.assembly_item_id (+)

and items.organization_id = r.organization_id (+)

and ( (r.routing_sequence_id IS NULL and b.bill

DBMS_METADATA

Use DBMS_METADATA package to generate the syntax of any object in database:

For example to get the definition of DEPT table in SCOTT schema you can use this command:

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

Here's an example from Apps:

SQL> select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual

*

ERROR at line 1:

ORA-24813: cannot send or receive an unsupported LOB

You'll get the above error if you are trying to use this command when your ORACLE_HOME is the 8.0.6 ORACLE_HOME.

Login through the 10.2.0 ORACLE_HOME

SQL> select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','FND_NODES','APPLSYS')

----------------------------------------------------------------

  CREATE TABLE "APPLSYS"."FND_NODES"

   (    "NODE_NAME" VARCHAR2(30) NOT NULL EN

You need to set long to 2000 or higher to see the full syntax:

SQL> set long2000

SQL> /

DBMS_METADATA.GET_DDL('TABLE','FND_NODES','APPLSYS')

--------------------------------------------------------------------

  CREATE TABLE "APPLSYS"."FND_NODES"

   (    "NODE_NAME" VARCHAR2(30) NOT NULL ENABLE,

        "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,

        "LAST_UPDATED_BY" NUMBER(15,0) NOT NULL ENABLE,

        "CREATION_DATE" DATE NOT NULL ENABLE,

        "CREATED_BY" NUMBER(15,0) NOT NULL ENABLE,

        "LAST_UPDATE_LOGIN" NUMBER(15,0) NOT NULL ENABLE,

        "PLATFORM_CODE" VARCHAR2(30) NOT NULL ENABLE,

        "DESCRIPTION" VARCHAR2(240),

        "BASEPATH" VARCHAR2(20),

        "SUPPORT_CP" VARCHAR2(1),

        "SUPPORT_FORMS" VARCHAR2(1),

        "SUPPORT_WEB" VARCHAR2(1),

        "SUPPORT_ADMIN" VARCHAR2(1),

        "STATUS" VARCHAR2(1),

        "PING_RESPONSE" VARCHAR2(2000),

        "LAST_MONITORED_TIME" DATE,

        "NODE_MODE" VARCHAR2(1),

        "NODE_ID" NUMBER,

        "SERVER_ID" VARCHAR2(64),

        "SERVER_ADDRESS" VARCHAR2(30),

        "HOST" VARCHAR2(256),

        "DOMAIN" VARCHAR2(256),

        "WEBHOST" VARCHAR2(256),

        "VIRTUAL_IP" VARCHAR2(256),

        "SUPPORT_DB" VARCHAR2(1),

        "APPLTOP_ID" RAW(16)

   ) PCTFREE 5 PCTUSED 80 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGI

NG

  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)

  TABLESPACE "APPS_TS_TX_DATA"

Why does login page become ICXINDEX.htm in a clone

Whenever we clone Production to Dev, the s_login_page changes from /oa_servlets/AppsLogin to the old style /OA_HTML/ICXINDEX.htm.

This is not a bother as it redirects to the correct page, however it always bothered me.  Using ICXINDEX.htm causes other problems as I learnt in a TAR with Oracle in 2006.

The context file is created from files in $COMMON_TOP/clone/context/apps, so I searched for the string ICXINDEX in all files in that directory:

$ cd $COMMON_TOP/clone/context/apps

$ grep ICXINDEX *

adxmlctx.tmp:      %s_login_page%

If you change the value of %s_login_page% variable to point to /

$COMMON_TOP/clone/context/apps/adxmlctx.tmp :

Current value:

      

%s_domainname%:%s_active_webport%/OA_HTML/US/ICXINDEX_%s_contextname%.htm">%s_lo

gin_page%

Change value:

      

%s_domainname%:%s_active_webport%/oa_servlets/AppsLogin" %s_login_page%

age

This would solve it temporarily.  However where does adxmlctx.tmp come from ?

It comes from $AD_TOP/admin/template/adxmlctx.tmp

If you modify that you should be ok.  I'll log an SR with Oracle to get them to fix this in their template.

What is my filesystem type

On Solaris: df -n gives you the file system type.

$ df -n

/                  : vxfs

/dev               : lofs

/lib               : lofs

/platform          : lofs

/sbin              : lofs

/usr               : lofs

/usr/local         : lofs

/usr/openv         : lofs

/proc              : proc

/system/contract   : ctfs

/etc/mnttab        : mntfs

/system/object     : objfs

/etc/svc/volatile  : tmpfs

/platform/sun4v/lib/libc_psr.so.1: lofs

/platform/sun4v/lib/sparcv9/libc_psr.so.1: lofs

/dev/fd            : fd

/tmp               : tmpfs

/var/run           : tmpfs

/oemgrid           : nfs

/apps11i/custom   : nfs

/tmp/emcpt         : nfs

/apps11i/app   : nfs

/apps11i/10gAS    : nfs

Oracle Forms Web CGI: Error detected

We faced this error after migrating to shared application file system on one of our dev instances. Forms sessions would not start up through self service or through dev60cgi/f60cgi and report these errors:

Oracle Forms Web CGI: Error detected

Your request cannot be serviced at this time due to the following error:

ERROR: Parameter "userid" not found in URL or configuration parameters.

After a lot of tests I came across Metalink Note: 299206.1 which pointed the cause as:

Cause

This error indicates that the baseHTML (typically this is =%OA_HTML%/US/appsbase.htm)  file for forms has parameters which are not defined in the associated appsweb*.cfg file.

In other words the appsbase.htm and appsweb.cfg are out of sync.

However we followed the steps given to compare and found that appsbase.htm and appsweb.cfg were in sync.  We then checked the versions of appsbase.htm on a different instance and found that the version of appsbase.htm in web tier and concurrently tier were very different.  

$OA_HTML/US/appsbase.htm

$ adident Header appsbase.htm

appsbase.htm:

$Header appsbase.htm 115.40 2008/03/05 06:12:28 upinjark ship $

$ adident Header appsbase.htm.original

appsbase.htm.original:

$Header appsbase.htm 115.9 2001/06/11 19:59:45 pkm ship      $

During migration to shared appl_top we had mistakenly preserved the older appsbase.htm of concurrent tier and did not copy the newer appsbase.htm of web tier.

After copying the latest appsbase.htm from a different instance, the problem was resolved.

JBO-25002: Definition oracle.apps.ak.region.server.AkAmParameterRegistryVO of type View Definition not found

Akhilesh pinged me today with this issue in one of the instances:

After keying in username and password this error would appear:

Error Page  

 

   

You have encountered an unexpected error. Please contact the System Administrator for assistance.  

Click here for exception details.  

   

About Previous Page 

Exception details: 
oracle.apps.fnd.framework.OAException: oracle.jbo.NoDefException: JBO-25002: Definition oracle.apps.ak.region.server.AkAmParameterRegistryVO of type View Definition not found
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1223)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1986)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:508)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:429)
at _oa__html._OA._jspService(_OA.java:85)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:95)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:95)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:595)
## Detail 0 ##
oracle.apps.fnd.framework.OAException: oracle.jbo.NoDefException: JBO-25002: Definition oracle.apps.ak.region.server.AkAmParameterRegistryVO of type View Definition not found
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1986)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:508)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:429)
at _oa__html._OA._jspService(_OA.java:85)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:95)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:95)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:595)
oracle.apps.fnd.framework.OAException: oracle.jbo.NoDefException: JBO-25002: Definition oracle.apps.ak.region.server.AkAmParameterRegistryVO of type View Definition not found
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1986)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:508)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:429)
at _oa__html._OA._jspService(_OA.java:85)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:95)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:95)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:595)

It was unable to find oracle.apps.ak.region.server.AkAmParameterRegistryVO of type View Definition, so I logged on to the web node and did this:

cd $OA_JAVA/oracle/apps/ak/region/server
$ ls -ltr AkAmParameterRegistryVO*
No files.
$ ls -ltr | wc -l
0
No files !!!!!

du -sk $OA_JAVA
1243892

I checked in concurrent node and did the same thing:

cd $OA_JAVA/oracle/apps/ak/region/server
$ ls -ltr AkAmParameterRegistryVO*
-rw-r--r-- 1 applmgr dba 1417 Sep 21 2001 AkAmParameterRegistryVO.xml
$ ls -ltr | wc -l
98

$ du -sk $OA_JAVA
1597226 $OA_JAVA

That's a difference of nearly 400 MB. Clearly all the files in $OA_JAVA were not copied during clone. Once the files were copied, the error went away.

OATM export import testcase

You can use export and import to migrate tablespaces to OATM, in cases where OATM is unable to do the migration without errors:

Here's how I tested:

Export a table

Drop the table

Change default tablespace of user to a new tablespace (Done by OATM automatically)

Drop the tablespace in which the table existed

import the table.

Table now exists in new tablespace.

This is not supported by Oracle and you should do this only as a last resort.

oracle.apps.fnd.common.PoolException: Not able to create database connection

Sandeep reported this error which was coming when the AppsLocalLogin.jsp page was accessed after applying ATG_PF.H RUP6:

oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException:
Not able to create new database connection.

jserv.log showed these errors:

[20/01/2009 15:22:24:984 EST] oracle.apps.mwa.wap.engine.WapServlet: init
[20/01/2009 15:22:24:985 EST] oracle.apps.mwa = $MWA_TOP
Exception in static block of jtf.cache.appsimpl.AppsCacheLogger. Stack trace is: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException:
Not able to create new database connection.
at oracle.apps.fnd.profiles.Profiles.getProfileOption(Profiles.java:1509)
at oracle.apps.fnd.profiles.Profiles.getProfile(Profiles.java:362)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfileFromDB(ExtendedProfileStore.java:210)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfile(ExtendedProfileStore.java:169)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getProfile(ExtendedProfileStore.java:148)
at oracle.apps.fnd.common.logging.DebugEventManager.configureUsingDatabaseValues(DebugEventManager.java:1201)
at oracle.apps.fnd.common.logging.DebugEventManager.configureLogging(DebugEventManager.java:1044)
at oracle.apps.fnd.common.logging.DebugEventManager.internalReinit(DebugEventManager.java:1013)
at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:980)
at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:967)
at oracle.apps.fnd.common.AppsLog.reInitialize(AppsLog.java:570)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:570)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.jav
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:518)
at oracle.apps.fnd.sso.SSOAccessEnabler.getAppsContext(SSOAccessEnabler.
at _oa__html._AppsLocalLogin._jspService(_AppsLocalLogin.java:198)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'FND_ENCRYPTED_PWD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
:570)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
:524)
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.jav
a:1002)
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:518)
at oracle.apps.fnd.sso.SSOAccessEnabler.getAppsContext(SSOAccessEnabler.
java:50)
at _oa__html._AppsLocalLogin._jspService(_AppsLocalLogin.java:198)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417
)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:
456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:570)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:524)
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.java:1002)
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:518)
at oracle.apps.fnd.sso.SSOAccessEnabler.getAppsContext(SSOAccessEnabler.java:50)
at _oa__html._AppsLocalLogin._jspService(_AppsLocalLogin.java:198)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'FND_ENCRYPTED_PWD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
at oracle.apps.fnd.security.AppsConnectionManager.getEncApplsysPwd(AppsConnectionManager.java:1328)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:1189)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:1109)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:1098)
at oracle.apps.fnd.security.AppsConnectionManager.makeGuestConnection(AppsConnectionManager.java:783)
at oracle.apps.fnd.security.DBConnObj.<init>(DBConnObj.java:246)
... 41 more

This instance is on ATG RUP6. Based on metalink note 744916.1 the root cause is:

oracle.apps.fnd.security.AppsConnectionManager.getEncApplsysPwd code in RUP2 shows:

String sql="BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;";
Whereas in RUP6:

String sql = "BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;";

I checked the code of FND_SECURITY_PKG by giving the command:

select line,text
from dba_source
where name='FND_SECURITY_PKG'
and type='PACKAGE'


LINE
----------
TEXT
--------------------------------------------------------------------------------

PROCEDURE fnd_encrypted_password(p_username IN VARCHAR2,
290 p_server_id IN VARCHAR2,
291 p_user_id IN OUT NOCOPY VARCHAR2,
292 p_password IN OUT NOCOPY VARCHAR2)
293 is
294 begin
295 fnd_encrypted_pwd(p_username, p_server_id, p_user_id, p_password,
296 'UNKNOWN', '');

I grepped for fnd_encrypted_password in $FND_TOP/patch/115/sql and found that the version of FND_SECURITY_PKG was older in the database compared to the one in the file system.

$ cd $FND_TOP/patch/115/sql
$ grep FND_SECURITY_PKG *
AFSCUSV8.pls:REM ER 5892249 - Password Hash project - Wrapped FND_SECURITY_PKG
AFSCUSV8.pls:1FND_SECURITY_PKG:
AFSCUSV8.pls:1FND_SECURITY_PKG.FND_ENCRYPTED_PWD:
AFSCUSVS.pls:REM | PL/SQL specification for package: FND_SECURITY_PKG |
afpub.sql:GRANT EXECUTE ON FND_SECURITY_PKG TO &3;
afpub.sql:DROP SYNONYM FND_SECURITY_PKG;
afpub.sql:CREATE SYNONYM FND_SECURITY_PKG FOR &1..FND_SECURITY_PKG;
afpubfix.sql:'EXECUTE ON FND_SECURITY_PKG',
afpubfix.sql:'EXECUTE ON FND_SECURITY_PKG',

So I ran AFSCUSV8.pls which complained about not able to find some other object and invalidated fnd_web_sec. So I ran AFSCJAV8.pls which recreated fnd_web_sec. I had multiple invalid objects and no clue what to run to correct it. I recalled that there was a post ATG RUP6 bug 6841295 ( DB SESSIONS FROM JVMS ARE VERY VERY HIGH AFTER ATG RUP6 + JDK 1.6 UPGRADE") which provided a patch 6841295 which recreated FND_SECURITY_PKG. As described in Metalink Note 459353.1 :

There are lots of idle database connections coming from the APPLSYSPUB user.

Use this script to identify if you are experiencing this problem:
select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%';


These connection leaks are NOT reported in ""AOL/J Database connection pool status" page.

Last SQL executed shows :

BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;
or
BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;

I downloaded that patch and unzipped it. The fnd/patch/115/sql directory had the following files:


I ran all the .pls files and all the invalids were fixed.

However this didn't fix the problem.

On accessing http://dev21.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp it showed:

JSP Error:

Request URI:/OA_HTML/AppsLocalLogin.jsp
Exception:
java.lang.NoClassDefFoundError


On accessing http://dev21.justanexample.com/oa_servlets/AppsLogin it shows:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, applmgr@dev21.justanexample.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

I went to sleep as it was very late in night and sent a mail to Sandeep about my R&D.

Finally, Sandeep told me that they had run ATG_PF.H RUP6 with the adpatch flag nodbportion by mistake, which had not run any of the database drivers in RUP6. They had to reclone and re-do the whole exercise.

Start Apache on port 80

Oracle HTTP Server will not start on ports below 1024 because on Unix systems only users with superuser authorizations can bind these ports.

Perform the following steps to enable Oracle HTTP Server to run as root on ports below 1024:

Log in as root.

Run the following commands in the middle-tier Oracle home:

cd ORACLE_HOME/Apache/Apache/bin
chown root .apachectl
chmod 6750 .apachectl

root has no powers on NFS drive

In our shared application file system instance, where a SAN mount on database server is NFS mounted on 4 application servers, we were installing Olite.  Olite requires running root.sh script. But the root.sh always gave permission denied whenever we ran it from the application tier. This was happening because the uid and gid of root did not match on the application server and the database server.  Application Server was installed as Solaris 10 where root's gid is 0.  DB server was upgraded from Solaris 8 to Solaris 10.  In Solaris 8 the root user's gid is 1.  After changing the root user's gid to 0, the script ran fine.

NFS does NOT behave the same as a local filesystem, especially for root! NFS is a very non-secure method to share filesystems and unless the NFS server exports the filesystem with a special root option, the root user on the local computer is nobody (literally!). That's why there is a 'nobody' user and it has a user ID of -2. This user has NO special privileges. Th1e reason is fairly obvious. If a server were to blindly export filesystems to any computer on the network (with root=root privileges), then the contents of the filesystem could NEVER be trusted as any machine could trash any file or directory.

So even if you were root on your local box, chown will return EPERM (errno 1) because the NFS filesystem says you are nobody and therefore not the file's owner. /etc/exports on the NFS server can allow root access but one should be very careful to restrict root privileges for the above mentioned reasons.

_XSERVTransSocketCreateListener: failed to bind listener

Raju further reported that he was unable to start vncserver process and was getting errors:

$ perl /usr/local/bin/vncserver :55

New 'X' desktop is prod2039:55

Starting applications specified in /export/home/applmgr/.vnc/xstartup

Log file is /export/home/applmgr/.vnc/prod2039:55.log

$ cat /export/home/applmgr/.vnc/prod2039:55.log

_XSERVTransSocketCreateListener: failed to bind listener

_XSERVTransSocketUNIXCreateListener: ...SocketCreateListener() failed

_XSERVTransMakeAllCOTSServerListeners: failed to create listener for local

Fatal server error:

Failed to establish all listening sockets

xrdb: Connection refused

xrdb: Can't open display 'prod2039:55'

$

I did a truss perl /usr/local/bin/vncserver and found that it was unable to write to /tmp/.X11-unix

I logged in as root and changed the permissions of 

chmod 777 /tmp/.X11-unix

It worked after that.

Sometimes when you are unable to start vnc on a specified port the reason is that the socket for that port is owned by some other user:

$ cd /tmp/.X11-unix

$ ls -ltr

total 0

srwxrwxrwx   1 root     root           0 Nov 25 17:42 X0

The format is Xn where n is the port number.  For example the file X0 for port 0, X1 for port 1.

You should login as that user and remove the /tmp/.X11-unix/Xn

Unrecognized character \x7F at /usr/local/bin/vncpasswd line 1

Today, Raju reported this error while trying to change vnc password:

$ perl /usr/local/bin/vncpasswd

Unrecognized character \x7F at /usr/local/bin/vncpasswd line 1.

This indicates that there is something wrong with the file vncpasswd.  I checked the file:

$ cd /usr/local/bin

$ file vnc*

vncconnect:     ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

vncpasswd:      ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

vncserver:      executable /usr/local/bin/perl script

vncviewer:      ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

This error comes because vncpasswd is not a perl script but a binary executable file.  So the correct way to invoke vncpasswd is:

$ /usr/local/bin/vncpasswd

Password:

Verify:

OATM fails to migrate data in long columns

I came across a very frightening bug today. Bug # 7628795 which says that even though the migration report states that tables with long columns have successfully migrated, you should go through the $APPL_TOP/admin/$TWO_TASK/log/fndmtimestamp.log to check if errors like this are reported:

Io exception: Connection

refused(DESCRIPTION=(TMP=)(VSNNUM=168822016)(ERR=12505)(ERROR_STACK=(ERROR=(CODE =12505)(EMFI=4))))


TAR Number(If customer issued)

------------------------------

7238244.993

.

Problem Statement

-----------------

As a result of similar issue in Bug 5646392 or Note 402717.1

data in long columns were not moved over new table in the new TS.

.

apps.fnd_ts_mig_cmds does not show any failure but

fndmlong<>.log shows error like

Io exception: Connection

refused(DESCRIPTION=(TMP=)(VSNNUM=168822016)(ERR=12505)(ERROR_STACK=(ERROR=(CO

DE =12505)(EMFI=4))))

Exception occured in obtaining the connection.

.

Staging tables holding the long data <><> does not exist.

.

At this point all the data lost. We are looking for a way to salvage the data

from backup taken before OATM.

.

Keep in mind there are new data in the system so restore is last resort

unless there is no other option.

.

Environment and Access Information

----------------------------------

.

Steps to reproduce the issue

----------------------------

- 11.5.9 ATG PF_H.RUP4 OATM Migration. DB Version 10.2.0.2 64 bit

- Migration completed fine, status shows success for all the tables

- When system released for users flexfield related errors encountered

- Investigation revealed that some tables are missing data in long columns

- Further investigation revealed that all the tables having long column has

no data in these columns.

On the bug they have given this possible cause:

The error the customer is getting shows that there is an error connecting to the database using JDBC. In one similar issue, development state: The long tables are migrated using a java program and use a jdbc connection.

It is this jdbc connection that is failing and producing the error message.

This error can happen if you use SID instead of Service name when you connect to the database. Please retry with correct service name.

This is probably what needs to be attempted in the customer's case but would like to see the customer's tnsnames.ora file first.

Please upload tnsnames.ora file.

I investigated this error in current test iteration of OATM and sure enough we had one table which was missing long data:

fndmlong20090116041440.log:Exception while moving data of column USER_EXIT from table APPLSYS.FND_FLEX_VALIDATION_EVENTS to table APPLSYS.FND_FLEX_VALIDATION_EVENTS5101
fndmlong20090116041440.log:Thread 2 : Exception : java.sql.SQLException: Io exception: invalid arguments in call
fndmlong20090116041440.log:Io exception: invalid arguments in call
fndmlong20090116041440.log:Thread 2 : Exception : Bigger type length than Maximum

From a previous run we got this:

Thread 4 : Exception : java.sql.SQLException: Io exception: Bigger type length than Maximum
Exception while moving data of column SQL_STATEMENT_TEXT from table ALR.ALR_ALERTS to table ALR.ALR_ALERTS5357
Thread 2 : Exception : java.lang.ArrayIndexOutOfBoundsException: 3
Io exception: Bigger type length than Maximum

As per the bug there are 168 tables with long columns.

Another bug 5934043 says:

OATM uses JAVA programm to migrate tables with long column. It seems that the "Protocol Violation" error you got is thrown from JDBC driver. The exception was thrown when jdbc driver reads something from the RDBMS that it didn't expect.

Possible reason could be the protocol engine in the thin driver and the protocol engine in the RDBMS are out of sync. Please verify if the upgrade finished successfully. It looks like this is an env issue, not an OATM specific isssue.

As a workaround we planned this to protect the data:

Export the 170 tables which have long, longraw columns, before starting OATM.
Take a row count of all tables where long column is not null
Do the OATM migration
Verify if the data migrated correctly by checking the row counts.
If any data is missing in any table, truncate it, import back the table.

Oracle still hasn't given a satisfactory answer for the cause of this error. But thankfully during our Production run, we didn't face this issue.

Jan 2009 CPU impact

Jan 2009 CPU is out.  The metalink note id for E-Business Suite 11i and R12 is 738923.1.

If you are current on your CPU patches, you only need to apply 5 patches:

1 Database patch

4 E-Busines Suite patches related to ATG:

7582702 has these two class files:

OAAboutPGCO$1.class

OAAboutPGCO.class

7327712 replaces the library ccmsssvc.o and updates the fnd/include header files.

7567354 changes the package bodies of procedures Oracleconfigure and ICX_DEFINE_PAGES

7610955 changes the following xml files in $ICX_TOP/mds/icatalog/shopping/webui

CompareItemsRN.xml

ItemSourceRN.xml

ShoppingCartRN.xml

Oct 2008 DB CPU patch 7369190 increases .patch_storage by 4.6GB

Many of our non-Production instances have 10GB allocated to RDBMS 10.2.0 ORACLE_HOME.  While applying Oct 2008 CPU for Database (patch 7369190), the size of $ORACLE_HOME/.patch_storage increased from 2.7 GB to 7.3 GB.  It increased by 4.6 GB because all database CPU patches are cumulative in nature.  So Oct 2008 CPU includes all security patches for database released till Oct 2008.  The .patch_storage directory has a backup of all the files replaced during a patch and a restore script to backout the patch.  Due to this, the .patch_storage directory is actually bigger than the $ORACLE_HOME legitimate binaries and libraries.  If you don't want your patch to fail, make sure that your instance has at least 10 GB free in its 10.2.0 $ORACLE_HOME mount.  

The DBAs improvised by moving .patch_storage directory to a different mount and created a soft link called .patch_storage in $ORACLE_HOME.

As already mentioned by an anonymous reader of this blog, I just found out that With the latest OPatch versions for 10.2.0.x and 11.1.0.x, available for download as Patch 6880880, there is a new command 'util' which can perform several utility actions. One of them is the cleanup of all the backup directories under .patch_storage not needed for rollback purposes. 

To see all the different option for the util command you could use 'opatch util -help'

To delete the directories not needed for rollback interim patches we should use the 'cleanup' option.

'opatch util cleanup -help' will show the associated help for the cleanup option:

Metalink Note 550522.1 has the details.

I tried this on an instance where .patch_storage was 6.4 GB and after running the command:

opatch util cleanup

The 6.4 GB was reduced to 137 MB.

Cannot copy from windows clipboard to forms

Srinivas Reddy reported this issue to me today.  In a particular instance, if he copied any text from forms, he could paste it to a text file on his desktop.  However, if he copied something from a text file on desktop to forms, it did not paste on the forms.  Instead it retained the text that was copied in the form.  For example, "Active users" was the text in one of the fields in the form.  If you copied that, you could paste it in desktop.  However when you copied text "sample text" from desktop and did a Ctrl + V it did not paste "sample text" but "Active users".  I found metalink note 735387.1  which describes a similar issue:

Cause

JAR file corruption on the client side.

The JAR files stored in the local Jinitiator cache control the interaction between forms and the

desktop.

C:\Program Files\Oracle\Jinitiator 1.1.7.27\jcache\

(Jinitiator version may be different)

These can become corrupted or out dated following patching.

Solution

To implement the solution, please execute the following steps:

1. Recreate certificate with adjkey

2. Regenerate force all jar files

3. Delete all files in directory :

C:\Program Files\Oracle\Jinitiator 1.1.7.27\jcache\

(Jinitiator version may be different)

4. Retest the issue

5. Migrate the solution as appropriate to other environments.

I have told Srinivas about this.  He will implement this and let me know if this solved the issue.

MSONWS64.exe Signal 11 error in ASCP

After applying Jan - Oct 2008 critical patch update, ASCP plan failed with Signal 11 error on MSONWS64.exe.

In past experience, we have encountered this error when APS64_ORACLE_HOME, APS64_ORA_NLS33 and ORA_NLS33 are not set or set incorrectly.

We checked for these variables and found them missing.  The reason why they were missing is that they were present in $CONTEXT_NAME.env file.  When you run autoconfig, these files are regenerated and any manual changes to these files are lost.  All such variables should be set in the customization section of $APPL_TOP/admin/adovars.env to prevent them from disappearing after every autoconfig.

Anand Reddy set them in adovars.env, sourced the environment again and restarted services.  However the signal 11 error came again when plan was launched.  I decided to investigate this and had a hunch that it was due to those environment variables.

env |grep APS64 did not return anything

but echo $APS64_ORACLE_HOME and echo $APS64_ORA_NLS33 returned values.

That was strange.  If you execute $MSO_TOP/bin/MSONWS64.exe on unix prompt this is the expected output:

$ cd $MSO_TOP/bin

$ ./MSONWS64.exe

Oracle Home Set to ORACLE_HOME=$MSO_TOP/bin/nls8174

return value 0

Oracle NLS33 Set to ORA_NLS33=$MSO_TOP/bin/nls8174/ocommon/nls/admin/data

return value 0

Segmentation Fault(coredump)

Yes coredump is the expected output as you are not passing any values to this executable.

However in our instance which was giving signal 11 error the output was:

$ ./MSONWS64.exe

Segmentation Fault(coredump)

I took up an instance on which the expected output was appearing and copied the MSONWS64.exe of that instance to our instance which was erroring out.  Strangely the output remained:

$ ./MSONWS64.exe

Segmentation Fault(coredump)

On the working instance I did this:

unset APS64_ORACLE_HOME

The output became:

$ ./MSONWS64.exe

Oracle NLS33 Set to ORA_NLS33=$MSO_TOP/bin/nls8174/ocommon/nls/admin/data

return value 0

Segmentation Fault(coredump)

Then I did 

unset APS64_ORACLE_NLS33

The output matched our instance's output:

$ ./MSONWS64.exe

Segmentation Fault(coredump)

So my hunch had proved right, somehow these two environment variables APS64_ORACLE_HOME and APS64_ORA_NLS33 were not getting set.

I decided to check the adovars.env file:

# Begin customizations

#

#   Add any custom variables or additional variable settings in this section.

# For example, you may wish to update your PATH to ensure it lists the

# directory containing the "jre" executable, typically $OA_JRE_TOP/bin.

#

# Make sure PATH does not include other versions of the JRE.

# Make sure PATH does not include the JDK.

APS64_USE_EXPORT=NO

APS64_ORACLE_HOME=/erppgfb2/erpapp/appl/mso/11.5.0/bin/nls8174

APS64_ORA_NLS33=/erppgfb2/erpapp/appl/mso/11.5.0//bin/nls8174/ocommon/nls/admin/data

ORA_NLS33=/erppgfb2/erpapp/appl/mso/11.5.0/bin/nls8174/ocommon/nls/admin/data

# End customizations

That looked ok.  But wait, I thought what is the current shell:

$ echo $SHELL

/bin/sh

If the shell is sh, setting an environment variable with equal to notation only, doesn't work.

So if you say:

APS64_ORACLE_HOME=/erppgfb2/erpapp/appl/mso/11.5.0/bin/nls8174

and don't do an

export APS64_ORACLE_HOME

It won't register with the sh shell.  If the shell is ksh, then it can be registered without the export command.

I added the export commands:

# Begin customizations

#

#   Add any custom variables or additional variable settings in this section.

# For example, you may wish to update your PATH to ensure it lists the

# directory containing the "jre" executable, typically $OA_JRE_TOP/bin.

#

# Make sure PATH does not include other versions of the JRE.

# Make sure PATH does not include the JDK.

APS64_USE_EXPORT=NO;export APS64_USE_EXPORT

APS64_ORACLE_HOME=$MSO_TOP/bin/nls8174;export APS64_ORACLE_HOME

APS64_ORA_NLS33=$MSO_TOP//bin/nls8174/ocommon/nls/admin/data;export APS64_ORA_NLS33

#ORA_NLS33=/erppgfb2/erpapp/appl/mso/11.5.0/bin/nls8174/ocommon/nls/admin/data;

export ORA_NLS33

# End customizations

I commented ORA_NLS33 as Oracle doesn't recommend it, but we have seen it work in some situations.  However it was working in production without this variable, so I commented it.

To test, this Ashish ran a small plan to check if it worked.  The small plan completed successfully without a signal 11 error.  He tested with full plan which takes 1 hour to complete, and that also succeeded.

An error occurred while attempting to establish an Applications File Server connection

Anand Reddy reported this error:

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_DEVAPP. There may be a network configuration problem, or the TNS listener on node FNDFS_DEVAPP may not be running. Please contact your system administrator.

This error comes when tnsnames.ora entries are missing from 806 home on web tier.  When you are clicking View Log or View Output, the tnsnames.ora of 806 home on web tier is used to connect to FNDFS_* listener on concurrent tier.  

After ensuring that the missing FNDFS_  entries were present in the $TNS_ADMIN/tnsnames.ora of 806 ORACLE_HOME on web server, it worked fine.

Happy 2009 from noodles@ma2.seikyou.ne.jp

Today, I got lot of mails with subject: Happy 2009 from noodles@ma2.seikyou.ne.jp with this text:

From: Maria [mailto:noodles@ma2.seikyou.ne.jp]
Sent: Thu 1/3/2002 12:53 PM
To: @STRGP3
Subject: Happy 2009!

Maria sent you a New Year postcard.
Collect it here: http://youryearcard.com/?cardnum=d06657452eb0162e3
Best Wishes, Christmas-Egreetings

Clearly it was spam, as the card downloads an executable, which is a virus.

Here's more information about the virus: http://safeweb.norton.com/report/show?name=youryearcard.com :

Hard to Uninstall (what's this?)

Threats found: 3
Here is a complete list:
Threat Name: Hard to Uninstall
Signature (MD5): ccddda141a19d693ad9cb206f2ae0de9
Location: http://youryearcard.com/postcard.exe


Threat Name: Hard to Uninstall
Signature (MD5): ccddda141a19d693ad9cb206f2ae0de9
Location: http://itsfatherchristmas.com/postcard.exe


Threat Name: Hard to Uninstall
Signature (MD5): 044317a6e6a482e5d491d2cac932d3fd
Location: http://superyearcard.com/postcard.exe



Threats found: 2
Here is a complete list:
Threat Name: 4336
File name: /apps/Symantec/shasta/analysis/cache/cache_25/postcard.exe
Signature (MD5): ccddda141a19d693ad9cb206f2ae0de9
Location: http://itsfatherchristmas.com/postcard.exe


Threat Name: 4336
File name: /apps/Symantec/shasta/analysis/cache/cache_4/postcard.exe
Signature (MD5): 044317a6e6a482e5d491d2cac932d3fd
Location: http://superyearcard.com/postcard.exe

Cannot reconnect to gateway

Yesterday, Anand Reddy called me and told me that adrepctl.sh was unable to start the reports server. This was the error:

adrepctl.sh start

Cannot reconnect to gateway
Cause: Application Object Library is unable to reconnect to your gateway
ORACLE account after you unsuccessfully attempted to sign-on.
Action: Check that your gateway environment variable is set correctly.
starting Reports Server for OSSTEST1 on port 7079.

Cannot reconnect to gateway
Cause: Application Object Library is unable to reconnect to your gateway
ORACLE account after you unsuccessfully attempted to sign-on.
Action: Check that your gateway environment variable is set correctly.
adrepctl.sh: exiting with status 0'

Metalink Note 261584.1 has a solution for this problem.

The cause is wrong Entry for TWO_TASK in the dbc file under the $FND_TOP/secure directory. I checked the dbc file I found that TWO_TASK=Database instead of erp11i. Instead of manually modifying the dbc file, I decided to regenerate it.

$COMMON_TOP/admin/install/adgendbc.sh

adgendbc.sh started at Thu Jan 1 01:39:02 EST 2009


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 1 01:39:26 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : erp11i_erp11i
Old Value : erp11i_erp11i

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
Application server ID already exists for this host - loading..
ADD executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Trying to update information ...

java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

UPDATE call failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
java.sql.SQLException: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.FND_APP_SERVER_PKG", line 357
ORA-06512: at line 1
- Database error modifying the server

DBC generation failed with exit code 1

Updating Server Security Authentication
java.sql.SQLException: Io exception: Invalid number format for port number
Database connection to jdbc:oracle:thin:@host_name:port_number:database failed
Updating Server Security Authentication failed with exit code 1
adgendbc.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
$

I decided to check which sql was failing in the package:

1 select line,text
2 from dba_source
3 where line between 356 and 367
4 and name='FND_APP_SERVER_PKG'
5* AND TYPE='PACKAGE BODY'
SQL> /

LINE
----------
TEXT
--------------------------------------------------------------------------------
356
-- the value of columns that determines a Desktop Node.

357
select SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN,

358
SUPPORT_DB, PLATFORM_CODE


LINE
----------
TEXT
--------------------------------------------------------------------------------
359
into l_support_cp, l_support_forms, l_support_web, l_support_admin,

360
l_support_db, l_platform

361
from fnd_nodes


LINE
----------
TEXT
--------------------------------------------------------------------------------
362
where server_address = p_address;

363


364
IF SQL%notfound THEN


LINE
----------
TEXT
--------------------------------------------------------------------------------
365
RAISE no_data_found;

366
END IF;

367



12 rows selected.

1* select node_name,server_address from fnd_nodes
SQL> /

NODE_NAME SERVER_ADDRESS
------------------------------ ------------------------------
erp11i 192.168.10.1
APPS11I 192.168.10.1
APACHE11I 192.168.22.13
AUTHENTICATION *

As you can see the nodes ERP11i and APPS11I have the same server_address. That is the reason for ORA-01422: exact fetch returns more than requested number of rows. To correct it, I updated the correct server_address for erp11i.

SQL> update fnd_nodes
2 set server_address='192.168.21.1'
3 where node_name='erp11i';

1 row updated.

SQL> commit;

Commit complete.

I moved all existing dbc files in $FND_SECURE to a backup directory

cd $FND_SECURE
mkdir back
mv * back


$ ./adgendbc.sh

adgendbc.sh started at Thu Jan 1 02:00:39 EST 2009


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 1 02:00:46 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : erp11i_erp11i
Old Value : erp11i_erp11i

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
ADD executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erpaai.dbc

Trying to update information ...

cp: cannot access /erp11i/erpapp/appl/fnd/11.5.0/secure/template.dbc
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_ erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_ erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_s ize=5 fnd_jdbc_usable_check=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
cp: cannot access /erp11i/erpapp/appl/fnd/11.5.0/secure/template.dbc
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

DBC generation failed with exit code 1
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Unable to locate /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

DBC generation failed with exit code 1

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
cp: cannot access /erp11i/erpapp/appl/fnd/11.5.0/secure/template.dbc
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fn d_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Updating Server Security Authentication
AUTHENTICATION OFF executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
adgendbc.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END

adgendbc.sh failed as it could not find template.dbc file in $FND_SECURE. So I moved it back into $FND_SECURE from backup:

mv $FND_SECURE/back/template.dbc $FND_SECURE

I re-ran adgendbc.sh:

$ ./adgendbc.sh

adgendbc.sh started at Thu Jan 1 02:03:02 EST 2009


Enter the APPS username: apps

Enter the APPS password:

SQL*Plus: Release 8.0.6.0.0 - Production on Thu Jan 1 02:03:09 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : APPS_DATABASE_ID
Level Id : 10001
New Value : erp11i_erp11i
Old Value : erp11i_erp11i

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


Params=fnd_jdbc_stmt_cache_free_mem=TRUE
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false


Unique constraint error (00001) is OK if key already exists
Application server ID already exists for this host - loading..
ADD executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Trying to update information ...

Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i.justanexample.com_erp11i.dbc

Generating /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
DBC argument specified, ignoring SECURE_PATH
Changed GUEST_USER_PWD to GUEST/ORACLE
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_stmt_cache_free_mem=TRUE fnd_jdbc_buffer_min=1 fnd_jdbc_buffer_max=5
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_buffer_decay_interval=300 fnd_jdbc_buffer_decay_size=5 fnd_jdbc_usable_check=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
Updating parameters: fnd_jdbc_context_check=true fnd_jdbc_plsql_reset=false
DBC argument specified, ignoring SECURE_PATH
UPDATE executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc

Updating Server Security Authentication
AUTHENTICATION OFF executed successfully - /erp11i/erpapp/appl/fnd/11.5.0/secure/erp11i_erp11i.dbc
adgendbc.sh exiting with status 0
ERRORCODE = 0 ERRORCODE_END
$


$ adrepctl.sh start

You are running adrepctl.sh version 115.33

starting Reports Server for erp11i on port 7005.

adrepctl.sh: exiting with status 0

$