Wednesday, 17 October 2012

R12: Apache Is Required To Be Bounced To See Item Update [ID 740222.1]

Cause

This issue is related to the cache component, so when someone update an item's information it will not be reflected in the customer UI because iStore will search the description in the cache and not in the database.

Solution

I. Set the Item Cache's Time-To-Live as short as possible
1. Use Functional Administrator responsibility
2. Go to Core Services > Caching Framework > Tuning
3. Search for Cache Component Name "Item Cache", click on Go
4. Click on Update
5. Configure the Cache Component as follow
Time Out Type: Time to Live
Time Out After: 5 Minutes

OR

II. Clear the cache relevant for the flow that you're using, in this case IBE_ITEM_CACHE.
1. Use Functional Administrator responsibility
2. Go to Core Services > Caching Framework > Name = Item Cache > Go
3. Check Select and press 'Clear Cache'

OR

III. Clear all the caches across all the mid-tier
1. Use Functional Administrator responsibility
2. Go to Core Services > Caching Framework > Global Configuration
3. Click on button Clear All Cache, then on Yes

For more details refer to NOTE 395439.1

GRANTING THE SELECT TO A PARTICULAR VIEW:

How to grant SELECT access to v$session to other users?

December 18th, 2008 | Tags: Grant, V$session, v_$session

One can’t grant direct access V$session as v$session is a synonym.

SQL> GRANT SELECT ON v$session TO scott;

grant select on v$session to test

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

– shows the V$SESSION is a public synonym

SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE

———————— ——————-

PUBLIC SYNONYM

– shows the object (table/view) the synonym points to

SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME

————– ——————————

SYS V_$SESSION

But one can grant access the underlying table/view.

Saturday, 9 June 2012

How to Count Total Number of Users Connected to ORACLE Application [ID 295206.1]

		Modified 04-OCT-2008 Type HOWTO Status MODERATED

In this Document
  Goal
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Application Object Library - Version: 11.5.9
Information in this document applies to any platform.

Goal

Want to determine how many users are connected to Oracle apps 11i,V$session does not give the true picture as mostly there are more than 1 oracle session for the same forms connection depending on how many forms the user has opened up.

Solution

1:- Can use this SQL statement to count concurrent_users in Oracle apps:
select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

2:- In the Oracle Applications Manager, go to the site map, and select the "Applications Usage"
option in the Activity region. There you will be able to report on many usage questions, such as:

Products Installed
Application Users Per Module Summary
Applications Usage Reports
Suppliers
Purchase Line Items Processed - Internet Supplier Portal
Purchase Line Items Processed - Purchasing Intelligence
Order Entry Lines Processed - Ordering Application
Purchase Line Items Processed - iProcurement
Expense Reports Processed - Internet Expense
Invoice Line Items Processed - Accounts Receivables

Note the Application Users per module summary is based on the number of users that have Active responsibilities for the Application module. I am not aware of there being any check on last usage date for the responsibility.

3:- Also if you are using responsibilities connected to a custom-application, which is standard Consulting practice in some countries, those users will be counted as users of the custom-application and not users of the actual Application-module for the screens they are accessing.

4:- Run the following queries:-
    This will give the number of users on the system in the past 1 hour.
   select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

    This will give the number of users on the system in the past 1 day.
    select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

    This will show the activity in the last 15 minutes.
    select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",

user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

5:- Check the Note:233871.1 which will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.

Surely this will affect the performance but how it affects is based on the number of users logged in and accessing the forms.

Factoring in System Overhead
========================

In planning your organization€™ Sign€“On Audit implementation, you should consider the additional system overhead required to precisely User and Data Auditing monitor and audit your users as they access Oracle Applications. The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.

References

NOTE:233871.1 - Oracle Application Object Library Active Users Data Collection Test

Oracle Application Object Library Active Users Data Collection Test [ID 233871.1]

This test may already be available within your E-Business suite!
To execute the test, do the following:

Login to Oracle E-Business Suite
Select the responsibility "Oracle Diagnostics Tool" (see Note 358831.1 for details)
Select application "Oracle Application Object Library" from the "Application" list of values
Click the "Advanced" tab
Scroll down to group "Data Collection"
Select test name "Active Users"
Input Parameters (* required)
Output contains

