Apps DBA Scripts

1. Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

3. Find out Inactive Form sessions for X hours?


set lines 200 pages 200
col event for a30

select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs, 
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
       a.process, b.spid
  from v$session a, v$process b
 where a.action like 'FRM%'
   and a.paddr = b.addr
   and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;

4. Find out Application Names (Products) in Oracle Apps?

set lines 200

set pagesize 300

col APPLICATION_NAME for a70

select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from apps.fnd_application b, apps.fnd_application_tl a  where a.APPLICATION_ID=b.APPLICATION_ID; 

5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?


set lines 200 pages 300

col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50

break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;

6. Find out session details  of a concurrent Request?


set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/

7. Find out All Concurrent Queue/Manager sizes/Processes?

set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;

8. Find out Responsibility name from a Concurrent program?

set lines 200 pages 300
SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE '%&Conc_Prog_name%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

8. Find out Currently running concurrent requests with OSPID,SID and Serial# etc?

SELECT a.request_id, d.sid, d.serial# , c.SPID
    FROM apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    WHERE a.controlling_manager = b.concurrent_process_id
    AND c.pid = b.oracle_process_id
    AND b.session_id=d.audsid
    AND a.phase_code = 'R' and status_code='R';

Simple PCP configuration

Simple PCP configuration on Non RAC Environment

1. Assume that you have your Application tier is shared across all internal middle tiers
2. Assume you have two CM nodes
3. Stop the Concurrent Managers on both the CM nodes
4. Apply patch 9074947 on application tier
5. set APPLDCP value ON in the context file of both the CM nodes.
6. Run autoconfig on Both cm nodes
7. Verify tnsnames.ora on both CM nodes has FNDFS entries of both the nodes
8. Ensure Internal Monitors on both CM nodes is defined properly and have workshifts assigned to them
9. Make sure 2 Internal monitor Managers of both the nodes is activated. If not activate from Concurrent -> Manager -> Administrator
10. Use the following query to find out your concurrent managers details.

SELECT distinct C.USER_CONCURRENT_QUEUE_NAME, C.MAX_PROCESSES, C.RUNNING_PROCESSES, C.TARGET_PROCESSES, C.NODE_NAME, C.NODE_NAME2
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id;

10. Now Define Primary and secondary nodes for each concurrent manager from Concurrent -> Manager -> Define as per your configuration.
11. Bring up Concurrent Managers on both the CM nodes.
12. Test the concurrent managers by submitting two uniqe concurrent programs that should be running on managers on both the nodes.

Failover Testing

Test Case #1

1. Bring down Apps Listener on CM node 2 using kill -9
2. Kill all FNDLIBR process on CM node 2 using kill -9
3. Start Apps listener on CM node 2 after 5 mins.
4. Monitor CM2, you should see all the managers on CM2 should come up automatially

Test Case #2

1. Bring down apps listener on CM node2 using kill -9.
2. Start the managers on CM1, you should see managers of CM2 will start on CM node 1.
3. Bring up Apps listenr on CM2.
4. You should see, Managers of CM2 should fall back to CM2 from CM1.

Test Case #3

1. Bring down host of CM node 2. It should be shutdown.
2. You should see all the managers of CM2 should be failed over to CM1
3. Bring up host of CM node2.
4. Bring up apps listener on cm node2.
5. You should see all the managers of CM2 should fall back to CM2 fro CM1.

Oracle Note id : How to Setup and Test Failover of PCP on Non-RAC Environments. [ID 743716.1]