Oracle Applications APPSLab: 11i

Showing posts with label 11i. Show all posts

Thursday, June 19, 2008

Script to Monitor Concurrent Jobs and Hanging Sessions

Here is a monitoring system to monitor all concurrent jobs, concurrent managers and hung sessions every

hour proactively and take appropriate action immediately. It gives the following reports

1. List of Concurrent Jobs that completed with error in last one hour.
2. List of Concurrent Jobs running for more then 1 hour.
3. List of concurrent Jobs completed with Warning in last one hour
4. List of Jobs that are Pending Normal for more than 10 Minutes.
5. List of Hung sessions or Orphan sessions.
6. List of Concurrent managers with Pending Normal jobs.
7. Critical Jobs completed in last one hour with completion time.

SELECT A FROM

(

select 'CONCURRENT PROGRAMS COMPLETED WITH ERROR STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'A' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'A' B,1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'A' B,1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'A' B, 1.4 SRT from fnd_conc_req_summary_v conc

where actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'E'

UNION

select RPAD('-',125,'-') A, 'A' B,1.6 SRT from dual

UNION

SELECT ' ', 'A', 1.8 FROM DUAL

UNION

SELECT ' ', 'A', 1.86 FROM DUAL

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

UNION

select 'CONCURRENT PROGRAMS COMPLETED WITH WARNING STATUS BETWEEN '||to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'D' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'D' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'D' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'D' B, 1.4 SRT from fnd_conc_req_summary_v conc where

actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'G'

and concurrent_program_id not in (47654,31881,47737)

UNION

select RPAD('-',125,'-') A, 'D' B, 1.8 SRT from dual

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

UNION

SELECT ' ', 'D', 1.86 FROM DUAL

UNION

SELECT ' ', 'D', 1.88 FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT ARE PENDING NORMAL FOR THE PAST 10 MINUTES ' A, 'E' B,1 SRT

from dual

UNION

select RPAD('-',125,'-') A, 'E' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'E' B, 1.2 FROM DUAL

UNION

select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)

||' '||to_char(Parent_Request_ID) ) A, 'E' B, 2 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - REQUEST_DATE > 0.00694444444444444 AND REQUESTED_START_DATE < SYSDATE

AND PHASE_CODE = 'P' AND STATUS_CODE = 'Q'

UNION

select RPAD('-',125,'-') A, 'E' B, 3 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'E' B, 4.4 SRT FROM DUAL

UNION

select 'CONCURRENT PROGRAMS THAT STARTED BEFORE '||to_char(sysdate - (1/24),'dd-mon-yyyy hh24:mi:ss')

||' AND ARE STILL RUNNING ' A, 'B' B,4.6 SRT FROM DUAL

UNION

SELECT RPAD('-',125,'-') A, 'B' B, 4.8 SRT FROM DUAL

UNION

SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||

rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'B' B, 4.84 SRT FROM DUAL

UNION

SELECT to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,

'dd-mon-yyyy hh24:mi:ss'),'-'),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),

10)||' '||to_char(Parent_Request_ID) ) A, 'B' B, 4.86 SRT FROM FND_CONC_REQ_SUMMARY_V CONC

WHERE SYSDATE - ACTUAL_START_DATE > 0.0416666666666667 AND PHASE_CODE = 'R' AND STATUS_CODE = 'R'

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

UNION

SELECT RPAD('-',125,'-') A, 'C' B, 1.1 SRT FROM DUAL

UNION

SELECT ' ', 'C', 1.2 FROM DUAL

UNION

SELECT ' ', 'C', 5.8 FROM DUAL

UNION

select ' FOLLOWING ARE THE DETAILS OF HUNG OR ORPHAN SESSIONS AS OF '||to_char(sysdate ,

'dd-mon-yyyy hh24:mi:ss') A, 'C' B,1.5 SRT from dual

UNION

select RPAD('-',125,'-') A, 'C' B, 1.6 SRT from dual

UNION

SELECT to_char(rpad(to_char('SID'),5) ||' '||rpad('PROCESS',12)|| ' ' ||rpad('MODULE',10)||' '||rpad('ACTION',

25)||' '||rpad('USERNAME',15)||' '||rpad('PROGRAM',20)||' '||rpad('EVENT',25)) A, 'C' B, 5.2 FROM DUAL

UNION

select to_char(rpad(nvl(to_char(a.sid), ' '),7,' ')||' '||rpad(nvl(a.process, ' '),19,' ')||' '||rpad(nvl(a.module,

' '),10)||' '||rpad(nvl(a.action, ' '),20)||' '||rpad(nvl(a.username, ' '),15)||' '||rpad(nvl(a.program, ' '),20)||' '||

rpad(c.event,25)) A,'C' B, 5.4 SRT from gv$session a, gv$process b, gv$session_Wait c where c.event not

like 'SQL%' and c.event not in ('pmon timer','rdbms ipc message','pipe get','queue messages','smon timer',

'wakeup time manager','PL/SQL lock timer','jobq slave wait','ges remote message','async disk IO','gcs remote

message','PX Deq: reap credit','PX Deq: Execute Reply') and a.paddr=b.addr and a.sid=c.sid and a.inst_id=

c.inst_id and a.inst_id=b.inst_id and a.last_call_et >1800

UNION

select RPAD('-',125,'-') A, 'C' B, 5.6 SRT from dual

UNION

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

SELECT ' ', 'F', 1.01 FROM DUAL

UNION

select 'PENDING NORMAL MANAGERS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A, 'F' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'F' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT MANAGER NAME',35) || rpad('ACTUAL',25)|| ' ' ||rpad('TARGET',

20)||' '||rpad('RUNNING',25)||' '||'PENDING'), 'F' B, 1.2 FROM DUAL

UNION

select to_char (

decode (

fcq.USER_CONCURRENT_QUEUE_NAME,

'XXXXXX: High Workload',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,53),

'XXXXXX: Standard Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: MRP Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),

'XXXXXX: Payroll Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

'XXXXXX: Fast Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Workflow',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'XXXXXX: Critical Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Inventory Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),

'Conflict Resolution Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),

null)

||' '||rpad(TO_CHAR(NVL(FCQ.RUNNING_PROCESSES,0)),30)||' '||

rpad(to_char(nvl(FCQ.MAX_PROCESSES,0)),30) ||' '||rpad(to_char(NVL(running,0)),30) || ' '||

to_char(NVL(PENDING,0))) A, 'F' B, 1.3 SRT

from

apps.fnd_concurrent_queues_vl FCQ,

(SELECT nvl(count(*),0) Running, fcwr.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwr

WHERE fcwr.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwr.phase_code = 'R')

AND fcwr.hold_flag != 'Y'

AND SYSDATE - fcwr.requested_start_date >= 0.00694444444444444

group by fcwr.concurrent_queue_id ) RUNNING ,

