TUNING CONCURRENT MANAGER

There are 5 ways to faster your CM

1.PMON cycle, queue size, and sleep time.
2.Purging Concurrent Requests
3.Adjusting the Concurrent Manager Cache Size
4.Analyzing Oracle Apps Dictionary Tables for High Performance
5. Number of Standard Managers
------------------------------------------------------------------------------------------
1.PMON cycle, queue size, and sleep time.

The ICM performance is affected by the three important Oracle parameters

PMON cycle
This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, are having problems with abnormal terminations.

Queue Size
The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.

Sleep Time
The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.

2.Purging Concurrent Requests
One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager.When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called “Purge Concurrent Requests And/Or Manager Data” should be scheduled to run on a regular basis.This request can be configured to purge the request data from the FND tables as well as the
log files and output files on accumulate on disk.

3.Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size
to be at lease twice the number of target processes. The cache size specifies the number of
requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of
the managers by attempting to avoid sleep time.

4.Analyzing Oracle Apps Dictionary Tables for High Performance
We can generate statistics that quantify the data distribution and storage characteristics of tables, columns,indexes, and partitions. The cost-based optimization approach uses these statistics to calculate the selectivity of predicates and to estimate the cost of each execution plan. Selectivity is the fraction of rows in a table that the SQL statement’s predicate chooses. The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method. We should gather statistics periodically for objects where the statistics become stale over time because of changing data volumes or changes in column values. New statistics should be gathered after a schema object’s data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows, but you might need new statistics on the average row length.It is also very important to run the request Gather Table Statistics on these tables:

FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.

5. Number of Standard Managers
Some of environments are copies of other environments, and you may find that the number of Standard Concurrent Managers are just 5. You can increase this to say 10 or 15, this will help the pending requests queue is not getting too long & the Conflict Resolution Manager will have less load.

Performance Problems - Concurrent Programs

You’ve noticed that a particular program is taking, on average, two hours to run. You can
turn trace on for this report by logging in with the System Administrator responsibility,
and navigating to
Concurrent – Program – Define - Query up your report. Click on the “Enable Trace” button - Turn On Tracing

Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes

Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)

Select the Checkbox for SQL Trace
Click Debug Options

TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using
TKPROF.
$tkprof raw_trace_file.trc output_file explain=apps/apps
sort=(exeela,fchela) sys=no
Where:
raw_trace_file.trc: Name of trace file
output_file:
tkprof out file
explain:
This option provides the explain plan for the sql
statements
sort:
This provides the sort criteria in which all sql
statements will be sorted. This will bring the bad
sql at
the top of the outputfile.
sys=no:
Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and
limits the results to the "Top 10" long running queries

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password
> sort='(prsela,exeela,fchela)' print=10
Now every time this report runs, until you turn trace off, it will generate a trace file.
We strongly recommend that you trace Concurrent Programs on your test
environment rather than your production environment, because anytime anyone
runs this report, it will generate a trace file. At the very least, if you run trace on your
production environment, don’t forget to turn it back off after you’re done!
cd $ORACLE_HOME/admin/<CONTEXT_NAME>/udump
run tkprof
tkprof filename.trc filename.prf explain=apps/<apps_pwd>
sys=no
sort='(prsela,exeela,fchela)' print=10
or
To run Trace Analyzer:
sqlplus apps/<apps_pwd>
SQL> START TRCANLZR.sql UDUMP vis_ora_1234.trc;
More options:
1. Login to Oracle Applications and select System Administrator responsibility.
2. Navigate to the Concurrent Program definition form.
Concurrent -> Program -> Define
3. Query the concurrent program ARBRFRRT
4. Enter your trace Options in the Options Field of the Executable Section using
the below format and save the changes:
TRACEOPTS=<option>TRACEFILE=<filename>
TRACEMODE=<TRACE_APPEND | TRACE_REPLACE>
Example :
TRACEOPTS=TRACE_ALL TRACEFILE=c:\Temp\SMtrace.trc
Note : TRACEOPTS can only be used for Oracle Reports type concurrent
programs as it is an execution option to an Oracle Reports R
untime executable
It will help if you can also enable Binds for the purposes of this SR.
If you wish you can enable binds, waits, and additional tracing options by
entering your Trace Options in the Options Field of the Executable Section.
List of values that can be used for TRACEOPTS :
Option Description
TRACE_ERR list error messages and warnings
TRACE_TMS enter a timestamp for each entry
TRACE_SQL log trace information on all of the SQL
TRACE_PRF log performance statistics
TRACE_PLS log trace information on all PL/SQL objects
TRACE_DST creates a log containing the distribution lists involved. Available
from Reports 6.0 and up.
TRACE_APP log trace information on all of the report objects
TRACE_ALL log all possible trace information

