Pages

Thursday, August 25, 2016

Monitor Instance Activity by logging sessions, processes, and Logon Trigger by Example

===============================
General
===============================
Example of loging activity on Instance by several processes:
Log Sessions.
Log Processes.
Use Logon trigger, writing all login, logout, and basic session activity to a log file

===============================
Current Sessions Usage

===============================

SELECT schemaname, COUNT(*)
FROM V$SESSION
GROUP BY schemaname
ORDER BY COUNT(*) DESC
;

===============================
Log Sessions and Processes
===============================
Files:

SH_PROCESSES_HIST.sql
SH_SESSIONS_HIST.sql
MONITOR_DB_PKG.sql


SH_PROCESSES_HIST.sql
-- Create table
create table SH_PROCESSES_HIST
(
  run_date      DATE not null,
  schema_name   VARCHAR2(30) not null,
  processes_num NUMBER(9) not null
)
tablespace IGT_TABLE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SH_PROCESSES_HIST
  add constraint SH_PROCESSES_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
  using index 

  tablespace IGT_INDEX

SH_SESSIONS_HIST.sql
  -- Create table
create table SH_SESSIONS_HIST
(
  run_date     DATE not null,
  schema_name  VARCHAR2(30) not null,
  sessions_num NUMBER(9) not null
)
tablespace IGT_TABLE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SH_SESSIONS_HIST
  add constraint SH_SESSIONS_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
  using index tablespace IGT_INDEX;

MONITOR_DB_PKG.sql
CREATE OR REPLACE PACKAGE BODY MONITOR_DB_PKG IS
  PROCEDURE LOG_SESSIONS IS
  BEGIN
    INSERT INTO SH_SESSIONS_HIST  (run_date, schema_name, sessions_num)
    SELECT SYSDATE, schemaname, count(*)
      FROM ( SELECT *
               FROM V$SESSION
              WHERE paddr IN (
                    SELECT addr
                      FROM V$PROCESS
                     WHERE background IS NULL
                       AND username = 'oracle'
                       AND program LIKE 'oracle@aut-tma%')
    )
    GROUP BY schemaname;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO SH_SESSIONS_HIST  (run_date, schema_name, sessions_num)
      VALUES (SYSDATE,'N/A',0);
      COMMIT;
  END LOG_SESSIONS;
------------------------------------------------
  PROCEDURE LOG_PROCESSES IS
  BEGIN
    INSERT INTO SH_PROCESSES_HIST  (run_date, schema_name, processes_num)
    SELECT SYSDATE, schema_name, COUNT(*) 
      FROM (
            SELECT SESSIONS.schemaname AS schema_name,
                   PROCESSES.spid as process
              FROM V$PROCESS PROCESSES,               
                   V$SESSION SESSIONS 
             WHERE PROCESSES.program like 'oracle@aut-tma%' 
               AND PROCESSES.background IS NULL 
               AND PROCESSES.addr=SESSIONS.paddr  
               )
    GROUP BY schema_name ;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO SH_PROCESSES_HIST  (run_date, schema_name, processes_num)
      VALUES (SYSDATE,'N/A',0);
      COMMIT;
  END LOG_PROCESSES;
------------------------------------------------
  PROCEDURE LOG_ACTIVITY IS
  BEGIN    
    LOG_SESSIONS;
    LOG_PROCESSES;
  EXCEPTION
    WHEN OTHERS THEN 
      NULL;  
  END LOG_ACTIVITY;
------------------------------------------------
END MONITOR_DB_PKG;



===============================
Logon Trigger
===============================
Files:
permission.sql
AUD_ACTIVITY.sql
LOGON_AUDIT_TRIGGER.sql
LOGOFF_AUDIT_TRIGGER.sql

permission.sql

CREATE USER MONITOR_USER IDENTIFIED BY MONITOR_PASS;

GRANT CONNECT, RESOURCE TO MONITOR_USER;

GRANT SELECT ANY DICTIONARY TO MONITOR_USER;

GRANT UNLIMITED TABLESPACE TO MONITOR_USER;

GRANT ADMINISTER DATABASE TRIGGER TO MONITOR_USER;

AUD_ACTIVITY.sql
-- Create table
create table AUD_ACTIVITY
(
  session_id          NUMBER(10) not null,
  session_sid         NUMBER(10) not null,
  session_serial_num  VARCHAR2(40) not null,
  user_id             VARCHAR2(120),
  process             VARCHAR2(24),
  session_host        VARCHAR2(64),
  session_os_user     VARCHAR2(30),
  session_schema_name VARCHAR2(30),
  session_program     VARCHAR2(48),
  session_module      VARCHAR2(64),
  session_action      VARCHAR2(64),
  last_program        VARCHAR2(48),
  last_action         VARCHAR2(64),
  last_module         VARCHAR2(64),
  logon_day           DATE,
  logon_time          VARCHAR2(40),
  logoff_day          DATE,
  logoff_time         VARCHAR2(40),
  elapsed_minutes     NUMBER(8)
)
tablespace IGT_TABLE;
-- Create/Recreate indexes 
create index AUD_ACTIVITY_IND1 on AUD_ACTIVITY (USER_ID, LOGON_DAY) tablespace IGT_INDEX;

  -- Create/Recreate primary, unique and foreign key constraints 
