Oracle 10g/11g:Database monitoring Tools/Utility

Oracle 10g/11g:Database monitoring Tools/Utility


This shell script mainly developed for monitoring database basic things in regular interval.


This is home made utilities that inside set of sql queries that give required output.

This utility basically useful for getting info of active sessions,inactive session,running jobs,top wait events,block and locking sessions and current load of server.

For this utility implement,Need to create set of queries and shell script which execute anywhere.

Follow this steps for create home made monitoring tool.

1.create directory on /usr/bin.

mkdir psmon

2.Create shell script for execute set of sql queries.

#!/bin/ksh
clear
echo
uptime
prc=`ps -e -o pid,pcpu|awk '$2>0 {print $1}'`
while :
do
echo "#############################################################################################"
echo "|r=Refresh|e=End|S=Query_By_SID|L=LongOP_QUERY|Q=Remaining_Query|t=Topas|j=Jobs|z=View_Query|"
echo "|l=List_Users|k=Lock_Session|c=cpu|w=wait|a=active_user|i=kill_session|b=lock_with_sqlid|p=explain_plan|"
echo "|y=Query_By_SQLID|d=Session_Count|m=Time_Consuming_Query|q=Quit|"
echo

opt=`/usr/bin/askopt 'r/e/S/L/Q/t/j/z/l/k/c/w/a/i/b/p/y/d/m/q' refresh `
[ "$opt" = "e" ] && break
[ "$opt" = "r" ] && prc=`ps -e -o pid,pcpu|awk '$2>0 {print $1}'`
[ "$opt" = "t" ] && topas
[ "$opt" = "q" ] && exit
[ "$opt" = "j" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/dbajobs.sql
continue
}
[ "$opt" = "S" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/Q_SID.sql
continue
}
[ "$opt" = "L" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/longop.sql
continue
}
[ "$opt" = "Q" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/remainingquery.sql
continue
}
[ "$opt" = "l" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/login_cnt.sql
continue
}
[ "$opt" = "k" ] && {
date
sqlplus -s username/password @/usr/bin/psmon/lock_check.sql
continue
}
[ "$opt" = "z" ] && {
sqlplus -s username/password @/usr/bin/unixusr.sql
continue
}
[ "$opt" = "c" ] && {
sqlplus -s username/password @/usr/bin/psmon/cpu.sql
continue
}

[ "$opt" = "w" ] && {
sqlplus -s username/password @/usr/bin/psmon/wait.sql
continue
}

[ "$opt" = "a" ] && {
sqlplus -s username/password @/usr/bin/psmon/au.sql
continue
}

[ "$opt" = "i" ] && {
sqlplus -s username/password @/usr/bin/psmon/killsession.sql
continue
}
[ "$opt" = "b" ] && {
sqlplus -s username/password @/usr/bin/psmon/lock.sql
continue
}

[ "$opt" = "p" ] && {
sqlplus -s username/password @/usr/bin/psmon/explain.sql
continue
}


[ "$opt" = "y" ] && {
sqlplus -s username/password @/usr/bin/psmon/sql_id.sql
continue
}


[ "$opt" = "d" ] && {
sqlplus -s username/password @/usr/bin/psmon/sessionstats.sql
continue
}

[ "$opt" = "m" ] && {
sqlplus -s username/password @/usr/bin/psmon/time.sql
continue
}
ps -fp "$prc"|awk '$4>0'|sort +3 -4n
uptime
done
ans=`/usr/bin/askopt 'Y/N/' Do you want to kill the session '`
[ "$ans" = "Y" ] && /usr/bin/killproc.ksh
ans1=`/usr/bin/askopt 'Y/N/' Do you want to see the INDEXES ON THE TABELE'`
[ "$ans1" = "Y" ] && /usr/bin/ind_col_list.ksh
exit;

Save this file named psmon into /usr/bin/

This willl execute every location.

3.Create .sql files that store the queries for respective options on psmon.

1./usr/bin/psmon/dbajobs.sql


col what format a45;
select sid,a.job,what from dba_jobs_running a,dba_jobs b
where a.job = b.job;
exit;
2./usr/bin/psmon/Q_SID.sql


column sql_text format a10000
SELECT a.sql_text
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &sid
ORDER BY a.piece;