Reference: How To Trace a Concurrent Request And Generate TKPROF File -
453527.1

Performance Problems – Forms

When an end user says that whenever they use a certain form a certain way, they have a performance problems
Have the user show you exactly which form runs slow.
Have the user select Help | Diagnostics | Trace and click in the “Trace With Binds
and Waits” box right before they do the part of the transaction that runs slow.

When the trace first starts, a pop-up screen will tell you where to look for the trace
file and what it is called

Next, have the user do the slow part of the transaction. Then have the user select
Help | Diagnostics | Trace and turn trace off by clicking on “No Trace.”

Go to the below directory and check whether trace file is created
cd $ORACLE_HOME/admin/<$CONTEXT_NAME>/udump.

Now you can run either tkprof or Trace Analyzer, using the command:
tkprof filename.trc filename.prf explain=apps/<apps_pwd> sort=prsela,exeela,fchela

To run Trace Analyzer:
sqlplus apps/<apps_pwd>
SQL> START TRCANLZR.sql UDUMP test_ora_12345.trc;

Important Queries to Troubleshoot concurrent request / manager issues

-- Given the concurrent program name, get the program run times
-- Failed / Error concurrent requests.. remove the comments if we want the time frame and concurrent program wise
-- Concurrent requests running for more than an hour as per sysdate


