Thursday 24 May 2012

Oracle Clound Countrol 12c and runaway agent


It would be good idea to limit number of session for DBSNMP user on all monitored hosts. Agent opened 1600+ sessions this morning and as a result `ORA-00020: maximum number of processes XXXX exceeded`.
CREATE PROFILE DBSNMP_PROFILE LIMIT
  SESSIONS_PER_USER 50
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 100000
  CONNECT_TIME UNLIMITED
  IDLE_TIME UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL;

ALTER USER DBSNMP PROFILE DBSNMP_PROFILE;

Thursday 3 May 2012

symstat output to oracle table

This post is for my own records. A couple of scripts I developed to load symstat output to oracle table for analyzis. Tested on DMX4 with the latest symcli.



symstat -i 60 -type DISK -sid 0990 > symdisks_stat.log

egrep -v "DISK|READ|Total" symdisks_stat.log > src_symdisks_stat.lst



CREATE TABLE DS
(
DISK VARCHAR2(20 BYTE),
IR NUMBER,
IW NUMBER,
KR NUMBER,
KW NUMBER,
TIMESTRING VARCHAR2(25 BYTE)
)


fsymdisks_stat.ctl

LOAD DATA
INFILE 'src_symdisks_stat.lst'
INTO TABLE ds
( TIMESTRING POSITION(1:9) CHAR,
DISK POSITION(10:16) CHAR,
IR POSITION(32:37) char ,
IW POSITION(39:44) CHAR ,
KR POSITION(46:52) CHAR ,
KW POSITION(54:60) CHAR )

--update timestamp
DECLARE
  t  VARCHAR2(15);
BEGIN
  FOR i IN (SELECT ROWID, timestring FROM ds) LOOP
    IF i.timestring IS NULL THEN
      UPDATE ds
         SET timestring = t
       WHERE ROWID = i.ROWID;
    ELSE
      t := i.timestring;
    END IF;
  END LOOP;
END;


WITH Q AS
(SELECT TRUNC(AVG(IW)) AS AVG_IW, SUM(IW) AS TOTAL_IW, TRUNC(AVG(IR)) AS AVG_IR, SUM(IR) AS TOTAL_IR, DISK FROM DS GROUP BY DISK ORDER BY 1 DESC)
SELECT * FROM Q WHERE AVG_IW > 100;

--50000 IS ENOUGH FOR ~7H WORKLOAD
WITH Q AS
(SELECT TRUNC(AVG(IW)) AS AVG_IW, SUM(IW) AS TOTAL_IW, TRUNC(AVG(IR)) AS AVG_IR, SUM(IR) AS TOTAL_IR, DISK FROM DS GROUP BY DISK ORDER BY 1 DESC)
SELECT SUM(TOTAL_IW+TOTAL_IR)||' '||'TOTAL' AS TOTAL_IOS, COUNT(*) AS CNT FROM Q
UNION
SELECT SUM(TOTAL_IW+TOTAL_IR)||' '||'BUSIEST', COUNT(*) FROM Q WHERE TOTAL_IW+TOTAL_IR > 50000;