SQL PLAN from AWR report for given SQLID

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('at1cy00n5p7u0'));

Which SQL PLAN ID ran between snapid given SQL ID

SELECT plan_hash_value,snap_id,SQL_PROFILE,OPTIMIZER_COST,ROWS_PROCESSED_TOTAL,ELAPSED_TIME_DELTA FROM dba_hist_sqlstat WHERE
sql_id = 'cdvxbyn5t3nsy'  and snap_id >=24035 and snap_id<=24040 ORDER BY 1




SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - 1/12) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( 'cdvxbyn5t3nsy')

TO take procedure backup from Sqlplus

REM procedure, package or package body.
REM This SCRIPT is provided as a guide and is not supported by
REM ORACLE Corporation.
set verify off
set feedback off
set lines 132
set pages 0
set heading off
set space 0
column text format a79
column line noprint
select DECODE(line,1,'create or replace ','')||text, line
from dba_source
where
owner = upper('APPS') and
type = upper('PACKAGE BODY')
and name = upper('XXLR_INTF_BOS_JDE_WO_PKG')
and line >= 1;
set verify on
set feedback on

Concurrent Request Count Day wise

set pause off
set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99
select request_id,
user_concurrent_program_name,
to_char(actual_start_date,'DD-MON HH24:MI:SS') START_TIME,
to_char(ACTUAL_COMPLETION_DATE,'DD-MON HH24:MI:SS') END_TIME,
(actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
--and user_concurrent_program_name like '%Gather Schema%'
and fnd_concurrent_requests.concurrent_program_id=57389
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id and status_code in ('C')
order by actual_start_date;


Example:


COUNT(*) TRUNC(A.ACTUAL_COM
---------- ------------------
       205 01-NOV-16
       202 02-NOV-16
       220 03-NOV-16
       244 04-NOV-16
        38 05-NOV-16

Concurrent Request ID Pending From

select request_id,to_char(round((SYSDATE - requested_start_date) * 1440, 2))
from fnd_Concurrent_requests
where concurrent_program_id=57389 and  phase_code='P'  and status_code='I' order by 1 desc;

To find plan hash value for SQL ID

select sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where sql_id='cdvxbyn5t3nsy';

Conc Request Complete Time Day Wise

select count(*),TRUNC(a.actual_completion_date) 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 1=1
--and a.REQUEST_ID = 183923555
AND 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.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 c2.concurrent_program_id=57389
and a.actual_completion_date   between to_date('01-NOV-2016 01:40:00','DD-MON-YYYY HH24:MI:SS') and to_Date('21-NOV-2016 23:00:00','DD-MON-YYYY HH24:MI:SS')
group by TRUNC(a.actual_completion_date) ORDER BY 2;


example :
REQUEST_ID START_TIME      END_TIME        COMP_TIME ARGUMENT_TEXT                                 USER_NAME       S P
---------- --------------- --------------- --------- --------------------------------------------- --------------- - -
  11048480 23-OCT 08:34:06 23-OCT 08:39:21      5.25 , 145704                                      428686          C C
  11048688 23-OCT 08:52:17 23-OCT 08:56:20      4.05 , 154046                                      186219          C C
  11048873 23-OCT 09:10:16 23-OCT 09:14:00      3.73 , 154046                                      186219          C C
  11049070 23-OCT 09:26:05 23-OCT 09:29:33      3.47 , 144482                                      428686          C C
  11049192 23-OCT 09:37:26 23-OCT 09:39:48      2.37 , 305806                                      428687          C C
  11049990 23-OCT 10:41:15 23-OCT 10:46:11      4.93 , 64050                                       428686          C C

To enable the oracle user session

column spid format a5
column sid format 9999
column ora_user format a10
column unix_user format a10
column osuser format a10
column server for a12
column machine for a15
col MACHINE for a10
column whenon format a18 heading "WHEN USER LOGGED ON"
column whendo format a18 heading "WHEN LAST ACTIVITY"
set lin 150
select  p.spid, s.sid, s.event,s.module,s.machine,s.serial#,Status,
       to_char(s.logon_time,'mm/dd/yy hh24:mi:ss') whenon,
       to_char(sysdate - (s.last_call_et) / 86400,'mm/dd/yy hh24:mi:ss') whendo
from v$process p, v$session s
where s.paddr(+) = p.addr  and sid=617
order by s.logon_time,s.status;

2. Start the debug session with the SPID of the process that needs traced.




SQL> oradebug setospid 32230


. Select the appropriate trace level. There are four different options when specifying a tracing level:

• Level 1 – provides “base set” tracing information. Bind variables are displayed as variables (:b1).

• Level 4 – provides Level 1 data and the actual data values of bind variables.

• Level 8 – provides Level 1 data and information on wait events when the elapsed time is greater than the CPU time.

• Level 12 – combines levels 1, 4 and 8 tracing information.

A Level 12 trace contains base set, bind variable values and wait events.
The oradebug command below will enable the maximum tracing possible:

SQL> oradebug event 10046 trace name context forever, level 12




4. Turn tracing off.

SQL> oradebug event 10046 trace name context off

5. Obtain the trace file name. The oradebug facility provides an easy way to obtain the file name:

SQL> oradebug tracefile_name

How to check oracle apps profile changed last few days

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name, '10003', rsp.responsibility_key,
'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,
'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE 
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('BNE%')
and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-3)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null
order by last_update_date desc, short_name, level_set;


select distinct  n.user_profile_option_name "PROFILE NAME",v.LAST_UPDATE_DATE,v.LAST_UPDATED_BY 
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('Initialization SQL Statement - Custom')
and trunc(v.LAST_UPDATE_DATE) >= trunc(sysdate)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
and v.LAST_UPDATE_DATE is not null ;
order by last_update_date desc, short_name,level_set;

ORACLE EBS R12.2 Log locations

Variable Name                                            Description

CONTEXT_FILE                                                  Context file name

EBS_DOMAIN_HOME                                 WLS Deployment of Oracle E-Business12.2  Domain

ADMIN_SCRIPTS_HOME                             Shell scripts start and stop the applications

RUN_BASE                                                          Run file system base(%s_current_base% context variable)

PATCH_BASE                                                     Patch file system base(%s_other_base% context variable)

APPL_TOP_NE                                                  Ne base (%s_ne_base% context variable)

EBS_ORACLE_HOME                                   Oracle E-Business Suite 12.2 FMW  directory

IAS_ORACLE_HOME                                       FMW Home Directory

LOG_HOME                                                        Application Log directory

1. Log Locations of Web logic MT :

Apache  OHS log files PATH:

$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/*/*log*

OPMN log files PATH :

                $IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/*

Nodemanager Log files PATH :

$IAS_ORACLE_HOME/../wlserver_10.3/common/nodemanager

OA core log files PATH

$EBS_DOMAIN_HOME/servers/oa*/logs/*out*

OA core out files PATH:

 $EBS_DOMAIN_HOME/servers/oa*/logs/*log*

Forms out Files PATH:

$EBS_DOMAIN_HOME/servers/forms*/logs/*out*

Form log files PATH:

$EBS_DOMAIN_HOME/servers/forms*/logs/*log*

Admin server out files PATH:

$EBS_DOMAIN_HOME/servers/AdminServer/logs/*out*

Admin server log files PATH:

$EBS_DOMAIN_HOME/servers/AdminServer/logs/*log*

SYSMAN log files  PATH:

$EBS_DOMAIN_HOME/sysman/log/*

 

1. Concurrent Manager Log file Location:

$APPLCSF/$APPLLOG –LOG files

$APPLCSF/$APPLOUT—Out files

$APPLCSF/$APPLLOG/*mgr – Manager log files.

$APPLCSF/$APPLLOG/FNDSM* --Fndsm log file

$APPLCSF/$APPLLOG/<instance_name>* --ICM log file

$APPLCSF/$APPLLOG/*opp* --OPP log file .

 

1. Log location of ADOP Patch :

 

<s_ne_base>/EBSapps/log/adop/<adop_session_id>/

WORKER log file location :

<s_ne_base>/EBSapps/log/adop/<adop_session_id>/apply_<no>/context_name/<patch_no>/log/

AUTO skip log path :

<s_ne_base>/EBSapps/log/adop/<adop_session_id>/apply_<no>/context_name/<patch_no>/log/autoskil.log

Patch log location :

<s_ne_base>/EBSapps/log/adop/<adop_session_id>/apply_<no>/ context_name /<patch_no>/u<patch>.log

 

1. Start and Stop scripts Path:

$ADMIN_SCRIPTS_HOME OR $INST_TOP/admin/scripts/

Start/stop scripts log PATH:

                $LOG_HOME/appl/admin/log/