Product Installation Status and Patchset Level

Parameters

Key Profile Options

Self Service Web Application Users

Forms-Based Products Users

Concurrent Programs Users

References

This test is available in Oracle Diagnostics 2.5 which introduces a new security model for test execution. See Note 409141.1 for more details.
If this test is not available or any of the above steps fail, click on the "Installation Guide" link in the "Reference & Resource Menu".

SQL Query To List Running Concurrent Requests:

col user_name format a20 word_wrapped
col ProgName format a25 word_wrapped
col requestId format 99999999
col StartDate format a20 word_Wrapped
col OS_PROCESS_ID format a6
col ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
set lines 200

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,’DD-MON-YYYY HH24:MI:SS’) StartDate,
request_id RequestId,
(sysdate – actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = ‘US’
and fcr.phase_code = ‘R’
and fcr.status_code = ‘R’
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC
/

1. Use this Script to get current sql statement that is running in the background for a given concurrent request.
2. Get all the sql statements that are being executed by a user.
3. Get all the blocking sessions

-- GET THE CURRENT SQL STATEMENT RUNNING FOR A CONCURRENT REQUEST
 
SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
       E.SQL_TEXT
  FROM APPS.FND_CONCURRENT_REQUESTS A,
       APPS.FND_CONCURRENT_PROCESSES B,
       V$PROCESS C,
       V$SESSION D,
       V$SQL E
 WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
   AND C.PID = B.ORACLE_PROCESS_ID
   AND B.SESSION_ID = D.AUDSID
   AND D.SQL_ADDRESS = E.ADDRESS
   AND A.REQUEST_ID = &REQUEST_ID;
 
 
--SQL STATEMENTS RUNNING BY A USER   
SELECT A.SID, A.SERIAL#, B.SQL_TEXT
  FROM V$SESSION A, V$SQLAREA B
 WHERE A.SQL_ADDRESS = B.ADDRESS AND A.USERNAME = 'APPS';
 
 
-- GET THE BLOCKING SESSIONS    
SELECT   BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, SECONDS_IN_WAIT
    FROM V$SESSION
   WHERE BLOCKING_SESSION IS NOT NULL
ORDER BY BLOCKING_SESSION;

Thursday, 24 May 2012

Where are profile options stored

Tables of Profile Options:

FND_PROFILE_OPTIONS
FND_PROFILE_OPTION_VALUES
FND_PROFILE_OPTIONS_TL

Wednesday, 23 May 2012

Responsibility Not Visible to user in the list of responsibilites assigned to User:

Recently came to know that though the system adminstrator is given the responsibility access to users, users are not able to view the responsibilities in the list of responsibilities assigned.

1. Check if the responsibility is not end dated.
2. Run the request " Syn responsibility role data into the WF table".
3. Run the request " Synchronize WF LOCAL tables" enter the paramters
- Orig System: ALL
- Parallel Process:0
- Logging Mode: LOGGING (Logging Activated)
- Temporary Tablespace : Blank
- Riase Errors: Yes
4. Run the request "Workflow Directory Services User/Role Validation" enter the paramters
- p_BatchSize:10000
- Fix dangling user/roles : Yes
- Add missing user/role assignments : Yes
- Update WHO columns in WF tables: Yes
5. Log off and login back and verify that the responsibility appears.

That should resolve the issue.

If the problem is not resolved still ask the sysadmin to start the following servives
Navigation: System Administrator > Workflow > Oracle application manager > Workflow Manager
- Workflow Deffered Notification Agent Listner
- Workflow Error Agent Listner
- Workflow Java Deffered Agent Listner
- Workflow Java Error Agent Listner.
In case still there is a issue please raise a Service request with Oracle.

Monday, 21 May 2012

Error: Viewing CM log files – “File server could not verify its initialization parameters” – Oracle Apps 11i

Some times we face following error while viewing CM log files from Concurrent Manager Administer screen.

‘File server could not verify its initialization parameters’

Cause:

The issue is because of the syntax error in listener.ora file on appmgr side.
If you check the parameter APPLFSTT in listener.ora on appmgr side (cd $TNS_ADMIN),
you will see there is a space between the value.

Example:

APPLFSTT=PQP10MS6_806_BALANCE; PQP10MS6_FO; PQP10MS6_BALANCE; PQP10MS6

FNDFS cannot interpret the space between the value

Workaround:

Remove the space between the values and bounce apps listener.

Permanent Fix:

As per metalink note ID 304568.1, apply patch 4244610 to the environment.

Scenario: Enable FRD(Forms Runtime Diagonostic) Trace

Solution:

1. Login into SYSADMIN --> System Administrator --> Profile --> System
Search the profile option for "ICX Forms Launcher" and Make sure Site and User leave has been checked.

2. Set the value following values at user level only according to the version

3. For R12, http://url:/OA_HTML/frmservlet?record=collect

4. For 11i, http://url:/dev60cgi/f60cgi?&record=collect&log=<>

5. Get the FRD trace from $FORMS_TRACE_DIR path at OS Level

APP-FND-01542 : This Application Server is not authorized to access this system

Scenario:
======= When you are trying to access the Front end URL it is not giving some error. So , you are using http://hostname:port/dev60cgi/f60cgi and trying to login to the server which gives the following error.

Error:
=====
“APP-FND-01542 : This Application Server is not authorized to access this system. Please contact your System Administrator. ”

Solution:
=======
Step 1 : Edit the Context file ie) $APPL_TOP/admin/SID_hostname.xml.
Step 2 : Change the Variable : s_appserverid_authentication to be OFF.Step
3 : Run Autoconfig $ cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME$ sh adautocfg.sh

