Scripts for oracle database - Readmeout Oracle DBA Blog

Scripts for oracle database | In this post we will discuss best and useful script for oracle database for oracle database and EBS to administration and monitor. So that we can proactively monitor oracle DB and Oracle Apps R12 using oracle db monitoring scripts.

Below are the Script for oracle database and advanced dba scripts to monitory APPS and DB.

Table of Contents

Script for APPS:-

Useful oracle database monitoring queries to list the concurrent programs: –

To list the concurrent programs that run today, Giving details about status and logs:-

SQL> SELECT DISTINCT fcp.user_concurrent_program_namefcp.concurrent_program_name,
fcr.request_id,fcr.request_date,flv.meaning status,fcr.status_code,fcr.completion_text,fcr.logfile_name, fcr.outfile_name,fcr.argument_text FROM apps.fnd_concurrent_programs_vl fcp,apps.fnd_concurrent_requests    fcr,apps.fnd_lookup_values flv WHERE fcr.concurrent_program_id = fcp.concurrent_program_id 
AND trunc(fcr.last_update_date) = trunc(SYSDATE) AND flv.lookup_code = fcr.status_code AND
flv.lookup_type = 'CP_STATUS_CODE' AND flv.language ='US' ORDER BY fcr.request_date,fcr.request_id DESC;

To Cancel specific concurrent request:-

SQL>update fnd_concurrent_requestsset status_code='D', phase_code='C'where request_id=24264995;

SQL>commit;

To Check running concurrent request using below oracle database monitoring scripts:

SQL>SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME, round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time, a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'), To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,(a.actual_start_date-a.request_date)*24*60*60 AS lag_time, d.user_name, a.phase_code,a.status_code,a.argument_text,a.priorityFROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b , apps.FND_CONCURRENT_PROGRAMS_TL c, apps.fnd_user dWHERE a.concurrent_program_id= b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND a.requested_by =d.user_id AND-- trunc(a.actual_completion_date) = '24-AUG-2005'c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like '%, , , , ,%';-- and status_code!='C';

To Check Custom Reports: –

SQL> SELECT CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME,USER_CONCURRENT_PROGRAM_NAME,DESCRIPTION, APPLICATION_ID FROM FND_CONCURRENT_PROGRAMS_VL  WHERE CREATED_BY NOT IN ( -1,1,2,120,121,0)--('AUTOINSTALL','INITIAL SETUP','ORACLE12.0.0','SYSADMIN','ANONYMOUS','ORACLE12.1.0') AND ENABLED_FLAG = 'Y' AND SRS_FLAG = 'Y';

To Check object locked status.

Here we can see below script is very useful to identify the locking in database. So that we can release the particular sid if it is block holder. Because locking will impact the performance of entire database performance.

SQL>SELECT c.owner ,c.object_name,c.object_type,       fu.user_name locking_fnd_user_name ,fl.start_time locking_fnd_user_login_time,      vs.module,vs.machine ,vs.osuser      ,vlocked.oracle_username,vs.sid,vp.pid ,vp.spid AS os_process,vs.serial# ,vs.status ,vs.saddr ,vs.audsid ,vs.processFROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked ,v$process vp ,v$session vs,dba_objects cWHERE vs.sid = vlocked.session_idAND vlocked.object_id = c.object_idAND vs.paddr = vp.addrAND vp.spid = fl.process_spid(+)AND vp.pid = fl.pid(+)AND fl.user_id = fu.user_id(+)--AND c.object_name LIKE '%' || upper('AP_INVOICES_ALL') || '%'AND nvl(vs.status ,'XX') != 'KILLED';
SELECT c.owner,c.object_name,c.object_type,fu.user_name locking_fnd_user_name,fl.start_time locking_fnd_user_login_time      ,vs.module,vs.machine,vs.osuser      ,vlocked.oracle_username,vs.sid,vp.pid,vp.spid AS os_process      ,vs.serial#,vs.status,vs.saddr,vs.audsid,vs.processFROM fnd_logins      fl,fnd_user fu,v$locked_object vlocked,v$process vp,v$session vs    ,dba_objects cWHERE vs.sid = vlocked.session_idAND vlocked.object_id = c.object_idAND vs.paddr=vp.addrAND vp.spid = fl.process_spid(+)AND vp.pid = fl.pid(+);