( SELECT nvl(count(*),0) Pending, fcwp.concurrent_queue_id

FROM fnd_concurrent_worker_requests fcwp

WHERE fcwp.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

AND (fcwp.phase_code = 'P')

AND fcwp.hold_flag != 'Y'

AND sysdate-fcwp.requested_start_date >= 0.00694444444444444

group by fcwp.concurrent_queue_id ) PENDING

WHERE FCQ.concurrent_queue_id=RUNNING.concurrent_queue_id(+)

AND FCQ.concurrent_queue_id=PENDING.concurrent_queue_id(+)

AND fcQ.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)

UNION

select RPAD('-',125,'-') A, 'F' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'F' B, 1.5 SRT FROM DUAL

UNION

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

SELECT ' ', 'G', 1.01 FROM DUAL

UNION

select 'CRITICAL PROGRAMS STATUS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),

'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A , 'G' B,1 SRT from dual

UNION

select RPAD('-',125,'-') A, 'G' B, 1.1 SRT from dual

UNION

SELECT to_char( rpad('CONCURRENT PROGRAM NAME',55) || rpad('AVG TIME',20)|| ' ' ||rpad

('CURR MAX TIME',16)||' '|| 'REQUEST_ID'), 'G' B, 1.1 FROM DUAL

UNION

SELECT to_char (

decode (PROGRAM_NAME,

'AutoCreate Configuration Items',RPAD(PROGRAM_NAME,70),

'Memory-based Snapshot',RPAD(PROGRAM_NAME,71),

'Order Import',RPAD(PROGRAM_NAME,80),

'Workflow Background Process',RPAD(PROGRAM_NAME,69),

PROGRAM_NAME) ||' '||

rpad(TO_CHAR(STATIC.AVG_TIME),25) || ' '||

rpad(TO_CHAR(DYNAMIC.CURR_MAX_TIME),25) || ' '||

to_char(NVL(REQUEST_ID,NULL))) A, 'G' B, 1.2 SRT

FROM

(SELECT

CONCURRENT_PROGRAM_ID,

USER_CONCURRENT_PROGRAM_NAME,

REQUEST_ID,

ROUND((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,0) CURR_MAX_TIME

FROM APPS.FND_CONC_REQ_SUMMARY_V fcr

WHERE CONCURRENT_PROGRAM_ID IN (36888,

48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

AND REQUEST_ID IN (

SELECT MAX(REQUEST_ID) FROM APPS.FND_CONC_REQ_SUMMARY_V fcr WHERE CONCURRENT_PROGRAM_ID

IN (36888,48681,39442,33137,47730,47731,47712,47729,31881)

and phase_code='C'

AND STATUS_CODE='C'

AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))

GROUP BY CONCURRENT_PROGRAM_ID) ) DYNAMIC ,

(select distinct CONCURRENT_PROGRAM_ID "CONCURRENT_PROGRAM_ID",

USER_CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",

DECODE ( CONCURRENT_PROGRAM_ID,36888,39442,33137,31881,10,NULL) AVG_TIME

FROM APPS.FND_CONCURRENT_PROGRAMS_TL fcr

WHERE CONCURRENT_PROGRAM_ID IN

(36888,39442,33137,31881)

AND LANGUAGE='US' ) STATIC

WHERE DYNAMIC.CONCURRENT_PROGRAM_ID(+)=STATIC.CONCURRENT_PROGRAM_ID

UNION

select RPAD('-',125,'-') A, 'G' B, 1.4 SRT from dual

UNION

SELECT chr(10)||chr(10) A, 'G' B, 1.5 SRT FROM DUAL

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

) TEMP

ORDER BY B, SRT, A

Prevent Concurrent Requests Executing on New Cloned Environment

To prevent concurrent requests executing on a newly cloned environment 2 steps will be required.

1) Prevent Concurrent Managers from starting as part of the Clone

2) Change data concerning concurrent requests so they do not execute when the concurrent managers start.

To prevent Applications Services (including Concurrent Managers) from starting automatically during Rapid Clone an enhancement request has been raised to see if this can be considered an option during the clone.

Until this feature is available there is the following workaround.

1. Edit the $COMMON_TOP/clone/bin/adcfgclone.pl

2. Go to the end of the file

3. Change the following lines:-

print "\n Starting application Services for $s_dbSid:\n";
print "Running:\n";
print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/<appspwd>\n");
system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

TO:

print "\nNOT Starting application Services for $s_dbSid:\n";
#print "Running:\n";
#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/<appspwd>\n");
#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

4. Run "perl adcfgclone.pl appsTier" as normal.

The services will not start Automatically when the clone completes allowing data to be changed safely.

There is a need to change 2 sets of concurrent requests to prevent execution

a) Terminate 'Running' Requests
b) Set Pending jobs to 'On Hold'

a) Set Terminating or Running to Completed/Terminated
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code ='T'
OR phase_code = 'R'
/

b) Place Pending/(Normal/Standby) to On Hold
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
/

Once these changes have been committed then the Application services including concurrent manager can be restarted and the concurrent jobs will not be executed.

Queries Related to Concurrent Requests in 11i Applications

As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.
Note: These queries needs to be run from APPS schema.

Scheduled concurrent requests

Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).

The below query will return all the concurrent requests which are scheduled using any of the above methods:

SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' || cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, cr.resubmit_interval, 
NVL2 (cr.resubmit_interval, 
'PERIODICALLY', 
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') 
) schedule_type, 
DECODE (NVL2 (cr.resubmit_interval, 
'PERIODICALLY', 
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') 
), 
'PERIODICALLY', 'EVERY ' 
|| cr.resubmit_interval 
|| ' ' 
|| cr.resubmit_interval_unit_code 
|| ' FROM ' 
|| cr.resubmit_interval_type_code 
|| ' OF PREV RUN', 
'ONCE', 'AT :' 
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 
'EVERY: ' || fcr.class_info 
) schedule, 
fu.user_name, requested_start_date 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu, 
apps.fnd_conc_release_classes fcr 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fcr.release_class_id(+) = cr.release_class_id 
AND fcr.application_id(+) = cr.release_class_app_id;

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.

Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.

Note: This query will return even though the request was submitted using a different responsibility.