set pages 999 lines 999 long 999
select a.sid, a.serial#, b.spid, a.username, a.osuser, a.machine, a.terminal, a.program, a.sql_id, s.hash_value, b.background, s.sql_fulltext
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id and a.sql_hash_value = s.hash_value and a.sid = &sid ;

exit;

3./usr/bin/psmon/longop.sql


COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' ||
MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# order by elapsed desc;
exit;

4./usr/bin/bafl/remainingquery.sql

COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A25
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' ||
MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# and MOD(sl.time_remaining,60) > 0 order  by elapsed desc;
exit;


 5./usr/bin/bafl/login_cnt.sql
set pages 50
set  feedback off
break on report
compute sum of cnt on report
select substr(USER_ID,1,1) usrs,count(*) cnt from finnsso_bfprod.sec_login_users a, finnsso_bfprod.SEC_APPL_USERS b
where a.APPL_USER_ID = b.APPL_USER_ID
and a.login_status = 'Y'
group by substr(USER_ID,1,1)
order by 1;
exit;

 6./usr/bin/bafl/lock_check.sql
column LOCK_TYPE format a25
column MODE_HELD format a25
column MODE_REQUESTED format a25
select WAITING_SESSION,HOLDING_SESSION,LOCK_TYPE,LOCK_ID1,LOCK_ID2 from dba_waiters;
exit;

 7./usr/bin/unixusr.sql
rem -----------------------------------------------------------------------
rem Filename:   unixusr.sql
rem Purpose:    Lookup database details for a given Unix process id
rem Date:       20-Dec-2004
rem Author:     Anuranjan Gupta
rem -----------------------------------------------------------------------

 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
          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;
/
exit;



8./usr/bin/bafl/cpu.sql
col what format a45;
select sid,a.job,what from dba_jobs_running a,dba_jobs b
where a.job = b.job;
exit;
you have mail in /usr/spool/mail/oracle
[oracle@bcfprddb|BCFPRD /oracle/products/apps]$cat /usr/bin/bafl/cpu.sql
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time       heading 'CPU|Time'
column elapsed_time   heading 'Elapsed|Time'
column disk_reads     heading 'Disk|Reads'
column buffer_gets    heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132 long 9999
ttitle 'SQL By CPU Usage'
select * from 
         (select sql_id, sql_text,   
                 cpu_time/1000000000 cpu_time,  
                 elapsed_time/1000000000 elapsed_time, 
                 disk_reads, 
                 buffer_gets, 
                 rows_processed 
          from v$sqlarea 
          order by cpu_time desc, disk_reads desc
          ) 
where rownum < 7 ;
exit;

 9./usr/bin/bafl/wait.sql
select event "Wait_Event" , count(*) from v$session_wait group by event order by 2 desc;
exit;

 10./usr/bin/bafl/au.sql