SELECT
fcr.request_id request_id,fcr.parent_request_id,
NVL (fu.description, fu.user_name) requested_by,
DECODE (fcp.concurrent_program_name,'FNDRSSUB', 'Request Set - ' || fcr.description, fcpt.user_concurrent_program_name) concurrent_program,
DECODE (fcr.phase_code,
'I', 'Inactive',
'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 'Pending'),
'R', 'Running',
'C', 'Complete',
fcr.phase_code)
phase,
DECODE (
fcr.status_code,
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', CASE
WHEN fcr.request_date < fcr.requested_start_date
THEN
'Scheduled'
ELSE
'Normal'
END,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
fcr.status_code)
status,
fcr.argument_text,
TO_CHAR (fcr.request_date, 'DD-Mon-RRRR HH24:MI:SS') date_requested,
TO_CHAR (fcr.requested_start_date, 'DD-Mon-RRRR HH24:MI:SS')
requested_start_date,
TO_CHAR (fcr.actual_start_date, 'DD-Mon-RRRR HH24:MI:SS')
date_started,
TO_CHAR (fcr.actual_completion_date, 'DD-Mon-RRRR HH24:MI:SS')
date_completed,
(fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 AS actual_Run_Time_Secs,
(fcr.actual_completion_date-fcr.request_date)*24*60*60 AS end_to_end_Secs,
(fcr.actual_start_date-fcr.request_date)*24*60*60 AS lag_time_secs,
fcr.oracle_process_id,
fcr.os_process_id,
fcr.pp_start_date AS OPP_Process_start_time,
fcr.pp_end_date AS OPP_Process_end_time,
fcr.logfile_node_name || ': ' || fcr.logfile_name logfile_name,
fcr.outfile_node_name || ': ' || fcr.outfile_name output_name,
fcr.completion_text
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcpt.language = 'US'
--AND trunc(fcr.last_update_date) >= trunc(SYSDATE-3)
--AND trunc(fcr.last_update_date) = '01-Jan-2023'
--AND fcpt.user_concurrent_program_name like '%<Concurrent Program Name>%' /* Given the concurrent program name, get the program run times as per sysdate */
--AND (fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 > 3600 /* Concurrent requests running for more than an hour as per sysdate */
--AND (fcr.pp_end_date - fcr.pp_start_date)*24*60*60 > 1800 /* Concurrent requests spending more than an hour in OPP */
--AND fcr.status_code = 'E' /* Failed / Error concurrent requests as per sysdate */
--AND fcr.request_id='<request_id>'
--AND NVL (fu.description, fu.user_name)='XXXXXXX'
ORDER BY 1 desc

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


To check Scheduled Concurrent Programs and Request Sets details

SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%XXXXXXXX%'
----------------------------------------------------------------------------------------------

To check the duplicated schedules of the same program with the same arguments

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

To check average pending time per request:


Please Note: 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


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

To check which manager is going to execute a program:

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


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


To check the incompatibilities between the programs:


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'

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

To Find The Concurrent Request Based On SQLID 

SELECT fcr.request_id,
fcp.user_concurrent_program_name,
vsq.sql_text,
vsq.SQL_ID
FROM fnd_concurrent_requests fcr,
v$process vp,
v$session vs,
v$sql vsq,
fnd_concurrent_programs_vl fcp
WHERE 1=1
AND vsq.SQL_ID = '&sqlid'
AND fcr.oracle_process_id = vp.spid
AND vs.sql_address = vsq.address(+)
AND vs.paddr = vp.addr
AND fcr.concurrent_program_id = fcp.concurrent_program_id;

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

To find All The Programs Assigned to the Manager Specialization Rules

select fcqv.user_concurrent_queue_name,

decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",

'Program' "Rule Type",

tab.user_concurrent_program_name "Entity"

from fnd_concurrent_queues_vl fcqv,

fnd_concurrent_queue_content fcqc,

fnd_concurrent_programs_vl tab

where fcqc.type_code = 'P'

and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id

and fcqc.type_id=tab.concurrent_program_id

and fcqc.type_application_id = tab.application_id

UNION

select fcqv.user_concurrent_queue_name,

decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",

'Combined Rule' "Rule Type",

tab.complex_rule_name "Entity"

from fnd_concurrent_queues_vl fcqv,

fnd_concurrent_queue_content fcqc,

fnd_concurrent_complex_rules tab

where fcqc.type_code = 'C'

and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id

and fcqc.type_id=tab.complex_rule_id

and fcqc.type_application_id = tab.application_id

UNION

select fcqv.user_concurrent_queue_name,

decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",

'Oracle ID' "Rule Type",

tab.oracle_username "Entity"

from fnd_concurrent_queues_vl fcqv,

fnd_concurrent_queue_content fcqc,

fnd_oracle_userid tab

where fcqc.type_code = 'O'

and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id

and fcqc.type_id=tab.oracle_id

UNION

select fcqv.user_concurrent_queue_name,

decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",

'User' "Rule Type",

tab.user_name "Entity"

from fnd_concurrent_queues_vl fcqv,

fnd_concurrent_queue_content fcqc,

fnd_user tab

where fcqc.type_code = 'U'

and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id

and fcqc.type_id=tab.user_id

UNION

select fcqv.user_concurrent_queue_name,

decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",

'Request Type' "Rule Type",

tab.request_class_name "Entity"

from fnd_concurrent_queues_vl fcqv,

fnd_concurrent_queue_content fcqc,

FND_CONCURRENT_REQUEST_CLASS tab

where fcqc.type_code = 'R'

and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id

and fcqc.type_id=tab.request_class_id

and fcqc.type_application_id = tab.application_id;

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

-- Concurrent program Reponsibility group 

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",

 DECODE(rgu.request_unit_type,

 'P', 'Program',

 'S', 'Set',

 rgu.request_unit_type) "Unit Type",

 cp.concurrent_program_name "Concurrent Program Short Name",

 rg.application_id "Application ID",

 rg.request_group_name "Request Group Name",

 fat.application_name "Application Name",

 fa.application_short_name "Application Short Name",

 fa.basepath "Basepath",

 rg.*

 FROM apps.fnd_request_groups rg,

 apps.fnd_request_group_units rgu,

 apps.fnd_concurrent_programs cp,

 apps.fnd_concurrent_programs_tl cpt,

 apps.fnd_application fa,

 apps.fnd_application_tl fat

WHERE rg.request_group_id = rgu.request_group_id

 AND rgu.request_unit_id = cp.concurrent_program_id

 AND cp.concurrent_program_id = cpt.concurrent_program_id

 AND rg.application_id = fat.application_id

 AND fa.application_id = fat.application_id

 --and fa.application_id =20083

 AND cpt.language = USERENV('LANG')

 AND fat.language = USERENV('LANG')

 AND cpt.user_concurrent_program_name like '%Diag%Apps%'

 --and rg.request_group_name='XXXXXXX'

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

-- Find the concurrent program name from the short name or vice versa

SELECT fcpl.user_concurrent_program_name "Concurrent Program Name",

       fcp.concurrent_program_name       "Short Name",

       fa.application_short_name         "Module",

       fe.executable_name                "Executable",

       fe.execution_file_name            "Executable Short Name",

       DECODE (fcp.execution_method_code,

               'X', 'FlexRpt',

               'F', 'FlexSql',

               'H', 'Host',

               'S', 'Immediate',

               'K', 'Java Concurrent Program',

               'J', 'Java Stored Procedure',

               'M', 'Multi Language Function',

               'P', 'Oracle Reports',

               'I', 'PL/SQL Stored Procedure',

               'E', 'Perl Concurrent Program',

               'B', 'Request Set Stage Function',

               'L', 'SQL*Loader',

               'Q', 'SQL*Plus',

               'R', 'SQL*Report',

               'Z', 'Shutdown Callback',

               'A', 'Spawned')           "Execution Method",

       fcpl.description                  "Program Description",

       fdfcuv.column_seq_num             "Column Seq Number",

       fdfcuv.end_user_column_name       "Parameter Name",

       fdfcuv.form_left_prompt           "Prompt",

       fdfcuv.enabled_flag               " Enabled Flag",

       fdfcuv.required_flag              "Required Flag",

       fdfcuv.display_flag               "Display Flag",

       ffvs.flex_value_set_name          "Value Set Name"

  FROM fnd_executables              fe,

       fnd_concurrent_programs_tl   fcpl,

       fnd_application              fa,

       fnd_concurrent_programs      fcp,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE     1 = 1

       AND fe.executable_id = fcp.executable_id

       AND fcpl.application_id = fa.application_id

       AND fcpl.concurrent_program_id = fcp.concurrent_program_id -- -- change Concurrent program name here.. 

       --AND user_concurrent_program_name = 'XXXXXX'  -- change Concurrent program name here.. 

       AND fcp.concurrent_program_name='XXXXXXX'

       AND fdfcuv.descriptive_flexfield_name =

           '$SRS$.' || fcp.concurrent_program_name

       AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

       AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'

       AND flv.lookup_code(+) = fdfcuv.default_type

       AND fcpl.LANGUAGE = 'US'

       AND flv.LANGUAGE(+) = USERENV ('LANG')

       ORDER BY fdfcuv.column_seq_num;

Unable To Login To Oracle Applications After Upgrading from 11.5.10.2 to R12 Or After Cloning

After the process of upgrading from 11.5.10.2 to R12 through executing the "Finish the Upgrade" , got the error : 

"Login Page" failed during the "Validate System Configuration". 

When t

rying to login via http://.:webport/OA_HTML/AppsLogin got :

"The page cannot be displayed" error message

ERROR
"login page" failed
.

Cause

The problem is caused by incomplete or malformed configuration files during the upgrade.

Solution

To implement the solution, please execute the following steps:

1. Cleanup the malformed configuration/setup files by running:

EXEC FND_CONC_CLONE.SETUP_CLEAN;
commit;
exit

2. Regenerate all configuration files by running autoconfig.

3. Delete all files under $COMMON_TOP/_pages.

4. Recompile the jsp's by running :

$FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

5. Start the Apache service.

Enabling the Apache, OC4J and OPMN logging in Oracle Applications R12

1. Oracle Applications R12 logging essentials:

Oracle Applications / E-Business Suite R12 introduces new easy way in order to enable logging for any of the components that are being used within it. Also it uses new directory structures for Oracle Home(s) either for the Database Tier or the Middle Tier.
Since this note is discussing the logging of middle tier services (Apache, OC4J & OPMN), let us give a spotlight at the following essential directory structures for the above middle tier services and its logging information.
    - Oracle Configuration Home for Apache, OC4J & OPMN

($ORA_CONFIG_HOME/10.1.3)

    - Apache server is still being used and its configuration path is as following:

$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf

    - OC4J services , by default Oracle Applications R12 creates 3 OC4J instances:

• OACore: runs OA Framework-based applications
• Forms: runs Forms-base applications
• OAFM (Oracle Apps Fusion Middleware): runs web services, mapviewer, ascontrol

Configuration files path for the above 3 OC4J instances is:

$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/config

Configuration file used to adjust log file path for the above 3 OC4J instances is:

$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/application-deployments/<oacore, forms, oafm>/orion-application.xml

2. Log files types:

Oracle Applications / E-Business Suite R12 can be configured to generate one of two types of logs files as following:
    a- Plain text (default):         - No log rotation available
         - Log files need to be manually removed
    b- Oracle Diagnostics Logging (ODL) XML:
         - Logs rotation based on filesize, directory size
    c- R12 logs by default are configured to generate plain text

3. Log files locations:

 All log files are written in one home ( $LOG_HOME ) which is by default the (logs) directory under ( $INST_TOP ).

Please note that after enbaling the logging for any of the following services you have to bounce the services in order the changes to take effect.

4. Steps to enable Apache logging:

     4.1. Apache Plain Text Logs
             a- By default the Apache log level is set to (warn) in

$ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf
LogLevel warn

Also you can update context variable (s_apache_loglevel) in the context file ($INST_TOP/appl/admin/<SID_HOSTNAME.xml>) to set Apache log level and then run autoconfig.

             b- You can select the desired LogLevel according to the following table:

Log Level	Description
emerg	Emergencies, system is not useable
alert	Action must be taken
crit	Critical conditions
error	Error conditions
warn	Warning conditions
notice	Normal but significant condition
info	Information
debug	Debug level messages

             c- To enable debug set ( LogLevel  debug ) in (httpd.conf) file
             d- Apache log files will be generated in the following path:

$LOG_HOME/ora/10.1.3/Apache

             e- There are 2 distinct types of log files are created:
                  - Access Log (CustomLog)
                    Filename format should be like: access_log.<unique id>
                  - Error Log (ErrorLog) this includes ECID information
                     Filename format should be like: error_log.<unique id>
      4.2. Apache ODL Logs

Important Note: Please make sure that you have a directory called "oracle" under $LOG_HOME/ora/10.1.3/Apache/ before enabling the logging and if not please create it first.

             a- To enable ODL logs you should set the following parameters in (httpd.conf) file        

OraLogMode <value>
OraLogSeverity <message type>:<message level>

             b- Set (OraLogMode) to (oracle), there are 3 options as per following table:

Value	Description
oracle	Multi-line log records in XML format, provides the most information
odl	Standard apache log format and ECID info. Default setting in R12
apache	Standard apache log format

             c- Set (OraLogSeverity) according to the desired log level using following syntax:

OraLogSeverity <message type>:<message level>
Message type: INTERNAL_ERROR, ERROR, WARNING, NOTIFICATION & TRACE
Message level: 1-32 (1 most severe, 32 least)

             d- Following table shows (Oracle Message Type) that can be used to set log level:

Apache Log Level	Oracle Message type
emerg	INTERNAL_ERROR:16
alert	INTERNAL_ERROR:32
crit	ERROR:16
error	ERROR:32
warn	WARNING:32
notice	NOTIFICATION:16
info	NOTIFICATION:32
debug	TRACE:32

   
             e- Apache ODL log files will be generated in the following path:

$LOG_HOME/ora/10.1.3/Apache/oracle

5. Steps to enable OC4J logging:

     a- Each OC4J instance has a separate logging configuration file:

$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/config/j2ee-logging.xml

      b- By default the log level is set to something like:

<logger name='oracle' level='NOTIFICATION:1'...

     c- You can set level to the desired value as using following syntax:

<message type>:<message level>
Message type: INTERNAL_ERROR, ERROR, WARNING, NOTIFICATION & TRACE
Message level: 1-32 (1 most severe, 32 least)

     d- Each OC4J instance has file:

$ORA_CONFIG_HOME/10.1.3/j2ee/<oacore, forms, oafm>/application-deployments/<oacore,forms,oafm>/orion-application.xml

         Which contains tag like: <log> <file path=...> </log> to specify the log file name.
      e- Each OC4J has its own log file in the following path:
          - Plain Text Log:

$LOG_HOME/ora/10.1.3/j2ee/<oacore, forms, oafm>/<oacore,forms,oafm>_<default_group_1>/application.log

          - ODL Log:

$LOG_HOME/ora/10.1.3/j2ee/<oacore, forms, oafm>/<oacore,forms,oafm>_<default_group_1>/log.xml

6. Steps to enable OPMN logging:

     a- OPMN log are generated in ( $LOG_HOME/ora/10.1.3/opmn).
     b- There are 3 log files generated:
          - (opmn.log): contains commands, exit status
          - (opmn.dbg): contains debug log messages
          - (opmn.out): contains stdout & stderr messages
               - Stdout messages are any messages written by application by System.out.println
               - Sterr messages are error messages recorded when opmn tries to start the services
     b- The above log files are plain text files.
     c- Rotation is enabled for the above log files based on file size only defined in:     

$ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

through parameters: ( s_opmn_log_rotation_size, s_opmn_log_rotation_time )

     d- Logging is enabled per component (internal, ons or pm)
     e- Levels that can be set are (component codes) as following:

none, fatal, error, warn, notify          (written to .log)
debug1, debug2, debug3, debug4    (written to .dbg)

     f- As an example, Set level for a running opmn as one of the following:

opmnctl set target=log comp=warn     
opmnctl set target=debug comp=debug1

7. Components (Services) Status & Logs:

     a- You can check the status of the services/components by passing 'status' parameter to any AD script
          in $ADMIN_SCRIPTS_HOME.

[oracle@mzlinux04 scripts]$ adapcctl.sh status

You are running adapcctl.sh version 120.6

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: VIS_mzlinux04.mzlinux04.uk.oracle.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 12983 | Alive
OC4J | forms | 12895 | Alive
OC4J | oacore | 11479 | Alive
HTTP_Server | HTTP_Server | 12742 | Alive
ASG | ASG | N/A | Down

adapcctl.sh: exiting with status 0

     b- The various logs written while starting and stopping the services are created in
          ($LOG_HOME/appl/admin/log/), the logs files are:

Service	Log file name
TNS Listener Start/Stop log	adalnctl.txt
Fulfillment Server Start/Stop log	jtffmctl.txt
Oracle HTTP Server start/stop log	adapcctl.txt
Concurrent Managers and ICM start/stop log	adcmctl.txt
Forms OC4J start/stop log	adformsctl.txt
OACore OC4J start/stop log	adoacorectl.txt
OAFM OC4J start/stop log	adoafmctl.txt
OPMN start/stop log	adopmnctl.txt

(adstpall.log) and (adstrtal.log) will record the messages while starting or stopping all services.

Error: Signal 25 Concurrent Program Completes with Error

When attempting to run a concurrent program which is accessing file "reports.log" in directory $INST_TOP/logs/appl/conc/log then following error occurs: "Error: emsg:was terminated by signal 25".

This happens when the file size of "reports.log" has reached its maximum limit at operating system which is 2GB. 

 If $INST_TOP/logs/appl/conc/log/reports.log will be huge -- 2GB in size, then reports can not append anything else to it and hence concurrent program accessing reports.log for its execution will error out.

To implement the solution, please execute the following steps: 

1. Stop the concurrent manager. 

2. Rename/Truncate existing "reports.log" in directory $INST_TOP/logs/appl/conc/log/reports.log and create a empty "reports.log". 

3. Start the concurrent manager. 

4. Retest the issue. 

5. Migrate the solution as appropriate to other environments. 

CSTRINVR - Inventory Valuation Report Errors with MSG-00999 and REP-1419

Issue :
When running the Inventory Value Report (CSTRINVR), you get the following

errors:
    MSG-00999 1: user defined exception
    MSG-00999 cstrinvr > X 30-Jun-1997 ...
    REP-1419 'beforereport : pl/sql program aborted.

Answer : 

Complete the following steps:

    1. Login to System Administrator responsibility.
    2. Navigate to Profile/System.
    3. Display User profiles.
    4. Change 'CST:View Cost Privilege' profile option = YES.
If the profile option is set to No, the Inventory Value report will complete 
with error because of Insufficient Cost View Privileges.
Explaination : 
By changing 'CST:View Cost Privilege' profile option to YES, this error does
not occur.

System Hangs or Errors Seen in Event Viewer or /var/log/messages for FRMWEB When Forms Session Closed Using Browser

Applicable to :

Oracle Forms - Version: 10.1.2.0.2 and later   [Release: Oracle10g and later ]
Linux x86-64

Warning or Error Message :

In /var/log/messages :
kernel: frmweb[16576]: segfault at 000000006974655e rip 00000000081d3ca9 rsp 00000000ffff9d2c error 4
(most numbers are not significant, they will likely differ on your system)
On Unix, huge core files can also be generated, possibly causing a system hang or various problems.

Reason for occurance : 

The users are closing the Forms sessions using the browser and not by using the exit mechanisms in the Forms.

Answer :
To prevent these error messages, close the Forms application normally before closing the browser.

The correct procedure to

--  Close a form   --   F4

-- Query a form  --  F11

--  Find the query  -- Ctrl + F11

If using Internet Explorer, you can pop-up a message window when users attempt to close the browser by following indications from NOTE 605327.1 - How To Trap Attempts At Closing The Parent Browser Running A Forms Application given in the oracle metalink

On Unix, setting "ulimit -c" to "0", for example before launching the "opmn" processes, will prevent writing of further "core" files. This can avoid completely the risk of a huge frmweb "core" file possibly impacting the system in case a user would close his browser without exiting Forms first:

ulimit -c 0
opmnctl startall

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12

On Oracle Applications R12 when checking the top processes on the OS level for the middle tier, you find that forms process (frmweb) almost consumes 100% of the CPU. The root cause of the issue is that returning rows from LOVs in core forms causes the forms process to grow up into memory depending on the number of rows returned.

When an end user login to forms and start working with LOV within core forms sometimes and according to the search criteria that the user will provide to filter the results in LOV, it may fetch huge numbers of records in which causes the frmweb process to grow very large, and in extreme cases this can even lock up the current process or even the whole machine.

So when executing a LOV query, every row is fetched into memory on the middle tier, the frmweb process can get extremely large, and the larger it gets the more likely it is to start paging. Eventually it starts consuming excessive CPU just paging the process in and out of memory, which is probably what you can see here in this case as the amount of memory consumed when the LOV records are fetched into memory obviously depends on the amount of data in each record.

Solution
To implement the solution, please execute the following steps:

1. Stop all services on the middle tier.

2. Set following forms environment variables:

FORMS_RECORD_GROUP_MAX to 10000 or if that proves too restrictive, increase it to 20000 or 30000.
FORMS_CATCHTERM=0

In order to set the above forms variables so next time autoconfig run does not override those values, do the following steps :

1- For Forms Variable "FORMS_CATCHTERM" the context vairable name is: "s_forms_catchterm" and you can update the context file located in ($INST_TOP/appl/admin/)

2- For other forms variable "FORMS_RECORD_GROUP_MAX" there is no variable defined in Autoconfig for that one and have to customize the autoconfig for the forms variables to set that environment as following:

  a) Go to the autoconfig Template folder:
$cd $AD_TOP/admin/template
  b) Create new directory named (custom)