SELECT request_id, NAME, argument_text, user_name 
FROM (SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' || cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, fu.user_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fu.user_name NOT LIKE 'PPG%') t1 
WHERE EXISTS ( 
SELECT 1 
FROM (SELECT cr.request_id, 
DECODE (cp.user_concurrent_program_name, 
'Report Set', 'Report Set:' 
|| cr.description, 
cp.user_concurrent_program_name 
) NAME, 
argument_text, fu.user_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_requests cr, 
apps.fnd_user fu 
WHERE cp.application_id = cr.program_application_id 
AND cp.concurrent_program_id = 
cr.concurrent_program_id 
AND cr.requested_by = fu.user_id 
AND cr.phase_code = 'P' 
AND cr.requested_start_date > SYSDATE 
AND cp.LANGUAGE = 'US' 
AND fu.user_name NOT LIKE 'PPG%') t2 
WHERE t1.NAME = t2.NAME 
AND t1.argument_text = t2.argument_text 
AND t1.user_name = t2.user_name 
GROUP BY NAME, argument_text, user_name 
HAVING COUNT (*) > 1) 
ORDER BY user_name, NAME

Average pending time per request

This is a very useful query to check the performance of the concurrent managers.

Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, 
concurrent_queue_name, 
(SUM ( ( actual_start_date 
- (CASE 
WHEN requested_start_date > request_date 
THEN requested_start_date 
ELSE request_date 
END 
) 
) 
* 24 
* 60 
* 60 
) 
) 
/ COUNT (*) "Wait_Time_per_Req_in_Secs" 
FROM apps.fnd_concurrent_requests cr, 
apps.fnd_concurrent_processes fcp, 
apps.fnd_concurrent_queues fcq 
WHERE cr.phase_code = 'C' 
AND cr.actual_start_date IS NOT NULL 
AND cr.requested_start_date IS NOT NULL 
AND cr.controlling_manager = fcp.concurrent_process_id 
AND fcp.queue_application_id = fcq.application_id 
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id 
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name 
ORDER BY 2

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.

SELECT user_concurrent_program_name, user_concurrent_queue_name 
FROM apps.fnd_concurrent_programs_tl cp, 
apps.fnd_concurrent_queue_content cqc, 
apps.fnd_concurrent_queues_tl cq 
WHERE cqc.type_application_id(+) = cp.application_id 
AND cqc.type_id(+) = cp.concurrent_program_id 
AND cqc.type_code(+) = 'P' 
AND cqc.include_flag(+) = 'I' 
AND cp.LANGUAGE = 'US' 
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id 
AND NVL (cqc.queue_application_id, 0) = cq.application_id 
AND cq.LANGUAGE = 'US'

To see all the pending / Running requests per each manager wise

SELECT request_id, phase_code, status_code, user_name, 
user_concurrent_queue_name 
FROM apps.fnd_concurrent_worker_requests cwr, 
apps.fnd_concurrent_queues_tl cq, 
apps.fnd_user fu 
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') 
AND cwr.hold_flag != 'Y' 
AND cwr.requested_start_date <= SYSDATE 
AND cwr.concurrent_queue_id = cq.concurrent_queue_id 
AND cwr.queue_application_id = cq.application_id 
AND cq.LANGUAGE = 'US' 
AND cwr.requested_by = fu.user_id 
ORDER BY 5

Note: The same information can be seen in Administer Concurrent Manager form for each manager.

Checking the incompatibilities between the programs

The below query can be used to find all incompatibilities in an application instance.

SELECT a2.application_name, a1.user_concurrent_program_name, 
DECODE (running_type, 
'P', 'Program', 
'S', 'Request set', 
'UNKNOWN' 
) "Type", 
b2.application_name "Incompatible App", 
b1.user_concurrent_program_name "Incompatible_Prog", 
DECODE (to_run_type, 
'P', 'Program', 
'S', 'Request set', 
'UNKNOWN' 
) incompatible_type 
FROM apps.fnd_concurrent_program_serial cps, 
apps.fnd_concurrent_programs_tl a1, 
apps.fnd_concurrent_programs_tl b1, 
apps.fnd_application_tl a2, 
apps.fnd_application_tl b2 
WHERE a1.application_id = cps.running_application_id 
AND a1.concurrent_program_id = cps.running_concurrent_program_id 
AND a2.application_id = cps.running_application_id 
AND b1.application_id = cps.to_run_application_id 
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id 
AND b2.application_id = cps.to_run_application_id 
AND a1.language = 'US' 
AND a2.language = 'US' 
AND b1.language = 'US' 
AND b2.language = 'US'

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.

Wednesday, June 18, 2008

Create a User Event Trace in 11i / R12

A user event trace is very handy for tracing sql operations to debug various issues. The benefit of a user event trace is that it is linked to a specific user so that only code run by this user is traced. This makes diagnosis easier when compared to similar tracing methods at the database level where all user calls are traced.

Step 1
Login to Oracle Applications and select the System Administrator responsibility.
Choose Profile - System

Step 2
In the find profile field select the user which you wish to trace. In the profile field enter 'Initialization SQL Statement - Custom"
Select find

Step 3
In the find profile results form copy and paste the following into the 'user' field. Do not update the site level field.

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');end;

Note: copy and paste the above as one line. Failure to paste the values properly or pasting the values with incorrect syntax will result in this user not being able to login

Step 4
Save the profile option change. Stop and start the java virtual machine for the change to take effect
11i: use adapcctl.sh
r12: use adoacorectl.sh

Step 5
Login to the application and reproduce the issue. Then quickly log off. Try and avoid any un-necessary keystrokes as this simply makes the log files larger and the issue hard to pinpoint in the logs.

Step 6
login to unix/windows as the oracle user. Navigate to the user dump destination which us usually $ORACLE_HOME/admin/<context>/udump
You will see a series of trace files generated during the time of your issue reproduction. Tkprof all of the generated files and upload both the raw and tkprof files to your service request

You can also check the user dump destination via the following:
sql> show parameter user_dump_dest;

Wednesday, June 11, 2008

Versions of APPS Technology Stack Components

How to find the versions of technology stack components (Forms, iAS, Framework, JDK, OJSP, etc.)?

Establish the needed environment parameters by sourcing the Applications environment file as the owner of the application tier file system.

Ensure "APPLRGF" variable is set in environment. If not, set it to the same value as "APPLTMP".

Navigate to <FND_TOP>/patch/115/bin. Run the utility as follows:

Operation System	Command Line
Unix or Linux	perl $FND_TOP/patch/115/bin/TXKScript.pl \ -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP \ -contextfile=$CONTEXT_FILE \ -appspass=apps \ -outfile=$APPLTMP/Report_Inventory.html
Windows	perl %FND_TOP%\patch\115\bin\TXKScript.pl -script=%FND_TOP%\patch\115\bin\txkInventory.pl -txktop=%APPLTMP% -contextfile=%CONTEXT_FILE% -appspass=apps -outfile=%APPLTMP%\Report_Inventory.html

Where:

txktop	Temporary working directory use by Perl Modules. Should not be an empty string.
contextfile	Location of the context file. If not passed, default is picked from the environment.
appspass	APPS schema password. If not passed, default password is used.
outfile	Location for the report being generated. If not passed, the default location is <APPLTMP>/TXK

To generate the report in text format, the parameter "-reporttype=text" needs to be passed to the above commands. For example:

outfile=$APPLTMP/Report_Inventory.html -reporttype=text

Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script
Upload this file to Oracle Support for review.

Friday, June 6, 2008

Oracle Apps Interview Questions - Part I

Here are some of the Oracle Apps Interview Questions. These Questions cover from Operating

System Installation, Pre-requisites for Apps, Apps Installation, Architecture, File System. In the

next part of Questions , I will cover more into advanced apps topics. For any doubts you may post

comment so that i could help you.

1) How to Check the memory of the system while the time of Linux Installation ?
2) How to assign hostname to a node ?
3) How to assign IP to a node ?
4) which are the files where kernel settings and security limits are stored ?
5) How to check the swap space and physical memory ?
6) How to do ftp from one node to another node ? Give Eg
7) How to do copy between two machines ? Give Eg
8) Give steps to enable ftp and telnet services ?
9) How to do confiugre nfs server and do a nfs mount from the client machine ?
10) what's the use of TOP, ps commands ?
11) How to create user and groups in Linux ?
12) How to see if user and group already exists ?
13) How to check the release version of OS ?
14) How to check the kernel version of OS ?
15) How to check OS is 32bit/64bit?
16) How will you check whether required os level software has installed?
17) what is the command to check required rpms are installed?
18) How will install and upgrade rpms in linux?
19) What is oraInventory?default location for aix , Linux ?
20) what is oratab ?
21) How can you check the groups to a user belong ?
22) What is configuration file?(config.txt) and where is the default location
23) What is rapid install?
24) Explain Single Node and Multi Node installation ? ( Exp preinstall and postinstall tasks )
25) Explain single user and multi user installation ?
26) What is difference between express install and advanced install ?
27) What is difference between fresh database and vision database install types ?
28) What is minimum / approx. disk requirement for 11.5.10 for vis and fresh and stage ?
29) What is staging area ? How you set up staging area ?
30) Is it possible to install apps without staging area ?
31) How will check perl, java versions?
32) where is location of staging logs ?
33) How can you clear the system after an incomplete installation ?
34) What's the difference between ORACLE_BASE and ORACLE_HOME
35) what is default port pool? what is default range and till what range it support?
36) If some of pre-install test has failed what will you do?
37) If the installtion has terminated before completion What will you do?
38) What are post installtion tests done by rapidwiz?
39) How will you check rapidwiz version?
40) Describe how will you do multinode installtion?
41) What are things will be checked during post installation tests?
42) What are required in post installtion steps additionally?
43) Where is the location of the Rapidwiz logs?
44) How could you Install technology stack alone?
45) What are the software require to maintain oracle application on windows ?
46) What are the Technology Stack Components?
47) What are the software required for Install Oracle Application on unix or linux machines ?
48) How can you check the version of Oracle Apps ?
49) What is Oracle Apps ?
50) Explain three tier architecture in Oracle Apps ( Including login workflow and middle tier services )
51) What is OATM ?
52) What is Oracle Jinitiator ? How can you check the jinitiator version ?
53) Where is location of Oracle Apache Cache and modplsql Cache ?
54) How many oracle Homes are there in 11i and what are they?
55) What is oracle applications technology layer ? what and why are these compnonents used for ?
56) what is Oracle Application Object Library ? How it differs for end user, developer and system admin ?
57) what is OAM ? What is OAM Login URL ?
58) What is Oracle Applications URL and Ebusiness Suite Apps Login URL ?
59) Which is the script location is Oracle apps for middle tier and db tier ?
60) where is the concurrent manger log and out location ?
61) what are environment files ? what are the different types of env files, and locations on both tiers ?
62) what are context files and locations for both tiers
63) what is APPLPTMP and APPLTMP ?
64) what are they key environment file parameters ?
65) which is the script used to start and stop apache and concurrent manager ? Tell the steps
66) Brief out the file system for Oracle Applications Ebusiness Suite ( and explain each directory structure,
      its contents )
