Oracle DBA Daily Experiences

Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area

Example:
– Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc

Information in the trace file:
…
*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3′ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?

..

Using “oradebug dump events 1″ one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

In another session login as an account as sysdba
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321

The Official Oracle Wiki – Oracle Wiki.

The following example shows how to set event on a oracle session

To turn on event, needs to be run as sysdba
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.

To turn off event
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.

List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

the script below will help you to see how big auto allocation is set plus all the report for the current used and free space.

[spocoradb1.aeso.ca]/dbadmin/workspace2/alima/sql>cat db_tablespaces_report.sql
set linesize 200
set trimspool on
set pagesize 100
set feedback off
column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a30 heading “Tablespace Name”
column Kbytes_alloc format 999,999,999,999 heading “Alloc|KBytes”
column currently_used format 999,999,999,999 heading “Used|KBytes”
column kbytes_free format 999,999,999,999 heading “Free|KBytes”
column Kbytes_max format 999,999,999,999 heading “MaxPoss|Kbytes”
column pct_free format 999.99 heading “%Free”
column max_kbytes_free format 999,999,999,999 heading “MaxPoss|Free|KBytes”
column max_pct_used format 999.99 heading “%Max|Used”
column host_name format a30

spool $vfile_email

select TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) TODAY FROM dual
/
SELECT host_name, instance_name FROM v$instance
/
break on report
compute sum of Kbytes_alloc on report
compute sum of kbytes_free on report
compute sum of currently_used on report
compute sum of Kbytes_max on report
select decode(extent_management,’LOCAL’,'*’,”) ||
decode(segment_space_management,’AUTO’,'a ‘,’m ‘) ||
b.tablespace_name name,
Kbytes_alloc,
kbytes_max,
(Kbytes_alloc – NVL(kbytes_free, 0)) currently_used,
NVL(kbytes_free, 0) kbytes_free,
(NVL(kbytes_free, 0))*100/Kbytes_alloc pct_free,
(Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) max_kbytes_free,
(Kbytes_alloc – NVL(kbytes_free, 0))*100/kbytes_max max_pct_used
FROM
( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(decode(AUTOEXTENSIBLE, ‘NO’, bytes,
decode(sign(maxbytes – bytes), 1, maxbytes, -1, bytes, 0,maxbytes)))/1024 Kbytes_max,tablespace_name
from sys.dba_data_files
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name
AND c.tablespace_name = b.tablespace_name
AND b.tablespace_name NOT IN (select distinct TABLESPACE_NAME from
DBA_UNDO_EXTENTS)
— 5% free and less than 2GB
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max < 5
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) < (2*1024*1024)
ORDER BY (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max, b.tablespace_name
/
spool off

This script will format the new 11G crsctl status resource in a tabular format.

#!/usr/bin/ksh
#
# Sample 11g CRS resource status query script
#
# Description:
# – Returns formatted version of crsctl status resource, in tabular
# format, with the complete rsc names and filtering keywords
# – The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# – $ORA_CRS_HOME should be set in your environment
#
#
# HISTORY ##################################
#
# 28/06/2010 – Alex Lima – UPgraded to 11G commands.
#
################################################################
export CRS_HOME=/usr/grid/product/11.2.0

RSC_KEY=$1
QSTAT=-u
AWK=/usr/bin/awk # if not available use /usr/xpg4/bin/awk

# Table header:echo “”
echo “”

$AWK \
‘BEGIN {printf “%-30s %-70s %-18s\n”, “HA Resource”, “State”, “Target”;
printf “%-30s %-70s %-18s\n”, “———–”, “—–”, “——”;}’

# Table body:
$CRS_HOME/bin/crsctl status resource | $AWK \
‘BEGIN { FS=”=”; state = 0; }
$1~/NAME/ && $2~/’$RSC_KEY’/ {appname = $2; state=1};
state == 0 {next;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
state == 3 {printf “%-30s %-70s %-18s\n”, appname, appstate, apptarget; state=0;}’
echo “”

Using dbms_monitor.client_id_trace_enable to turn trace on for all sessions that have client identifier set to 'debug' and using dbms_session.set_identifier which will turn on trace for session.

Example:
To setup trace for client identifier
SQL> exec dbms_monitor.client_id_trace_enable('debug', true, true);

PL/SQL procedure successfully completed.

– displays list of client identifers for which trace can be turned on
SQL> select trace_type, primary_id, waits, binds from dba_enabled_traces;

TRACE_TYPE
———————
PRIMARY_ID WAITS BINDS
—————————————————————- —– —–
CLIENT_ID
debug TRUE TRUE

PL/SQL procedure successfully completed.

In another session in which you want to turn on trace
– set client identifier on a session which will turn on trace
SQL> exec dbms_session.set_identifier('debug');

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
———
20-JUN-10

To turn off trace
– to disable trace
SQL> exec dbms_monitor.client_id_trace_disable('debug');

Oracle OpenWorld 2010.

At times you may have a scenario where you may want to monitor temp space usage in a running session. So using the following PL/SQL code loops which inserts every interval on temp usage for a specific sid (session id).

create table monitor_usage (
RUN_DATE DATE,
BLOCKS NUMBER(38),
BYTES NUMBER(38),
APP VARCHAR2(10)
);

declare
cursor l_cur IS SELECT sysdate, b.blocks, b.blocks*d.value bytes
FROM v$session a, (select session_addr, sum(blocks) blocks from v$tempseg_usage group by session_addr) b, v$sqlarea c,
(select value from v$parameter where name=’db_block_size’) d
WHERE a.saddr = b.session_addr
and a.sid = &sid
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*d.value > 1024;
l_rec l_cur%rowtype;
app varchar2(2) := ‘&1′;
interval integer := 10;
begin
while true loop
open l_cur;
fetch l_cur into l_rec;
If l_cur%found THEN
insert into monitor_usage values (l_rec.sysdate, l_rec.blocks, l_rec.bytes, app);
commit;
end if;
DBMS_LOCK.sleep(seconds => interval);
close l_cur;
end loop;
end;
/

Example:

SQL> alter session set nls_Date_format=’DD-MON-YYY

Session altered.

SQL> select * from monitor_usage where app = ‘R1′
RUN_DATE BLOCKS BYTES APP
——————– ———- ———- ——-
15-JUN-2010 10:21:21 1792 14680064 R1
15-JUN-2010 10:21:32 8576 70254592 R1
…
15-JUN-2010 10:27:52 119808 981467136 R1
15-JUN-2010 10:28:04 122240 1001390080 R1
15-JUN-2010 10:28:15 124672 1021313024 R1
…
15-JUN-2010 10:29:59 33920 277872640 R1
15-JUN-2010 10:30:10 34048 278921216 R1