set echo off
set pagesize 1000
set lines 140
set verify off
set heading on
set feedback on
col SESS format a12
col status format a10
col program format a30
col terminal format a12
col "Machine Name" format a15
col "Machine Name" format a15
col "DB User" format a14
col "Logon Time" format a14
col "OS User" format a10
select rpad(s.username,14,' ') as "DB User",
   to_char(logon_time,'hh24:mi Mon/dd') as "Logon Time",
   s.sid||','||s.serial# SESS,
   rpad(upper(substr(s.program,instr(s.program,'\',-1)+1)),30,' ') as "Program",
   rpad(lower(osuser),10,' ') as "OS User", rpad(s.terminal,12,' ') "Terminal",
   rpad(initcap(machine),15,' ') as "Machine Name",round(LAST_CALL_ET/60) MINS_ACTIVE 
    from v$session s
   where upper(s.username) like upper('%&Username%') and s.status='ACTIVE'
   order by LAST_CALL_ET;
exit;

 11./usr/bin/bafl/killsession.sql
select 'alter system kill session '||''''||sid||','||serial#||''';' from v$session
where sid in (SELECT a.sid
from    v$session b,
        (select distinct b.*
        from    v$lock a,
                v$lock b
        where   a.id1 = b.id1
        and     a.id2 = b.id2
        and     a.request > 0) a
where   a.sid = b.sid
and b.status='INACTIVE')
and username is not null;
exit;

 12./usr/bin/bafl/lock.sql
set line 200
set pages 2000
col sess for a15
col username for a15 
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,12) sess, a.type, a.lmode, a.request,a.block, a.ctime, b.username, b.status, b.sql_id, b.prev_sql_id, b.ROW_WAIT_OBJ#
from    gv$session b,
        (select distinct b.*
        from    gv$lock a,
                gv$lock b
        where   a.id1 = b.id1
        and     a.id2 = b.id2
        and     a.request > 0) a
where   a.sid = b.sid
and     a.inst_id = b.inst_id
order by 8 desc;
exit;


13./usr/bin/bafl/explain.sql

set pages 999 lines 999 long 999
select  'Explain Plan from Historical AWR'  from dual;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

 select 'Explain Plan from Current Cursor' from dual;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
exit;

 14./usr/bin/bafl/sql_id.sql
set pages 999 lines 999 long 999
column sql_fulltext format a10000
select a.sid, a.serial#, b.spid, a.username, a.osuser, a.machine, a.terminal, a.program, a.sql_id, s.hash_value, b.background, s.sql_fulltext
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id 
and a.sql_hash_value = s.hash_value and a.sql_id = '&SQLID' ;

 exit;

 15./usr/bin/bafl/sessionstats.sql
set pages 9999 lines 9999 long 999999
col  username format a30
col  status format a30
col  machine format a30
col  terminal format a40
col  program format a40
col  sql_id format a20
col  count(1) format 99999.999
select a.username,a.status,  a.machine, a.terminal, a.program, a.sql_id , count(1) 
from v$session a, v$process b, v$sqlarea s where a.paddr = b.addr and a.sql_id = s.sql_id 
and a.sql_hash_value = s.hash_value group by a.username,a.status,  a.machine, a.terminal, a.program, a.sql_id 
order by count(1) desc, a.username asc ;
exit;

 16./usr/bin/bafl/time.sql
set pagesize 9999 linesize 9999 long 9999

 select * from 
(
select sql_id,PLAN_HASH_VALUE,executions,  DISK_READS/executions, BUFFER_GETS/executions,  (elapsed_time/nvl(executions,1))/1000000 "Query Time per execution"
from v$sqlstats where executions > 0 order by 6  desc
)
where rownum < 15 ;
exit;
4.Run this bash level.

 How look psmon









Comments

  1. UnknownJanuary 27, 2015 at 1:02 AM

    After reading this post I got an idea about on this note.Really something grate in this article ,Thanks for sharing this. We are providing ORACLE courses training online. After reading this slightly am changed my way of introduction about my training to people. To know more Visit Us ORACLEOnline Training Course

    ReplyDelete

Post a Comment

Dear User,

Thank you very much for your kind response

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Image
Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c This document is prepared for monitoring windows server where sql server installed.Mostly used OEM for monitoring databases. In this document we will cover agent installation with target addition into OEM 12c. We will also cover prerequisite like enabling port for communication from agent to oms server Agent Installation on windows server  As a part of installation, we require to fulfil prerequisite like connectivity between OEM server and Window server. For Port opening and communication between two servers Ports required to open : 4903 , 7799 , 3872 Following steps require doing for manually installation of Agent in Windows server. Verifying if the agent exists on OMS. To begin, open your Cloud Control and go to "Setup -> Extensibility -> Self Update". Open the "Agent Software" folder and check if the Agent Software of Windows x64 is with statu...
Read more

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Image
SMS Alert logs ORA- errors This is something new in database administration part.Suppose DBA was out of office and got call from company manager to come to office.There is some problem in database.Generally DBA connect database and check connection possible or not then check any alert log entry.It is better if DBA and their company people got messages whenever any ORA- errors came in certain period of time. For this,I found some solution to read alert log and send sms to respective people. It is mandatory if we put database in archive log and where frequent archives generated.This is require because of the timestamp of archives used to get the start and end time of alertlogs entry. This is all done through SHELL script and on linux machine. Here it is the magical script can help to get the info of SMS by mail ans SMS. This shell designed to read alerts of last 30 minutes. #!/bin/bash #Go to bdump location cd /oracle1/oradata/pindb/bdump/ #SMS d...
Read more

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server. ***What is Instant Client &benefits? As per Oracle docs, Oracle Database Instant Client Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle. ISVs and Partners   benefit greatly from packaging Instant Client along with their applications, saving customers the extra step of installing and configuring an Oracle client. Free. The same fully-featured, high-performance applications that Oracle ISVs and partners have always delivered will continue to work, untouched. Customers can try new packaged applications and Oracle client features quickly without worrying about other installations. Larger enterprises ca...
Read more