Script to Find High Impact Concurrent Programs

Sql Script to find High Impact concurrent Programs

#Script to find High Impact Concurrent Programs in html format
set linesize 4000
set verify off
set feedback off
set pagesize 999
set markup html on entmap on spool on preformat off
spool Highimpactconcurrentprograms.html

PROMPT High Impact Concurrent Programs

select substr(cname.user_concurrent_program_name,1,80) "prog name",
exec_date,creq.occur,creq.max_time,creq.min_time,creq.avg_time
from (select
concurrent_program_id,trunc(actual_start_date) exec_date,
count(concurrent_program_id) occur,
round(min(actual_completion_date-actual_start_date)*24*60,2) min_time,
round(max(actual_completion_date-actual_start_date)*24*60,2) max_time,
round(avg(actual_completion_date-actual_start_date)*24*60,2) avg_time
from apps.fnd_concurrent_requests
where status_code='C'
and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id ,trunc(actual_start_date)
having max(actual_completion_date-actual_start_date)*24*60 >30) creq
,apps.fnd_concurrent_programs_tl cname
where creq.concurrent_program_id=cname.concurrent_program_id
and cname.language='US'
order by max_time,occur;

set markup html off entmap off spool off preformat on
set linesize 2000 verify on feedback on
spool off

Adding a New Language(s) to the Oracle Applications

Most often when Customers want to add a New Language, the DBA team goes through the process of searching tons of their Emails, Tickets, CSR's for all patches's that need to be applied to keep in synch with US Language. Most often this list can make it messy and go uncontrolled.

This is the simple way to add a new Language(s) to the Oracle Applications provided by Oracle.

Verify Prerequisites

Release 11i

Verify AD Patchset Level : You must be at least on AD mini-pack I (11i.AD.I.2)

Apply Update : If you are not on 11i.AD.I.6 or later, apply update 5837664. This update delivers the latest tool for generating manifest file.

Prepare the Applications Instance for New Language(s): If you are planning to request Translation Synchronization patch for a language which has not yet been activated in your instance, you need to do the following:
· Activate new language(s) using the Oracle Applications Manager (OAM) License Manager.
· Run the AD Administration utility to maintain multilingual tables from the Maintain Applications Database Entities menu.

Maintain Snapshot Information : Recommended

Release 12:

Verify AD Patchset Level: If you are not on R12.AD.A.1 or later, apply update 5635729.
Prepare the Applications Instance for New Language(s): See Installing Translations in Oracle Applications NLS Release Notes, Release 12.0.4.

Requesting Translation Synchronization Patch

Generate and Upload the Manifest of Customer Specific Files :

1. Log on to your Applications' administration node as the APPLMGR user

2. Source the APPL_TOP environment file.

3. Use the manifest generation tool to generate customer specific manifest file by executing the following command:
perl $AD_TOP/bin/adgennls.pl

4. The ouput generated from the manifest tool is in the format :
$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt

5. The manifest contains all of your active languages. You do not need to run the tool for each individual language.

Once the manifest is generated, you can go to http://updates.oracle.com/TransSync & Follow the instructions on the screen to upload the manifest file.

A Translation synchronization patch will be generated specifically based on your manifest for each of your active languages.The patch will synchronize the language file versions and American English file versions in your Applications instance.

If you select the option to get latest translations, the patch will also bring your translations up-to-date. If your current translations are already up-to-date and in sync with your American English file versions, you will be notified by e-mail that there is no need to apply Translation Synchronization patch.

Applying Translation Synchronization Patch

Download and Apply the Customer Specific NLS Patch

1. Download it from OracleMetaLink. Follow the instructions in the README file to apply the patch.

2. A Merged Translation patch may be generated if full translations are not available.

3. You can merge multiple language Translation Synchronization Patches and apply the single merged patch to your system.

Post Install Steps:

If you apply Translation Synchronization patch to add a new language, you need to run the AD Administration utility to generate message files from the Generate Applications Files menu.

Reference : Metalink Doc: 252422.1

--Srinivas Ramineni
Oracle Apps DBA

To Find ICM log file path

Smal Script to Find ICM log file path

SELECT 'ICM_LOG_NAME=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';


ICM logfile: Default is std.mgr, can be changed with the mgrname

Startup Parameter
Concurrent manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr

--
Srinivas Ramineni
Oracle Apps DBA

Concurrent Manager Profile Values

CONCURRENT MANAGER PROFILE OPTIONS
----------------------------------

1. Concurrent: Active Request Limit

Value: Numeric

This profile options will restrict the number of concurrent requests that may be run simultaneously by each user. If you do not specify a limit, then no limit is imposed.

2. Concurrent: Attach URL

Values: YES/NO

If you set this option to YES, then this causes a URL to be attached to request completion notifications. For example, when a user submits a request and uses the 'Defining Completion Options' region to specify people to be notified, a notification is sent each of the people designated.

3. Concurrent: Collect Request Statistics

Values: YES/NO

If you set this option to YES, this causes applications to collect statistics on your run-time concurrent processes.

4. Concurrent: Conflicts Domain

Values: LOV (list of values: domains)

This option identifies the domain within which all the incompatibilities between programs has to be resolved

5. Concurrent: Debug Flags

This option is used to debug the transactions managers.You should only use this option at the request of Oracle Support.

6. Concurrent: Directory for Copy

You can identify a directory on your operating system to store copies of your report output or log files. This directory is used when a copy operation is requested in character mode of the applications.

7. Concurrent: Enable Request Submission in View Mode

Values: YES/NO

Setting this option to YES will enable the 'Submit a New Request' button
when users invoke the form FNDRSRUN (Find Requests Form). Navigation path is either: (1) Menu: Help > View My Requests, or (2) navigate (with system administrator responsibility) to, Requests > View.

8. Concurrent: Hold Requests

Values: YES/NO

This option enables you to automatically place requests on hold after submission.

9. Concurrent: Multiple Time Zones

Values: YES/NO

When the client's session and the concurrent manager are running in different times zones, use this option to ensure that the request is scheduled immediately regardless of you client session's time zone.

10. Concurrent: PMON Method

This option is presented for documentation purposes only. Users cannot see or alter this profile option. This option is not visible or cannot be updated from the System Profile Option (FNDPOMPV) form. The PMON method refers to the process monitor. The Internal Concurrent Manager (ICM) monitors the individual
concurrent managers' processes to verify the managers are running. Normally, the PMON method must be set to LOCK. To change the profile option setting, you must execute the SQL script "afimpmon.sql" which resides in directory"$FND_TOP/sql/".

11. Concurrent: Report Access Level
Values: RESPONSIBILITY/USER

This option determines access privileges to report output files and log files generated by a concurrent program. This option can beset by a system administrator or by the user.

12. Concurrent: Report Copies

Values: Numeric

This option determines the number of default copies that print for each submitted concurrent request.

13. Concurrent: Request Priority

Values: Numeric

Concurrent requests in Oracle Applications are queued. Requests normally run according to a start time on a first submitted, first run basis. Priority overrides this request start time. A higher priority request starts before an earlier request. This option displays the default priority for you concurrentrequests. Only a system administrator can change your request priority.

14. Concurrent: Request Start Time

Values: Numeric (Date/Time)

With this profile option, you can set the date and time that your requests are available to start running

* If the start time is at or before the current date and time, requests are available to run immediately

* Start a request in the future, for example, at 6:00 PM on December 31, 2000, enter the following value 31-DEC-2000 18:00

* This profile option requires the date and time,
for example, 31-DEC-2000 18:00

* Changing values does not affect request already submitted

15. Concurrent: Save Output

Values: YES/NO

--

Srinivas Ramineni

Oracle Apps DBA

ConCurrent Manager FAQ

Concurrent Manager FAQs
Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
Q: Where do the concurrent request output and log files reside?
Q: Where are temporary files located?
Q: How to change PMON method to LOCK.
Q: How to start/shutdown the CCM on Unix as APPS
Q: How to terminate a concurrent request that cannot be cancelled.
Q: A Manager is not activating.
Q: Internal Concurrent Manager functionality.
Q: How to purge requests that are in Pending status.

1. Q: 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

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

3. 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. From 11i this profile Option set at O.S level

4. Q: How to start and shutdown the CCM on Unix as APPS
A: To Start:

STARTMGR sysmgr=APPS/passwd

To Shutdown:

concsub apps/ 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

5. 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 = request number)

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


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


8. 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'

--
Srinivas Ramineni
Oracle Apps DBA