$ mkdir custom
  c) Make sure that new directory has same file permissions as ($AD_TOP/admin/template)
  d) Copy the following autoconfig template to the new custom directory:
$cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env
  e) Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################
# Oracle Forms environment variables
####################################

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

  f) Save and exit from the file.
  g) Next time autoconfig run, it will read the custom directory and check for any customizations there.

3. Run Autoconfig on the middle tier and make sure it is completed successfully.

4. Startup all services.

5. Monitor the forms process to see its CPU usage, and you will see that form process usage is reduced and not causing any more CPU consumption up to 100% as before.

6. Migrate the solution as appropriate to other environments.

Backups using RMAN

How to take backup of the entire database?  i.e database files and the control files

 RMAN won't take backup of log files.

 RMAN>backup database;

If you want to take a complete backup as a compressed backup and also specify the location with control file, then use the following syntax

RMAN> backup as compressed backupset format 'Desired location' include current controlfile;

    It will backup entire database files along with control file.

How to take backup of the entire tablespace?

RMAN>backup tablespace tablespacename;

eg: RMAN>backup tablespace SYSTEM;

It will take backup of all data files belong to SYSTEM tablespace.

Assume system contains 3 datafiles it will take all dbf files

How to take backup of a particular datafile?

RMAN>backup datafile '/home/sales/userdata.dbf';