67) Whats the difference between APPS,APPLSYS,APPLSYSPUB users ?
68) what are the diff types of users available is oracle apps ?
69) What is Multiple Organization ? How can you check if MO is enabled ?
70) How can you check if multiple languages are installed with oracle apps ?
71) How can you check if an oracle apps installations in multi node or single node and in which
      node each services are running ?
72) How can you check how many database are up and running in both linux and windows machines
73) How can you check if concurrent manager and apache server is up and running
74) Where is apache access log and error log location
75) Where is the location of Oracle Alert logfiles and Trace files ( Give complete path )

Thursday, June 5, 2008

Personalizing The Login Page

1. Attributes of the login page

It is possible to control the display of some attributes of the login page, for instance user name or password hints, language switchers, forgot password link, corporate policy message, etc.

For this, you need to set the profile option 'Local Login Mask' (FND_SSO_LOCAL_LOGIN_MASK) with a number being the sum of the mask values described in the table below:

Description	Mask value
Hint for Username (USERNAME_HINT)	01
Hint for Password (PASSWORD_HINT)	02
Cancel button (CANCEL_BUTTON)	04
Forgot Password link (FORGOT_PASSWORD_URL)	08
Registration link (REGISTER_URL)	16
Language Images (LANGUAGE_IMAGES)	32
Corporate Policy Message (SARBANES_OXLEY_TEXT)	64

* For instance the value 32 (default) displays only the language icons and value 127 will show all the attributes on the page.

* The change takes effect immediately after re-login to E-Business Suite.

2. Message texts

It is possible to modify or add text on the login page by changing the value of some messages.
The following table shows the related messages and their default value:

Description	Default value
FND_SSO_WELCOME	Login
FND_SSO_EBIZ_SUITE	E-Business Suite
FND_SSO_COPYRIGHT_TEXT	Copyright (c) 2007, Oracle. All rights reserved.
FND_SSO_SARBANES_OXLEY_TEXT	Corporate Policy Message

To change the value of a message:

1. Go to "Application Developer" responsibility 2. Select "Messages" from the menu 3. Query the message name and then enter your message text in the

"Current Message Text" field 4. Save changes and exit 5. Clear cache and bounce Apache to see the change

* Note that some messages can be used elsewhere that in the login page and can be updated by a patch

* If you want to change also the default branding 'E-Business Suite' on other pages, to match the text on the login page (defined by FND_SSO_EBIZ_SUITE message), then follow the steps below:

a. Login with System Administrator responsibility
b. Navigate: Application ---> Function
c. Query the function 'FWK_HOMEPAGE_BRAND'
d. Replace the value of the 'User Function Name' with the desired text
c. Logout and login to see the change (you shouldn't need to clear caches and bounce apache)

* The 'FND_SSO_SARBANES_OXLEY_TEXT' message is only displayed when the mask
value 64 is added to the profile option 'Local Login Mask'.

3. Corporate branding logo

The Oracle logo is displayed on various E-Business Suite pages and can be changed by setting the 'Corporate Branding Image for Oracle Applications' (FND_CORPORATE_BRANDING_IMAGE) profile option to the full path name of an image file (.gif) that contains your corporate image.

However it is not possible to use this method for AppsLocalLogin.jsp since it is hard coded with the Oracle logo image file 'FNDSSCORP.gif'.
The non supported solution consists in:

 1. Go to the $OA_HTML directory
2. Backup the AppsLocalLogin.jsp file
3. Copy your own corporate branding image under $OA_MEDIA directory
4. Edit the AppsLocalLogin.jsp file :

  from :
 ImageBean imgBean1 = new ImageBean("/OA_MEDIA/FNDSSCORP.gif", FND_ORACLE_LOGO);
   to :
 ImageBean imgBean1 = new ImageBean("/OA_MEDIA/<your image file name>", FND_ORACLE_LOGO);
 5. Clear caches and bounce Apache to see the change

4. Other modifications

AppsLocalLogin.jsp being a Java Server Page you can change the HTML or Java code (for instance with JDeveloper), create you own messages in the Messages Dictionary thru AOL responsibility, etc., if you want to add other customizations. This is considered a customization and thus not supported by Oracle. If you apply patches replacing AppsLocalLogin.jsp the file will be overwritten.

Tuesday, June 3, 2008

How to Add a Web Link to the APPS Forms Menu

Forms personalization feature may be used to add a new field in the menus of the forms in the e-business suite 11 i .

Below is the Forms Personalization code which will add a Web Link in the Tools menu :

1) Login to your responsibility
2) Go to the Forms you need to personalize
3) Open personalizations forms : Help/Diagnostics/Custom Code/personalize
4) Enter sequence as '1' and in description enter 'Init Web URL menu'
5) In condition tab, enter trigger event as 'WHEN-NEW-FORM-INSTANCE'
6) Click on 'Actions' tab. Enter sequence as '1' and Type = 'Menu'.
7) Choose Menu Entry (any of the special menu entries). Eg. Special 12
6) Enter menu label ( eg: Web URL )
8) In 'Enabled in Blocks' field you can choose for which blocks the special menu needs to be enabled or leave blank to be enabled for all the blocks of the forms
9) Go back to the main block where you entered 'Init Web URL menu'.
10) Enter a new record with sequence as '2' and in description enter 'Launch URL'
11)In Conditions tab, enter trigger event as 'SPECIAL12'
12) Click on Actions tab. Enter sequence as '1' and Type = 'Builtin'.
13) Choose 'Launch a URL' for Builtin Type
13) Enter the argument as your URL ( eg : www.oracle.com ) and click 'Apply now' button.
14 ) Save .
15) Exit form , logout , login and go to your Forms again.
16) Go to Tools menu. The new special menu ( Web URL ) entry will be displayed.
Click to launch URL.

Obtaining Forms Runtime Diagnostics (FRD) In Oracle Applications

A. Introduction

When starting a Forms-based session in Applications it is possible to pass various useful parameters like formname=xyz, or lang=US, or NLS=xyz etc. This is really helpful when debugging Forms. In addition, there are two other parameters config=debug and record=all which make it possible to perform Forms Runtime Diagnostics (FRD.)

FRD is a runtime event-based logging system intended to aid in the debugging of Forms applications. FRD is enabled at runtime on a user by user basis. When a form is run with FRD enabled, a combination of external user-application interactions and internal Forms processing events are written in chronological order to a log on the file system.

<>FRD generates extensive output useful for analysis and debugging, and it is therefore recommended FRD be utilized as a logging mechanism only when specific issues are encountered.

B. The Contents of an FRD Log File

The following data is typically collected in an FRD trace file:

- Trigger firing: includes name and hierarchical location.

- Built-in Execution: includes name and IN and OUT parameter types and values.

- Messages: includes message numbers and text.

- The opening of forms executables (.fmx) and menu executables (.mmx)

- Unhandled exceptions: includes error message if available, otherwise only
error numbers.

- All external user events via normal runtime (RT) recording mechanism

To see more details about the expected output in Forms, please see
<Note:62664.1> Forms Server Logging and Forms Runtime Diagnostics (FRD) Explained.

C. Tracing in R12

1. Set the user value for profile option 'ICX: Forms Launcher' to be

'http://hostname.domain:port/forms/frmservice?record=forms'

2. By default, this will enable tracing for errors only. As an optional step, you can enable different trace options (e.g. user actions, user-exit events, dbsql events, network events) by setting a trace group in file $ORA_CONFIG_HOME/10.1.2/forms/server/ftrace.cfg

3. Login to the Personal Home Page, navigate to the form and perform the steps that you wish to trace.

4. Locate the file in the directory set by variable FORMS_TRACE_DIR. Note: by default, this directory is named forms_<pid>.trc, where <pid> is the process identifier.

Related reference:
<Note:373548.1> Using Forms Trace in Oracle Applications Release 12.

D. Tracing in 11.5.10

1. Make the user value for profile option 'ICX: Forms Launcher' the same as the site value

   e.g. copy value of ICX: Forms Launcher for the site Test115
   'http://testserver.oracle.com:8005/dev60cgi/f60cgi'
    to be the value for the user.

2. Append the user value of ICX: Forms Launcher with the Forms parameters for FRD

e.g. update the value of ICX: Forms Launcher for the user to be
'http://testserver.oracle.com:8005/dev60cgi/f60cgi?record=all&log=/tmp/username_frd.log'

3. Login to the Personal Home Page, navigate to the form and perform the steps that you wish to trace.

4. Locate the file in the directory set by variable $FORMS60_TRACE_PATH. Note: the default directory for
$FORMS60_TRACE_PATH is $ORACLE_HOME/forms60/log.

Related reference:
<Note:290210.1> Forms FRD Trace With New Variable FORMS60_TRACE_PATH.

E. FRD Tracing in 11.5.x to 11.5.9

This method enables users to run FRD tracing through the Personal Home Page by setting debugging to true using the profile option 'ICX: Forms Launcher'.

When accessing Applications through the Personal Home Page (PHP), there is no way to pass command line parameters such as config=debug and record=all for the duration of a single session. For example, you cannot add these variables to the PHP connect string
http://testserver.oracle.com/OA_HTML/US/ICXINDEX_test115.htm.

The advantages of using the profile option 'ICX: Forms Launcher' are that you can easily trace the activities of multiple users and assign different log file names.

With Oracle Developer Forms patch 16 and higher, users cannot create adhoc FRD trace files in any directory. The trace files are either created in the directory set by parameter $FORMS60_TRACE_PATH or they are created in 8.0.6 $ORACLE_HOME/forms60/log directory.

Steps:

To start tracing in this way, modify Profile Option 'ICX: Forms Launcher' at USER level to include the Forms parameters required for FRD logging:

1. Make the user value for profile option 'ICX: Forms Launcher' the same as the site value

e.g. copy value of ICX: Forms Launcher for the site Test115
'http://testserver.oracle.com:8005/dev60cgi/f60cgi' to be the value for the user.

2. Append the user value of ICX: Forms Launcher with the Forms parameters for FRD

