APPS DBA Scripts

Oracle Applications System Administration Scripts [ID 214088.1]
		Use These First! If the associated script does not allow you to solve your issue, then please upload the output file from the script when you first log a Service Request (SR)

Applications Performance

Locks for given Session ID

• Note: 200590.1 bde_session_locks.sql(<-- Click here for details & download)
  This script creates a report with locks for one session that is not responding (hanging), because of a Lock.




---

Validates and Rebuilds Fragmentated Indexes

• Note: 182699.1 bde_rebuild.sql(<-- Click here for details & download)
  This script validates and proceeds to generate a dynamic script to rebuild them. 




---

Analyze Indexes to determine Fragmentation

• Note: 182049.1 bde_analyze_indexes.sql(<-- Click here for details & download)
  This script will analyze indexes to determine fragmentation




---

Current, Required and Recommended Apps 11i init.ora params

• Note: 174605.1 bde_chk_cbo.sql(<-- Click here for details & download)
  This script displays the current value, the required value (if any), the recommended value (if any), and the default value (if any). 




---

Simple Explain Plan for given SQL Statement

• Note: 174603.1  bde_x.sql(<-- Click here for details & download)
  Script generates a Simple Explain Plan for one SQL statement, required to diagnose apps performance issues (transaction tuning). 




---

Expensive SQL and Resources Utilization for a Given Session ID

• Note: 169630.1 bde_session.sql(<-- Click here for details & download)
  This script creates a report with relevant information for one session that is either performing poorly or not responding (hanging). 




---

Verifies Statistics for all Installed Apps Modules 11.5

• Note: 163208.1 bde_last_analyzed.sql(<-- Click here for details & download)
  Script verifies statistics for all installed Apps modules 11.5




---

Changes CBO Stats Selectivity for a Given Index and Column

• Note: 157276.1 bde_chg_stats.sql(<-- Click here for details & download)
  Script that changes CBO stats selectivity for a given index and column 




---

Clone Views Across Instances for SQL Tuning Exercises

• Note: 156972.1 coe_view.sql(<-- Click here for details & download)
  This script facilitates cloning views across instances for SQL tuning exercises




---

Trace Apps Online Transactions with Event 10046

• Note: 156969.1 coe_trace.sql(<-- Click here for details & download)
  This script will Generates SQL Trace with bind variables and waits information for an Oracle Applications Form (equivalent of Event 10046 level 12) 




---

Automate CBO Stats Gathering

• Note: 156968.1 coe_stats.sql(<-- Click here for details & download)
  This script automates CBO stats gathering using FND_STATS and table sizes




---

Top 10 Expensive SQL from SQL Area

• Note: 156967.1  coe_sqlarea.sql(<-- Click here for details & download)
  This script scans sql area and sql text v$ dynamic performance views and displays Top n SQL Statements in terms of resources utilization. 




---

SQL Tracing online transactions using Event 10046

• Note: 156966.1 coe_event_10046.sql(<-- Click here for details & download)
  Process and Session info for one Concurrent Request




---

Session and Serial# for Locked Rows

• Note: 156965.1 coe_locks.sql(<-- Click here for details & download)
  This script displays all sessions holding a lock on a table or row. 




---

Import CBO Stats from COE_STATTAB_XYZ

• Note: 156964.1 coe_import_stattab.sql(<-- Click here for details & download)
  This script uploads data dictionary statistics from table COE_STATTAB_XYZ...




---

Enhanced Explain Plan for given SQL Statement

• Note: 215187.1 SQLTXPLAIN.sql(<-- Click here for details & download)
  Given one SQL Statement as input, generate a comprehensive report.. (sql tuning).




---

Audit Trail

Active Users executing FORMs or Conc Programs

• Note: 233871.1 FNDFindActiveUsers115.sql(<-- Click here for details & download)
  This script generates a list of active apps users with at least one open form, or executing at least one concurrent program. 




---

Trace

Enhanced Explain Plan for given SQL Statement

• Note: 179848.1 bde_system_event_10046.sql(<-- Click here for details & download)
  This script is used to turn SQL trace ON with LEVEL 12 for any concurrent program that starts its execution AFTER the EVENT 10046 is turned ON at the SYSTEM level.
  
