Wednesday, March 24, 2010

DB Links

DB Links
Query for getting DB Link name,password,username
========================================

set lines 1000
column Owner format a10
column DbLinkName format a30
column CONN_USER format a10
column CONN_PWD format a10
column CONN_STRING format a20


    * select l.name DbLinkName , du.username Owner ,l.userid CONN_User, l.password CONN_Pwd, l.host Conn_String from sys.link$ l,dba_users du where l.owner#=du.user_id (+)

----Public Link will have Owner as Null

    * select l.name Name , l.userid CONN_User, l.password CONN_Pwd, l.host Conn_String, l.ctime from sys.link$ l

Monday, March 22, 2010

Workflow Builder

When I want to access a Workflow Builder, I get the following error.

220: Unable to set NLS_LANGUAGE.
210: Oracle Error: ORA-00942: table or view does not exist. SQL text: SELECT NLS_LANGUAGE FROM WF_LANGUAGES WHERE :l IN (NLS_LANGUAGE, CODE)


Resolution
=========

there is a Key in the Registry called NLS_LANG
run regedit
then search for NLS_LANG
the Value is somthing like this "AMERICAN_AMERICA.AR8MSAWIN"
u should remove the ".AR8MSAWIN" and place it the same as ur server lang

if u wanna know ur server language

connect as system/manager
then write this sql statment

select value$
from sys.props$
where name like 'NLS_CHARACTERSET';

best reguards

Wednesday, March 10, 2010

Troubleshoot long running Concurrent Request in Apps 11i/R12

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

.



SQL Script to Troubleshoot a long-running concurrent request
.

set term on
set feedback on
set echo on
set arraysize 4
set linesize 200
set pages 9999
set underline =;
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
column sql_text format a100
column name format a80
break on id1 skip 1 dup
undefine v_request_id
define v_request_id
undefine v_spid
define v_spid
undefine v_sid
define v_sid
spool vj_concurrent_monitor.lst

Prompt Enter the concurrent_request_id
Accept v_request_id
prompt checking requests
select oracle_process_id from fnd_concurrent_requests where request_id='&v_request_id';

Prompt Enter the operating system oracle process id for this concurrent request
accept v_spid
Prompt Getting the sid
SELECT SID,SERIAL#,LOGON_TIME FROM V$SESSION WHERE PADDR IN
(SELECT ADDR FROM V$PROCESS WHERE SPID='&v_spid');

prompt Enter the session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';

prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;


prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;


prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';


prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;


prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';

prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;



prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;

prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;


prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;


spool off
clear columns
clear breaks