Life Cycle of the Concurrent Request
You are welcome to leave a comment....
Showing posts with label Concurrent. Show all posts
Showing posts with label Concurrent. Show all posts
Concurrent manager in Oracle Apps
Unknown on Tuesday, 7 August 2012
Concurrent manager in Oracle Apps
Lets discuss the very basics of Concurrent Managers, again the very basics for the beginners that read :/http://appstier.blogspot.in/
Two things are obvious:-
1. Concurrent Manager is related to Concurrent Programs
2. Concurrent manager manages the concurrent(oops I mean parallel) execution of concurrent programs.
So what's left to explain?.....Well nothing much, but I gave a commitment to one of my readers that I shall write something about concurrent managers today. And now when I begin to write, I realize it is worth writing something in plain English on this topic.
Lets explain this with some Q&A
Q : How to run a concurrent program?
Ans: In oracle apps you have a concurrent program submission screen. You can submit the concurrent program from that screen.
Q: What happens when you submit a concurrent program?
Ans: There is something known as Concurrent Manager that runs in the background all the time. This background process, called Concurrent Manager ideally will be running 24x7.
As the name suggests, purpose of a concurrent manager is to manage the submitted concurrent programs.
Q: When I submit a concurrent program( or call it concurrent request), how does concurrent manager pick this up?
Ans: Concurrent manager will be running in the background waiting for a concurrent program to be submitted. As soon as a concurrent program is submitted, it then gets put in an execution queue by concurrent manager.
Q: Why does the Concurrent manager put a concurrent program into a queue? Why doesn't the manager simply let the program run?
Ans: Because at any given point in time a concurrent manager can run no more than say 10 programs concurrently. This figure of 10 is configurable of course. First the manager puts a submitted program into a queue, next the manager checks if there is a slot available (i.e. Less than 10 programs are currently running). If a slot is found available, the concurrent manager then runs the program, or else it keeps the concurrent program in a queue with status Pending.
Q: If we have two concurrent programs, that must never run in parallel(oops I mean concurrently)....can concurrent manager manage such scenarios?
Ans: Of course it can. When you define a concurrent program, you can specify if there are any incompatible programs. If incompatible concurrent programs exist, then concurrent manager will wait for the incompatible program to complete.
Q: Is that all what concurrent manager does?
Ans: Much more, if interested, then read on….
Concurrent manager is responsible for below things too…..
Managing the printer:-
An Oracle Report is registered as a concurrent program too. During submission or during the definition of concurrent program, we can specify the printer where report gets printed. Concurrent manager will send the output of the program to that printer.
Managing the programs completion status-
For example a pl/sql concurrent program can set retcode=2 to make a program complete with warning. Hence concurrent manager not just executes the program, but it manages the completion status of the program too.
Classpath of a java program:-
A concurrent program can be of type java too. If for this specific concurrent program you wish to use a set of java libraries, then you can specify the path of that library in concurrent program definition. Concurrent manager will amend the CLASSPATH to reflect the path of the java library.
Interaction with host concurrent program-
When running a host concurrent program, the concurrent manager passes the apps password as a parameter to the unix script
Tracing a concurrent program
Concurrent manager enable the session trace for the concurrent program, if enable trace checkbox is checked in program definition. You can then go to user dump directory and do tkprof on the file.
Optimization options:-
The concurrent program definition provides an option to specify optimization mode, like choose, fist rows,all rows, rule based etc. The concurrent manager will alter the optimization mode of the session before the submission of the program. Obviously this option has no relevance to Host type concurrent program.
How to Start/Stop Concurrent Manager ?
In Oracle Applications 11i as you might already be aware that all startup shutdown scripts in 11i(11.5.10) are in $INST_TOP/admin/scripts/$CONTEXT_NAME where $CONTEXT_NAME is your SID_hostname , lets say your SID or Instance Name is VISION and its on machine with name machine1 so your CONTEXT_NAME will be VISION_machine1. So script to start/stop concurrent manager is adcmctl.sh where ad (In my view) is application dba , cm is concurrent manager & ctl means control. Well if you really want to explore more I'll suggest you open this file & go through it . If you open this script you will see syntax to run it in start or stop mode so I am not going to spoon feed you by giving syntax. When you run adcmctl.sh it records execution of this command in text file with name adcmctl.txt at directory $INST_TOP/logs/appl/admin/log/$CONTEXT_NAME. adcmctl.sh called another script startmgr which in turn calls batchmgr in $FND_TOP/bin and this is actual file which fire up Internal Concurrent Manager Process (Open these two files as they are text file and read content in these files.
Lets check important parameter in this file
DISPLAY Set it to some valid XServer , if this is not set properly your reports display will fail (specially one with PDF or Bar code dislay)
DIAG_PARAM Diagnosis parameter it will take value Y or N with default N, If you encounter some error while starting Concurrent Manager you change this to Y and then extra logging will be enabled then check Concurrent Manager log file.
CONCSUB stands for CONC(Concurrent) SUB(Submit) , used to submit concurrent request like shut down concurrent manager.
Lets discuss the very basics of Concurrent Managers, again the very basics for the beginners that read :/http://appstier.blogspot.in/
Two things are obvious:-
1. Concurrent Manager is related to Concurrent Programs
2. Concurrent manager manages the concurrent(oops I mean parallel) execution of concurrent programs.
So what's left to explain?.....Well nothing much, but I gave a commitment to one of my readers that I shall write something about concurrent managers today. And now when I begin to write, I realize it is worth writing something in plain English on this topic.
Lets explain this with some Q&A
Q : How to run a concurrent program?
Ans: In oracle apps you have a concurrent program submission screen. You can submit the concurrent program from that screen.
Q: What happens when you submit a concurrent program?
Ans: There is something known as Concurrent Manager that runs in the background all the time. This background process, called Concurrent Manager ideally will be running 24x7.
As the name suggests, purpose of a concurrent manager is to manage the submitted concurrent programs.
Q: When I submit a concurrent program( or call it concurrent request), how does concurrent manager pick this up?
Ans: Concurrent manager will be running in the background waiting for a concurrent program to be submitted. As soon as a concurrent program is submitted, it then gets put in an execution queue by concurrent manager.
Q: Why does the Concurrent manager put a concurrent program into a queue? Why doesn't the manager simply let the program run?
Ans: Because at any given point in time a concurrent manager can run no more than say 10 programs concurrently. This figure of 10 is configurable of course. First the manager puts a submitted program into a queue, next the manager checks if there is a slot available (i.e. Less than 10 programs are currently running). If a slot is found available, the concurrent manager then runs the program, or else it keeps the concurrent program in a queue with status Pending.
Q: If we have two concurrent programs, that must never run in parallel(oops I mean concurrently)....can concurrent manager manage such scenarios?
Ans: Of course it can. When you define a concurrent program, you can specify if there are any incompatible programs. If incompatible concurrent programs exist, then concurrent manager will wait for the incompatible program to complete.
Q: Is that all what concurrent manager does?
Ans: Much more, if interested, then read on….
Concurrent manager is responsible for below things too…..
Managing the printer:-
An Oracle Report is registered as a concurrent program too. During submission or during the definition of concurrent program, we can specify the printer where report gets printed. Concurrent manager will send the output of the program to that printer.
Managing the programs completion status-
For example a pl/sql concurrent program can set retcode=2 to make a program complete with warning. Hence concurrent manager not just executes the program, but it manages the completion status of the program too.
Classpath of a java program:-
A concurrent program can be of type java too. If for this specific concurrent program you wish to use a set of java libraries, then you can specify the path of that library in concurrent program definition. Concurrent manager will amend the CLASSPATH to reflect the path of the java library.
Interaction with host concurrent program-
When running a host concurrent program, the concurrent manager passes the apps password as a parameter to the unix script
Tracing a concurrent program
Concurrent manager enable the session trace for the concurrent program, if enable trace checkbox is checked in program definition. You can then go to user dump directory and do tkprof on the file.
Optimization options:-
The concurrent program definition provides an option to specify optimization mode, like choose, fist rows,all rows, rule based etc. The concurrent manager will alter the optimization mode of the session before the submission of the program. Obviously this option has no relevance to Host type concurrent program.
How to Start/Stop Concurrent Manager ?
In Oracle Applications 11i as you might already be aware that all startup shutdown scripts in 11i(11.5.10) are in $INST_TOP/admin/scripts/$CONTEXT_NAME where $CONTEXT_NAME is your SID_hostname , lets say your SID or Instance Name is VISION and its on machine with name machine1 so your CONTEXT_NAME will be VISION_machine1. So script to start/stop concurrent manager is adcmctl.sh where ad (In my view) is application dba , cm is concurrent manager & ctl means control. Well if you really want to explore more I'll suggest you open this file & go through it . If you open this script you will see syntax to run it in start or stop mode so I am not going to spoon feed you by giving syntax. When you run adcmctl.sh it records execution of this command in text file with name adcmctl.txt at directory $INST_TOP/logs/appl/admin/log/$CONTEXT_NAME. adcmctl.sh called another script startmgr which in turn calls batchmgr in $FND_TOP/bin and this is actual file which fire up Internal Concurrent Manager Process (Open these two files as they are text file and read content in these files.
Lets check important parameter in this file
DISPLAY Set it to some valid XServer , if this is not set properly your reports display will fail (specially one with PDF or Bar code dislay)
DIAG_PARAM Diagnosis parameter it will take value Y or N with default N, If you encounter some error while starting Concurrent Manager you change this to Y and then extra logging will be enabled then check Concurrent Manager log file.
CONCSUB stands for CONC(Concurrent) SUB(Submit) , used to submit concurrent request like shut down concurrent manager.
If you like this post Please keep on Comment............
Found dead process in ICM log file
Unknown on Tuesday, 5 June 2012
Found dead process in ICM log file
There is an issue occurred on my E-Business Suite.Many concurrent mangers are not started after restarted the concurrent manger server.The Internal Manager log file shows that the following information.
Found dead process: spid=(12099), cpid=(1125806),
ORA pid=(123), manager=(0/15293)
Found dead process: spid=(12053), cpid=(1125722),
ORA pid=(104), manager=(0/14293)
This was happened due to the controlling manager was not brought down gracefully.Gracefully meaning that it was brought down when and only when, all concurrent processes are completed.I have applied the following solution to resolve this issue.
Solution:
Step1. Stop all middle tier services including the concurrent managers and verify that all APPS-Tier processes are down :
ps -fu<APPS User> | grep FND
OR
ps -fu<APPS User> | grep FND | wc -l
The command will show all processes on OS level, which are running for the Concurrent Managers. It can take some minutes until all CCM processes are stopped.
Step2. Stop the Database and verify, that all Database processes are down.
Step3. Start the database.
Step4. Execute the CMCLEAN.SQL script from the referenced note below (don’t forget to commit).
For details, please see NOTE:134007.1 – ‘CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables’.
Step5. Start the Middle Tier Services including your Concurrent Manager.
Step6. Verify the issue .
Here Second step and Third Steps are optional.Normally it s not necessary to stop the database for run the cmclean.sql.In my case the cmclean script is hanged when i do run it and its needed to restart the database.
There is an issue occurred on my E-Business Suite.Many concurrent mangers are not started after restarted the concurrent manger server.The Internal Manager log file shows that the following information.
Found dead process: spid=(12099), cpid=(1125806),
ORA pid=(123), manager=(0/15293)
Found dead process: spid=(12053), cpid=(1125722),
ORA pid=(104), manager=(0/14293)
This was happened due to the controlling manager was not brought down gracefully.Gracefully meaning that it was brought down when and only when, all concurrent processes are completed.I have applied the following solution to resolve this issue.
Solution:
Step1. Stop all middle tier services including the concurrent managers and verify that all APPS-Tier processes are down :
ps -fu<APPS User> | grep FND
OR
ps -fu<APPS User> | grep FND | wc -l
The command will show all processes on OS level, which are running for the Concurrent Managers. It can take some minutes until all CCM processes are stopped.
Step2. Stop the Database and verify, that all Database processes are down.
Step3. Start the database.
Step4. Execute the CMCLEAN.SQL script from the referenced note below (don’t forget to commit).
For details, please see NOTE:134007.1 – ‘CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables’.
Step5. Start the Middle Tier Services including your Concurrent Manager.
Step6. Verify the issue .
Here Second step and Third Steps are optional.Normally it s not necessary to stop the database for run the cmclean.sql.In my case the cmclean script is hanged when i do run it and its needed to restart the database.
How to cancel a concurrent request from db level?
Unknown on
How to cancel a concurrent request from db level?
- The following query returns sid” and “serial#” once you have provided “request_id” of a Concurrent Program which is currently running.
- Take the values “sid” and “serial#” and kill the sessions at the database level.
Step1:Connect to SQLPLUS as the APPS user:
SQL> SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);
Enter value for request_id:
Step2:Connect to SQLPLUS as the SYSTEM user:
SQL> ALTER SYSTEM KILL SESSION '&sid,&serial#'IMMEDIATE;
Enter value for sid:
Enter value for serial:
Concurrent Manager FAQs
Unknown on Tuesday, 8 May 2012
Concurrent Manager FAQs
Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
Internal Concurrent Manager (ICM)
Standard Manager
Conflict Resolution Manager (CRM)
The setup of product-specific managers related issues should
be directed to the associated product group, i.e. the Inventory manager
Q: Where do the concurrent request output and log files reside?
A: $APPLCSF/out or $Product_Top/out for output files
$APPLCSF/log or $Product_Top/log for log files
Q: Where are temporary files located?
A: Temporary files reside in:
$APPLPTMP$APPLTMP
$REPORTS25_TMP (Release 11.0)
$REPORTS60_TMP (Release 11.5)
Q: How to change PMON method to LOCK.
To check the process monitor (PMON) method status:
Connect as database user “APPS”.
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id = 10001
AND level_value = 0
AND application_id = 0
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = ‘CONC_PMON_METHOD’);
This should return one row with a value of ‘LOCK’
If the value is ‘RDBMS’ or ‘OS’ run the following script:
$FND_TOP/sql/AFIMPMON.SQL – this will set the PMON method to LOCK instead of RDBMS.
Bounce the database. If running on NT, restart the NT Server completely.
Within 11.5 Applications, the only PMON method is LOCK.
Q: How to start and shutdown the CCM on Unix as APPS
A: To Start:
STARTMGR sysmgr=APPS/passwd
To Shutdown:
concsub apps/<APPS password> sysadmin ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
11.5 => 11.5.4
adcmctl.sh apps/passwd start/stop prod
11.5.5 =>
adcmctl.sh start/stop apps/passwd prod
Q: How to terminate a concurrent request that cannot be cancelled?
A: Identify the request number to terminate. Shut down the concurrent managers and issue the following sql command as applsys:
update fnd_concurrent_requests
set status_code = ‘E’, phase_code = ‘C’
where Request_id = <reqnum>; (reqnum = request number)
Q: A Manager is not activating.
A: Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each. If the count is greater, change to one only. This can be done from sqlplus as follows:
As user apps:
select *
from SYS.DUAL;
Any extra rows should be deleted.
delete rownum
from SYS.DUAL;
(rownum = the row number to delete)
Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:
delete from fnd_dual
where rownum < (select max(rownum) from fnd_dual);
Bounce the Concurrent Managers.
Q: Internal Concurrent Manager.
A: If the ICM should go down, requests will continue to run normally, except for ‘queue control’ requests.
If the ICM should go down, you can restart it with ‘startmgr’. There is no need to shut down the other managers first.
Q: How to purge requests that are in Pending status.
A: The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests to Completed
before purging them. The Internal Manager Must be DOWN!
Using sqlplus as APPLSYS perform the following:
UPDATE fnd_concurrent_requests
SET phase_code = ‘C’
WHERE phase_code = ‘P’.
Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
Internal Concurrent Manager (ICM)
Standard Manager
Conflict Resolution Manager (CRM)
The setup of product-specific managers related issues should
be directed to the associated product group, i.e. the Inventory manager
Q: Where do the concurrent request output and log files reside?
A: $APPLCSF/out or $Product_Top/out for output files
$APPLCSF/log or $Product_Top/log for log files
Q: Where are temporary files located?
A: Temporary files reside in:
$APPLPTMP$APPLTMP
$REPORTS25_TMP (Release 11.0)
$REPORTS60_TMP (Release 11.5)
Q: How to change PMON method to LOCK.
To check the process monitor (PMON) method status:
Connect as database user “APPS”.
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id = 10001
AND level_value = 0
AND application_id = 0
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = ‘CONC_PMON_METHOD’);
This should return one row with a value of ‘LOCK’
If the value is ‘RDBMS’ or ‘OS’ run the following script:
$FND_TOP/sql/AFIMPMON.SQL – this will set the PMON method to LOCK instead of RDBMS.
Bounce the database. If running on NT, restart the NT Server completely.
Within 11.5 Applications, the only PMON method is LOCK.
Q: How to start and shutdown the CCM on Unix as APPS
A: To Start:
STARTMGR sysmgr=APPS/passwd
To Shutdown:
concsub apps/<APPS password> sysadmin ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
11.5 => 11.5.4
adcmctl.sh apps/passwd start/stop prod
11.5.5 =>
adcmctl.sh start/stop apps/passwd prod
Q: How to terminate a concurrent request that cannot be cancelled?
A: Identify the request number to terminate. Shut down the concurrent managers and issue the following sql command as applsys:
update fnd_concurrent_requests
set status_code = ‘E’, phase_code = ‘C’
where Request_id = <reqnum>; (reqnum = request number)
Q: A Manager is not activating.
A: Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each. If the count is greater, change to one only. This can be done from sqlplus as follows:
As user apps:
select *
from SYS.DUAL;
Any extra rows should be deleted.
delete rownum
from SYS.DUAL;
(rownum = the row number to delete)
Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:
delete from fnd_dual
where rownum < (select max(rownum) from fnd_dual);
Bounce the Concurrent Managers.
Q: Internal Concurrent Manager.
A: If the ICM should go down, requests will continue to run normally, except for ‘queue control’ requests.
If the ICM should go down, you can restart it with ‘startmgr’. There is no need to shut down the other managers first.
Q: How to purge requests that are in Pending status.
A: The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests to Completed
before purging them. The Internal Manager Must be DOWN!
Using sqlplus as APPLSYS perform the following:
UPDATE fnd_concurrent_requests
SET phase_code = ‘C’
WHERE phase_code = ‘P’.
Troubleshooting CM
Unknown on
Lets classify how to start troubleshooting CM
Issues related to startup/shutdown of CM
If you try to start CM using adcmctl.sh or adstrtall.sh script & you encounter problem in starting CM then check for log files. All information related to why Concurrent Manager not coming up is recorder in log file at $APPLCSF/$APPLLOG with default name as $SID_$DD ( where DD is date on which you started Concurrent Manager ). This Concurrent Manager naming convention will be listed in file adcmctl.sh file (Concurrent Manager startup file). If $APPLCSF is not set then you can look for this file in $FND_TOP$APPLLOG
ICM is Up but some managers are still down
As you know adcmctl.sh script starts ICM (Internal Concurrent Manager) and its duty of ICM to start other Managers. If you see ICM is up with some other managers & Issue is with only Particular Managers then look for log files of these managers .
Concurrent -> Manager -> Administer -> Select manager which is not starting -> Click on "Processes" -> Click on "Manager Log" to find out error message.
These individual manager log file, you can access under $APPLCSF/$APPLLOG directory as well.
If you error message is not enough to find out cause of problem then you can enable debug on CM log files . You can enable diagnostics by changing DIAG_PARAM="" to DIAG_PARAM="Y" in adcmctl.sh (CM Startup script file)
Other things you can try for CM startup Issues :
Try to bounce CM & see all FNDLIBR processes are cleared before restrarting CM. You can run cmclean.sql command to clear any Invalid State against any request or manager. Check for any messages in log file. Check status of Manager from administer Concurrent Manager Window.
Concurrent -> Manager -> Administer (Login as System Administrator Responsibility)
Issues related to startup/shutdown of CM
If you try to start CM using adcmctl.sh or adstrtall.sh script & you encounter problem in starting CM then check for log files. All information related to why Concurrent Manager not coming up is recorder in log file at $APPLCSF/$APPLLOG with default name as $SID_$DD ( where DD is date on which you started Concurrent Manager ). This Concurrent Manager naming convention will be listed in file adcmctl.sh file (Concurrent Manager startup file). If $APPLCSF is not set then you can look for this file in $FND_TOP$APPLLOG
ICM is Up but some managers are still down
As you know adcmctl.sh script starts ICM (Internal Concurrent Manager) and its duty of ICM to start other Managers. If you see ICM is up with some other managers & Issue is with only Particular Managers then look for log files of these managers .
Concurrent -> Manager -> Administer -> Select manager which is not starting -> Click on "Processes" -> Click on "Manager Log" to find out error message.
These individual manager log file, you can access under $APPLCSF/$APPLLOG directory as well.
If you error message is not enough to find out cause of problem then you can enable debug on CM log files . You can enable diagnostics by changing DIAG_PARAM="" to DIAG_PARAM="Y" in adcmctl.sh (CM Startup script file)
Other things you can try for CM startup Issues :
Try to bounce CM & see all FNDLIBR processes are cleared before restrarting CM. You can run cmclean.sql command to clear any Invalid State against any request or manager. Check for any messages in log file. Check status of Manager from administer Concurrent Manager Window.
Concurrent -> Manager -> Administer (Login as System Administrator Responsibility)
Concurrent Manager(CM) Trouble shooting
Unknown on Sunday, 6 May 2012
Concurrent Manager(CM) Trouble shooting :
For Concurrent manager(CM) trouble shooting
1. First check the CM is up or not by using below any one procedure.
i. ps –ef|grep FNDLIBR
ii. adcmctl.sh status apps/<appsPWD>
iii. Login as system administrator responsibilities go to the below navigation Concurrent -> manager -> Administer -> see the Actual and target for all the managers
2. If CM in down check the internal manager Logfile in $APPLCSF/APPLLOG location for errors.
3. If any errors related to FNDFS then check the Application listener STATUS , if it is not running start it.
4. If CM is up/running then log file and output file are not able see the user from the front end then check the Application listener is status if it is not running then start it.
5. If any user is complained some request is running long time then
i. First check the CM status and ICM status.
ii. Check any Locks on DB particularly related to that program.
iii. Check any INVALID objects related to that program.
iv. Check any incompatible requests/programs are running for that program.
Login as system administrator responsibility and query the long running request /program. Navigate to concurrent program define query that long running request in the same form select incompatible requests .
If any incompatible request is running then find the user name who is running that program inform them regarding the incompatibilities.
For Concurrent manager(CM) trouble shooting
1. First check the CM is up or not by using below any one procedure.
i. ps –ef|grep FNDLIBR
ii. adcmctl.sh status apps/<appsPWD>
iii. Login as system administrator responsibilities go to the below navigation Concurrent -> manager -> Administer -> see the Actual and target for all the managers
2. If CM in down check the internal manager Logfile in $APPLCSF/APPLLOG location for errors.
3. If any errors related to FNDFS then check the Application listener STATUS , if it is not running start it.
4. If CM is up/running then log file and output file are not able see the user from the front end then check the Application listener is status if it is not running then start it.
5. If any user is complained some request is running long time then
i. First check the CM status and ICM status.
ii. Check any Locks on DB particularly related to that program.
iii. Check any INVALID objects related to that program.
iv. Check any incompatible requests/programs are running for that program.
Login as system administrator responsibility and query the long running request /program. Navigate to concurrent program define query that long running request in the same form select incompatible requests .
If any incompatible request is running then find the user name who is running that program inform them regarding the incompatibilities.
Concurrent Manager not starting after Cloning ?
Unknown on
Concurrent Manager not starting after Cloning ?
CM not starting after Cloning ?
Once after doing Rapid cloning some of concurrent managers were not starting.
The issue was that the node was wrongly defined as source node in Administer Manager screen.
Then i checked in the FND_NODES table which wrongly had the source nodes as well apart from the target nodes.
I performed the following action plan which resolved the issue->
CM not starting after Cloning ?
Once after doing Rapid cloning some of concurrent managers were not starting.
The issue was that the node was wrongly defined as source node in Administer Manager screen.
Then i checked in the FND_NODES table which wrongly had the source nodes as well apart from the target nodes.
I performed the following action plan which resolved the issue->
* Shutdown the apps services
* EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
* Ran AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers.
* Start the apps services.
Actually FND_CONC_CLONE.SETUP_CLEAN clears up the tables with nodes information and when we run autoconfig, it repopulates these tables with correct node information.
Concurrent Manager Issues
Unknown on Thursday, 3 May 2012
Concurrent Manager Issues
REP-3000: Internal error starting Oracle Toolkit
Issue:
Recently we had issue with Concurrent requests which having print format of PDF or XML, and the exact
error is "REP-3000: Internal error starting Oracle Toolkit"
Solution:
1. Log on as root on the UNIX box.
2. Start The VNC server:
Example:
$ vncserver :0
3. Set the DISPLAY environment variable for Oracle Reports by performing the
following steps:
a. $ set DISPLAY=hostname:0; export DISPLAY
b. $ xhost +
c. Set the DISPLAY parameter in adcmctl.sh, adrepctl.sh, adfrmctl.sh and gsmstart.sh to
hostname:0
d. Restart concurrent manager and report server
How to Verify:
1. Login with system administrator responsibility
2. Concurrent – program-Define-Query Active Users-Change Print format to PDF- save
3. Run the Active Users Concurrent Request
4. It should be completed without any issue.
Not able to view Concurrent request Output
Unknown on
Not able to view Concurrent request Output
Steps to debug "Not able to view Concurrent request Output "
When we try to view the concurrent request output or log files we would get the following Error:
" An error occurred while attempting to establish an Applications File
Server connection. There may be a network configuration problem, or
the TNS listener may not be running on <nodename> "
1. Run a report, is there actually a Report Output and Log file being produced at
the Operating System level?
- Check the $APPLCSF/out and the $APPLCSF/log directories
for the Request_ID out and log file
- If there are no log and output file being produces there,
Check the permissions on those directories (APPLMGR should have write)
- If $APPLCSF is not set, check the $FND_TOP/log and out for output and logs
2. Confirm that there are 2 listener processes actively running for the Instance that
you cannot view reports on -- 1 from 8.0.6 and from Database directory structures
- type the following:
ps -ef | grep tnslsnr
- If there is only 1 listener running from the Database directory structure
That is the Database Listener
The FNDFS listener needs to be started separately from the Database Listener
- To start the FNDFS Listener:
a. Log the Operating System Level as the APPLMGR account user
b. Set the environment for the $APPL_TOP & 8.0.6 $ORACLE_HOME (APPSORA.env)
c. Go to the $OAD_TOP/admin/scripts (Common_top area)
d. Run the following script:
adalnctl.sh start APPS_<SID name>
(you can confirm the FNDFS Listener name from viewing the
8.0.6 ORACLE_HOME/network/admin/listener.ora file)
3. Check the actual machine name for the Concurrent Processor Server
- type the following:
uname -n
4. Go to the Applications and check the CONCURRENT > MANAGER > ADMINISTER screen
in the System Administrator Responsibility
The INTERNAL MANAGER NODE NAME; is it the same as what was returned from question 4?
- If it is the same then continue with next step
- If it is not the same,
Shutdown Concurrent Managers and update the FND table:
a. Go to the $OAD_TOP/admin/scripts
b. adcmctl.sh apps/apps stop
c. Connect to SQL*PLUS apps/apps
d. SQL > select target_node
from FND_CONCURRENT_QUEUES;
--------------------
This may return the name of an old machine
e. SQL > update FND_CONCURRENT_QUEUES
set target_node='<newmachinename>'
where target_node='<oldmachinename>';
f. SQL > commit;
g. SQL > exit
h. adcmctl.sh apps/apps start
5. In the 8.0.6 TNSNAMES.ora file, look at the FNDFS_<hostname> connection string entry.
Is the hostname value the same as is in questions 4?
- The Rapid Install creates 2 or sometimes 3 FNDFS entries in the TNSNAMES.ora
a. 1 with the FNDFS_<hostname>
b. 1 with the FNDFS_<SID>
c. 1 with the FNDFS_<hostname.domain>
- The one that is the correct entry is FNDFS_<hostname>
(hostname being what is returned from a "uname -n")
- If this file does not contain the correct Hostname value
Make a backup of it and edit it to change the FNDFS entry.
6. Check the Network Connection for the FNDFS entry that was defined by the Rapid Install.
- At the Operating System Level
a. tnsping FNDFS_<hostname>
- This should return the Hostname name and Port information
identical to the FNDFS entry in the TNSNAMES.ora file
7. Check the Following PROFILE > SYSTEM Options in The System Administrator Responsibility
CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY)
RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer)
- Viewer: Text can Be set to "browser" to view with Netscape or IE
8. This step should be performed in a case where there might have been patches applied
and possibly the FNDFS executable was not Relinked, or it may be
missing from the file system or corrupted.
- Shutdown the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh stop APPS_<SID name>
- Rename or Move the Current FNDFS executable before relinking
a. $FND_TOP/bin
mv FNDFS FNDFS.bak
b. As APPLMGR account user
adrelink.sh force=y "fnd FNDFS"
- Start the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh start APPS_<SID name>
Steps to debug "Not able to view Concurrent request Output "
When we try to view the concurrent request output or log files we would get the following Error:
" An error occurred while attempting to establish an Applications File
Server connection. There may be a network configuration problem, or
the TNS listener may not be running on <nodename> "
1. Run a report, is there actually a Report Output and Log file being produced at
the Operating System level?
- Check the $APPLCSF/out and the $APPLCSF/log directories
for the Request_ID out and log file
- If there are no log and output file being produces there,
Check the permissions on those directories (APPLMGR should have write)
- If $APPLCSF is not set, check the $FND_TOP/log and out for output and logs
2. Confirm that there are 2 listener processes actively running for the Instance that
you cannot view reports on -- 1 from 8.0.6 and from Database directory structures
- type the following:
ps -ef | grep tnslsnr
- If there is only 1 listener running from the Database directory structure
That is the Database Listener
The FNDFS listener needs to be started separately from the Database Listener
- To start the FNDFS Listener:
a. Log the Operating System Level as the APPLMGR account user
b. Set the environment for the $APPL_TOP & 8.0.6 $ORACLE_HOME (APPSORA.env)
c. Go to the $OAD_TOP/admin/scripts (Common_top area)
d. Run the following script:
adalnctl.sh start APPS_<SID name>
(you can confirm the FNDFS Listener name from viewing the
8.0.6 ORACLE_HOME/network/admin/listener.ora file)
3. Check the actual machine name for the Concurrent Processor Server
- type the following:
uname -n
4. Go to the Applications and check the CONCURRENT > MANAGER > ADMINISTER screen
in the System Administrator Responsibility
The INTERNAL MANAGER NODE NAME; is it the same as what was returned from question 4?
- If it is the same then continue with next step
- If it is not the same,
Shutdown Concurrent Managers and update the FND table:
a. Go to the $OAD_TOP/admin/scripts
b. adcmctl.sh apps/apps stop
c. Connect to SQL*PLUS apps/apps
d. SQL > select target_node
from FND_CONCURRENT_QUEUES;
--------------------
This may return the name of an old machine
e. SQL > update FND_CONCURRENT_QUEUES
set target_node='<newmachinename>'
where target_node='<oldmachinename>';
f. SQL > commit;
g. SQL > exit
h. adcmctl.sh apps/apps start
5. In the 8.0.6 TNSNAMES.ora file, look at the FNDFS_<hostname> connection string entry.
Is the hostname value the same as is in questions 4?
- The Rapid Install creates 2 or sometimes 3 FNDFS entries in the TNSNAMES.ora
a. 1 with the FNDFS_<hostname>
b. 1 with the FNDFS_<SID>
c. 1 with the FNDFS_<hostname.domain>
- The one that is the correct entry is FNDFS_<hostname>
(hostname being what is returned from a "uname -n")
- If this file does not contain the correct Hostname value
Make a backup of it and edit it to change the FNDFS entry.
6. Check the Network Connection for the FNDFS entry that was defined by the Rapid Install.
- At the Operating System Level
a. tnsping FNDFS_<hostname>
- This should return the Hostname name and Port information
identical to the FNDFS entry in the TNSNAMES.ora file
7. Check the Following PROFILE > SYSTEM Options in The System Administrator Responsibility
CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY)
RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer)
- Viewer: Text can Be set to "browser" to view with Netscape or IE
8. This step should be performed in a case where there might have been patches applied
and possibly the FNDFS executable was not Relinked, or it may be
missing from the file system or corrupted.
- Shutdown the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh stop APPS_<SID name>
- Rename or Move the Current FNDFS executable before relinking
a. $FND_TOP/bin
mv FNDFS FNDFS.bak
b. As APPLMGR account user
adrelink.sh force=y "fnd FNDFS"
- Start the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh start APPS_<SID name>
List Responsibilities That Can Run a Given Concurrent Program
Unknown on
List Responsibilities That Can Run a Given Concurrent Program
Purpose
-------
This script helps in identifying the list of responsibilities that can run a given concurrent request
REM Save this file as whocanrun.sql
REM Usage: sqlplus apps/apps @whocanrun
REM or: sqlplus apps/apps @whocanrun
REM
REM Implemented remark 64545.1 to modify script for use with 11i
REM changed to use fnd_concurrent_programs_vl, and fnd_responsibility_vl
REM in place of fnd_concurrent_programs, and fnd_responsibility.
REM
set verify off
set pagesize 1000
column RN format A40 heading "Responsibility Name"
prompt
prompt &1 can be run by:
SELECT responsibility_name RN
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
OR
UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
/
Purpose
-------
This script helps in identifying the list of responsibilities that can run a given concurrent request
REM Save this file as whocanrun.sql
REM Usage: sqlplus apps/apps @whocanrun
REM or: sqlplus apps/apps @whocanrun
REM
REM Implemented remark 64545.1 to modify script for use with 11i
REM changed to use fnd_concurrent_programs_vl, and fnd_responsibility_vl
REM in place of fnd_concurrent_programs, and fnd_responsibility.
REM
set verify off
set pagesize 1000
column RN format A40 heading "Responsibility Name"
prompt
prompt &1 can be run by:
SELECT responsibility_name RN
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND (UPPER(fcpv.concurrent_program_name) = UPPER('&1')
OR
UPPER(fcpv.user_concurrent_program_name) = UPPER('&1'))
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
/
How to Identify the manager for a given concurrent program
Unknown on
How to Identify the manager for a given concurrent program
The below given script identifies which manager is going to execute a given Concurrent program:
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.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'
The below given script identifies which manager is going to execute a given Concurrent program:
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl fcp, apps.fnd_concurrent_queue_content fcqc, apps.fnd_concurrent_queues_tl cq
WHERE
fcqc.type_application_id(+) = fcp.application_id AND
fcqc.type_id(+) = fcp.concurrent_program_id AND
fcqc.type_code(+) = 'P' AND
fcqc.include_flag(+) = 'I' AND
fcp.LANGUAGE = 'US' AND
fcp.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'
Various scripts used with Concurrent Manager/ Concurrent Requests
Unknown on
Various scripts used with Concurrent Manager/ Concurrent Requests
a. To list all running programs
SELECT FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') PHASE
,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') STATUS
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV('Lang')
AND FCR.PHASE_CODE = 'R'
ORDER BY FCR.ACTUAL_START_DATE DESC
b. To list all pending programs
SELECT FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') PHASE
,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') STATUS
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV('Lang')
AND FCR.PHASE_CODE = 'P'
ORDER BY FCR.ACTUAL_START_DATE DESC
c. To list all programs that are scheduled and yet to run
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
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, cr.PHASE_CODE, cr.STATUS_CODE
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 trunc(cr.requested_start_date) >= trunc(SYSDATE)
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND cr.release_class_id is not null
order by name, requested_start_date;
d. To list avg / min / max Execution time / Wait time and number of executions for a given program for a given date range
select c.CONCURRENT_PROGRAM_ID,
q.concurrent_queue_name qname,
c.concurrent_program_name||' - '||
ctl.user_concurrent_program_name "Program"
,to_char(min(actual_start_date),'mm/dd/rr') earlieststart
,sum(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(tot)"
,avg(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(avg)"
,min(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(min)"
,max(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(max)"
,count(*) "Jobs"
,ls.meaning lsm
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_concurrent_programs_tl ctl,
APPLSYS.fnd_lookup_values ls,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q
where c.concurrent_program_id = &cmprogid
and a.concurrent_program_id = c.concurrent_program_id
and a.controlling_manager = b.concurrent_process_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and ctl.language = 'US'
and a.program_application_id = c.application_id
and ctl.application_id = c.application_id
and ls.lookup_type = 'CP_STATUS_CODE'
and ls.language = 'US'
and ls.enabled_flag = 'Y'
and (ls.start_date_active <= sysdate and ls.start_date_active is not null)
and (ls.end_date_active > sysdate or ls.end_date_active is null)
and a.status_code || '' = ls.lookup_code
and a.phase_code || '' = 'C'
group by c.CONCURRENT_PROGRAM_ID ,q.concurrent_queue_name, c.concurrent_program_name
,ctl.user_concurrent_program_name
,ls.meaning
order by 4 desc
e. To get the input the Concurrent request number and if it is running then give the Oracle Sid, serial# , unix process id, along with program or module it is linked with
SELECT d.sid, d.serial# ,d.process , c.SPID, fcpv.user_concurrent_program_name, a.REQUEST_ID, fa.APPLICATION_NAME,
d.osuser, replace(d.machine,'HEADLANDS\',null) user_srvr, a.REQUESTED_START_DATE
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d,
fnd_concurrent_programs_vl fcpv,
fnd_application_tl fa
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R'
AND fcpv.concurrent_program_id = a.CONCURRENT_PROGRAM_ID
AND a.RESPONSIBILITY_APPLICATION_ID = fa.APPLICATION_ID
f. List all programs that can be run by a given user
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application,
FCPT.USER_CONCURRENT_PROGRAM_NAME
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE g.user_id(+) = u.user_id
AND u.USER_NAME = '&USER_NAME'
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND FCP.APPLICATION_ID = A.APPLICATION_ID
AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
ORDER BY responsiblity, application, FCPT.USER_CONCURRENT_PROGRAM_NAME
g. List all programs that are defined as incompatible for a given program
SELECT FAL.APPLICATION_NAME INCOMPATIBLE_APPLICATION , FCPT.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_PROGRAM
FROM FND_CONCURRENT_PROGRAM_SERIAL FCPS,
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION_TL FAL,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPS.TO_RUN_APPLICATION_ID = FCP.APPLICATION_ID
AND FCPS.TO_RUN_CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCPS.RUNNING_CONCURRENT_PROGRAM_ID IN (SELECT FCPT.CONCURRENT_PROGRAM_ID FROM FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPT.USER_CONCURRENT_PROGRAM_NAME = :USER_CONCURRENT_PROGRAM_NAME)
AND FAL.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FCPS.TO_RUN_CONCURRENT_PROGRAM_ID
h. Given a program name in which manager it will run
select ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
from Fnd_Concurrent_Requests t,
FND_CONCURRENT_PROCESSES k,
Fnd_Concurrent_Queues_TL QTL,
Fnd_Concurrent_Programs_TL PTL
where k.concurrent_process_id = t.controlling_manager
and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
and ptl.concurrent_program_id=t.concurrent_program_id
and qtl.language='US'
and PTL.USER_CONCURRENT_PROGRAM_NAME = :CONCURRENT_PROGRAM_NAME
ORDER BY ptl.user_concurrent_program_name DESC
a. To list all running programs
SELECT FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') PHASE
,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') STATUS
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV('Lang')
AND FCR.PHASE_CODE = 'R'
ORDER BY FCR.ACTUAL_START_DATE DESC
b. To list all pending programs
SELECT FCR.REQUEST_ID REQUEST_ID
,FCPT.USER_CONCURRENT_PROGRAM_NAME REQUEST_NAME
,FCR.ACTUAL_START_DATE START_DATE
,DECODE(FCR.PHASE_CODE, 'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running') PHASE
,DECODE(FCR.STATUS_CODE, 'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') STATUS
,FU.USER_NAME REQUESTED_BY
FROM FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_REQUESTS FCR,
FND_USER FU
WHERE --TRUNC(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)/(1/24))*60) > NVL('&MIN',45)
FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID = FCPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCPT.APPLICATION_ID
AND FU.USER_ID = FCR.REQUESTED_BY
AND FCPT.LANGUAGE = USERENV('Lang')
AND FCR.PHASE_CODE = 'P'
ORDER BY FCR.ACTUAL_START_DATE DESC
c. To list all programs that are scheduled and yet to run
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
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, cr.PHASE_CODE, cr.STATUS_CODE
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 trunc(cr.requested_start_date) >= trunc(SYSDATE)
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id
AND cr.release_class_id is not null
order by name, requested_start_date;
d. To list avg / min / max Execution time / Wait time and number of executions for a given program for a given date range
select c.CONCURRENT_PROGRAM_ID,
q.concurrent_queue_name qname,
c.concurrent_program_name||' - '||
ctl.user_concurrent_program_name "Program"
,to_char(min(actual_start_date),'mm/dd/rr') earlieststart
,sum(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(tot)"
,avg(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(avg)"
,min(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(min)"
,max(nvl(actual_completion_date-actual_start_date,0))*1440 "Time(max)"
,count(*) "Jobs"
,ls.meaning lsm
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_programs c,
APPLSYS.fnd_concurrent_programs_tl ctl,
APPLSYS.fnd_lookup_values ls,
APPLSYS.fnd_concurrent_processes b,
applsys.fnd_concurrent_queues q
where c.concurrent_program_id = &cmprogid
and a.concurrent_program_id = c.concurrent_program_id
and a.controlling_manager = b.concurrent_process_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and ctl.language = 'US'
and a.program_application_id = c.application_id
and ctl.application_id = c.application_id
and ls.lookup_type = 'CP_STATUS_CODE'
and ls.language = 'US'
and ls.enabled_flag = 'Y'
and (ls.start_date_active <= sysdate and ls.start_date_active is not null)
and (ls.end_date_active > sysdate or ls.end_date_active is null)
and a.status_code || '' = ls.lookup_code
and a.phase_code || '' = 'C'
group by c.CONCURRENT_PROGRAM_ID ,q.concurrent_queue_name, c.concurrent_program_name
,ctl.user_concurrent_program_name
,ls.meaning
order by 4 desc
e. To get the input the Concurrent request number and if it is running then give the Oracle Sid, serial# , unix process id, along with program or module it is linked with
SELECT d.sid, d.serial# ,d.process , c.SPID, fcpv.user_concurrent_program_name, a.REQUEST_ID, fa.APPLICATION_NAME,
d.osuser, replace(d.machine,'HEADLANDS\',null) user_srvr, a.REQUESTED_START_DATE
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d,
fnd_concurrent_programs_vl fcpv,
fnd_application_tl fa
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R'
AND fcpv.concurrent_program_id = a.CONCURRENT_PROGRAM_ID
AND a.RESPONSIBILITY_APPLICATION_ID = fa.APPLICATION_ID
f. List all programs that can be run by a given user
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application,
FCPT.USER_CONCURRENT_PROGRAM_NAME
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r,
FND_CONCURRENT_PROGRAMS FCP,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE g.user_id(+) = u.user_id
AND u.USER_NAME = '&USER_NAME'
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
AND FCP.APPLICATION_ID = A.APPLICATION_ID
AND FCP.APPLICATION_ID = FCPT.APPLICATION_ID
ORDER BY responsiblity, application, FCPT.USER_CONCURRENT_PROGRAM_NAME
g. List all programs that are defined as incompatible for a given program
SELECT FAL.APPLICATION_NAME INCOMPATIBLE_APPLICATION , FCPT.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_PROGRAM
FROM FND_CONCURRENT_PROGRAM_SERIAL FCPS,
FND_CONCURRENT_PROGRAMS FCP,
FND_APPLICATION_TL FAL,
FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPS.TO_RUN_APPLICATION_ID = FCP.APPLICATION_ID
AND FCPS.TO_RUN_CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCPS.RUNNING_CONCURRENT_PROGRAM_ID IN (SELECT FCPT.CONCURRENT_PROGRAM_ID FROM FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FCPT.USER_CONCURRENT_PROGRAM_NAME = :USER_CONCURRENT_PROGRAM_NAME)
AND FAL.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.APPLICATION_ID = FCPS.TO_RUN_APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FCPS.TO_RUN_CONCURRENT_PROGRAM_ID
h. Given a program name in which manager it will run
select ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
from Fnd_Concurrent_Requests t,
FND_CONCURRENT_PROCESSES k,
Fnd_Concurrent_Queues_TL QTL,
Fnd_Concurrent_Programs_TL PTL
where k.concurrent_process_id = t.controlling_manager
and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
and ptl.concurrent_program_id=t.concurrent_program_id
and qtl.language='US'
and PTL.USER_CONCURRENT_PROGRAM_NAME = :CONCURRENT_PROGRAM_NAME
ORDER BY ptl.user_concurrent_program_name DESC
Concurrent Requests Average/Max/Min hours runtime details
Unknown on
Concurrent Requests Average/Max/Min hours runtime details
set linesize 200
col username for a10
col status for a10
col phase for a10
col PNAME for a70
col request_id for 99999999999
col PNAME for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;
set linesize 200
col username for a10
col status for a10
col phase for a10
col PNAME for a70
col request_id for 99999999999
col PNAME for a40
select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
avg((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) avg_Hrs_running,
max((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;