• Note: 224270.1 TRCANLZR.sql(<-- Click here for details & download)
  Trace Analyzer - Interpreting Raw SQL Traces generated by EVENT 10046

FAQ (Concurrent Manager Unix specific) [ID 105101.1]

FAQ (Concurrent Manager Unix specific) [ID 105101.1]
  Modified 17-AUG-2011     Type FAQ     Status PUBLISHED
Checked for relevance 12-May-2010

Application Install 11.0.0 to 11.5.10

Concurrent Manager FAQs

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

Internal Concurrent Manager (ICM)

Standard Manager

Conflict Resolution Manager  (CRM)



The setup of product-specific managers related issues should

be directed to the associated product group, i.e. the Inventory manager

[top]


  Q: Where do the concurrent request output and log files reside?

 A:  $APPLCSF/out or $Product_Top/out for output files

    $APPLCSF/log or $Product_Top/log for log files

See Where do the concurrent request output and log files reside? Note:105133.1



 [top]

Q: Where are temporary files located?

 A:  Temporary files reside in:

$APPLPTMP

$APPLTMP

$REPORTS25_TMP (Release 11.0)

$REPORTS60_TMP (Release 11.5)

[top]
  Q: How to change PMON  method to LOCK.



To check the process monitor (PMON) method status:

          Connect as database user "APPS".

            SELECT profile_option_value

            FROM fnd_profile_option_values

            WHERE level_id = 10001

            AND level_value = 0

            AND application_id = 0

            AND profile_option_id =

                    (SELECT profile_option_id

                    FROM fnd_profile_options

                    WHERE profile_option_name = 'CONC_PMON_METHOD');



           This should return one row with a value of 'LOCK'



If the value is 'RDBMS' or 'OS' run the following script:



$FND_TOP/sql/AFIMPMON.SQL - this will set the PMON method to LOCK instead of RDBMS.



Bounce the database.  If running on NT, restart the NT Server completely.



Within 11.5 Applications, the only PMON method is LOCK.

[top]
   Q: How to start and shutdown the CCM on Unix as APPS

A:  To Start:

     

         STARTMGR sysmgr=APPS/passwd



      To Shutdown:



         concsub apps/ sysadmin 'System Administrator' SYSADMIN CONCURRENT FND ABORT

11.5 => 11.5.4

adcmctl.sh apps/passwd start/stop prod

11.5.5 =>

adcmctl.sh start/stop apps/passwd prod

[top]


 Q: How to terminate a concurrent request that cannot be cancelled.

A:    Identify the request number to terminate.

       Shut down the concurrent managers and issue the following sql command as applsys:

     

            update fnd_concurrent_requests

            set status_code = 'E', phase_code = 'C'

            where Request_id = ; (reqnum = request number)

[top]
  Q: A Manager is not activating.        

 A:    Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each.

        If the count is greater, change to one only. This can be done from sqlplus as  follows:



    As user apps:

                   select *

                   from SYS.DUAL;

         Any extra rows should be deleted.

                   delete rownum

                   from SYS.DUAL;

               (rownum = the row number to delete)



        Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:



                 delete from fnd_dual

                 where rownum < (select max(rownum) from fnd_dual);



Bounce the Concurrent Managers.


[top]

 .  Q: Internal Concurrent Manager.        

A:   If the ICM should go down, requests will continue to run normally, except for 'queue control' requests.



      If the ICM should go down, you can restart it with 'startmgr'. There is no need to shut down the other managers first.



[top]

 Q: How to purge requests that are in Pending status.        

A:  The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests  to Completed

     before purging them. The Internal Manager Must be DOWN!



     Using sqlplus as APPLSYS perform the following:



              UPDATE fnd_concurrent_requests

              SET phase_code = 'C'

              WHERE phase_code = 'P'

cmdailylong.sql see the top jobs based on total runtime > xx minutes for today first parameter is the nbr of minutes runtime to use

/* cmdailylong.sql
see the top jobs based on total runtime > xx minutes for today
first parameter is the nbr of minutes runtime to use
SDR-Oracle 12/5/02 Created from various other scripts
*/
set pages 66
set lines 132
set verify off
col program form A35 trunc head "Program Full Name"
col intprog format a35 trunc head "Internal Name"
col RunTime form 99999
col actstart format a6 head Started
col actcompl format a6 head Finishd
col qname head "Queue" format a15 trunc