To check Incompatibility between program: –

This is very useful oracle ebs script to identify that our programs getting conflict with which programs. So that we can further investigate the issue.

SQL> 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_typeFROM 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 b2WHERE a1.application_id = cps.running_application_idAND a1.concurrent_program_id = cps.running_concurrent_program_idAND a2.application_id = cps.running_application_idAND b1.application_id = cps.to_run_application_idAND b1.concurrent_program_id = cps.to_run_concurrent_program_idAND b2.application_id = cps.to_run_application_idAND a1.language = 'US'AND a2.language = 'US'AND b1.language = 'US'AND b2.language = 'US';

To check Manager of running concurrent request.

Again, we have very import script to monitor check that how many concurrent requests are currently running. However, we can manage the server utilization as well as proactively check the valuable solution using Script for oracle database. Because on the basis of how many requests are running current, we can do the proactive steps to make resources free to complete the those request successfully.

SQL>SELECT user_concurrent_program_name, user_concurrent_queue_nameFROM apps.fnd_concurrent_programs_tl cp,apps.fnd_concurrent_queue_content cqc,apps.fnd_concurrent_queues_tl cqWHERE cqc.type_application_id(+) = cp.application_idAND cqc.type_id(+) = cp.concurrent_program_idAND 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_idAND NVL (cqc.queue_application_id, 0) = cq.application_idAND cq.LANGUAGE = 'US';

To check RDF file location: –

SQL>SELECTAPPLICATION_NAME,'$'||BASEPATH||'/'||'reports/US'Reports_Path,EXECUTION_FILE_NAME FROM APPS.FND_EXECUTABLES_VL A,APPS.FND_APPLICATION_VL BWHERE EXECUTION_METHOD_CODE='P'AND A.APPLICATION_ID=B.APPLICATION_ID--AND EXECUTION_FILE_NAME ='QPXPRQFS'

To check concurrent programs running currently with Details of Processed time– and Start Date


SQL>SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME, round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),  To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end, (a.actual_start_date-a.request_date)*24*60*60 AS lag_time, d.user_name, a.phase_code,a.status_code,a.argument_text,a.priorityFROM   apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b ,            apps.FND_CONCURRENT_PROGRAMS_TL c, apps.fnd_user dWHERE       a.concurrent_program_id= b.concurrent_program_id AND            b.concurrent_program_id=c.concurrent_program_id AND a.requested_by =d.user_id AND --trunc(a.actual_completion_date) = '24-AUG-2005'c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' --  and argument_text like  '%, , , , ,%'; -- and status_code!='C';

To check running concurrent request

SQL>select request_id Reqst, User_Concurrent_Program_Name Program,To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date, To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_Atfrom fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,     fnd_oracle_userid Owhere Status_Code = 'A'  And ( Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And  Program_Application_ID = Application_ID )  And  Fcr.Oracle_ID = O.Oracle_IDOrder By  Requested_Start_Date Asc,Decode(Priority, Null, 9999999, Priority) Asc,Request_ID Asc;

To check assigned responsibility of FND User

SQL>select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAMEfrom apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grpwhere upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')--and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'--and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'and grp.responsibility_id = res.responsibility_idand grp.user_id = usr.user_id;

To find the currently running SQL after finding SID of concurrent job

SQL> SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID=11710);

To check scheduled concurrent request.

SQL>SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

To check trace file of concurrent request.

Using below query we can find out the trace of any concurrent request.

SQL> SELECT 'Request id: '||request_id ,
'Trace_id: '||oracle_Process_id,
'Trace_Flag: '||req.enable_trace,
'Trace_Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.executable_application_id = execname.application_id
and prog.executable_id=execname.executable_id;