alter table AUD_ACTIVITY
  add constraint AUD_ACTIVITY_PK primary key (SESSION_ID, SESSION_SID, SESSION_SERIAL_NUM)  using index tablespace IGT_INDEX;


LOGON_AUDIT_TRIGGER.sql
CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER AFTER LOGON ON DATABASE

DECLARE

 v_session_id    NUMBER(10):=0;


 v_session_sid         V$SESSION.sid%TYPE;
 v_session_serial      V$SESSION.serial#%TYPE;
 v_session_paddr       V$SESSION.paddr%TYPE;
 v_session_module      V$SESSION.module%TYPE;
 v_session_program     V$SESSION.program%TYPE;
 v_session_action      V$SESSION.action%TYPE;
 v_session_host        V$SESSION.machine%TYPE;
 v_session_os_user     V$SESSION.osuser%TYPE;
 v_schema_name         V$SESSION.schemaname%TYPE;

 v_process             V$PROCESS.spid%TYPE;

BEGIN

  v_session_id    := sys_context('USERENV','SESSIONID');

  IF v_session_id != 0 AND USER NOT IN ('SYS','SYSTEM','SITEBACKUP','SHDAEMON') AND USER = 'MONITOR_USER'
  THEN
    BEGIN
      SELECT sid, serial#, paddr, module, program, action, machine, osuser, schemaname
      INTO v_session_sid, v_session_serial, v_session_paddr, v_session_module,  v_session_program, v_session_action, v_session_host, v_session_os_user, v_schema_name
      FROM V$SESSION
      WHERE audsid=v_session_id
      AND ROWNUM<2;
    EXCEPTION
      WHEN OTHERS THEN
        v_session_sid:=9999999;
        v_session_serial:='9999999';
        v_session_module := 'N/A';
        v_session_program:= 'N/A';
        v_session_host := 'N/A';
        v_session_os_user := 'N/A';
        v_schema_name := 'N/A';
        v_session_action := 'N/A';
    END;

    BEGIN
      SELECT spid
        INTO v_process
        FROM V$PROCESS
       WHERE ADDR = v_session_paddr;
    EXCEPTION
      WHEN OTHERS THEN
        v_process := 0;
    END;

    INSERT INTO AUD_ACTIVITY
    (
      session_id,   
      session_sid,
      session_serial_num,
      user_id,
      process,
      session_host,
      session_os_user,
      session_schema_name,
      session_program,
      session_module,
      session_action,
      last_program,
      last_action,
      last_module,
      logon_day,
      logon_time,
      logoff_day,
      logoff_time,
      elapsed_minutes     
    )
    VALUES
    ( v_session_id,
      v_session_sid,
      v_session_serial,
      USER,
      v_process,
      v_session_host,
      v_session_os_user,
      v_schema_name,
      v_session_program,
      v_session_module,
      v_session_action,
      NULL,
      NULL,
      NULL,
      SYSDATE,
      TO_CHAR(SYSDATE,'HH24:MI:SS'),
      NULL,
      NULL,
      NULL);

  END IF;

EXCEPTION
  WHEN OTHERS THEN
    NULL;

END;


LOGOFF_AUDIT_TRIGGER.sql
CREATE OR REPLACE TRIGGER LOGOFF_AUDIT_TRIGGER BEFORE LOGOFF ON DATABASE
DECLARE

  v_session_id    NUMBER(10):=0;

  v_last_module   V$SESSION.module%TYPE;
  v_last_program  V$SESSION.program%TYPE;
  v_last_action   V$SESSION.action%TYPE;  

BEGIN
  -- ***************************************************
  -- Update the last action accessed
  -- ***************************************************
  v_session_id    := sys_context('USERENV','SESSIONID');

  IF v_session_id != 0 AND USER NOT IN ('SYS','SYSTEM','SITEBACKUP','SHDAEMON') AND USER = 'MONITOR_USER'
  THEN

    BEGIN
      SELECT action, program, module
        INTO v_last_action, v_last_program, v_last_module
        FROM V$SESSION
       WHERE audsid = v_session_id
         AND rownum < 2;
    EXCEPTION
      WHEN OTHERS THEN
        v_last_action   := NULL;
        v_last_program  := NULL;
        v_last_module   := NULL;
    END;

    UPDATE AUD_ACTIVITY
     SET last_action  = v_last_action,
         last_program = v_last_program,
         last_module  = v_last_module,
         logoff_day   = sysdate,
         logoff_time  = to_char(sysdate, 'hh24:mi:ss'),
         elapsed_minutes = round((sysdate - logon_day) * 1440)
    WHERE session_id = v_session_id;

  END IF;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

No comments:

Post a Comment