This will take backup of only specified file.             

How to take backup of control file using RMAN?

sql>conn /as sysdba

sql>select cotnrolfile_type from v$database;

 CURRENT

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

RMAN>backup current cotnrolfile;

Note: if the target database is running using spfile.

Then only we can take spfile backup.

RMAN>backup spfile;

How to take backup of archive logs?

RMAN>backup archivelog all;

How to take the backup of database plus archive log files?

RMAN>backup database plus archivelog;

 This will take datafiles, controlfiles and arhivelogs.

Practical demo

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

RMAN>connect catalog rman/rman@to_sales;

RMAN>connect target sys/prd@to_sales;

RMAN>backup database;

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

Note: cp command will simply copy whether the block is corrupted

      or not. RMAN won't take the backup in the corrupted  blocks.     

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

go to serverside and check it.

]$cd $ORACLE_HOME/dbs

dbs]$ls

Here we can find backup files.

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

Take backup of tablespace

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

RMAN>backup tablespace system;

RMAN>report schema;

Take backup of datafile

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

rman>backup datafile 'path';

rman>list backup;

(we can check whether the backups are available or not)

Detailed information

rman>list backup summary;

How to delete rman backups?

RMAN>list backup summary;

RMAN>delete backupset backupsetkey;

eg: RMAN>delete backupset 51;

    (enter yes/no)?yes