select q.concurrent_queue_name qname,
       c.concurrent_program_name  ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
       ctl.user_concurrent_program_name "program",
request_id,
phase_code,
status_code,
to_char(actual_start_date,'hh24:mi') actstart,
to_char(actual_completion_date,'hh24:mi') actcompl,
      ((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) "RunTime"
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 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'
  and trunc(actual_completion_date) = trunc(sysdate)
  and actual_start_date is not null
  and actual_completion_date is not null
  and ((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) > &1
order by 9 desc;

find the number of jobs requested for a queue by each hour in one day useful to find out if the nbr of workers is sufficient for the requests

/* cmqhourly.sql
        find the number of jobs requested for a queue by each hour in one day
useful to find out if the nbr of workers is sufficient for the requests
SDR-OCS 10/16/02 Creation based on archlogs.sql and cmqjobs.sql
*/
set verify off
set lines 160
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
col ccpid head ProgID-Name format a15 trunc
break on report
compute sum of 11PM on report
compute sum of 10PM on report
compute sum of 9PM on report
compute sum of 8PM on report
compute sum of 7PM on report
compute sum of 6PM on report
compute sum of 5PM on report
compute sum of 4PM on report
compute sum of 3PM on report
compute sum of 2PM on report
compute sum of 1PM on report
compute sum of Noon on report
compute sum of 11AM on report
compute sum of 10AM on report
compute sum of 9AM on report
compute sum of 8AM on report
compute sum of 7AM on report
compute sum of 6AM on report
compute sum of 5AM on report
compute sum of 4AM on report
compute sum of 3AM on report
compute sum of 2AM on report
compute sum of 1AM on report
compute sum of MidN on report
accept trgtqname char default NONE prompt 'Limit to which ccmgr queue name : '
accept trgtprog char default ALL prompt 'Limit to which ccmgr job name : '
accept trgtdte char default TODAY prompt 'Limit to what date (dd-mon-yyyy) : '
accept trgtproc char default ALL prompt 'Limit to which ccmgr os pid : '
select g.concurrent_program_id || '-' || g.concurrent_program_name ccpid,
sum(decode(to_char(requested_start_date,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(requested_start_date,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(requested_start_date,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(requested_start_date,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(requested_start_date,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(requested_start_date,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(requested_start_date,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(requested_start_date,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(requested_start_date,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(requested_start_date,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(requested_start_date,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(requested_start_date,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(requested_start_date,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(requested_start_date,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(requested_start_date,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(requested_start_date,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(requested_start_date,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(requested_start_date,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(requested_start_date,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(requested_start_date,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(requested_start_date,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(requested_start_date,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(requested_start_date,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(requested_start_date,'hh24'),'23',1,0)) "11PM"
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager (+) = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and trunc(r.requested_start_date) = decode(upper('&trgtdte'),'TODAY',trunc(sysdate),'&trgtdte')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
   and (p.os_process_id = '&trgtproc' or upper('&trgtproc') = 'ALL')
group by g.concurrent_program_id || '-' || g.concurrent_program_name
order by 1
/          

prompt Getting queue statistics.....
set verify off
set lines 132
prompt Note - times are expressed in decimal minutes
col qname head "Activated Concurrent Queue" format a26
col actual head "Actual" format 999999
col target head "Target" format 999999
col minr head MinRTime format 9999.99
col maxr head MaxRTime format 9999.99
col avgr head AvgRun format 9999.99
col avgq head AvgQTime format 9999.99
col totreq head "# Jobs" format 99999
col qname head "Queue Name" format a25 trunc
clear breaks
clear computes
select  q.concurrent_queue_name || '(' || q.target_node || ')' qname,
count(r.request_id) totreq,
       q.running_processes actual,
       q.max_processes target,
       avg((r.actual_start_date - r.requested_start_date)*1440)   avgq,
       min((r.actual_completion_date - r.actual_start_date)*1440) minr,
       max((r.actual_completion_date - r.actual_start_date)*1440) maxr,
       avg((r.actual_completion_date - r.actual_start_date)*1440) avgr
  from applsys.fnd_concurrent_requests r,
       applsys.fnd_concurrent_processes p,
       applsys.fnd_concurrent_programs g,
       applsys.fnd_concurrent_queues q
 where r.controlling_manager = p.concurrent_process_id
   and p.queue_application_id = q.application_id
   and p.concurrent_queue_id = q.concurrent_queue_id
   and trunc(r.requested_start_date) = decode(upper('&trgtdte'),'TODAY',trunc(sysdate),'&trgtdte')
   and (upper(q.concurrent_queue_name) like upper('%&trgtqname%') or upper('&trgtqname') = 'ALL')
   and (upper(g.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
   and r.concurrent_program_id = g.concurrent_program_id
   and r.program_application_id = g.application_id
group by q.concurrent_queue_name || '(' || q.target_node || ')',
       q.running_processes, q.max_processes;

Certified Oracle Solaris and SPARC Virtualization and Partitioning Technologies for Oracle E-Business Suite

Certified Oracle Solaris and SPARC Virtualization and Partitioning Technologies for Oracle E-Business Suite

Oracle E-Business Suite has been supported on hardware vendor virtualization technologies (running on non x86/x86-64 architectures) following the 'supported but not explicitly certified' principle as outlined in the My Oracle Support Document 794016.1.

Oracle E-Business Suite is comprised of both an application tier (applications code and Fusion Middleware components) as well as a database tier (Oracle Database) - any limitations of the Oracle Database or Fusion Middleware with respect to the use of virtualization technologies necessarily also apply to Oracle E-Business Suite's tiers (see References section below for links).

All requirements listed in the Oracle E-Business Suite Installation and Upgrade Notes (see References section below) must be met in order to use E-Business Suite with these virtualization technologies.

Certifications for E-Business Suite Release 12

The following are the specific technologies certified explicitly for Oracle E-Business Suite Release 12:

Dynamic System Domains

Dynamic System Domains is a hardware partitioning technology (with electrical and physical isolation) built in to the Sun SPARC Enterprise M-Series server family. Dynamic Reconfiguration technology is used with Dynamic System Domains in order to reallocate resources between different domains (such as removing and adding CPUs, memory and I/O subsystems). See this white paper for information on the use of these technologies.

Oracle E-Business Suite Release 12 (12.0.4 or higher, 12.1.1 or higher) is certified with this hardware partitioning technology for Sun SPARC Enterprise M-Series servers running Oracle Solaris on SPARC versions 9 or 10.

Oracle VM Server for SPARC

Oracle VM Server for SPARC (previously known as Logical Domains or LDoms) is a hypervisor-based virtual partition technology available on Sun SPARC T-Series servers. It allows for a single physical server to be partitioned into domains each with its own access to resources while running a separate instance of the operating system. See this data sheet for more information.

Oracle VM Server for SPARC version 1.3 or higher is certified with Oracle E-Business Release 12 (12.0.4 or higher, 12.1.1 or higher) running Solaris 10 10/09 (Update 8) or higher. The Fusion Middleware versions bundled with EBS must be upgraded to 10gR2 (10.1.2.3 or higher) and 10gR3 (10.1.3.4 or higher).

Oracle Solaris Containers

Oracle Solaris Containers is an OS partitioning technology specific to the Solaris operating system - it allows for multiple instances of 'local' hosts running within a single global instance of an OS.

Solaris Containers version Solaris 10 10/09 (Update 8) or higher is certified with Oracle E-Business Suite Release 12 (12.0.4 or higher, 12.1.1 or higher). The Fusion Middleware versions bundled with EBS must be upgraded to 10gR2 (10.1.2.3 or higher) and 10gR3 (10.1.3.4 or higher).

On the application tier, both local and global Containers are certified with the E-Business Suite.

On the database tier, please refer to the specific requirements and limitations listed in the 'Supported Virtualization and Partitioning Technologies for Oracle Database and RAC Product Releases' document.

Solaris Branded Zones

The use of Branded Zones (specifically Solaris 10 Zones) for use with the SPARC-based Oracle Exalogic Elastic Cloud T3-1B system is certified for the E-Business Suite R12 (12.1.1 or higher) application tier. A Solaris 10 zone is an instance of Solaris 10 running as a container within a native Solaris 11 Express operating system (see the System Administration Guide in the References section below) which is the Solaris version installed on the Oracle Exalogic Elastic Cloud T3-1B.

Note that Oracle Solaris 11 Express (SPARC) is not a fully certified Oracle E-Business Suite platform - there are plans to certify E-Business Suite Release 12 (both the application and database tiers) on Solaris 11 when it becomes generally available in the future.

All requirements pertaining to the use of E-Business Release 12 on Solaris 10 applies to Solaris 10 Zones with the addition of the following requirements as outlined in the current E-Business Suite Installation and Upgrade Notes for Oracle Solaris on SPARC (64-bit):

Patch 127111-11 which supersedes 127111-02
Patch 137111-09 which supersedes 137111-04
The Oracle Database is not supported or certified to run on a Solaris 10 branded zone within Solaris 11 Express - the use of an Oracle E-Business Suite R12 application tier in a Solaris 10 zone on Oracle Exalogic T3-1B must be in conjunction with running the database tier on a native Solaris 10 or other E-Business Suite certified database tier platform.



Support for E-Business Suite Release 11i

Oracle E-Business Suite 11i's terminal release (11.5.10.2) has been in Extended Support since December 2010 (see the Oracle Lifetime Support web page for details).

For Dynamic Systems Domains, Oracle VM Server for SPARC and Solaris Containers, E-Business Suite 11i continues to be supported under the previous 'supported but not explicitly certified' philosophy as outlined in the My Oracle Support Document 794016.1.

References

Hardware Vendor Virtualization Technologies on non x86/x86-64 Architectures and Oracle E-Business Suite (My Oracle Support Note 794016.1)
Supported Virtualization and Partitioning Technologies for Oracle Fusion Middleware
Supported Virtualization and Partitioning Technologies for Oracle Database and RAC Product Releases
Oracle's Optimized Solution for Mission Critical Databases - A Technical White Paper
Oracle VM Server for SPARC
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Oracle Solaris on SPARC (64-bit) (My Oracle Support Note 761568.1)
Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Solaris Operating System (SPARC) (My Oracle Support Note 402312.1)
System Administration Guide: Oracle Solaris Zones, Oracle Solaris 10 Containers, and Resource Management
Oracle Announces SPARC Solaris-Based Exalogic Elastic Cloud System
Change Record

Troubleshoot long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in  Oracle Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

.

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 2355) 

Step 2 : Run below command to find SPID, provide concurrent request ID (2355 in my case) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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’;

REQUEST_ID        SID    SERIAL# OSUSER           PROCESS    SPID
—————-
   2355        514         28 applmgr                        17794   1633.
. 

Step 3.1 : Check and confirm SPID on Database Node
oraclevis11i@onlineappsdba>ps-ef | grep 1633
 ovis11i  1633     1  0 13:30:43 ?        0:03 oraclevis11i (LOCAL=NO)

Step 3.2 : Set OSPID (1633 in my case) for ORADEBUG
SQL> oradebug setospid 1633
—
Oracle pid: 68, Unix process pid: 1633, image: oraclevis11i@onlineappsdba
—

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name

/oracle/apps/vis11idb/10.2.0/admin/vis11i_onlineappsdba/udump/vis11i_ora_1633.trc

Wait for 15-20 minutes

Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like
tkprof  ‘/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ vis11i_ora_1633.trc’   ’/oracle/ apps/ vis11idb/ 10.2.0/ admin/ vis11i_onlineappsdba/ udump/ tkprof_1633.txt’ explain=apps/[apps_passwd]  fchela …

Step 6 : Check TKPROF file to find root cause of slow concurrent requet

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

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

WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)

Many times I faced this issue “WARNING: inbound connection timed out (ORA-3136)” in alert log and if you are using data guard, then you can see that dgmgrl status will show failed because standby is not able to sync up with primary.

The “WARNING: inbound connection timed out (ORA-3136)” in the alert log indicates that the client was not able to complete it’s authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

Root cause of this issue could be

1. malicious client is flooding database servers with connect requests that consumes resources

2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.

3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified

As a workaround to avoid only this warning messages, you can set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT=0 in $ORACLE_HOME/network/admin/sqlnet.ora file

Once you set this parameter the warnings in alert log will go away. If you are using data guard, may be you want to enable the databases on which you were getting warnings.

Hope this helps !!