
-- run this script as SYS user

-- create database user for nagios:

CREATE USER NAGIOS IDENTIFIED BY nagiospass;

grant create table to nagios;

CREATE TABLE EXP_JOB_DETAILS
  (
    "EXP_TYPE"     VARCHAR2(5 BYTE),
    "EXP_EXITCODE" NUMBER(5,0),
    "EXP_STATUS"   VARCHAR2(5 BYTE),
    "EXP_DATE" DATE
  );

CREATE TABLE coldbackup_job_details
  (
	BACKUP_EXITCODE   NUMBER(5),
	BACKUP_STATUS	  VARCHAR2(5),
	BACKUP_DATE		  DATE    
  );
  
---
-- objects for alert.log maintenance (11g and above)
---

CREATE OR REPLACE FORCE VIEW alert_log (alert_date, alert_text) as
SELECT ORIGINATING_TIMESTAMP, message_text from X$DBGALERTEXT;

---
-- objects for alert.log maintenance (10g and below)
--
-- based on:
-- http://techsudo.blogspot.com/2009/01/query-oracle-alert-log-using-sql.html
---

DECLARE
 BDumpDir  VARCHAR2(200);
 SID       VARCHAR2(16);
 ObjectExists EXCEPTION;
 PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
 -- get the bdump dir
 SELECT value
 INTO BDumpDir
 FROM v$parameter
 WHERE name='background_dump_dest';
 -- create the directory for the bdump dir
 EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
   BDumpDir||'''';
 -- grant the necessary privileges
 EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
 -- get the SID
 SELECT instance_name INTO SID FROM v$instance;
 -- create the external table
 EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXTERNAL
   (TEXT VARCHAR2(255)
   ) ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY BDUMP_DIR
    ACCESS PARAMETERS
    (records delimited by newline
     nobadfile
     nologfile
    )
    LOCATION (''alert_'||SID||'.log'')
   )
   REJECT LIMIT UNLIMITED'
 ;
-- ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/

CREATE OR REPLACE FUNCTION system.alert_log_date( text IN VARCHAR2 )
 RETURN DATE
IS
 InvalidDate  EXCEPTION;
 PRAGMA EXCEPTION_INIT(InvalidDate, -1846);
BEGIN
 RETURN TO_DATE(text,'Dy Mon DD HH24:MI:SS YYYY', 'NLS_DATE_LANGUAGE=AMERICAN');
EXCEPTION
 WHEN InvalidDate THEN RETURN NULL;
END;
/


CREATE OR REPLACE FORCE VIEW system.alert_log as
SELECT row_num
     ,LAST_VALUE(low_row_num IGNORE NULLS)
        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW) start_row
     ,LAST_VALUE(alert_date  IGNORE NULLS)
        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW) alert_date
     ,alert_text
FROM (SELECT ROWNUM row_num
           ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num
           ,system.alert_log_date(text) alert_date
           ,text alert_text
     FROM system.alert_log_external
    )
;

create synonym sys.alert_log for system.alert_log;
  
-- privileges:

grant read on directory BDUMP_DIR to nagios;
grant create session to nagios;
grant select on dba_data_files to nagios;
grant select on dba_free_space to nagios;
grant select on filext$ to nagios;
grant select on v_$librarycache to nagios;
grant select on v_$process to nagios;
grant select on v_$sesstat to nagios;
grant select on v_$sort_segment to nagios;
grant select on v_$sysstat to nagios;
grant select on v_$instance to nagios;
grant select on v_$parameter to nagios;
grant select on v_$session to nagios;
grant select on dba_tablespaces to nagios;
grant select on sys.alert_log to nagios;
grant select on system.alert_log to nagios;
grant select on system.alert_log_external to nagios;
grant execute on system.alert_log_date to nagios;
grant select on V_$RMAN_BACKUP_JOB_DETAILS to nagios;
grant select on V_$LOGSTDBY_PROGRESS to nagios;
grant select on DBA_LOGSTDBY_LOG to nagios;
grant select on v_$archived_log to nagios;
grant select on v_$archive_gap to nagios;
grant select on dba_autotask_job_history to nagios;
grant select on DBA_SCHEDULER_JOB_RUN_DETAILS to nagios;
grant select on dba_tables to nagios;
grant select on V_$DATAGUARD_STATS to nagios;
grant select on v_$backup_set to nagios;
grant select on v_$backup_piece to nagios;
grant select on V_$BACKUP_DATAFILE to nagios;
grant select on DBA_TAB_STATS_HISTORY to nagios;
