Skip navigation.

Vikram Das

Syndicate content
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps
Updated: 8 hours 31 min ago

Table TXK_TCC_RESULTS needs to be installed by running the EBS Technology Codelevel Checker (available as patch 17537119).

Wed, 2014-11-19 12:06
I got this error while trying to apply a patch in R12.2:

 [EVENT]     [START 2014/11/19 09:18:39] Performing database sanity checks
   [ERROR]     Table TXK_TCC_RESULTS needs to be installed by running the EBS Technology Codelevel Checker (available as patch 17537119).

This table TXK_TCC-RESULTS is created in APPLSYS schema, by the latest version of the checkDBpatch.sh script delivered by 17537119.
So go ahead, download patch 17537119.  
Login as oracle user on your database node.Source environmentcd $ORACLE_HOME/appsutilunzip p17537119*$ ./checkDBpatch.sh 
+===============================================================+ | Copyright (c) 2005, 2014 Oracle and/or its affiliates. | | All rights reserved. | | EBS Technology Codelevel Checker | +===============================================================+ 
Executing Technology Codelevel Checker version: 120.18 
Enter ORACLE_HOME value : /exampler122/oracle/11.2.0 
Enter ORACLE_SID value : exampler122
Bugfix XML file version: 120.0.12020000.16 
Proceeding with the checks... 
Getting the database release ... Setting database release to 11.2.0.3 
DB connectivity successful. 
The given ORACLE_HOME is RAC enabled. NOTE: For a multi-node RAC environment - run this tool on all non-shared ORACLE_HOMEs. - run this tool on one of the shared ORACLE_HOMEs. 

Created the table to store Technology Codelevel Checker results. 
STARTED Pre-req Patch Testing : Wed Nov 19 10:53:00 EST 2014 
Log file for this session : ./checkDBpatch_7044.log 
Got the list of bug fixes to be applied and the ones to be rolled back. Checking against the given ORACLE_HOME 

Opatch is at the required version. 
Found patch records in the inventory. 
All the required one-offs are present in Oracle Database Home 
Stored Technology Codelevel Checker results in the database successfully. 
FINISHED Pre-req Patch Testing : Wed Nov 19 10:53:03 EST 2014 
========================================================= 
1 select owner,table_name from dba_tables 2* where table_name='TXK_TCC_RESULTS' SQL> / 
OWNER TABLE_NAME ------------------------------ ------------------------------ APPLSYS TXK_TCC_RESULTS 
SQL> 
Once you have done this, restart your patch with adop with additional parameter restart=yes
Categories: APPS Blogs

Mystery of java.sql.SQLRecoverableException: IO Error: Socket read timed out during adop/adpatch

Tue, 2014-11-11 21:19
While applying the R12.2 upgrade driver, we faced the issue of WFXLoad.class failing in adworker log but showing up as running on adctrl

        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     2  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     3  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     4  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     5  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     6  Run       AutoPatch R120 pl                              Wait        
     7  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     8  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     9  Run       AutoPatch R120 pl  WFXLoad.class               Running      
    10  Run       AutoPatch R120 pl                              Wait        

adworker log shows:
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Socket read timed out        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:482)        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:678)        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:238)        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)        at java.sql.DriverManager.getConnection(DriverManager.java:571)        at java.sql.DriverManager.getConnection(DriverManager.java:215)        at oracle.apps.ad.worker.AdJavaWorker.getAppsConnection(AdJavaWorker.java:1041)        at oracle.apps.ad.worker.AdJavaWorker.main(AdJavaWorker.java:276)Caused by: oracle.net.ns.NetException: Socket read timed out        at oracle.net.ns.Packet.receive(Packet.java:341)        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:308)        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1222)        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:330)        ... 8 more
This was happening again and again. The DBAs were suspecting network issue, cluster issue, server issue and all the usual suspects.  In Database alert log we saw these errors coming every few seconds:
Fatal NI connect error 12537, connecting to: (LOCAL=NO)
  VERSION INFORMATION:        TNS for Linux: Version 11.2.0.3.0 - Production        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production  Time: 11-NOV-2014 19:58:19  Tracing not turned on.  Tns error struct:    ns main err code: 12537
TNS-12537: TNS:connection closed    ns secondary err code: 12560    nt main err code: 0    nt secondary err code: 0    nt OS err code: 0opiodr aborting process unknown ospid (26388) as a result of ORA-609

