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

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.

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;

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 )

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 …

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.

How to setup Password Security?

 

Signon Password Failure Limit The Signon Password Failure Limit profile option determines the maximum number of login attempts before the user’s account is disabled. Users cannot see or update this profile option. The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT. Signon Password Hard to Guess The Signon Password Hard to Guess profile option sets rules for choosing passwords to ensure that they will be “hard to guess.” A password is considered hard-to-guess if it follows these rules: . The password contains at least one letter and at least one number. . The password does not contain the username. . The password does not contain repeating characters. Users can see but not update this profile option. The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS. Signon Password Length Signon Password Length sets the minimum length of an Applications signon password. If no value is entered the minimum length defaults to 5. Users can see but not update this profile option. The internal name for this profile option is SIGNON_PASSWORD_LENGTH. Signon Password No Reuse This profile option specifies the number of days that a user must wait before being allowed to reuse a password. Users can see but not update this profile option. The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE. Signon Password Case This profile option is not available from the beginning. With 11i.ATG_PF.H RUP3  comes the system profile ‘Password Case Option’ After 11i.ATG_PF.H.RUP4  this system profile option was renamed to  ‘Signon Password Case’. There are two settings: ‘Sensitive’ and ‘Insensitive’. The default is ‘Insensitive’. Setting this profile option to ‘Sensitive’ will make the password case sensitive. ‘Mixed’ is no longer supported. These profiles should only be set at Site level. They can be set at other levels, such as User or Responsibility. However, when logging in there is no User context, so if a User is prompted to change their password at login, the profiles are only evaluated at site level. Once logged in and resetting passwords using Preferences->Change Password, or the Security ->User->Define form these other levels will have effect and will confuse the issue. So these profiles should only be set at Site level, for consistent enforcement.

How to compile invalid objects in an APPS Environment

 

Applying Patches can create invalid objects. To get a quick count of the number of existing invalids (if any), use the following select statement : SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID'; For a more detailed query, use the following script : SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' GROUP BY OWNER, OBJECT_TYPE; To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type : alter package <package_name> compile; (package specification) alter package <package_name> compile body; (package body) alter view <view_name> compile; (view) If the object compiles with warnings, use either of the following to see the errors that caused the warnings : show errors OR select * from user_errors where name = '<OBJECT_NAME>'; Another way to correct invalid objects is to run the adadmin utility as follows: UNIX OPERATING PLATFORM 1. Log in as APPS User : <applmgr username>/<applmgr password> 2. Start the adadmin-Utility from the Unix prompt with this command : adadmin The utility will then ask you a series of questions. 3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s) This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses the same parallel phases as AutoInstall. Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba ) Within Applications, there is a script to compile INVALID objects – called ADCOMPSC.pls Arguments for ADCOMPSC.pls : 1 – Schema to run in 2 – Password for schema 3 – Check errors for objects starting with #3 NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema’s. In case of an ORA-1555 error while running adcompsc.pls, restart the script. The script can be run as followed : cd $AD_TOP/sql sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD % Example : SQL> @adcompsc.pls apps apps % After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing. If there are any objects still left INVALID, verify them by using the script ‘aderrchk.sql’ to record the remaining INVALID objects. ‘Aderrchk.sql’ uses the same syntax as ‘adcompsc.pls’. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool <file> command in sqlplus. e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD % For objects which will not compile, try the following : select text from user_source where name = 'OBJECTNAME' and text like '%Header%'; This script will provide the script that creates the packages/recreates the packages. SQL>@packageheader SQL>@packagebody If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package : select text from user_errors where name = '<PACKAGENAME>';

Pinning Oracle Applications Objects into the shared pool