Now, you can test the application login.

Note :
=====

There are three modes of authentication you can configure your applications by setting the parameter
s_appserverid_authentication to ON :
s_appserverid_authentication to Partial
s_appserverid_authentication to SECURE : activates full server security (SECURE mode)
s_appserverid_authentication to OFF : deactivates server security
By default in 11.5.10, this is set to SECURE. In previous 11i versions, this wasset to OFF.

Scenario:

=========

While applying a big patch, suddenly it has been failed due to some prereq patch is missing.

Solution:

1. Shutdown the workers using "adctrl" utility.

a. adctrl

-- It will ask some prompts and enter those

Select option "Tell worker to shutdown/quit"

2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema

a. sqlplus applsys/

b. create table fnd_Install_processes_back as select * from fnd_Install_processes;

c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes_back; select count(*) from fnd_Install_processes;

3. Backup the AD_DEFERRED_JOBS table.

a. sqlplus applsys/

b. create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;

c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS;

4. Backup the .rf9 files located in $APPL_TOP/admin/restart directory.

At this point, the adpatch session should have ended and the cursor should be back at the Unix prompt.

a. cd $APPL_TOP/admin/

b. mv restart restart_orig

c. mkdir restart

5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.

a. sqlplus applsys/

b. drop table FND_INSTALL_PROCESSES;

c. drop table AD_DEFERRED_JOBS;

6. Apply the new patch.

7. Restore the .rf9 files located in $APPL_TOP/admin//restart_back directory.

a. cd $APPL_TOP/admin/

b. mv restart restart_

c. mv restart_orig restart

8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema.

a. sqlplus applsys/

b. create table fnd_Install_processes as select * from fnd_Install_processes_back;

c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes; select count(*) from fnd_Install_processes_back;

9. Restore the AD_DEFERRED_JOBS table.

a. sqlplus applsys/passwd

b. create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;

c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS;

10. Re-create synonyms

a. sqlplus apps/apps

b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;

c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

Scenario: While exporting file from front-end, got error like below:

fndgfm.jsp error : Java Exception<pre>java.lang.NullPointerException at _fndgfm.getExportFileExtn(_fndgfm.java:275) at _fndgfm.downloadBlob(_fndgfm.java:187) at _fndgfm._jspService(_fndgfm.java:585) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:473) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26) at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15) at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:619)</pre>

Solution:

Export To Excel failed, fndgfm.jsp Error [ID 1364656.1]

	Modified 05-OCT-2011 Type PROBLEM Status PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Application Object Library - Version: 12.1.3 and later [Release: 12.1 and later ]
Information in this document applies to any platform.

Symptoms

Export to Excel errors for all type data

=== ERROR Message in Excel file ===

