RMAN Commands Summary
Summary
of RMAN Commands
|
Allocate a channel
in preparation for issuing maintenance commands such as DELETE.
|
|
|
A subclause used to
specify which items will be displayed by the LIST command.
|
|
|
Release a channel
that was allocated with an ALLOCATE CHANNEL command.
|
|
|
Release a channel
allocated with an ALLOCATE CHANNEL FOR MAINTENANCE command.
|
|
FDPSTP failed due to ORA-20100
Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o4824028.tmp in the directory, /usr/tmp.You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417
Solution:
To Implement the solution please follow the below
Take backup of /usr/tmp directory
1. Purge the /usr/tmp directory in TEST instance.
2. Check if the Directory has all the accesses ie.mode should be 777.
3. try to move a new file in the directory manually to check if its allowing to copy.
4.If its successful please reproduce the issue and check.
5.Move the fix to appropriate environment if the issue is resolved.
=========
File o0006564.tmp already exists in /tmp with no re-write permission
applmgrp [/tmp] ls -l /tmp/o0006564.tmp
-rw-r--r-- 1 oraclet dbat 368 Apr 14 22:10 /tmp/o0006564.tmp
As the file already exists it cannot be overwritten and process fails
1- From the UNIX user that starts the concurrent processors
ls -l /tmp/o0006564.tmp
rm /tmp/o0006564.tmp
==========================
Ensure that the directories specified in the utl_file_dir parameter in the V$PARAMETER table are writeable by the Oracle owner.
The profile option CSI: Log File Path must be the first entry in the utl_file_dir parameter.
Make sure that $APPLPTMP and $APPLTMP are set to this same value.
Verify that the profile 'CSI: Log File Path' has a valid directory path (NO trailing slash) and that it is defined in the 'utl_file_dir' parameter.
=========================
FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, oXXXXXXXX.tmp in the directory, /usr/tmp
A TEST instance was already up and running and I created another production clone on the same server/machine. Once the services of both the instances
The concurrent programs were completing in error and when checked the log files, the following error message was there:
ORACLE error 20100 in FDPSTP
Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o0031866.tmp in the directory, /usr/tmp.
You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417
When investigated, I found that both the instnaces were creating .tmp files in /usr/tmp directory with the same name. This error was being thrown when one instance was trying to create .tmp file and a file with the same name was already created by the other instance.
To resolve the issue I shutdown both the apps and db services of one instance.
Created a directory 'temp' in '/usr/tmp' and changed the ownership of this dir to user owner of this instance
Logon to database as sysdba
Create pfile from spfile
modified UTL_FILE_DIR parameter's first entry from '/usr/tmp' to '/usr/tmp/temp'
Created spfile from pfile
Brought up the db and listener
Now modified the $APPLPTMP variable in TEST_oratest.xml file from '/usr/tmp' to '/usr/tmp/temp'
Run the autoconfig on apps tier/node
Brought up the apps services
Retested the issue and it was resolved
==================
Scripts for Troubleshooting Concurrent Manager
Concurrent Manager Status Codes and Phase Codes.What are status codes and Phase codes of Concurrent Manager.
STATUS_CODE Column:
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
PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running
Above can be used for fnd_concurrent_requests table to get Concurrent Request status.
Long running Concurrent Requests
1.How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
=========================================================================
Long Running Concurrent Request In Oracle :
Looking on how to check long running concurrent request in Oracle Apps 11i or R12? Here’s the overview of the SQL query script to detect the session information of each program.
First you need to get the listing of running concurrent request in Oracle Apps 11i or R12. You can use the SQL query script as below to obtain the list of running request.
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC
You can see the request id and other relevant information from the result.
Based on the SPID associated to each running request, query the v$session or v$session_longops table to see what is the request id doing in the backend.
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '11696'
ORDER BY a.spid, c.piece
Replace v$session with gv$session if the database is running on RAC environment. Enable or set trace if you wish to know more details on the session.
====================================================================
2.Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
4.To find sid,serial# for a given concurrent request id?
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;
6.To find the sql query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/
7. To find child requests
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
8. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 .Concurrent Request running by SID
SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;
11. Find out request id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
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'
ORDER BY 5 DESC;
13. Request submitted by User
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
14.Concurrent Program enable with trace
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID
15.Checking which manager is going to execute a program
The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'
16.To see all the pending / Running requests per each manager wise
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
Note: The same information can be seen in Administer Concurrent Manager form for each manager.
17 .Checking the incompatibilities between the programs
The below query can be used to find all incompatibilities in an application instance.
SELECT a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US'
The table apps.fnd_concurrent_program_serial has the information about incompatibilities.
18 .How to find if module is installed or not in Oracle Apps
We have 3 ways to find out if a module is installed in oracle apps
1 We can run the following script
cd $AD_TOP/sql/adutconf.sql
In this script, there is a section->"Product Installation Status, Version Info and Patch Level"
In this, status of installed means the product is installed.
2 Through OAM
In oracle apps, navigate to
OAM>Site Map>/License Manager>Reports>Licensed Products
Here filter the products by using "Licensed". These are the licensed and installed products in oracle apps.
3 Using a sql query->
We can use the following query
SELECT a.application_name,a.product_code,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status='I'
order by product_code asc;
19.How to Find out Product Installations and Patch Set level in Oracle Applications 11i ?
Use Following Query to find out Product Installation
SELECT application_name "Application Name",
SUBSTR (application_short_name, 1, 10) "Short Name",
RPAD (DECODE (fpi.status, 'I', 'Installed', 'S', 'Shared Install', 'N', 'Not Installed' ), 14, ' ' ) "Install Status", SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath"
FROM fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fat
WHERE fa.application_id = fpi.application_id
AND fa.application_id = fat.application_id
ORDER BY fpi.application_id;
SELECT application_name,SUBSTR (application_short_name, 1, 10) "Short Name",SUBSTR (patch_level, 1, 12) "Patch Level", fa.BASEPATH "Basepath" FROM fnd_product_installations fpi,fnd_application fa,fnd_application_tl fat
WHERE fa.application_id = fpi.application_id AND fa.application_id = fat.application_id order by fa.BASEPATH;
Select product_version,patch_level from fnd_product_installations where patch_level like '%AP%';
select BASEPATH from fnd_application order by BASEPATH;
20. How to Find Environment Variables from database table - Oracle E-Business Suite
Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?
Try out out this query that shows you $FND_TOP:
select value
from fnd_env_context
where variable_name = 'FND_TOP'
and concurrent_process_id =
( select max(concurrent_process_id) from fnd_env_context );
VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/apps/apps_st/appl/fnd/12.0.0
-------------------------------------------------------------------------------
Or
Did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?
col variable_name format a15
col value format a64
select variable_name, value
from fnd_env_context
where variable_name like '%\_TOP' escape '\'
and concurrent_process_id =
( select max(concurrent_process_id) from fnd_env_context )
order by 1;
VARIABLE_NAME VALUE
--------------- ----------------------------------------------------------------
AD_TOP /d01/oracle/VIS/apps/apps_st/appl/ad/12.0.0
AF_JRE_TOP /d01/oracle/VIS/apps/tech_st/10.1.3/appsutil/jdk/jre
AHL_TOP /d01/oracle/VIS/apps/apps_st/appl/ahl/12.0.0
AK_TOP /d01/oracle/VIS/apps/apps_st/appl/ak/12.0.0
ALR_TOP /d01/oracle/VIS/apps/apps_st/appl/alr/12.0.0
AME_TOP /d01/oracle/VIS/apps/apps_st/appl/ame/12.0.0
AMS_TOP /d01/oracle/VIS/apps/apps_st/appl/ams/12.0.0
AMV_TOP /d01/oracle/VIS/apps/apps_st/appl/amv/12.0.0
AMW_TOP /d01/oracle/VIS/apps/apps_st/appl/amw/12.0.0
APPL_TOP /d01/oracle/VIS/apps/apps_st/appl
AP_TOP /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0
AR_TOP /d01/oracle/VIS/apps/apps_st/appl/ar/12.0.0
...
21.How to find the full directory path to $APPLTMP?
select value
from fnd_env_context
where variable_name = 'APPLTMP'
and concurrent_process_id =
( select max(concurrent_process_id) from fnd_env_context );
VALUE
--------------------------------------------------------------------------------
/d01/oracle/VIS/inst/apps/VIS_demo/appltmp
NB: These queries assume your concurrent managers are running!
22. Script to Get OS user name with terminal name
REM: Script to Get Os user name with terminal name
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************
SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;
23 .Query to Identify the Concurrent Program for Long time.....................
Step1 : Run the first query , this will list all the programs that currently running in Application. Take the SID and use it in the second query.
SELECT
f.user_name
,a.request_id "Req Id"
,a.concurrent_program_id "Prg Id"
,a.RESPONSIBILITY_ID Responsibility
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vs.serial# "Serial#"
,vp.spid
,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
,v$session vs
,v$process vp
WHERE
a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id = c.application_id
AND a.phase_code IN ('I','P','R','T')
AND a.requested_by = f.user_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND c2.LANGUAGE = 'US'
AND a.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
ORDER BY 9
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query ( select / insert / update ) which is actually taking time in your concurrent program.
SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE
Step 3 :
------------
The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.
Change the concurrent program name (tl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program is "Autoinvoice Import Program". You can also uncomment the "&Start_Date" line to get the list for a specific date.
-------------------------------------------------------------------------------
-- Query to find runtime for a concurrent program
-------------------------------------------------------------------------------
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
-- AND TRUNC(rq.actual_start_date) = '&start_date' -- uncomment this for a specific date
ORDER BY rq.request_id DESC;
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-iopHbsx3ixMt3pk2iCSGvo7My8mOvzcs-HUFgRW3aRpYS1s4jgWARe_VNvmIWmMA-pylzXwX3RSoO9Ryf8BG19DFBPPI4j6O6Ewtcgz_sBT4tR3XXuVTKWHXtV-okYTmJHAv9rhqiQQ/s640/conc_prog_runtime.png
*************************************************************
24 .Script to reset Oracle Apps front end User ID Password
Here is a sample anonymous PL/SQL block which will reset the Oracle Apps frontend password for a given user from backend
DECLARE
flag_value BOOLEAN;
BEGIN
flag_value :=
fnd_user_pkg.changepassword(username=> 'NKUMAR2'
,newpassword => 'welcome1');
IF flag_value
THEN
DBMS_OUTPU.PUT_LINE('The password reset successfully');
ELSE
DBMS_OUTPUT.PUT_LINE('The password reset has failed');
END IF;
END;
/
COMMIT;
25 .Checking the duplicated schedules of the same program with the same arguments
The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.
Note: This query will return even though the request was submitted using a different responsibility.
SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME;
26.Average pending time per request
This is a very useful query to check the performance of the concurrent managers.
Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests
A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)
We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2;
Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.
27.Script to Monitor Concurrent requests average run time
Do you need a daily report which would give an average run time of all concurrent requests in your environment?
So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:
set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,' Not Run')) Dbydy_tim,to_char(nvl(ydy.ydy_time,' Not Run')) ydy_tim,to_char(nvl(tdy.tdy_time,' Not Run')) tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND ydy.user_concurrent_program_name = tdy.user_concurrent_program_name (+)
order by wkly_time desc;
28. Find currently spooling temp file from request
col outfile format a30
col logfile format a30
select cp.plsql_dir || '/' || cp.plsql_out outfile
, cp.plsql_dir || '/' || cp.plsql_log logfile
from apps.fnd_concurrent_requests cr
, apps.fnd_concurrent_processes cp
where cp.concurrent_process_id = cr.controlling_manager
and cr.request_id = &request_id;
OUTFILE LOGFILE
------------------------------ ------------------------------
/usr/tmp/PROD/o0068190.tmp /usr/tmp/PROD/l0068190.tmp
REM Now tail log file on database node to see where it is at, near realtime
REM tail -f /usr/tmp/l0068190.tmp
29.Currently held locks per concurrent request
set lines 150
col object_name format a32
col mode_held format a15
select /*+ ordered */
fcr.request_id
, object_name
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global'
) status
, decode( v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive'
, to_char(lmode)
) mode_held
from apps.fnd_concurrent_requests fcr
, gv$process pro
, gv$session sess
, gv$locked_object v
, gv$lock l
, dba_objects d
where fcr.phase_code = 'R'
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and sess.sid = v.session_id (+)
and v.object_id = d.object_id (+)
and v.object_id = l.id1 (+)
;
REQUEST_ID OBJECT_NAME OBJECT_TYPE STATUS MODE_HELD
---------- -------------------------------- ------------------- ------------ ---------------
1070780 VIRTUATE_GL_OLAP_REFRESH TABLE Not Blocking Exclusive
-------------------------------------------------------------------------------
-- Query to find concurrent request status related information
------------------------------------------------------------------------------
SELECT fu.user_name "User ID",
frt.responsibility_name "Responsibility Used",
fcr.request_id "Request ID",
fcpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(fcr.phase_code,
'C', 'Completed',
'P', 'Pending',
'R', 'Running',
'I', 'Inactive',
fcr.phase_code) "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',
fcr.status_code) "Status",
fcr.request_date "Request Date",
fcr.requested_start_date "Request Start Date",
fcr.hold_flag "Hold Flag",
fcr.printer "Printer Name",
fcr.parent_request_id "Parent Request ID"
-- fcr.number_of_arguments,
-- fcr.argument_text,
-- fcr.logfile_name,
-- fcr.outfile_name
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt
WHERE fu.user_id = fcr.requested_by
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND frt.LANGUAGE = USERENV('LANG')
AND fcpt.LANGUAGE = USERENV('LANG')
-- AND fcr.request_id = 7137350 -- <change it>
AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program' -- <change it>
ORDER BY fcr.request_date DESC;
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCyVb8auwbUWm6LNA2O2hV_0zago1Xmjrq9ufaeeQ3xRnsMNeT4a5xSQdEJFhzKkIgEjtGMSI7YjPoD5eW4Fizlxol8twnjsWodEYT3Av0IfIfBz3fX338wy7qJhnRxYrO1NjN40em1Vs/s1600/conc_request_status.png
30) CONCURRENT REQUEST MONITORING QUERIES
SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';
********************************************************
Retrieve a list of all responsibilities.
Parameters
None
*//
SELECT
(SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id)
application
, frt.responsibility_id
, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;
//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//
SELECT DISTINCT
a.responsibility_name
, c.user_menu_name
FROM
apps.fnd_responsibility_tl a
, apps.fnd_responsibility b
, apps.fnd_menus_tl c
, apps.fnd_menus d
, apps.fnd_application_tl e
, apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;
//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//
SELECT
c.prompt
, c.description
FROM
apps.fnd_menus_tl a
, fnd_menu_entries_tl c
WHERE
a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu – System Administrator GUI’;
//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//
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
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60);
//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of submitting requests)
Parameters
None
*//
SELECT
responsibility_name responsibility
, request_group_name
, frg.description
FROM
fnd_request_groups frg
, fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//
SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date "Change Date"
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By"
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//
SELECT
ffft.user_function_name "User Form Name"
, ffcr.SEQUENCE
, ffcr.description
, ffcr.rule_type
, ffcr.enabled
, ffcr.trigger_event
, ffcr.trigger_object
, ffcr.condition
, ffcr.fire_in_enter_query
, (SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
fnd_form_custom_rules ffcr
, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;
//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*//
SELECT
a.application_name
, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
, patch_level
FROM
apps.fnd_application_vl a
, apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;
//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//
SELECT
responsibility_name
, frg.request_group_name
, fcpv.user_concurrent_program_name
, fcpv.description
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 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;
//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*//
SELECT
fa.application_short_name
, fcpv.user_concurrent_program_name
, description
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, output_file_type
, program_type
, printer_name
, minimum_width
, minimum_length
, concurrent_program_name
, concurrent_program_id
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
ORDER BY description
//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//
SELECT
fa.application_short_name
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, COUNT (concurrent_program_id) COUNT
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
GROUP BY
fa.application_short_name
, fcpv.execution_method_code
ORDER BY 1;
//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//
SELECT
f.request_id
, pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
‘ HOURS ‘
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
‘ MINUTES ‘
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
‘ SECS ‘ time_difference
, DECODE(p.concurrent_program_name
,’ALECDC’
,p.concurrent_program_name’['
f.description']‘
,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code
,’R',’Running’
,’C',’Complete’
,f.phase_code) Phase
, f.status_code
FROM
apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*//
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
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//
SELECT
patch_name
, patch_type
, maint_pack_level
, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC
15. How to check the System Administarator responsiblity for the users in ORACLE EBS ?
set lines 2000
set pages 5000
col USER_NAME for a45
Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and c.responsibility_name='System Administrator'
Subscribe to:
Posts
(
Atom
)