Oracle Applications DBAs who want to improve database performance by pinning packages into shared pool. Why pinning objects into the shared pool? Oracle Applications requires space in the ORACLE System Global Area (SGA) for stored packages and functions. If SGA space is fragmented, there may not be enough space to load a package or function. You should pre-allocate space in the SGA shared pool for packages, functions, and sequences by “pinning” them. Pinning objects in the shared pool can provide a tremendous increase in database performance, if it is done correctly. Since pinned objects reside in the SQL and PL/PLSQL memory areas, they do not need to be loaded and parsed from the database, which saves considerable time. What objects to be pinned into the shared pool? Most performance improvement can be gained from pinning large, frequently used packages. Pinned objects are expensive in terms of memory space, since other not-pinned objects need this memory space, too. In general do not pin all objects or rarely used objects – this could even decrease database performance. As a general rule, you should always pin the following packages which are owned by SYS: (see Note 61623.1 SHARED POOL TUNING) STANDARD DBMS_STANDARD DBMS_UTILITY DBMS_DESCRIBE DBMS_OUTPUT and maybe other SYS packages that are often used (DBMS_LOCK, DBMS_ALERT, etc.). The Applications objects that should be pinned are harder to identify and will vary from site to site, depending on what the users are doing. To identify good candidates for pinning, you need to know which objects are being executed the most. To do this, let the system run long enough to reach a steady state (several days to a week). Then initiate a SQL*Plus session as system (or sys or apps) and run the following script $AD_TOP/sql/ADXCKPIN.sql. This will spool object execution and reload statistics into the output file ADXCKPIN.lst. Example output: OBJECT TYPE SPACE(K) LOADS EXECS KEPT ———————————- ———— ——– —— ——- —- APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE 15.2 1 9 NO APPS.FND_ATTACHMENT_UTIL_PKG PACKAGE BODY 13.7 1 8 NO APPS.FND_CLIENT_INFO PACKAGE 2.7 1 206 NO APPS.FND_CLIENT_INFO PACKAGE BODY 13.0 1 206 NO APPS.FND_CONCURRENT PACKAGE 15.2 1 199 NO APPS.FND_CONCURRENT PACKAGE BODY 24.2 1 197 NO   Another handy script: SELECT substr(owner,1,10)||’.'||substr(name,1,35) “Object Name”, ‘ Type: ‘||substr(type,1,12)|| ‘ size: ‘||sharable_mem || ‘ execs: ‘||executions|| ‘ loads: ‘||loads|| ‘ Kept: ‘||kept FROM v$db_object_cache WHERE type in (‘TRIGGER’,'PROCEDURE’,'PACKAGE BODY’,'PACKAGE’) AND executions > 0 ORDER BY executions desc, loads desc, sharable_mem desc;   Choose the objects with a high number of executions (EXECS) or very large (SPACE(K)), frequently used objects. If the decision is between two objects that have been executed the same number of times, then preference should be given to the larger object. From experience, very good results have been achieved with having pinned only about 10 packages.   How to pin object into shared pool? The pl/sql scripts $AD_TOP/sql/ADXGNPIN.sql (packages, functions) and ADXSPPNS.sql (sequences) generate pinning scripts, which can be executed in Sql*Plus. Do not run them without having edited them, otherwise the scripts would try to pin all objects. Create your own script to pin the packages and pin them in a descending order according to their size. The pl/sql command to pin a package (i.e. FNDCP_TMSRV) manually is: SQL> execute dbms_shared_pool.keep(‘APPS.FNDCP_TMSRV’);

How to Change IP Address in an Oracle Applications Environment

1. Change the IP Address in the Server; 2. Verify the current ip address setup in the Oracle Applications environment. Connect as apps user into SQL*Plus and run: select NODE_NAME, STATUS, NODE_MODE, NODE_ID,SERVER_ADDRESS, HOST, DOMAIN, WEBHOST, VIRTUAL_IP from fnd_nodes where node_name = upper(‘hostname’); 3. Run the following command to remove the old ip address from the Oracle Applications tables: perl $AD_TOP/bin/adgentns.pl appspass=apps contextfile=$APPL_TOP/admin/<SID>_hostname.xml -removeserver replace <SID>_hostname.xml for the context file name under the $APPL_TOP/admin directory; then connect to SQL*Plus as apps user and run: begin FND_NET_SERVICES.remove_server(‘<SID>’, ‘<hostname>’); end; / commit; / replace <SID> by the SID of the environment and <hostname> by the hostname in the environment. Both must be entered in upper case. 4. Run autoconfig to populate the values using the new IP Address. 5. Confirm the ip address has been changed to the new value changed in the step 1: select NODE_NAME, STATUS, NODE_MODE, NODE_ID,SERVER_ADDRESS, HOST, DOMAIN, WEBHOST, VIRTUAL_IP from fnd_nodes where node_name = upper(‘hostname’);