e.g. update the value of ICX: Forms Launcher for the user to be
'http://testserver.oracle.com:8005/dev60cgi/f60cgi?record=all&log=/tmp/username_frd.log'

3. Save this change at the USER level, and the next time you access the test115 database through PHP, the Forms variables will be enabled.

E. Points to Consider When Running FRD

1. Check that sufficient space exists in the directory in which the FRD log file is to be created

2. For each run of FRD, specify a different log file name. The FRD log file will replace an existing file of the same filename.

3. On certain platforms, the log file is only created after the user exits Applications. Therefore, remember to exit Applications.

4. If no file name is specified for FRD, a unique file name is used based on the operating system process ID. The file is created in the current working directory. The file may be overwritten if the operating system reuses the process ID.

5. The log file is likely to be hundreds of lines long. To keep the content of the trace file to a minimum amount, follow the steps to reproduce a problem precisely and avoid performing any other actions. This is particularly helpful when using for troubleshooting purposes.

6. Specify the tmp directory for the log file location since all users usually have permission to write to it.

7. In a multi-node environment, the trace file is created on the Applications tier.

8. It is recommended to pass FRD variables at the USER level and not at the SITE level since SITE level affects all users.

9. Please create your own user to set this value. Please do not enable FRD for user 'vision' or any of the seeded users since you will then start FRD logs for all people using those login accounts.

10. If the 'ICX: Forms Launcher' profile option is not updatable at the USER level, then you may need to access 'Application Developer' to set the option 'Updatable' for the profile ICX_FORMS_LAUNCHER.

F. Extracts From an FRD Log File For 11i

Below displays brief extracts from a log file. The FRD documents the steps executed from Forms when signing on to Applications 11i and navigating to a form within core Applications.

Forms Runtime Diagnostic Collection Log
File Name: /tmp/form1.frd
Process ID: 19166
Client IP: @ x
Forms 6.0 (Forms Runtime) Version 6.0.8.12.1 (Production)
PL/SQL Version 8.0.6.1.0 (Production)
Oracle Virtual Graphics System Version 6.0.5.36.0 (Production)
Oracle Multimedia Version 6.0.5.33.0 (Production)
Oracle Tools Integration Version 6.0.5.32.0 (Production)
Oracle Tools Common Area Version 6.0.5.32.0
Oracle CORE Version 4.0.6.0.0 - Production

Opened file: /stable/oracle/vis006/vis006appl/fnd/11.5.0/forms/US/FNDSCSGN.fmx

ON-LOGON Trigger Fired:
Form: FNDSCSGN

State Delta:
FORM FNDSCSGN
STATUS     NEW
BLOCK PROGRESS_INDICATOR
    STATUS     NEW
    RECSTATUS ""
    FIELD TEXT
      CANVAS     PROGRESS_INDICATOR
      GEOMETRY   100,250:4000,400
      ENABLED    TRUE
      NAVIGABLE TRUE
      INSERTABLE TRUE
      QUERYABLE TRUE
      UPDATEABLE TRUE
...
WHEN-NEW-ITEM-INSTANCE Trigger Fired:
Form: FNDSCSGN
Block: SIGNON
Item: USERNAME
...
WHEN-BUTTON-PRESSED Trigger Fired:
Form: FNDSCSGN
Block: SIGNON
Item: CONNECT_BUTTON
...
MENU_TO_APPCORE Trigger Fired:
Form: FNDNLDLG
...
CLOSE_WINDOW Trigger Fired:
Form: FNDNLDLG
...
Executing DO_KEY Built-in:
In Argument 0 - Type: String   Value: EXIT_FORM

KEY-EXIT Trigger Fired:
Form: FNDSCSGN
...

How To Generate A Stack Trace For Forms With Applications 11i

When troubleshooting forms issues such as FRM-92100, it is often useful to generate a stack trace. Below are the steps for Applications DBAs to follow to produce a stack trace for forms in an Oracle EBusiness Suite 11i environment.

Solution

1. Set the following environment variables:
    a) check the value of $FORMS60_TRACE_PATH and make sure this directory has write permissions and
    has space.
    b) set $FORMS60_CATCHTERM=0

2. If your platform is Unix, check that the operating system ulimit values are sufficient.
    Run `ulimit -a` as the applmgr, assuming applmgr starts the forms server,
    to check the memory, data and stack sizes.

3. Stop the forms server.

4. Backup file f60webmx and then relink this executable with debug symbols.
    Make sure you are signed in as the user who owns f60webmx executable, then run this command:
    adrelink.sh force=y ranlib=y link_debug=y "fnd f60webmx"

5. Start the forms server.

6. Reproduce the forms error that you are investigating.

7. Check for a new core file in the following directories:

a) $FORMS60_TRACE_PATH directory.

b) check the $PWD directory for the forms server.
   By default in an Autoconfig enabled environment, this is the directory in which the adfrmctl.sh starts;
   $PWD is $COMMON_TOP/admin/scripts/<context>/ directory.

To confirm the $PWD directory setting, you can also check by opening any form and navigating to
         Help->Diagnostics->Examine
            In field 1 select $environment$
            In field 2 enter PWD (no $ or quotes)
           The value is populated automatically when you select the tab key.

Sunday, June 1, 2008

How AutoConfig sets ICX: Session Timeout

AutoConfig (adconfig) uses the variable s_sesstimeout from the $APPL_TOP/admin/<context>.xml to determine the ICX:Session Timeout Profile Option.

adconfig runs the script afwebprf.sql which does the following :

Setting ICX_SESSION_TIMEOUT to correspond to Jserv Session Timeout
      set_profile(178, 'ICX_SESSION_TIMEOUT',10001, 0,
                 '%s_sesstimeout%'/(1000*60),
                  NULL);

In the template file for the <context>.xml file (adxmlctx.tmp) there is a default value for s_sesstimeout :

In adxmlctx.tmp version 115.32 and earlier, the default value for s_sesstimeout is 600000 which when plugged into the afwebprf.sql script would cause the ICX: Session Timeout Profile Option to be a value of 10.
In adxmlctx.tmp version 115.33 and newer, the default value for s_sesstimeout is 1800000 which when plugged into the afwebprf.sql script would cause the ICX: Session Timeout Profile Option to be a value of 30.

If you want the ICX: Session Timeout to be different, you will have to update the .xml file so that s_sesstimeout/(1000*60) equals the value you want it to be. Keep in mind that the s_sesstimeout is used in the zone.properties file as well.
The session timeout in zone.properties is basically set to the same value as the profile option, except for the fact that in zone.properties it is specified in milliseconds and the profile option is in minutes (hence the calculation in afwebprf.sql).

Here is some additional information regarding timeouts within 11i Apps, specifically for the zone.properties and the profile option.

1. Edit $APACHE_TOP/Apache/Jserv/etc/zone.properties and set the session.timeout in miliseconds) :