You can check

RMAN>list backup summary;

It is displaying all

How to delete all?

If we don't want confirmation

RMAN>delete noprompt backupset backupsetno;

eg: RMAN>delete noprompt backupset 52;

RMAN>delte backup; 

The above command will delete everything.

RMAN>list backup summary;

20090907

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

RMAN>backup database;

RMAN>list backup summary;

RMAN>delete force backup;

RMAN>list backup;

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

How to take backup at desired location?

 With the help of format command we can keep the backup files in particular location.

DEMO

----

On the target (server side) go to opt directory

opt]$mkdir backup

login as root user and give the permissions

opt]$ls

backup

These format specifiers are used with format command

 %d - It indicates database name

 %u - 8 character unique id

 %T --Current system date

 backupfilename_%d_%T20090907

RMAN>backup tablespace userdata format '/opt/backup/rman_%d_%T';

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

Take complete backup for recovery purposes

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

RMAN> backup database;

Starting backup at 29-AUG-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/home/sales/system.dbf

input datafile fno=00002 name=/home/sales/undosales.dbf

input datafile fno=00003 name=/home/sales/sysaux.dbf

input datafile fno=00004 name=/home/sales/userdata.dbf

channel ORA_DISK_1: starting piece 1 at 29-AUG-09

channel ORA_DISK_1: finished piece 1 at 29-AUG-09

piece handle=/home/oracle/product/10.2.0/db_1/dbs/11knu0kf_1_1 tag=TAG20090829T1 75942 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 29-AUG-09

channel ORA_DISK_1: finished piece 1 at 29-AUG-09

piece handle=/home/oracle/product/10.2.0/db_1/dbs/12knu0km_1_1 tag=TAG20090829T1 75942 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 29-AUG-09

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


RMAN>