fndgfm.jsp error : Java Exception
<pre>
java.lang.NullPointerException
at _fndgfm.getExportFileExtn(_fndgfm.java:287)
at _fndgfm.downloadBlob(_fndgfm.java:188)
at _fndgfm._jspService(_fndgfm.java:591)
at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:473)
at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:642)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:391)
at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:908)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:458)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:619)
</pre>

Cause

Either Patch:12675589 is needed
or JSPs are out of sync.

Solution

Please make sure that Patch:12675589
- ONE-OFF: FORMS INLINE ATTACHMENT which contains fndgfm.jsp 120.17.12010000.10
is applied
Please re-compiled all JSP's.
For details, please see Note:783094.1 - Compile jsp files at Application R12 at Windows

References

NOTE:1356402.1 - EBS Export Creates File Fndgfm.Jsp With java.lang.NullPointerException
NOTE:464469.1 - Export To Excel: How To Activate Debugging?
NOTE:783094.1 - Compile JSP Files Fail on Oracle Application R12 At Windows
NOTE:870221.1 - How To Export Purchase Order (PO) Header And Line Data For Multiple POs From iSupplier?

Tuesday, 8 May 2012

ORA-04030: out of process memory

SQL> alter package apps.XLA_00200_AAD_S_000010_PKG COMPILE BODY;
alter package apps.XLA_00200_AAD_S_000010_PKG COMPILE BODY
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 4108 bytes (PLS
non-lib hp,pdzgM64_New_Link)
Solution1.SQL>select object_name,object_type from dba_objects where status='INVALID';
OBJECT_NAME OBJECT_TYPE
----------------------------------- -------------------
XLA_00200_AAD_S_000010_PKG PACKAGE BODY
XLA_00200_AAD_S_000011_PKG PACKAGE BODY

2. SQL> alter system set plsql_optimize_level =0;
System altered.
3.SQL> alter package apps.XLA_00200_AAD_S_000010_PKG COMPILE BODY;
Package body altered.

4.SQL> alter package apps.XLA_00200_AAD_S_0000011_PKG COMPILE BODY;
Package body altered.

5.SQL> alter system set plsql_optimize_level =2;
System altered.

Enable Trace in Oracle E-Business Suite R12

Debugging tool for HTTP, OC4J and OPMN in E-Business Suite R12

How To Enable and Collect Debug for HTTP, OC4J and OPMN
Shutdown the http server, oc4j and opmn services

adapcctl.sh stop
adoacorectl.sh stop
adformsctl.sh stop
adoafmctl.sh stop
adopmnctl.sh stop

Enable http ODL logging
-----------------------
$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf
OraLogMode oracleOraLogSeverity TRACE:32OraLogDir $LOG_HOME/ora/10.1.3/Apache/oracle

Please use the full path to $LOG_HOME e.g. OraLogDir /u01/inst/apps/JCB_atg/logs/ora/10.1.3/Apache/oracle

mkdir $LOG_HOME/ora/10.1.3/Apache/oracle
Increase OC4J logging for oacore
-------------------------------
edit j2ee-logging.xml adjust the following in file:

$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/j2ee-logging.xml

How To License A New Product in Oracle Applications

1. After purchasing a license for an Application Product/Module, the first thing is to use license manager to record that the product/module is now licensed. License manager can be run 2 ways:
a. log in through Oracle Application Manager (OAM) and select Site Map -> Administration tab -> License Manager
b. run adlicmgr.sh from AD_TOP/bin
Using both methods you can license by Module or by individual Product. Select the check box for the Module/Product to be licensed and Save/Next
2. The Products are now licensed but not ready to be used as none of the Forms/Packages etc would have been compiled and loaded into the Database for these new Products. Use adadmin to generate the files for the new Product/s
a. run adadmin
b. Select Generate Application Files menu
c. Select all of the following Menu options:
- Generate message files
- Generate forms files
- Generate report files
- Generate graphic files
- Generate product JAR files
d. Back to Main Menu
e. Select Compile/Reload Applications Database Entities menu
f. Select all of the following Menu options:
- Compile APPS schema
- Compile menu information
- Compile flexfields
3. Once complete, check that the Forms/JSPs for the new Products licensed now work. The Products are now ready to be configured.
4. Run autoconfig.