session.timeout=600000

This is Apache Jserv Session timeout and should not be any higher than 30 minutes. Longer idle sessions will drain the JVM resources and can also cause out of memory errors.

2. Login into Oracle Applications as SYSADMIN and search for profile ICX:Session Timeout; set this Profile Option to the same value as the one specified by session.timeout in zone.properties.

This is session timeout limit for Self Service Framework Applications and is specified in minutes. If the ICX session expires before the Jserv session, the user will be presented with a login page even though the Jserv session is still active. If the user logs back in before the Jserv session expires, they will see the old state of their middle-tier transaction. This can be confusing, since from the point of view of the user there is no distinction between the ICX session and the Jserv session.

Monday, May 12, 2008

Count Total Number of Users Connected to ORACLE Applications

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 organizations 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.

Thursday, April 17, 2008

Replacing Jinitiator with Sun Java JRE for APPS

Why wait until 2009 July when Oracle desupports Jinitiator for Oracle Applications. Replace your jinitiator now with Sun Java JRE and see the difference

PLATFORM   : Red Hat Linux AS4
DB               : 9.2.0.8
APPS           : 11.5.10.2
JINITIATOR : 1.3.1.21

UPGRADING TO : JRE 1.6_05

Download Interoperability patch 6863618

Download the Java Runtime Environment (JRE) 6 plug-in at, http://java.sun.com/javase/downloads/index.jsp

Downloaded JRE Native plug-in file from jre-6_uX-windows-i586-p.exe and rename to j2se160x.exe
(Where X = the version number. Using jre-6u5 as an example this would be j2se16005.exe)

Move the j2se1600X.exe file to the web application tier and place it in [COMMON_TOP]/util/jinitiator

Apply Developer 6i Patchset 18 ( 4948577 )

unzip patch to $ORACLE_HOME
cd $ORACLE_HOME/developer6i_patch18
./patch_install.sh 2>&1 | tee patch_install_p18.log

cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install
cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install

cp developer6i_patch18/bin/genshlib $ORACLE_HOME/bin

Additional Patches
------------------
5713544

sh patch.sh
adrelink.sh force=y "fnd f60webmx"

4261542

cd $ORACLE_HOME
unzip p4261542_600_GENERIC.zip

cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542

cd $ORACLE_HOME/4261542/oracle/forms/engine
cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
cd $ORACLE_HOME/4261542/oracle/forms/handler
cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class

Generate Product JAR Files ( adadmin )

5216496
sh patch.sh

5753922
sh patch.sh
adrelink.sh force=y "fnd f60webmx"

6195758
cd $ORACLE_HOME
unzip p6195758_600_GENERIC.zip
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758
cd $ORACLE_HOME/6195758/oracle/forms/handler
cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class
Generate Product JAR Files ( adadmin )

5938515
sh patch.sh
adrelink.sh force=y "fnd f60webmx"

3830807
sh patch.sh

4586086
cd $ORACLE_HOME
unzip <patch_file>.zip
cd $ORACLE_HOME/forms60/lib
mv env_forms60.mk env_forms60.mk.PRE_BUG4586086
cp $ORACLE_HOME/bug4586086/env_forms60.mk $ORACLE_HOME/forms60/lib
cd $ORACLE_HOME/forms60/lib
make -f cus_forms60w.mk libso_install
adrelink.sh force=y "fnd f60webmx"

relink - adadmin
select 'Maintain Applications Files Menu' and then select 'Relink Applications Program'
Enter list of products to link ('all' for all products)[all] : fnd
Generate specific executables for each selected product [No] ? y
Relink with debug information [No] ? n
Enter executables to relink, or enter 'all' [all] : f60webmx ar60run ar60runb ar60rund *

4888294 (adpatch )

5884875

cd $ORACLE_HOME
unzip p5884875_600_GENERIC.zip
cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG5884875
cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG5884875
cd $ORACLE_HOME/5884875/oracle/forms/engine
cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
cd $ORACLE_HOME/5884875/oracle/forms/handler
cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/engine/AlertDialog.class
Run adadmin "Generate JAR Files"

(6863618)
------------

Apply the Patch Driver using adpatch
Run the txkSetPlugin.sh script from the <patch_top>/6863618/fnd/bin/

txkSetPlugin.sh 16005

Start Oracle Applicatons 11.5.10.2 and start using Java instead of Jinititor for Applets

How to Reload APPS Java Class Objects

How to reload the APPS Java Classes

This should only be necessary if the JVM was rebuilt for some reason.
The solution can also be helpful for customers that have some invalid APPS Java Classes.

Using the utility adadmin it is possible to rebuild the JAR/ZIP files and have these loaded into the database. Before providing steps to do the rebuild and load it is necessary to be aware of unpublished bugs

The issue is that the file $AD_TOP/admin/driver/adldjava.drv used by adadmin to reload the JAR files into the Database is incorrect.

The contents of $AD_TOP/admin/driver/adldjava.drv should be:

loadjava csf java/jar csflf.jar
loadjava cz java/jar czdb.jar
loadjava eam java/jar eampmsch.jar
loadjava eam java/jar eamwkbch.jar
loadjava ecx java/jar ecxutils.jar
loadjava ecx java/jar ecxprocess.jar
loadjava fnd java/3rdparty/stdalone xmlparserv2.zip
loadjava fnd java/jar fndcct.jar
loadjava inv java/jar invdbtrx.jar
loadjava inv java/jar invlabel.jar
loadjava itg java/jar itgv1.jar
loadjava jtf java/jar jtfaoljdepen.zip
loadjava per java/jar perimage.jar
loadjava per java/jar perkigfclient.jar
loadjava per java/jar perkpiclient.jar
loadjava wms java/jar wmscrtzn.jar
loadjava xtr java/jar xtrintrp.jar

UPDATE ADLDJAVA.DRV

1. Make a back up of the current driver file $AD_TOP/admin/driver/adldjava.drv
2. Modify the contents of $AD_TOP/admin/driver/adldjava.drv to be as the list given above.

REBUILD THE JAR/ZIP FILES

1. Run Adadmin
2. Select Generate Applications Files menu
3. Select Generate product JAR files

RELOAD THE JAR/ZIP FILES INTO THE DATABASE

1. Run Adadmin
2. Select Compile/Reload Applications Database Entities menu
3. Select Reload JAR files to database

RECOMPILE THE LOADED JAVA CLASSES

1. Run Adadmin
2. Select Compile/Reload Applications Database Entities menu
3. Select Compile APPS schema

NOTE:

The number of APPS Java Classes from before rebuilding the JVM will be more than the number of APPS Java Classes after completing above steps. This is because when Applications is first installed it also loads a Base image of the Database. This image includes a lot of Java Classes that are no longer necessary to be loaded. The fact that the JVM has been recreated and the Java Classes reloaded, only those Java Classes that should be loaded have been loaded. The fact that the number of Java Classes decreases is correct and expected.

