About Me.

My self Adinarayana working as Implementation Application DBA with advanced technologies like RAC/PCP,OID/SSO,DMZ,Exadata and Fusion Middleware i.e Demantra,Application Server,SOA,FMW,BPEL and UPK. Created this blog to share the useful information related to DBA and Application DBA Your comments and suggestions are most welcome. Disclaimer: Please note all the views and opinions expressed in this site are my own. It's not recommend to use the fixes/suggestions provided in this site directly in production instance, please test them before implementing.

Thursday, February 9, 2017

Oracle Applications user Trace

a. First make sure the necessary profile has the proper permissions.
        1. Log onto the Applications Forms with the Application Developer Responsibility
        2. Navigate to the Profile menu
        3. Query up the profile name "FND_INIT_SQL"
        4. In the bottom block of the form, make sure that ALL checkboxes are checked
    Typically, you will have to enable the checkboxes under "User Access" to make it "Visible" and "Updatable".

b. Now switch to the System Administrator Responsibility

 1. Navigate to - Profile - System
 2. On the "Find System Profile Values" form, make sure the checkboxes for "User" and "Profiles with no Values" are checked
 3. Beside the "User" checkbox, use the LOV to select the user who's activity you need to trace
 4. In the "Profile" field, enter the following profile and click the <Find> button:
 'Initialization SQL Statement - Custom'
 5. In the "System Profile Values" form, enter the following in the User Field: (This is one line and all single quotes)

 begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'' tracefile_identifier=''OracleSupport'''); end;

 6. DO NOT SAVE THE PROFILE YET

 7. In another browser window, login as the user you are going to trace and prepare to reproduce the problem
 8. Once you are ready to reproduce the problem, go back to the Applications Forms and Save the profile change
 9. Reproduce the problem
 10. Back in the Applications form, set profile to null so it does not trace anymore and Save the change
 11. The trace will be located in the user_dump_dest. To find location run the following in SQL*Plus: select value from v$parameter where name = 'user_dump_dest';
 12. The trace file will have current date/time and can be identified with the word OracleSupport in it.

Managed Server addition in EBSO 12.2

Step 1. perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-create-managedserver -contextfile=<RUN_CONTEXT_FILE> -managedsrvname=oacore_server2 -servicetype=oacore 
-managedsrvport=<port no> -logfile=<LOGFILE>

Step 2. perl <FND_TOP>/patch/115/bin/txkSetAppsConf.pl -ctxfile=$CONTEXT_FILE -outfile=<OUT_FILE>

Step 3. Run fs_clone

Thursday, January 19, 2017

Important Performance Tunning Scripts

How much work done:
*******************
select sid,sofar,totalwork,time_remaining from V$SESSION_LONGOPS where sofar < totalwork;

Blocking sessions:
****************
**** Run below query in SQL Developer and check for Blocking session details ****

Query 1#
********

 select a.session_id SID, a.oracle_username, a.os_user_name, a.locked_mode,
b.object_name, b.owner, b.object_type, '**SID ' || l1.sid  ||' IS BLOCKING ' ||  l2.sid  || '**' "Blocking Session Details"
from gv$locked_object a, dba_objects b, gv$lock l1, gv$lock l2
where a.object_id = b.object_id and l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

Blocking session Info:
========================
select  'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

Query 2#
********

set linesize 200
SELECT DECODE(request,0,'Holder: ','  Waiter: ')||sid sess,id1,id2,inst_id, lmode, request,type FROM GV$LOCK    WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1,request;

Output:
*******
SESS                                                      ID1        ID2    INST_ID      LMODE    REQUEST TY
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- --
Holder: 973                                           5177344     629773          2          6          0 TX
  Waiter: 9108                                        5177344     629773          2          0          6 TX
Holder: 9108                                         19726357     124651          2          6          0 TX
  Waiter: 9130                                       19726357     124651          1          0          6 TX
  Waiter: 5959                                       19726357     124651          2          0          6 TX

Based on SID:
**************  
set verify  off  
set head off      
set feedback on
set long 5000
select distinct
'========================================================='||chr(10)||
'Sid , Serial#     : '||S.Sid   ||' , '||S.Serial#  ||Chr(10)||
'Server/Shadow     : '||P.Spid    ||Chr(10)||
'Client/Foreground : '||S.Process   ||Chr(10)||
'Terminal / Machine: '||S.terminal||' / '||S.Machine   ||Chr(10)||
'Username........: '||S.Username   ||Chr(10)||
'Osuser..........: '||S.Osuser   ||Chr(10)||
'Program.........: '||S.Program    ||Chr(10)||
'Module..........: '||S.Module    ||Chr(10)||
'Status..........: '||S.Status  ||Chr(10)||
'Action..........: '||S.Action   ||Chr(10)||
'Wait_time.......: '||W.Wait_time   ||Chr(10)||
'State ..........: '||W.State   ||Chr(10)||
'Wait Event .....: '||W.Event   ||Chr(10)||
'Seconds_in_wait.: '||W.Seconds_in_wait   ||Chr(10)||
'Pga_alloc....: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb'  ||Chr(10)||
'Pga_used.....: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_free.....: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb'  ||Chr(10)||
'Pga_max......: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99') ||' Mb'  ||Chr(10)||
'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None')   ||Chr(10)||
'Blocking Sessi=on: '||s.blocking_session||Chr(10)||
'Blocking Session Status: '|| s.blocking_session_status||Chr(10)||
'Latch Spin...: '|| Nvl(P.Latchspin, 'None')   ||Chr(10)||
'Logon Time...: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss')   ||Chr(10)||
'Last Call....: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || '  -> ' || To_char(S.Last_call_et/60, '99999.0') || ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') || ' Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days'  ||Chr(10)||
'Sql Address. : '||S.Sql_address   ||Chr(10)||
'Sql Hash.... : '||S.Sql_hash_value   ||Chr(10)||
'Prev Sql Hash: '||S.Prev_hash_value   ||Chr(10)||
'Trans Status : '|| Nvl(T.Status,'None') || Chr(10)||
'Trans Active : '|| Nvl(S.Taddr, 'None')||Chr(10)||
'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'||Chr(10)||
'Changed Blocks : '||I.Block_changes||' Blocks'||Chr(10)||
'............    Current  Sql Statment   .................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q.Sql_text,'No Current Sql Statment')    ||Chr(10)||
'========================================================='||Chr(10)||
'................  Prev Sql Statment  ..................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q2.Sql_text,'No Sql Statment')    ||Chr(10)||
'========================================================='
from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2 ,gv$session_wait w ,
gv$transaction t, gv$sess_io i
where s.inst_id = p.inst_id
and p.addr=s.paddr
and s.inst_id = i.inst_id
and s.sid=i.sid
and s.sid=nvl('&sid',s.sid)
and s.inst_id = w.inst_id
and s.sid=w.sid
and p.spid=nvl('&spid',p.spid)
and q.inst_id(+)   = s.inst_id
and q.HASH_VALUE(+)=s.sql_hash_value
and q2.inst_id(+)   = s.inst_id
and q2.hash_value(+)=s.prev_hash_value
and s.inst_id = t.inst_id(+)
and s.taddr=t.addr(+)
and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

To get sid details:
===================
select sid,serial#,inst_id,username,status,machine,program,type,action,event from gv$session where sid=&a;

To get spid:
============
select sid, b.serial#, osuser, b.username,b.program, status, process, paddr, spid from gv$process a,gv$session b where sid = '&sid' and a.addr = b.paddr and b.username != ' ' order by 1;

To get concurrent requests details:(Get spid from above query)
==============================================================
set lines 180
set pages 1000
set verify off
set numwidth 100
undef spid
column req_id format 999999
column OPID format a10
column PPID 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
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
                        and d.spid=&&spid) 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
/

Get the details about the requests using below query.

##QUERY##

set lines 180
set pages 300
col name format a20
col QUEUE for a20
col U_NAME for a20
select fcr.request_id req_id,
substr(fcq.concurrent_queue_name, 1, 20) queue,
to_char(fcr.actual_start_date,'hh24:mi') s_time,
substr(fcr.user_concurrent_program_name, 1, 60) name,
substr(fcr.requestor, 1, 9 ) u_name,
round((sysdate -actual_start_date) *24, 2) elap,
decode(fcr.phase_code,'R','Running','P','Inactive','C','Completed', fcr.phase_code) Phase,
substr(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', 'TERMINATED',
'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
'UNKNOWN'), 1, 10)
from
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
apps.fnd_conc_req_summary_v fcr
where fcp.concurrent_queue_id = fcq.concurrent_queue_id
and fcp.queue_application_id = fcq.application_id
and fcr.controlling_manager = fcp.concurrent_process_id
and fcr.request_id = '&RequstID'
order by request_id ;

select fcr.request_id req_id,
fcr.phase_code||'/'||fcr.status_code sta,
fcr.pid pid,
sess.sid sid,
sess.inst_id inst_id,
fcr.running_time time,
nvl(t.used_urec,0) u_rec,
substrb(fcr.user_desc,1,8) user_desc,
substrb(decode(fcr.pgm_code,
'FNDRSSUB',fcr.pgm_name||'-'||rs.user_request_set_name,
'FNDRSSTG',fcr.pgm_name||'-'||rs.user_request_set_name
||'-'||rss.user_stage_name,
fcr.pgm_name), 1,48) pgm_name
from gv$session sess,
gv$transaction t,
fnd_request_sets_vl rs,
fnd_request_set_stages_vl rss,
(select /*+ ordered
index (r fnd_concurrent_requests_n7)
index (pt fnd_concurrent_programs_tl_u1) */
r.request_id request_id,
r.program_application_id application_id,
c.os_process_id pid,
r.oracle_session_id audsid,
r.concurrent_program_id concurrent_program_id,
p.concurrent_program_name pgm_code,
u.description user_desc,
decode(p.concurrent_program_name,
'FNDRSSUB','SET',
'FNDRSSTG','Set Stage',
pt.user_concurrent_program_name) pgm_name,
r.phase_code phase_code,
r.status_code status_code,
to_char(r.request_date,'yymmdd hh24:mi:ss') request_date, to_char(r.actual_completion_date,'yymmdd hh24:mi:ss') actual_completion_date,
to_char(r.requested_start_date,'yymmdd hh24:mi:ss') requested_start_date,
ceil((nvl(r.actual_completion_date,sysdate)-r.actual_start_date)*1440) running_time,
r.actual_start_date actual_start_date_org,
u.user_id user_id,
u.user_name user_name,
r.argument_text arguments,
decode(p.concurrent_program_name,
'FNDRSSUB',r.argument2,
'FNDRSSTG',r.argument2,
-1) request_set_id,
decode(p.concurrent_program_name,
'FNDRSSTG',r.argument3,
-1) request_set_stage_id
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs p,
fnd_user u,
fnd_concurrent_processes c
where r.requested_by = u.user_id
and r.program_application_id = pt.application_id
and r.concurrent_program_id = pt.concurrent_program_id
and pt.language = 'US'
and pt.application_id = p.application_id
and pt.concurrent_program_id = p.concurrent_program_id
and r.controlling_manager = c.concurrent_process_id
and r.phase_code = 'R'
and r.status_code = 'R') fcr
where fcr.audsid = sess.audsid(+)
and sess.saddr = t.ses_addr(+)
and fcr.request_set_id = rs.request_set_id(+)
and fcr.request_set_id = rss.request_set_id(+)
and fcr.request_set_stage_id = rss.request_set_stage_id(+)
order by fcr.actual_start_date_org desc, fcr.running_time, fcr.request_id desc;

Total work done:
================
SQL> select sid,serial#,sofar,totalwork from gv$session_longops where sid=1751;

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
      1751      27743      82170      82170
      1751      27743      91317      91317
      1751      27743     195210     195210
      1751      27743      91781      91781
      1751      27743     141648     141648
      1751      27743      82115      82115
      1751      27743      91744      91744
      1751      27743     195181     195181
      1751      27743     103149     103149
      1751      27743      84657      84657
      1751      27743     159815     159815
      1751      27743     185505     185505
      1751      27743      69919     141598
      1751      27743      91803      91803
      1751      27743      84672      84672
      1751      27743      91803      91803
      1751      27743     103155     103155
      1751      27743     159840     159840

18 rows selected.

SQL >select * from gv$session where process in(select os_process_id from fnd_concurrent_requests where request_id='&request');

Get SID from above sql.
Take that sid and find object_id's from below command
SQL >select * from v$locked_objects where session_id='SID'

Now take all the object_id's and check any other session is locking those object_ids apart from the above SID...
SQL >select * from v$locked_objects where object_id =''

if any session is locking check the status of that session from v$session.
if that is inactive kill that session ... to free up the resource

SQL >alter system kill session 'SID,SERIAL#';

Query 3#
********
select sid, b.serial#, osuser, b.username,b.program, status, process, paddr, spid from gv$process a,gv$session b where sid = '&sid' and a.addr = b.paddr and b.username != ' ' order by 1;
/

       SID    SERIAL# OSUSER                         USERNAME PROGRAM                        STATUS   PROCESS                  PADDR            SPID
---------- ---------- ------------------------------ -------- ------------------------------ -------- ------------------------ ---------------- ------------------------
       973      27137 appvtasi                       APPS     JDBC Thin Client               ACTIVE   114454                   0000001F755F05A0 918
       973      27137 appvtasi                       APPS     JDBC Thin Client               ACTIVE   114454                   0000001F755F05A0 8791

select sid,serial#,username,status,machine,program from gv$session where sid=&a;  
 
select INST_ID,SID,OBJECT,TYPE from gv$access where SID=

SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE file_id = &file_no AND ( &block_value BETWEEN block_id AND ( block_id + blocks));

Query 4#
********
SQL> set lines 200
set pages 200
spool DB_Locks.lst
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
spool off

Output:
=======
BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS                                                       SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
             370       4401      13493 Idle                                                                           1
             973       9130      33099 Application                                                                  588

Query 5#
********
SQL> select sql_text from v$sql where sql_id='8wtrwg1u27uyy';

Thursday, January 28, 2016

SOA Doc id's

Troubleshooting SOA Suite 11gR1 Installation for Versions 11.1.1.0 and Higher (Doc ID 1292813.1)

1.Frequently Asked Questions While Preparing to Install SOA Suite 11g

Note 853543.1 Kick starting SOA 11g Installation - Information About Downloads and Installation Steps, RCU as Well as Configuring and Starting the Servers.
Note 860428.1 Is BPEL Available as a Standalone Installer in 11GR1?
Note 1108487.1 Can SOA 11g be Installed With an XE database?
Note 1280585.1 ORABPEL-35010 Unable to Encrypt data During Start if Installing SOA and WebCenter into Same MWHome.
Note 1073776.1 Steps to Maintain Oracle Fusion Middleware 11g (11.1.1)
Note 579872.1 What Documents Should Be Read Before Installing a BPEL, ESB or SOA Suite for High Availability Cluster?

2. Troubleshooting Repository Creation Utility / RCU

For documentation related to RCU check the Oracle Fusion Middleware Repository Creation Utility User's Guide for information on how to obtain RCU and on how to create the SOA-related schemas. Answers to specific questions related to RCU and solutions for common issues can be found in the following doucments:

Note 1304790.1 Oracle Fusion Middleware 11g Repository Creation Utility (RCU) - FAQ.
Note 1203465.1 How To Find RCU Schema Names And Their Versions in FMW 11g?
Note 1290493.1 Can SQL Scripts be Executed Instead of RCU Utility in Order to Create SOA/BPM Schemas?
Note 1209587.1 RCU Is Failing to Connect to the Database with Error "Invalid username/password"
Note 1298609.1 OSB or SOA Suite Installation - How to create Schemas on Solaris, HP/UX, AIX?
Note 879950.1 FMW 11g RCU Database Connection Timeout - "Unable to connect to the database using the provided details"
Note 1054399.1 How To Automatically Supply Passwords To RCU Silent Installation.
Note 1286233.1 Oracle Fusion Middleware - Release Notes (11.1.1.4):
2.1.14 Installing the SOAINFRA Schema with DBA Permissions
2.1.18 Database Connection Failure During Schema Creation
6.2.14 Access Control Exception After Expanding Cluster Against an Extended Domain
37.2.28 Benign Exception May Appear When Using Repository Creation Utility to Seed Schedule Jobs
40.1.1.4 Creating a New Audit Schema
Note 1269247.1 BPM 11g and SOA 11g patch installation troubleshooting and FAQ
Some Oracle products (such as RCU or the Opatch itself) are installed in a different ORACLE_HOME (i.e C:\Oracle\Middleware\oracle_common) - How to install a patch for these?
Note 1110883.1 CFGFWK-60850 and CFGFWK-60853 Error Occurs When Testing Schemas In SOA Domain Configuration Wizard.
Note 1173979.1 SOA 11.1.1.3 Log Files are Full of JDBC Errors [OWLS][SQLServer JDBC Driver][SQLServer]Invalid object name While Creating Schema in Microsoft SQL Server 2005.
Note 1068044.1 ORA-01875 and ORA-06512 Errors While Running RCU With Timezone Setting Different Than US.
Note 1108487.1 Can SOA 11g be Installed With an XE database?
Note 1354696.1 Repository Creation Utility Errors Out With Sql Server 2008 Express Edition.

3. Troubleshooting Installation

Troubleshooting tasks 3, 4 and 5 of the SOA Suite Installation Roadmap cover installation of WebLogic Server and SOA Suite. In this section we also have information for troubleshooting installation of Adapters with SOA Suite. Also question how to install SOA Suite together with Oracle Service Bus (OSB) and an issue that may happen if installing WebCenter into the same Fusion Middleware Home as SOA Suite. Finally, some frequently asked questions for OPatch and patching.
   
a. Troubleshooting WLS and SOA Suite Installation :

For troubleshooting WLS installation topics, the following document will be helpful:
Note 1298931.1 Master Note on WebLogic Server (WLS) Installation and Configuration.

 b. Adapters in a SOA 11g Installation :

This sections lists issues related to adapter installation used within a 11g SOA Suite.
Note 1261854.1 Adapters 11.1.1.3.0: Can Not be Installed on Windows.
Note 1189583.1 11g Oracle Application Adapters Install Fails with "could not load wizard specified in /wizard.inf (104)"
      c. How to Install SOA Suite and OSB into the Same Fusion Middleware Home?

This section has information about topics arising if SOA Suite is installed into the same Fusion Middleware Home as OSB, or if you want to connect both products.
Note 1270686.1 Can you install an OSB cluster in the same domain as a SOA cluster?
Note 1267207.1 Troubleshooting Interoperability Between OSB and SOA Suite.
Note 788141.1 How to Use the Oracle Service Bus (OSB) Native BPEL Transport to Call a BPEL Process.
      d. Troubleshooting while Installing WebCenter and SOA Suite into the Same Fusion Middleware Home

Note 1280585.1 ORABPEL-35010 Unable to Encrypt data During Start if Installing SOA and WebCenter into Same MWHome.
      e. Frequently Asked Questions Related to OPatch and Patching

This section has information about issues around opatch used to apply patches for SOA Suite or BPEL Process Manager.
Note 1221133.1 How to install HOTFIX using OPATCH in BPM 11g / SOA 11g?
Note 1288864.1 How to apply patches using "opatch" to combined BPM/SOA/JDEV environments.
Note 1269247.1 BPM 11g and SOA 11g patch installation troubleshooting and FAQ.
Note 454549.1 OPATCH Error "Cannot look for invPtrLoc pattern", "error code = 100".

4. Troubleshooting Domain Creation and Configuration Wizard

 The referenced documents have additional information and solutions.
Note 1090630.1 SOA 11g Enterprise Deployment Guide (EDG) Cluster Installation/Config: Failed to Start Service "Cluster".
This document describes an issue while starting managed servers related to Coherence and it's solution.
Note 1264302.1 SOA-INFRA Unable To Start In Weblogic Cluster: Coherence GE 3.3.2/391 <Error>.


5. Troubleshooting Administration and Managed Server Startup

This section lists next steps after your installation is completed that are needed to ensure proper configuration of your system. During a first start of your Admin- and Managed Servers belonging to your SOA Suite installation issues may happen. The documents will help to troubleshoot some of the known issues in this area.

For information on installing JDeveloper and SOA extensions check Oracle Fusion Middleware Installation Guide for Oracle JDeveloper and Enabling Oracle JDeveloper Extensions. If download of SOA extensions is not possible from the JDeveloper Help Menu, you can alternately download them from Oracle JDeveloper Extensions - Details for Oracle SOA Composite Editor, then install from a local file as described in the documentation.

Note 1224953.1 SOA 11.1.1.3 - After Install, The SOAINFRA Application Does Not Deploy With Error Message "ORABPEL START-UP ERROR!!!!!!!!".
Note 1090630.1 SOA 11g Enterprise Deployment Guide (EDG) Cluster Installation/Config: Failed to Start Service "Cluster".
Note 1264302.1 SOA-INFRA Unable To Start In Weblogic Cluster: Coherence GE 3.3.2/391 <Error>.
These two documents describe an issue related to Coherence that will show up during start of your servers and it's solution. Check both documents to resolve the Coherence error.
Note 1224953.1 SOA 11.1.1.3 - After Install, The SOAINFRA Application Does Not Deploy With Error Message "ORABPEL START-UP ERROR!!!!!!!!"
Note 741373.1 How to Verify the Exact Version of BPEL/ESB Installed with Oracle SOA Suite?
Note 880313.1 How to Configure Transaction Timeout for BPEL on SOA 11g?
Note 748473.1 Tuning SUN Java Virtual Machine (JVM) for BPEL 10.1.3 and SOA Suite 11g .
Note 748289.1 Troubleshooting JVM Related Issues for SOA Suite.
Note 783157.1 How to Start a ManagedServer for SOA From the Command Line Instead of the WebLogic Admin Console?
Note 1236286.1 How to Start SOA Suite Domain AdminServer as Windows Service and soa_server Through NodeManager?
Note 1274476.1 Managed Server Does Not Start After Adding This Managed Server to an Existing Domain.
Note 1291516.1 The Health Status of soa-infra Application Deployment in Warning State After SOA 11gR1 PS 3 Upgrade.
Note 1288692.1 SOA Extensions are not Installed Properly in JDeveloper 11.1.1.4.0.
Note 1303698.1 SOA - "ORA-28001: the password has expired" Errors - How to Change the Repository Database Passwords for the SOA Schemas?
6. References to Troubleshooting Documents for AIA Installation on Top of SOA Suite 11g

Note 1200173.1 Application Integration Architecture Foundation Pack 11g Release 1 Master Support Note.
Note 1225466.1 Requirement to Install AIA Foundation Pack 11gR1 (11.1.1.2) with SOA Suite 11g (11.1.1.3).
Note 1130226.1 Errors While Installing AIA 11gR1 Install Relating To Missing Metadata.
Note 852222.1 Installing AIA Foundation Pack on Oracle SOA Suite Cluster Deployments.
7. Additional Reading

After the installation is completed, the following documents may be helpful with next steps.
Note 1070613.1 Oracle SOA Suite 10g High Availability FAQ and Troubleshooting Guide.
Note 1175789.1 Master Note for SOA 11g Clustering and High Availability.
Note 1297250.1 Purge Scripts Master Note - Oracle BPEL Process Manager, SOA Suite and Business Process Management Suite.
Note 1076329.1 Oracle SOA Suite 10g XA and RAC Database Configuration Guide.
Note 1286231.1 Oracle 10g SOA Suite Best Practices Guide.
Note 1290512.1 Oracle SOA Suite and Business Process Management Suite (11.1.1.4) - Release Notes E10133-23 Excerpt.

8. Still Have Questions?

If the information in this document did not help to resolve your issue, search in our knowledge base for other known issues related to error messages you find in your log file or the issue you have. We add new knowledge articles all the time that may not yet be included here.

If you identify any additional issues,  please add a comment to this note (use the feedback link on the right) so we can include the problem/solution.

Open a Service Request with our Support to get help in analyzing your issue. Make sure to document the steps you have already done to analyze and fix your issue to avoid duplication of efforts.Check Note 1274493.1 How to Locate SOA 11g configuration Files and Log Files to locate all related log and configuration files.

REFERENCES

NOTE:1054399.1 - How To Automatically Supply Passwords To RCU Silent Installation
NOTE:1068044.1 - Using The Repository Creation Utility (RCU) Returns An ORA-01875 Error
NOTE:1070613.1 - Oracle SOA Suite 10g High Availability FAQ and Troubleshooting Guide
NOTE:1073776.1 - Steps to Maintain Oracle Fusion Middleware 11g Release 1 (11.1.1)
NOTE:1076309.1 - Information Center: Oracle BPEL Process Manager 11g
NOTE:1076329.1 - Oracle SOA Suite 10g XA and RAC Database Configuration Guide
NOTE:1090630.1 - SOA 11g Enterprise Deployment Guide (EDG) Cluster Installation/Config: Failed to Start Service "Cluster"
NOTE:1108487.1 - Can SOA 11g be Installed with an XE database?
NOTE:1110883.1 - CFGFWK-60850 and CFGFWK-60853 Error Occurs When Testing Schemas In SOA Domain Configuration Wizard
NOTE:1130226.1 - AIA 11gR1 Install fails while MetaData deployment with Exception oracle.fabric.common.wsdl.XSDException
NOTE:1173979.1 - SOA 11g Log Files are Full of JDBC Errors [OWLS][SQLServer JDBC Driver][SQLServer]Invalid object name
NOTE:1175789.1 - Master Note for SOA 11g Clustering and High Availability
NOTE:1189583.1 - 11g Oracle Application Adapters Install Fails with "could not load wizard specified in /wizard.inf (104)"
NOTE:1200173.1 - Information Center: Application Integration Architecture Foundation Pack 11g
NOTE:1203465.1 - How To Find RCU Schema Names And Their Versions in FMW 11g
NOTE:1209587.1 - RCU Is Failing to Connect to the Database with Error "Invalid username/password"
NOTE:1221133.1 - How to install HOTFIX using OPATCH in BPM 11g / SOA 11g
NOTE:1224953.1 - SOA 11.1.1.3 - After Install, The SOAINFRA Application Does Not Deploy
NOTE:1225466.1 - Requirement to Install AIA Foundation Pack 11gR1 (11.1.1.2) with SOA Suite 11g (11.1.1.3)
NOTE:1236286.1 - How to Start SOA Suite Domain AdminServer as Windows Service and soa_server Through NodeManager
NOTE:1261854.1 - Application Adapters 11.1.1.3.0: Can not be Installed on Windows if Using a JRE in C:\Program Files
NOTE:1264302.1 - SOA-INFRA Unable To Start In Weblogic Cluster: Coherence GE 3.3.2/391
NOTE:1267207.1 - Troubleshooting Interoperability Between OSB and SOA Suite 10g and 11gR1
NOTE:1269247.1 - BPM 11g and SOA 11g patch installation troubleshooting and FAQ
NOTE:1270686.1 - How to Install an OSB Cluster in the Same Domain as a SOA Cluster?
NOTE:1274476.1 - Managed Server Does Not Start After Adding This Managed Server to an Existing Domain
NOTE:1280585.1 - ORABPEL-35010 Unable to Encrypt data During Start if Installing SOA and WebCenter into Same MWHome
NOTE:1286231.1 - Oracle 10g SOA Suite Best Practices Guide
NOTE:1286233.1 - Oracle Fusion Middleware - Release Notes (11.1.1.4.0)
NOTE:1288692.1 - SOA Extensions are not installed properly in JDeveloper 11.1.1.4.0
NOTE:1288864.1 - How to Apply Patches to a BPM and/or SOA Environment in 11.1.1.3
NOTE:1290493.1 - Can SQL scripts be executed instead of RCU utility in order to create SOA/BPM schemas ?
NOTE:1290512.1 - Oracle SOA Suite and Business Process Management Suite (11.1.1.4) - Release Notes
NOTE:1291516.1 - SOA 11g: Health Status of the soa-infra Application in Warning State After SOA 11gR1 PS 3 Upgrade
NOTE:1292813.1 - Troubleshooting SOA Suite 11gR1 Installation for Versions 11.1.1.0 and Higher
NOTE:1293679.1 - Oracle SOA and BPM 11g Documentation and Install Resources
NOTE:1297250.1 - Purge Scripts Master Note - Oracle SOA Suite 11g
NOTE:1298609.1 - OSB or SOA Suite Installation - How to create Schemas on Solaris, HP/UX, AIX?
NOTE:1298931.1 - Master Note on WebLogic Server (WLS) Installation and Configuration
NOTE:1303698.1 - SOA - "ORA-28001: the password has expired" Errors - How to Change the Repository Database Passwords for the SOA Schemas?
NOTE:1304790.1 - Oracle Fusion Middleware 11g Repository Creation Utility (RCU) - FAQ
NOTE:1306230.1 - Troubleshooting BPEL Process Manager / SOA Suite 10gR3 Installation
NOTE:1339818.1 - Changing Oracle 11G SOA Suite Data Source (Repository) Passwords
NOTE:1354696.1 - Repository Creation Utility Fails to Connect to SQL Server 2008 Express Edition with SQLFatalErrorException
NOTE:454549.1 - OPATCH Error "Cannot look for invPtrLoc pattern", "error code = 100"
NOTE:579872.1 - What Documents Should Be Referenced Before Installing a BPEL, ESB or SOA Suite for High Availability Cluster?
NOTE:741373.1 - How to Verify the Exact Version of BPEL/ESB Installed with the Oracle SOA Suite?
NOTE:748289.1 - Troubleshooting JVM Related Issues for the SOA Suite
NOTE:748473.1 - Tuning SUN Java Virtual Machine (JVM) for BPEL 10.1.3 and SOA Suite 11g

NOTE:783157.1 - How to Start a ManagedServer for SOA from the Command Line Instead of the WebLogic Admin Console
NOTE:788141.1 - How to Use the Oracle Service Bus (OSB) Native BPEL Transport to Call a BPEL Process
NOTE:852222.1 - Installing AIA Foundation Pack on Oracle SOA Suite Cluster Deployments (2.4-2.5)
NOTE:853543.1 - Kickstarting SOA 11g
NOTE:858748.1 - Oracle Fusion Middleware 11g - Getting Started FAQ
NOTE:860428.1 - Is BPEL Available as a Standalone Install in 11GR1?
NOTE:879950.1 - FMW 11g RCU Database Connection Timeout - "Unable to connect to the database using the provided details"
NOTE:880313.1 - How to Configure Transaction Timeout for BPEL on SOA 11g

Wednesday, December 9, 2015

EBSO 12.2

Patching Syntax:

1.To apply nls patches:

adop phase=apply patches=18188506_FRC:u18188506.drv,18188506_PTB:u18188506.drv,18188506_ESA:u18188506.drv

2.To reapply already applied patch:

adop phase=apply patches=17171948 options=forceapply,nofilecheck

3.To restart patch from where it failed:

adop phase=apply patches=17171948 restart=yes

4.To apply prereq patch and abandon current patch#20986633

adop phase=apply patches=19003733 restart=no abandon=yes

adop parameters:

export T2P_JAVA_OPTIONS="-Djava.io.tmpdir=/test/product/temp""
export TIMEDPROCESS_TIMEOUT=-1
export CONFIG_JVM_ARGS="-Xms1024m -Xmx2048m"

adop phase=fs_clone restart=yes force=yes

adop phase=cleanup cleanup_mode=full

adop phase=apply patches=17171948 options=forceapply

adop phase=apply patches=17171948 options= restart=yes 

Monitoring scripts

sessions with highest CPU consumption:
======================================
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as
"CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

sessions with highest DB Time usage:
====================================
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as
"DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "%
CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v
$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;

sessions with the highest time for a certain wait:
==================================================
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

Inactive sessions:
==================
set linesize 1000
select count(s.status) INACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR:
===========================================================
select count(s.status) "INACTIVE SESSIONS > 1HOUR " from gv$session s, v$process p where p.addr=s.paddr and s.last_call_et > 3600 and s.status='INACTIVE';

COUNT OF ACTIVE SESSIONS:
=========================
select count(s.status) ACTIVE_SESSIONS from gv$session s, v$process p where p.addr=s.paddr and s.status='ACTIVE';

TOTAL SESSIONS COUNT ORDERED BY PROGRAM:
========================================
col program for a30
select s.program,count(s.program) Total_Sessions from gv$session s, v$process p where  p.addr=s.paddr group by s.program;

TOTAL COUNT OF SESSIONS ORDERED BY MODULE:
==========================================
col module  for a30
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions from gv$session s, v$process p where  p.addr=s.paddr group by s.module;

TOTAL COUNT OF SESSIONS ORDERED BY ACTION:
==========================================
col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions from gv$session s, v$process p where  p.addr=s.paddr group by s.action;

INACTIVE SESSIONS:
==================
prompt INACTIVE SESSIONS
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';

INACTIVE:
=========
prompt INACTIVE SESSIONS
select count(s.status) INACTIVE from gv$session s, gv$sqlarea t,v$process p where s.sql_address =t.address and s.sql_hash_value =t.hash_value and p.addr=s.paddr and s.status='INACTIVE';

INACTIVE PROGRAMS:
==================
col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module from gv$session s, v$process p where  p.addr=s.paddr and s.status='INACTIVE';

INACTIVE PROGRAMS with disk reads:
==================================
prompt INACTIVE SESSIONS
select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS) from gv$session s, gv$sqlarea t,v$process p where s.sql_address =t.address and s.sql_hash_value =t.hash_value and p.addr=s.paddr and s.status='INACTIVE' GROUP BY S.PROGRAM;

INACTIVE SESSIONS COUNT WITH PROGRAM:
=====================================
col program for a30
prompt TOTAL INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions from gv$session s,v$process p where p.addr=s.paddr AND s.status='INACTIVE' group by s.program order by 2 desc;

TOTAL INACTIVE SESSIONS MORE THAN 1HOUR:
========================================
col program for a30
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
and s.last_call_et > (3600)
group by s.program
order by 2 desc;

TOTAL INACTIVE SESSIONS GROUP BY  MODULE:
=========================================
col program for a60
COL MODULE FOR A30
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.module,count(s.module) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.module;

INACTIVE SESSION DETAILS MORE THAN 1 HOUR:
==========================================

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (3600)
order by last_call_et;


Inactive sessions for 15 mins:
==============================
set lines 1000
col sid format 9999
col username heading 'User' format a10
col osuser format a10 trunc
col process format a7 trunc
col program format a25 trunc
col lt format a13
col spid format a8
col et format 99999.99 heading 'ETime'
col action format a40 heading 'Action/Machine'
col pum format 99,9999 heading 'PGA UM'
spool inactive_sessions.lst
select s.sid, to_char(logon_time,'mm/dd hh24:mi') lt,
       s.username, p.spid, nvl(s.program, s.module) program,
       p.pga_used_mem/1024/1024 pum,
       s.last_call_et/60 et, p.pid,
       nvl(s.action,s.machine) action
  from v$session s, v$process p
 where s.status = 'INACTIVE'
   and s.username is not null
   and s.last_call_et/60 > 2000
   and s.paddr = p.addr
 order by 7
/

INACTIVE PROGRAM  --ANY--:
==========================

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
And s.program='&PROGRAM_NAME'
order by last_call_et;

INACTIVE MODULES  --ANY--:
==========================
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
And s.module like '%order_cleanup_hazmat_v3.sql'
order by last_call_et;

INACTIVE JDBC SESSIONS:
=======================

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR:
==============================================

SELECT COUNT(P.SPID)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

blocking inactive sessions with sql_id null:
============================================
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select * from  gv$session where status='INACTIVE' and sql_id IS NULL AND sql_hash_value=0;

FORMS:
========
TOTAL FORM SESSIONS:
===================
SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S WHERE S.STATUS='INACTIVE' and s.action like ('%FRM%');

FORMS SESSIONS DETAILS:
=======================
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600
order by spid;                    

col machine for a15
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process Client_Process,s.machine
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600;        
order by 4;                          

INACTIVE FORMS SESSIONS DETAILS:
================================

col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;

UNIQUE SPID:
============

select unique(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

COUNT FORMS:
============

select COUNT(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

ZERO HASH VALUE

select COUNT(p.spid)
from gv$session s,gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
AND S.SQL_HASH_VALUE=0;

INACTIVE FORM BY NAME

select count(s.sid) from v$session S
where s.action like ('%&ACTION%')
AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%')
group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152
col spid for a10
col process_spid for a10
col user_name for a20
col form_name for a20
select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,
d.user_form_name "FORM_NAME"
from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl d
where
a.login_id=b.login_id
and c.user_name like 'JROMO'
and a.user_id=c.user_id
and trunc(b.start_time) >trunc(sysdate -11)
and trunc(b.end_time) is null
and b.form_id=d.form_id
and d.language='US';

INACTIVE FORM

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like ('FRM%')
and s.last_call_et > (3600*3)
order by last_call_et;





INACTIVE FORM SESSIONS

col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id        = rf.form_id
AND ft.language       = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins           fl,
gv$process            vp,
apps.fnd_login_resp_forms rf,
gv$session            vs
WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */
AND fl.login_type   = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid          = vp.pid
AND fl.login_id     = rf.login_id
AND rf.end_time    IS NULL
AND rf.audsid       = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;

ACTIVE

prompt ACTIVE SESSIONS
select count(s.status) ACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='ACTIVE';

MODULE

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
and s.MODULE like ('&MODULE_NAME_1HR%')
and s.last_call_et > ('&TIME_HRS' * 3600)
order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S
where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0
AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.machine like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr and
s.status='INACTIVE';

GROUP BY  program

col program for a60
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr  AND
s.machine like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;



Locks and Blocking sessions

1.DB Locks:
  ---------
set lines 200
set pages 200
spool DB_Locks.lst
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
spool off

SQL> /

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS                                                       SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
             543       3604      44225 Application                                                                13168
            1637       5027      34229 Application                                                                 4629
            2347       1637      34589 Concurrency                                                                  790
            2347       1772      30581 Concurrency                                                                  712
            2347       2133      30613 Concurrency                                                                  113
            2347        427      51691 Concurrency                                                                  790
            2347       6552       9531 Concurrency                                                                   73
            2347        283      32075 Concurrency                                                                  791
            2347       6336      43573 Concurrency                                                                  791
            2347       6226       6863 Concurrency                                                                  781
            2347       2598       9991 Concurrency                                                                  553
            2347       2884       6587 Concurrency                                                                  790
            2347       2908      19783 Concurrency                                                                  790
            2347       3095      15317 Concurrency                                                                  791
            2347       3301      11573 Concurrency                                                                  556
            2347       3839      28959 Concurrency                                                                  790
            2347       3958       5749 Concurrency                                                                  790
            2347       4044      13291 Concurrency                                                                  766
            2347       4279       5453 Concurrency                                                                  789
            2347       4370      36515 Concurrency                                                                  790
            2347       4813         87 Concurrency                                                                  789
            2347       4962      15327 Concurrency                                                                  556
            2347       5202      63227 Concurrency                                                                  790
            2347       5375      16397 Concurrency                                                                  790
            2347       5994      21109 Concurrency                                                                  790
            3604        111      19897 Application                                                                13165
            4692       2347      25165 Concurrency                                                                  791
            5202        543      54389 Application                                                                18975

28 rows selected.

2.SQL> select sql_id from v$session where sid=543;

SQL_ID
-------------
7xsrcn3c3ssfy

3.
SQL> select sql_text from v$sql where sql_id='7xsrcn3c3ssfy';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1
UPDATE OE_ORDER_HEADERS SET PRICE_REQUEST_CODE = :B2 WHERE HEADER_ID = :B1



scenario 1
==========


first query
===========

Enter the request which is running long time,
Example 1:request Id=11263341


SQL> SELECT request_id,oracle_process_id,
DECODE(phase_code,'R','Running','C','Completed','P','Inactive',phase_code) Phase,
DECODE(status_code,'R','Normal','C','Normal','E','Error','Q','No Manager','T',
'Terminating','X','Terminated','G','Warning','Canceled','D',status_code) Status
FROM apps.fnd_concurrent_requests
where request_id=11263341;

REQUEST_ID ORACLE_PROCESS_ID              PHASE     STATUS
---------- ------------------------------ --------- -----------
  11263341 7694                           Running   Normal

O/p Gives u the Oracle Process ID:7694


Second query
=============

Enter the request which is running long time,
Example 1:request Id=11263341

Also gives u the Oracle Process ID...

SQL> select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',  'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id';
Enter value for enter_conn_req_id: 11263341
old   4: from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'
new   4: from apps.fnd_concurrent_requests where request_id='11263341'

ORACLE_PROCESS_ID              Status_code P Login Time
------------------------------ ----------- - ---------------------------
7694                           Running     R 07-OCT-2010=>09:36:09

select oracle_process_id ,request_id,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',  'Warning','T','Terminating')"Status_code",
phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where oracle_process_id='&a';

Third Query
===========

With that oracle Process ID We can find the SID and Serial# and waht it is doing ..
Oralce Procedd ID = 7694

select s.sid,s.serial#,module,s.status from v$session s,v$process p where s.paddr=p.addr and p.spid=&oracle_process_id;
Enter value for oracle_process_id: 7694
old   3: and p.spid=&oracle_process_id
new   3: and p.spid=7694

       SID    SERIAL# MODULE                                           STATUS
---------- ---------- ------------------------------------------------ --------
      3342       5626 ALTMFGINVFA                                      ACTIVE

SQL>


Fourth Query
=============

To find the Text we can find out what it is running..
USING SID=3342


SELECT sql_text FROM V$sql WHERE address IN ( SELECT sql_address FROM v$session WHERE sid =3342);

SQL> SELECT sql_text FROM V$sql WHERE address IN ( SELECT sql_address FROM v$session WHERE sid =3342);


SQL_TEXT
--------------------------------------------------------------------------------
SELECT NVL(CIC.ITEM_COST, NVL(CIC_DEF.ITEM_COST, 0)), NVL(CIC.BASED_ON_ROLLUP_FL
AG,NVL(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)), NVL(CIC.SHRINKAGE_RATE, NVL(CIC_DEF.SH
RINKAGE_RATE, 0)), (BET1.EXTENDED_QUANTITY * NVL(BET.COMPONENT_QUANTITY, 0) * (N
VL(BET.PLANNING_FACTOR, 100) / 100) ) / (DECODE(:B7 , 2, 1, DECODE(NVL(BET.COMPO
NENT_YIELD_FACTOR, 0), 0, 1, NVL(BET.COMPONENT_YIELD_FACTOR, 0) ) ) * (1 - NVL(B
ET1.SHRINKAGE_RATE, 0)) ), DECODE(BET1.EXTEND_COST_FLAG, 2, 2, DECODE(BET1.INVEN
TORY_ASSET_FLAG, 2, 2, DECODE(BET1.BASED_ON_ROLLUP_FLAG, 2, 2, DECODE(BET.INCLUD
E_IN_ROLLUP_FLAG, 1, 1, 2)))), NVL(CIC.COST_TYPE_ID, NVL(CIC_DEF.COST_TYPE_ID, B
ET1.ACTUAL_COST_TYPE_ID)), NVL(CIC.INVENTORY_ASSET_FLAG, NVL(CIC_DEF.INVENTORY_A
SSET_FLAG, 2)), BET.TOP_BILL_SEQUENCE_ID, BET.ROWID FROM CST_ITEM_COSTS CIC, CST
_ITEM_COSTS CIC_DEF, BOM_EXPLOSION_TEMP BET_MUST_HAVE, BOM_EXPLOSION_TEMP BET1,

SQL_TEXT
--------------------------------------------------------------------------------
BOM_EXPLOSION_TEMP BET WHERE BET1.GROUP_ID = :B2 AND BET1.TOP_BILL_SEQUENCE_ID =
 BET.TOP_BILL_SEQUENCE_ID AND BET1.SORT_




SQL> select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session where blocking_session is not null;

PROCESS                         SID BLOCKING_SESSION BLOCKING_INSTANCE
------------------------ ---------- ---------------- -----------------
115740                         1267             5265                 2
20969                          5265             7261                 2
21000                          6003             5265                 2
20993                          6754             5265                 2
93890                          7275             7261                 2
20131                           772             7261                 2

SQL> select sid,serial#,p2,program,module from gv$session where sid=5265;

Tablespace usage

Query to check 30% below:
-------------------------

set lines 1000
set pages 200
col host_name format a25
select a.tablespace_name,
(a.BYTES/1024)/1024 "Used Space(MB)",total "allocated size(MB)",
max "maximum allowable (MB)",max-(a.BYTES/1024)/1024 "effective free(MB)",round(((max-(a.BYTES/1024)/1024)/max)*100,2) "% FREE" from SM$TS_USED a,(select tablespace_name,sum((BYTES/1024)/1024) total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 6 desc;

Adding Space:
-------------
alter tablespace APPS_TS_TX_DATA add datafile '/test/data/data1.dbf' size 100M autoextend on maxsize 5g;

Temp tablespace usage:
----------------------
SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP2                                3870336        4352     3865984
TEMP                                       0           0           0
TEMP1                                5181056        2848     5178208


Temporary Segments

The following query displays information about all sort segments in the database. (As a reminder, we use the term “sort segment” to refer to a temporary segment in a temporary tablespace.) Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP1                                50480       22.5    50457.5
TEMP2                                40240         38      40202

ALTER TABLESPACE TEMP1 ADD TEMPFILE '/test/data/temp01.dbf' size 100M autoextend on maxsize 5g;

Sunday, December 14, 2014

Big Data Contents

Introduction to Data Storage and Processing

  • Installing the Hadoop Distributed File System (HDFS)
  • Defining key design assumptions and architecture
  • Configuring and setting up the file system
  • Issuing commands from the console
  • Reading and writing files


Setting the stage for MapReduce

  • Reviewing the MapReduce approach
  • Introducing the computing daemons
  • Dissecting a MapReduce job


Defining Hadoop Cluster Requirements

  • Planning the architecture
  • Selecting appropriate hardware
  • Designing a scalable cluster


Building the cluster

  • Installing Hadoop daemons
  • Optimizing the network architecture


Configuring a Cluster

  • Preparing HDFS
  • Setting basic configuration parameters
  • Configuring block allocation, redundancy and replication


Deploying MapReduce

  • Installing and setting up the MapReduce environment
  • Delivering redundant load balancing via Rack Awareness


Maximizing HDFS Robustness

  • Creating a fault-tolerant file system
  • Isolating single points of failure
  • Maintaining High Availability
  • Triggering manual failover
  • Automating failover with Zookeeper


Leveraging NameNode Federation

  • Extending HDFS resources
  • Managing the namespace volumes


Introducing YARN

  • Critiquing the YARN architecture
  • Identifying the new daemons


Managing Resources and Cluster Health

  • Allocating resources
  • Setting quotas to constrain HDFS utilization
  • Prioritizing access to MapReduce using schedulers


Maintaining HDFS

  • Starting and stopping Hadoop daemons
  • Monitoring HDFS status
  • Adding and removing data nodes


Administering MapReduce

  • Managing MapReduce jobs
  • Tracking progress with monitoring tools
  • Commissioning and decommissioning compute nodes


Maintaining a Cluster

  • Employing the standard built-in tools
  • Managing and debugging processes using JVM metrics


Performing Hadoop status checks

  • Tuning with supplementary tools
  • Assessing performance with Ganglia
  • Benchmarking to ensure continued performance


Extending Hadoop

  • Simplifying information access
  • Enabling SQL-like querying with Hive
  • Installing Pig to create MapReduce jobs


Integrating additional elements of the ecosystem

  • Imposing a tabular view on HDFS with HBase
  • Configuring Oozie to schedule workflows


Implementing Data Ingress and Egress

  • Facilitating generic input/output
  • Moving bulk data into and out of Hadoop
  • Transmitting HDFS data over HTTP with WebHDFS


Acquiring application-specific data

  • Collecting multi-sourced log files with Flume
  • Importing and exporting relational information with Sqoop


Planning for Backup, Recovery and Security

  • Coping with inevitable hardware failures
  • Securing your Hadoop cluster