Ferhat's Blog

There will be only one database

Archive for September, 2010

Do not have to wake up in order to get a trace

Posted by fsengonul on September 2, 2010

I’m sure there is a better way to get processstate dumps in a RAC environment but I needed something fast and not very dirty.
The following solution wakes up at 10 minute  periods and finds the sessions who causes ST contention and gets the processstate dump as long as locks continue.
crontab:
*/10 * * * * home/oracle/dba/debug.sh >> /home/oracle/dba/debug.log 2>> /tmp/oradebug.err
debug.sh:
. /home/oracle/.bash_profile
date
[ -e /home/oracle/dba/debug_ST.sql ] && exit
cd /home/oracle/dba
sqlplus / as sysdba @debug.sql
#if debug_ST is empty then leave else rerun the job so keep getting traces.
if [ -e /home/oracle/dba/debug_ST.sql ] && [ ! -s /home/oracle/dba/debug_ST.sql ]
then
rm debug_ST.sql
else
rm debug_ST.sql
nohup /home/oracle/dba/debug.sh >> /home/oracle/dba/debug.log 2>> /tmp/oradebug.err &
fi
exit
debug.sql:
set heading off;
set feedback off;
select sql_id,count(*) from gv$session where s.program like ‘sqlldr%’ group by rollup(sql_id);
spool debug_ST.sql
select /*+ RULE */ ‘conn sys/***@DBNAME’||p.inst_id||’ as sysdba ‘|| chr(10)
||’set feedback off’|| chr(10)
||’oradebug setorapid ‘||pid|| chr(10)
|| ‘oradebug unlimit’|| chr(10)
||’oradebug tracefile_name’|| chr(10)
||’oradebug dump processstate 10′ || chr(10)
|| ‘oradebug dump errorstack 3’ ||chr(10)
||’ho sleep 10′ ||chr(10)
||’oradebug dump processstate 10′ || chr(10)
|| ‘oradebug dump errorstack 3’ ||chr(10)
||’ho sleep 10′ ||chr(10)
||’oradebug dump processstate 10′ || chr(10)
|| ‘oradebug dump errorstack 3’ ||chr(10)
from  gv$session s,gv$process p where
s.inst_id=p.inst_id and s.paddr=p.addr and
(s.inst_id,s.sid) IN
—                (select 7,249 from dual)
(SELECT distinct
final_blocking_instance,
final_blocking_Session
from gv$session s
WHERE s.program like ‘sqlldr%’
and event like ‘enq: ST%’)   ;
spool off;
@debug_ST.sql
exit;

Posted in oracle | Leave a Comment »