Weekly script #3 unix_usr.sql

I use this one all the time. You have a high usage unix PID that is an Oracle Process. The script will take your defined pid and lookup the relating SQL and database user details to help you figure out the root cause.


set serveroutput on size 50000
set echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '

DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));

dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;

-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;

dbms_output.put_line('Locks:');
for c1 in ( select /*+ ordered */
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;

dbms_output.put_line('=====================================================================');

END;
/

undef 1

Weekly script #2 pid_from_sid.sql

A fairly easy script today that isn’t really hard to work out for yourself but I find it useful to have this script kicking around for when you find high resource using Oracle server process and you want to identify the offending SQL.


set pages 1000;
set lines 200;
col username for a10;

select b.status,a.inst_id,a.PID "Ora PID",a.SPID "Server PID",
a.LATCHWAIT,b.Program, b.Username,b.Osuser,b.machine,
b.terminal,a.terminal,b.Process "Client Process"
from gv$process a,gv$session b where a.addr=b.paddr and b.sid=&SID
/

See you next week!

Le Paz
 

 

Weekly Script #1 – ssh_key_scp.sh

Since I dont post on here to often I will try and post a weekly script that I find useful day-to-day.

First up is a short simple script to scp your private key to a defined server. Usage is pretty simple call the script with the username@hostname

e.g ./ssh_key_scp.sh username@hostname

If no ssh key already exists the script gives you the option to generate one.


#!/bin/sh
# Andrew Paz
# 06/01/15
#

KEY="$HOME/.ssh/id_rsa.pub"

if [ ! -f ~/.ssh/id_rsa.pub ];then
echo "Private key not found at $KEY"
while true; do
read -p "Do you wish to generate a private key?" yn
case $yn in
[Yy]* ) ssh-keygen -t rsa; break;;
[Nn]* ) exit;;
* ) echo "Please answer yes or no!";;
esac
done
exit

fi

if [ -z $1 ];then
echo "Please specify user@host as the first parameter of this script"
exit
fi

echo "Putting your key on $1... "

KEYCODE=`cat $KEY`
ssh -q $1 "mkdir ~/.ssh 2>/dev/null; chmod 700 ~/.ssh; echo "$KEYCODE" >> ~/.ssh/authorized_keys; chmod 644 ~/.ssh/authorized_keys"

echo "done :D Key Uploaded!"

exit;
end

Thats it for this week, will post another next week.

Cheers

Check Node Manager Script

Hi Again,

I just knocked up this nodemanager check script that you can run as a cron job making sure the Weblogic Java NodeManager is running and if not email you. See below for the script.


#!/bin/ksh
 #
 #########################################################
 # NAME:check_node_manager.sh #
 # AUTHOR: Paz #
 # DESC: Check no make sure Weblogic Node Manager is #
 # running #
 # DATE: 19/06/13 #
 # VERSION 1.0 #
 # CHANGE LOG: #
 # AP 19/06/2013 Creation #
 # #
 # #
 #########################################################
 #
 #set -x

. $HOME/.profile
 export SCRIPT_HOME='add you scripts home'
 ####################CHECK NODE MANAGER JAVA PROCESS IS RUNNING########################
 cd $SCRIPT_HOME

jps |grep -i NodeManager >Nodemgr_jps_status.log

sleep 2

nodemgr_jps_status=$(cat Nodemgr_jps_status.log |grep -i 'NodeManager' |wc -l)

if [ ${nodemgr_jps_status} -gt 0 ]
 then
 echo 'do nothing Node Manager Alive'
 else
 mailx -s 'NODEMANAGER DOWN' your@email.com
 fi

exit

EOF

The script can be changed if you use a script based nodemanager, if you change ‘jps’ for ‘ps -ef’ so the line would read:

ps -ef |grep -i NodeManager >Nodemgr_jps_status.log

Until next time