We tried changing the parameters in sqlnet.ora and listener.ora as instructed in the article:Troubleshooting Guide for ORA-12537 / TNS-12537 TNS:Connection Closed (Doc ID 555609.1)
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120
However, the errors continued.  To rule out any issues in network, I also restarted the network service on Linux:
service network restart
One thing which I noticed was the extra amount of time that the connect was taking 4 seconds:
21:17:38 SQL> conn apps/appsConnected.21:17:42 SQL> 
Checked from remote app tier and it was same 4 seconds.
Stopped listener and checked on DB server that uses bequeath protocol:
21:18:47 SQL> conn / as sysdbaConnected.21:18:51 SQL> conn / as sysdbaConnected.
Again it took 4 seconds.
A few days back, I had seen that connect time had increased after turning setting the DB initialization parameter pre_page_sga to true in a different instance.  On a hunch, I checked this and indeed pre_page_sga was set to true.  I set this back to false:
alter system set pre_page_sga=false scope=spfile;shutdown immediate;exitsqlplus /nologconn / as sysdbastartupSQL> set time on22:09:46 SQL> conn / as sysdbaConnected.22:09:49 SQL>
The connections were happening instantly.  So I went ahead and resumed the patch after setting:
update fnd_install_processes set control_code='W', status='W';
commit;
I restarted the patch and all the workers completed successfully.  And the patch was running significantly faster.  So I did a search on support.oracle.com to substantiate my solution with official Oracle documentation.  I found the following articles:
Slow Connection or ORA-12170 During Connect when PRE_PAGE_SGA init.ora Parameter is Set (Doc ID 289585.1) Health Check Alert: Consider setting PRE_PAGE_SGA to FALSE (Doc ID 957525.1)
The first article (289585.1) says:PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off. The advantage that PRE_PAGE_SGA can afford depends on page size.
The second article (957525.1) says:Having the PRE_PAGE_SGA initialization parameter set to TRUE can significantly increase the time required to establish database connections.
The golden words here are "Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.".  That is exactly what happens when you do adpatch or adop.
Keep this in mind, whenever you do adpatch or adop, make sure that pre_page_sga is set to false.  It is possible that you may get the error "java.sql.SQLRecoverableException: IO Error: Socket read timed out" if you don't.  Also the patch will run significantly slower if pre_page_sga is set to true.  So set it to false and avoid these issues.


Categories: APPS Blogs

oracle.ias.cache.CacheFullException: J2EE JOC-017 The cache is full

Sat, 2014-09-27 10:40
Yesterday, the users of an EBS R12.2 instance got this error when they logged in:

Error Page
You have Encountered an unexpected error.  Please contact the System Administrator for assistance.

On checking the $EBS_DOMAIN_HOME/servers/oacore_server1/logs/oacore_server1.out, we found this error:



oracle.ias.cache.CacheFullException: J2EE JOC-017 The cache is full.
       at oracle.ias.cache.CacheHandle.findObject(CacheHandle.java:1680)
       at oracle.ias.cache.CacheHandle.locateObject(CacheHandle.java:1118)
       at oracle.ias.cache.CacheAccess.get(CacheAccess.java:877)
       at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:771)
       at oracle.apps.jtf.cache.CacheManager.getInternal(CacheManager.java:4802)
       at oracle.apps.jtf.cache.CacheManager.get(CacheManager.java:4624)
       at oracle.apps.fnd.cache.AppsCache.get(Unknown Source)
       at oracle.apps.fnd.functionSecurity.Grant.getGrantArray(Unknown Source)
       at oracle.apps.fnd.functionSecurity.Authorization.getFunctionSecurityGrantedMenusForGrantee(Authorization.java:829)
       at oracle.apps.fnd.functionSecurity.Authorization.getFunctionSecurityGrantedMenus(Authorization.java:744)
       at oracle.apps.fnd.functionSecurity.Authorization.getFuncSecGrants(Authorization.java:251)
       at oracle.apps.fnd.functionSecurity.Authorization.testMenuTreeFunction(Authorization.java:499)
       at oracle.apps.fnd.functionSecurity.Navigation.getMenuTree(Navigation.java:254)
       at oracle.apps.fnd.functionSecurity.Navigation.getMenuTree(Navigation.java:279)
       at oracle.apps.fnd.functionSecurity.Navigation.getMenuTree(Navigation.java:160)
We tried bouncing services and deleting $EBS_DOMAIN_HOME/servers/oacore_server1/cache.  None of those actions helped.  Things got back to normal only after the Xmx, Xms,and permsize startup parameters for the oacore JVM were changed in weblogic console on Gary's suggestion:
-XX:PermSize=512m -XX:MaxPermSize=512m –Xms4096m –Xmx4096m
I also changed it in the context file:
Old: s_oacore_jvm_start_options">-XX:PermSize=128m -XX:MaxPermSize=384m -Xms512m -Xmx512mNew: s_oacore_jvm_start_options">-XX:PermSize=512m -XX:MaxPermSize=512m –Xms4096m –Xmx4096m
The oacore_server1 and oacore_server2 were bounced after this.  We haven't seen that error ever since.

There is a support.oracle.com article: Receive Intermittent Error You Have Encountered An Unexpected Error. Please Contact Your System Administrator (Doc ID 1519012.1)CauseThere are user accounts having extremely high numbers of FUN_ADHOC_RECI_XXXXXXX / FUN_ADHOC_INIT_XXXXXXX assigned.