Tuesday, April 1, 2008

Oracle Applications 11i Hot Backup Cloning

You can make use of rapid clone for hot backup cloning of oracle applications 11i.

These are very useful while Oracle Applications Production server where downtime

is very much limited and is required to 24/7.

For 9i database, you can generate script to put tablespaces in offline mode.

For 10g datbase, you can put database itself in offline mode.

Here are the steps explained from Atul Kumars Blog :

Hot Backup Cloning ( which are explained for a 10g database with 11i )

Downtime and Apache Restricted Mode in Release 11

E-Business Suite Release 11 provides a useful mechanism for the Applications administrators

to start the Apache on the applications tier during down time. Applications administrator can

start the apache in a mode called restricted mode. Down time tasks like patching can continue

to be performed while the Apache is in restricted mode. Restricted access to Oracle Applications

Manager (OAM) is available in this mode. This allows the system administrator to monitor tasks

like patching from OAM. When the Apache is started in restricted mode, normal users are

redirected to a downtime URL containing downtime details.

Here are the details explained from Oracle Blogs (Steven Chan)

http://blogs.oracle.com/schan/2008/03/28#a2561

Hope this help everyone.

Saturday, March 22, 2008

Steps to recover Applications context file if it is corrupted or deleted accidentally?

The Applications context file can be retrieved by running the adclonectx.pl script.

To retrieve the Applications tier context file,

perl /clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the option of retrieving the

Applications tier context file that has been lost and retrieve it to the default location specified

by the script.

The above command can be used only when INST_TOP the is still intact. In case that has also been lost

accidentally, the Applications tier context file may be retrieved as follows:

Execute the following command on the Database tier:
perl /appsutil/clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the option of retrieving the

Applications tier context file that has been lost.

While confirming the location for the context file, set it to any existing directory with write permission.
Once the context file has been generated in the specified location, move it to the location specified

for the context file in the context variable 's_contextfile'.

To retrieve the Database tier context file,

Execute the following command on the Database tier:
perl /appsutil/clone/bin/adclonectx.pl retrieve
On being prompted for the context file to be retrieved, select the Database tier context file and

retrieve it to the default location specified by the script.

Sunday, February 24, 2008

Redirect Rapid Install page to access the E-Business Suite login page

In some environments the URL to access E-Business Suite is in the form of:

http://<web server host name>.<domain>

http://<web server host name>.<domain>:<port number>

which brings up the 'Oracle Applications Rapid Install Portal' page and displays the logon link

'E-Business Home Page' (<host name>.<domain>/OA_HTML/AppsLocalLogin.jsp) to login.

In some cases it could be interesting to access directly to the AppsLocalLogin.jsp login page bypassing

the Rapid Install portal page to avoid to click on the 'Apps Logon Links' then 'E-Business Home Page' links.

Solution

----------

1. Edit index.html and add at the top of the file :
<html>
<head>
<meta http-equiv="refresh"
content="0;url=http://<host name>.<domain>/OA_HTML/AppsLocalLogin.jsp">
...
</head>
</html>

2. Add the following line in the url_fw.conf file

(should be under $IAS_CONFIG_HOME/Apache/Apache/conf directory):

RewriteRule ^/$ /OA_HTML/AppsLocalLogin.jsp [R,L]
then check that the following line exists in the httpd.conf file:
include "%s_iASconfig_home%/Apache/Apache/conf/url_fw.conf"

3. Bounce apache and clear browser caches

Tuesday, January 29, 2008

How to relink the whole Oracle Applications 11i

Technology Stack Binaries inclusive Database :

1. Source the 9i ORACLE_HOME Environment
2. Execute the script $ORACLE_HOME/appsutil/install/adlnkoh.sh
3. Source the iAS ORACLE_HOME Envrionment
4. Execute the script $ORACLE_HOME/bin/adlnkiAS.sh
5. Source the APPS Environment
6. Execute the script $ORACLE_HOME/bin/adlnk806.sh

Oracle E-Business-Suite Binaries :

1. Source the APPS Environment
2. Execute following command : adrelink.sh force=y "ad adadmin"
3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink

Before starting the Instance, please open a new shell and source the Environment.

Wednesday, November 14, 2007

How to check if a certain Patch was applied to Oracle Applications instance using 'adpatch'?

There are few methods to check this.

Method 1

Check Patches applied in Oracle Applications Manager (OAM).

a) Connect to OAM:

http://hostname.domain:PORT/servlets/weboam/oam/oamLogin

Go to Site Map-->Maintenance-->Applied Patches

Enter Patch ID and press 'Go'

See if Patch was returned.

Method 2

Use 'adphrept' utility

Patch History report usage:

adphrept query_depth \
bug_number or ALL \
bug_product or ALL \
end_date_from (mm/dd/rr or ALL) \
end_date_to (mm/dd/rr or ALL) \
patchname/ALL \
patchtype/ALL \
level/ALL \
language/ALL \
appltop/ALL \
limit to forms server? (Y/N) \
limit to web server? (Y/N) \
limit to node server? (Y/N) \
limit to admin server? (Y/N) \
only patches that change DB? (Y/N)

Specify 1 or 2 or 3 for query_depth

1-> Details of patches only
2-> Details of patches and their Bug Fixes only
3-> Details of patches their Bug Fixes and Bug Actions

Example: To get the complete patch details for patches applied in Dec 2000:

On UNIX:

$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/00 12/31/00 ALL ALL ALL ALL ALL N N N N N

Method 3

Use the following sql.

set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
  select appl_top_id, name
  from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
  dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('','');
--
LOOP
  FETCH alist INTO p_appl_top_id,p_appltop_name;
  EXIT WHEN alist%NOTFOUND;
  --
  IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
  THEN
    println(p_appltop_name || ':');
    for i in 1..p_patchlist.count
    loop
       p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,
       p_patchlist(i));
       println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
    end loop;
  END if;
  println('.');
END LOOP;
--
close alist;
END;
/

Note: Please enter Patch number in place of and , e.g '3240000'

Example Output

when p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943'):

..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was EXPLICIT
.
dcollierpc8:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED

Method 4

You might also use the following query, however the most reliable methods are the described above.

SQL> SELECT DISTINCT  RPAD(a.bug_number,
11)|| RPAD(e.patch_name,
11)||  RPAD(TRUNC(c.end_date),
12)|| RPAD(b.applied_flag, 4)   BUG_APPLIED
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs  c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id =  b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id =  d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id  AND
a.bug_number in  ('','');
ORDER BY 1  DESC;

Note: Please enter Patch number in place of and , e.g '3453499'

Blog Archive

March (3)
May (2)
December (1)
March (1)
September (2)
June (2)
March (4)
June (2)
February (5)
August (1)
July (1)
June (23)
May (3)
April (11)
March (6)
February (1)
January (5)
November (12)
October (18)