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;

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;