Users have an extremely high number of (ADHOC) ROLES assigned to them, so when these attempt to login this fills the JOC ( Java object cache ) and causes it to run into it's limits resulting in the errors reported. Once bounce is done all is working fine until such an user logs in again.

While working in AGIS and creating and progressing batches, in the workflow there are several ad hoc roles created which remain on the system and do not get end dated or deleted. This can cause performance issues.
Ad Hoc Roles in WF_LOCAL_ROLES starting with FUN_ADHOC_RECI_XXXXXXX ; FUN_ADHOC_INIT_XXXXXXX with no expiration_date.

A. Run the following SQL to verify if there are accounts having extreme numbers of roles assigned

SQL> SELECT user_name, count(*) FROM wf_user_roles WHERE role_name <> user_name GROUP BY user_name ORDER BY 2;


B. Run following for particular user

SQL> SELECT distinct role_name FROM wf_user_roles
WHERE user_name = cp_user_name
or (user_name = (SELECT name FROM wf_local_roles wlr, fnd_user fusr
WHERE fusr.person_party_id = wlr.orig_system_id
AND wlr.orig_system = 'HZ_PARTY'
AND fusr.user_name = cp_user_name
AND rownum < 2))
AND role_name <> user_name;

Note: Replace cp_user_name with name of user having high number of ADHOC roles


SolutionTo implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Follow the steps given in document to purge the WF_LOCAL_ROLES for the AGIS transactions in 'COMPLETE' status.

AGIS: HOW TO DELETE AD HOC ROLES CREATED IN WORKFLOW (Doc ID 1446561.1)

3. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.



We had also logged SR with Oracle where they pointed us to the very same article and also asked us to do the following:

Action Plan
===========

1. How to find out the existing adhoc roles?

select name, start_date, start_date, expiration_date
from wf_local_roles
where orig_system = 'WF_LOCAL_ROLES'
order by name;

2. Define an expiration date for the ad hoc role:

exec WF_DIRECTORY.SetAdHocRoleExpiration (role_name=> >,expiration_date=>sysdate-1);


3. Periodically, purge expired users and roles in order to improve performance.

exec WF_PURGE.Directory(end_date);

This purges all users and roles in the WF_LOCAL_ROLES,WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS tables whose expiration date is less than or
equal to the specified end date and that are not referenced in any notification.

Parameter: end_date Date to purge to.

For more information, please refer to Oracle Workflow API Reference on page 2 – 128.
Use the workflow API's to purge the ad hoc roles:

NOTE:
After end dating the adhoc roles, the expired adhoc roles can also be purged by running the Purge Obsolete Workflow Runtime Data concurrent program. Make sure the "Core Workflow Only" parameter set to N.
Oracle also shared 3 open bugs (unpublished, can be read only Oracle employees) for this issue:
Bug 19025537 : ORACLE.IAS.CACHE.CACHEFULLEXCEPTION: J2EE JOC-017 THE CACHE IS FULL.
Bug 11772304 : JOC INVESTIGATION WITH 12.2
Bug 19582421 : R12.2 THE CACHE IS FULL; EXCEPTION IN OACORE_SERVER1 LOG.

Oracle finally shared the contents of Bug 19582421:

Action Plan
==========

Please review the following from Bug 19582421 : R12.2 THE CACHE IS FULL; EXCEPTION IN OACORE_SERVER1 LOG.


Workaround Steps:

1. Extract CacheDefaultConfig.xml from cache.jar


cd $FMW_HOME/oracle_common/modules/oracle.javacache_11.1.1
jar -xf cache.jar CacheDefaultConfig.xml

2. Edit CacheDefaultConfig.xml.
- diskCache size was the parameter that fixed it.
- changed the max-objects as well as per the bug.

Original:

xmlns="http://www.oracle.com/oracle/ias/cache/configuration11"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" max-objects="5000"
max-size="10" private="false" cache-dump-path="jocdump" system="false"
clean-interval="60" version="11.1.1.2.0" internal-version="110000">

init-retry-delay="2000" enable-ssl="false" auto-recover="false">
dedicated-coordinator="false" outOfProc="false">




default-level="SEVERE"/>




Modified:

xmlns="http://www.oracle.com/oracle/ias/cache/configuration11"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" max-objects="100000"
max-size="50" private="false" cache-dump-pa
th="jocdump" system="false" clean-interval="60" version="11.1.1.2.0"
internal-version="110000">

init-retry-delay="2000" enable-ssl="false" auto-recover="false">
dedicated-coordinator="false" outOfProc="false">





default-level="SEVERE"/>




3. Upload the changed file to the jar file

jar uf cache.jar CacheDefaultConfig.xml

4. Modify javacache.xml and make the same changes. This file is probably not
getting used. But made the changes anyway to keep the values in sync.
cd $EBS_DOMAIN_HOME/config/fmwconfig/servers/oacore_server1

5. Bounce apache and oacore.

These 5 steps resolved the issue.
Categories: APPS Blogs