primary.11.cfg

# vim: syntax=sql
# modified 25.04.2020

[auto_discovery_1000]
minutes: 1000
expu.lld: select '' "{#PDB}", username "{#USERNAME}"
            from dba_users s
            where account_status IN('OPEN', 'EXPIRED(GRACE)')
            and expiry_date > sysdate
            and expiry_date < (sysdate + 30)
ustat.lld: select d.name "{#PDB}", account_status "{#STATUS}"
            from dba_users, v$database d
            group by account_status, d.name
features.lld: select  c.name "{#PDB}", f.name "{#NAME}"
              from dba_feature_usage_statistics f cross join v$database c
              join dba_registry r on(r.version=f.version)
              where r.comp_id = 'CATALOG'
[auto_discovery_60]
minutes: 60
inst.lld: select distinct(inst_name) "{#INST_NAME}"
            from (select rtrim(substr(regexp_replace(inst_name, '[^[:print:]]', ''), instr(inst_name, ':')+1)) inst_name from v$active_instances
                  union
                  select instance_name from gv$instance)
db.lld: select name "{#PDB}" from v$database
parm.lld: select i.instance_name "{#INST_NAME}", p.name "{#PARAMETER}"
            from gv$instance i, gv$parameter p
            where i.instance_number = p.inst_id
            and p.type in (3, 6) and p.isdefault = 'FALSE'
p_ts.lld: select ts.tablespace_name "{#TS_NAME}", d.name "{#PDB}"
            from dba_tablespaces ts cross join v$database d
            where ts.contents = 'PERMANENT'
t_ts.lld: select ts.tablespace_name "{#TS_NAME}", d.name "{#PDB}"
            from dba_tablespaces ts cross join v$database d
            where ts.contents = 'TEMPORARY'
u_ts.lld: select ts.tablespace_name "{#TS_NAME}", d.name "{#PDB}"
            from dba_tablespaces ts cross join v$database d
            where ts.contents = 'UNDO'
service.lld: select '' "{#PDB}", i.instance_name "{#INST_NAME}", s.name "{#SERVICE_NAME}"
               from gv$services s join gv$instance i
                 on(s.inst_id=i.inst_id)
rman.lld: select distinct(object_type) "{#OBJ_TYPE}" from v$rman_status where operation like 'BACKUP%'
          union all
	  select 'usermanaged' from v$backup where change# > 0 and file# = 1
arl_dest.lld: select i.instance_name "{#INST_NAME}", d.dest_name "{#ARL_DEST}"
            from gv$archive_dest d
            , gv$instance i
            , v$database db
            where d.status != 'INACTIVE'
            and d.inst_id = i.inst_id
            and db.log_mode = 'ARCHIVELOG'
schema.lld: select d.name "{#PDB}", c.owner "{#SCHEMA}"
            from dba_objects c, v$database d
            group by c.owner, c.status, d.name
[startup]
minutes: 0
version: select 'inst[' | |instance_name | |',version]', version from gv$instance
lastpatch: select  'db[last_patch_hist]', ACTION | |':' | |NAMESPACE | |':' | |VERSION | |':' | |ID | |':' | |COMMENTS
        from sys.registry$history
        where action_time = (select max(action_time) from sys.registry$history)

[checks_01m]
minutes: 1
inst.uptime: select 'inst[' | |instance_name | |',uptime]' key, (sysdate - startup_time)*60*60*24 val from gv$instance
db.openmode: select 'db[' | |name | |',openstatus]', decode(open_mode, 'MOUNTED', 1, 'READ ONLY', 2, 'READ WRITE', 3, 0) from v$database
scn: select 'db[current_scn]', current_scn from v$database
     union all
     select 'db[delta_scn]', current_scn from v$database
blocked: select 'blocked[topsid]', topsid | |'(' | |blocked | |')'
          from (
          select final_blocking_instance | |'/' | |final_blocking_session topsid, count(*) blocked
          from gv$session
          where  FINAL_BLOCKING_SESSION_STATUS='VALID'
          group by final_blocking_instance | |'/' | |final_blocking_session
          order by 2 desc, 1
          )
          where rownum < 2
          union all
          select 'blocked[count]', '' | |count(*)
           from gv$session
           where  FINAL_BLOCKING_SESSION_STATUS = 'VALID'

sysmetric: select 'inst['||replace(m.event,' ','_')||','||replace(m.unit,' ','_')||']' "metric, units", m.value
  -- AU: pereformance metrics from v$sysmetric
  -- values from 15 sec intervals
            from (
               select
                substr(metric_name,0,45) event,
                substr(metric_unit,0,25) unit,
                round(value,2) value
                  from v$sysmetric
                    where
                    group_id = 2
  -- group_id = 2 - 15 sec interval, 3 = 60 sec interval
                    AND
                    metric_name in (
  -- these metrics requere UNIT for explain
                      'DB Block Changes Per Sec',
                      'Library Cache Hit Ratio',
                      'Network Traffic Volume Per Sec',
                      'PGA Cache Hit %',
                      'Redo Generated Per Sec',
                      'Response Time Per Txn')
               ) m
    union
    select 'inst['||replace(n.event,' ','_')||']' "metric", n.value
            from (
               select
                substr(metric_name,0,45) event,
                round(value,2) value
                  from v$sysmetric
                    where
                    group_id = 2
  -- group_id = 2 - 15 sec interval, 3 = 60 sec interval
                    AND
                    metric_name in (
  -- these metrics do not requere units
                      'Executions Per Sec',
                      'I/O Megabytes per Second',
                      'I/O Requests per Second',
                      'Logical Reads Per Sec',
                      'Physical Read Total Bytes Per Sec',
                      'Physical Read Total IO Requests Per Sec',
                      'Physical Reads Per Sec',
                      'Physical Write Bytes Per Sec',
                      'Physical Write IO Requests Per Sec',
                      'Physical Write Total Bytes Per Sec',
                      'Physical Write Total IO Requests Per Sec',
                      'Physical Writes Per Sec',
                      'Recursive Calls Per Sec',
                      'Redo Writes Per Sec',
                      'Total Index Scans Per Sec',
                      'Total Parse Count Per Sec',
                      'Total Table Scans Per Sec',
                      'User Calls Per Sec')
                ) n

eventmetric: select  'inst['||replace(substr(n.name,0,25),' ','_')||',ms]' "metric, units",
  -- AU: IO pereformance metrics from v$eventmetric in AWR style
  -- values from 60 sec intervals (more precisely INTSIZE_CSEC)
              nvl(round(10*m.time_waited/nullif(m.wait_count,0),3) ,0) avg_ms -- to ms = * 1000 / 100
            from v$eventmetric m,
                 v$event_name n
            where m.event_id=n.event_id
            and n.name in (
              'db file sequential read',
              'db file scattered read',
              'db file single write',
              'db file parallel write',
              'direct path read',
              'direct path write',
              'direct path read temp',
              'direct path write temp',
              'log file sync',
              'log file parallel write')

sysstat: select 'inst[' | |i.instance_name | |',' | |s.name | |']', s.value
          from (
          select inst_id, name, value from gv$sysstat
          where name in ('execute count', 'DB time', 'user calls')
          union all
          select inst_id, 'processes' item, count(*) cnt
          from gv$process
          group by inst_id
          union all
          select inst_id, 'sessions', count(*)
          from gv$session
          group by inst_id
          ) s,
          gv$instance i
          where i.inst_id = s.inst_id
[checks_05m]
minutes: 5
parm.val:  select 'parm[' | |i.instance_name | |',' | |p.name | |',value]' key, p.value
            from gv$instance i, gv$parameter p
            where i.instance_number = p.inst_id
            and p.type in (3, 6) and p.isdefault = 'FALSE'
            and upper(p.description) not like '%SIZE%'
            union all
            select 'parm[' | |i.instance_name | |',' | |p.name | |',size]' key, p.value
            from gv$instance i, gv$parameter p
            where i.instance_number = p.inst_id
            and p.type in (3, 6) and p.isdefault = 'FALSE'
            and upper(p.description) like '%SIZE%'
service.cnt: select 'service[' | |d.name | |',' | |i.instance_name | |',' | | s.service_name | |',sess]', count(*)
               from gv$session s join gv$instance i
                 on(s.inst_id=i.inst_id)
								 cross join v$database d
                 group by i.instance_name, s.service_name, d.name
obj.stat: with all_status as
					(
					select 'INVALID' status from dual
					union all
					select 'VALID' status from dual
					)
					, schemas as
					(
					select distinct s.owner, a.status
					from dba_objects s
					cross join all_status a
					)
					select 'objects[' | |d.name | |',' | |a.owner | |',' | |a.status | |',count]', count(obj.status)
					from schemas a
					left join dba_objects obj
					on(a.status=obj.status
					and a.owner=obj.owner)
					, v$database d
					group by a.status, d.name, a.owner

u_ts: SELECT REPLACE(a.key, '{#PDB}', d.name) KEY, a.value
      FROM
      v$database d,
      (
          SELECT 'u_ts[{#PDB},' | | tablespace_name | | ',' | |
              CASE
                  WHEN k=1 THEN 'filesize]'
                  WHEN k=2 THEN 'maxsize]'
                  WHEN k=3 THEN 'freebytes]'
                  WHEN k=4 THEN 'pctfree]'
                  WHEN k=5 THEN 'pctfreeMAX]'
              END KEY,
              CASE
                  WHEN k=1 THEN file_size
                  WHEN k=2 THEN file_max_size
                  WHEN k=3 THEN file_size-file_used_space
                  WHEN k=4 THEN ROUND((file_size-file_used_space)/file_size*100, 2)
                  WHEN k=5 THEN ROUND((file_max_size-file_used_space)/file_max_size*100, 2)
              END value
          FROM
              (
                  SELECT t1.tablespace_name, t1.file_size, t1.file_max_size,
                      CASE
                          WHEN t1.retention='GUARANTEE'
                          THEN NVL(t2.active_file_used_space, 0)+NVL(t2.unexpired_file_used_space, 0)
                          WHEN t1.retention='NOGUARANTEE'
                          THEN NVL(t2.active_file_used_space, 0)
                      END file_used_space
                  FROM
                      (
                          SELECT t.tablespace_name, t.retention,
                              ROUND(SUM(f.bytes)) file_size,
                              ROUND(SUM(
                                  CASE
                                      WHEN f.autoextensible='NO'
                                      THEN f.bytes
                                      ELSE greatest(f.bytes, f.maxbytes)
                                  END)) file_max_size
                          FROM
                              dba_data_files f,
                              dba_tablespaces t
                          WHERE
                              t.contents='UNDO'
                          AND t.tablespace_name=f.tablespace_name
                          GROUP BY
                              t.tablespace_name,
                              t.retention) t1,
                      (
                          SELECT *
                          FROM
                              (
                                  SELECT tablespace_name, status, bytes
                                  FROM
                                      dba_undo_extents) pivot(SUM(bytes) AS file_used_space FOR
                              status IN('UNEXPIRED' AS unexpired,
                                         'EXPIRED'   AS expired,
                                         'ACTIVE'    AS active))) t2
                  WHERE
                      t1.tablespace_name=t2.tablespace_name(+))
          CROSS JOIN
              (SELECT level k FROM dual CONNECT BY level <= 5) k) a


t_ts: select   't_ts[' | |dbname | |',' | |t.TABLESPACE | |',filesize]', t.totalspace
    from (select   round(sum(d.bytes))  AS totalspace,
                   round(sum(case when maxbytes < bytes then bytes else maxbytes end)) max_bytes,
									 d.tablespace_name tablespace,
                   db.name dbname
              from dba_temp_files d      cross join v$database db
          group by d.tablespace_name, db.name) t
   union all
   select   't_ts['||dbname||','||t.TABLESPACE_name||',maxsize]', sum(maxbytes)
        from (select case when autoextensible = 'NO'
                               then bytes
                     else
                      case when bytes > maxbytes
                               then bytes
                      else          maxbytes
                      end
                     end maxbytes, tablespace_name,
                     db.name dbname
                from dba_temp_files cross join v$database db) f
            , dba_tablespaces t
       where t.contents = 'TEMPORARY'
         and  t.tablespace_name = f.tablespace_name
       group by t.tablespace_name, dbname
  union all
  select 't_ts['||db.name||','||t.tablespace_name||',usedbytes]', nvl(sum(u.blocks*t.block_size),0) bytes
    from gv$sort_usage u right join
       dba_tablespaces t
           on ( u.tablespace = t.tablespace_name)
                cross join v$database db
             where   t.contents = 'TEMPORARY'
               group by t.tablespace_name, db.name
  union all
  select   't_ts['||dbname||','||t.TABLESPACE_name||',pctfree]', round(((t.totalspace - nvl(u.usedbytes,0))/t.totalspace)*100,2) "PCTFREE"
    from (select   round (sum (d.bytes))  AS totalspace,
                   round (sum ( case when maxbytes < bytes then bytes else maxbytes end)) max_bytes,
                   d.tablespace_name, db.name dbname
              from dba_temp_files d cross join v$database db
          group by d.tablespace_name, db.name) t
      left join (
                        select u.tablespace tablespace_name, round(sum(u.blocks*t.block_size)) usedbytes
                        from gv$sort_usage u
                        , dba_tablespaces t
                        where u.tablespace = t.tablespace_name
                        and   t.contents = 'TEMPORARY'
                        group by tablespace
                 )u
           on t.tablespace_name = u.tablespace_name
  union all
  select   't_ts['||dbname||','||t.TABLESPACE_name||',pctfreeMAX]', round(((t.max_bytes - nvl(u.usedbytes,0))/t.max_bytes)*100,2) "PCTFREEmax"
    from (select   round (sum (d.bytes))  AS totalspace,
                   round (sum ( case when maxbytes < bytes then bytes else maxbytes end)) max_bytes,
                   d.tablespace_name, db.name dbname
              from dba_temp_files d cross join v$database db
          group by d.tablespace_name, db.name) t
      left join (
                        select u.tablespace tablespace_name, round(sum(u.blocks*t.block_size)) usedbytes
                        from gv$sort_usage u
                        , dba_tablespaces t
                        where u.tablespace = t.tablespace_name
                        and   t.contents = 'TEMPORARY'
                        group by tablespace
                 )u
           on t.tablespace_name = u.tablespace_name

arl_dest: select 'arl_dest['|| i.instance_name||','||d.dest_name||',status]', ''||decode (d.status,'VALID',0,'DEFERRED',1,'ERROR',2,3)
            from gv$archive_dest d
            , gv$instance i
            , v$database db
            where d.status != 'INACTIVE'
            and   d.inst_id = i.inst_id
            and db.log_mode = 'ARCHIVELOG'
          union all
          select 'arl_dest['|| i.instance_name||','||d.dest_name||',sequence]', ''||d.log_sequence
            from gv$archive_dest d
            , gv$instance i
            , v$database db
            where d.status != 'INACTIVE'
            and   d.inst_id = i.inst_id
            and db.log_mode = 'ARCHIVELOG'
          union all
          select 'arl_dest['|| i.instance_name||','||d.dest_name||',error]', '"'||d.error||'"'
            from gv$archive_dest d
            , gv$instance i
                , v$database db
            where d.status != 'INACTIVE'
            and   d.inst_id = i.inst_id
            and db.log_mode = 'ARCHIVELOG'

fra: select 'fra[limit]', space_limit from v$recovery_file_dest def
      union all
     select 'fra[used]', space_used from v$recovery_file_dest def
      union all
     select 'fra[reclaimable]', space_reclaimable from v$recovery_file_dest def
      union all
     select 'fra[files]', number_of_files from v$recovery_file_dest def
      union all
     select 'fra[usable,pct]',decode(space_limit, 0,0,(100-(100*(space_used - space_reclaimable)/space_limit)))
       from v$recovery_file_dest
      union all
     select 'rp', count(*) from v$restore_point
[checks_20m]
minutes: 20
rman: with stats as (
        select r.object_type, r.operation, r.start_time, r.end_time, r.status
               ,max(start_time) over (partition by  r.object_type, r.operation) max_start
               , input_bytes, output_bytes
        from v$rman_status r
        where r.row_type = 'COMMAND'
        and   not r.object_type is null
        and   r.operation like 'BACKUP%'
        )
        , types as (
        select 'ARCHIVELOG' object_type from dual
        union all
        select 'CONTROLFILE' from dual
        union all
        select 'SPFILE' from dual
        union all
        select 'DB INCR' from dual
        union all
        select 'DB FULL' from dual
        union all
        select 'RECVR AREA' from dual
        )
        , data as (
        select t.object_type, s.start_time, nvl(s.status,'noinfo') status, round(nvl((s.end_time - s.start_time),0)*24*60*60) elapsed
        , nvl(input_bytes,0) input_bytes, nvl(output_bytes,0) output_bytes
        from types t left outer join
             stats s on (s.object_type = t.object_type)
        where nvl(s.start_time,sysdate) = nvl(s.max_start,sysdate)
        )
        select 'rman['||object_type||',status]', ''||decode(status,'COMPLETED',0,
                                               'FAILED',   1,
                                               'COMPLETED WITH WARNINGS',2,
                                               'COMPLETED WITH ERRORS',  3,
                                               'noinfo',                 4,
                                               'RUNNING',                5,
                                               9) status
        from data
        union all
        select 'rman['||object_type||',ela]',''||elapsed
        from data
        union all
        select 'rman['||object_type||',input]',''||input_bytes
        from data
        union all
        select 'rman['||object_type||',output]',''||output_bytes
        from data
        union all
        select 'rman['||object_type||',age]',''||
                        case when round((sysdate - nvl(start_time,sysdate))*24*3600) < 0 then
                        0
                        else round((sysdate - nvl(start_time,sysdate))*24*3600)
                        end age
        from data
        union all select 'rman[bct,status]', ''||decode(status,'ENABLED',0,'DISABLED',1,2) from v$block_change_tracking
        union all select 'rman[bct,file]', filename from v$block_change_tracking
        union all select 'rman[bct,bytes]', ''||nvl(bytes,0) from v$block_change_tracking
				union all select * from (
                    select 'rman[usermanaged,status]', ''||decode(STATUS, 'ACTIVE',5, 0)
                    from v$backup
                    where change# > 0
                    order by status
                  )
			           where rownum = 1
        union all select 'rman[usermanaged,age]', ''||round((sysdate - nvl(min(time),sysdate))*24*3600)
						      from v$backup
[checks_60m]
minutes: 60
p_ts: SELECT   'p_ts['||d.name||','||tablespace_name||','||
           CASE
             WHEN k = 1 THEN 'filesize]'
             WHEN k = 2 THEN 'maxsize]'
             WHEN k = 3 THEN 'usedbytes]'
             WHEN k = 4 THEN 'pctfree]'
             WHEN k = 5 THEN 'pctfreeMAX]'
           END key
  ,        CASE
           WHEN k = 1 THEN file_size
           WHEN k = 2 THEN file_max_size
           WHEN k = 3 THEN file_size - file_free_space
           WHEN k = 4 THEN ROUND(file_free_space / file_size * 100,2)
           WHEN k = 5 THEN ROUND((file_free_space + (file_max_size - file_size)) / file_max_size * 100,2)
           END value
  FROM   ( --
         select t1.tablespace_name,
               t1.file_size,
               t1.file_max_size,
               t2.file_free_space
          from (  select t.tablespace_name, sum (f.bytes) file_size, sum (case when f.autoextensible = 'NO' then f.bytes else greatest (f.bytes, f.maxbytes) end) file_max_size
                    from dba_tablespaces t, dba_data_files f
                   where t.CONTENTS = 'PERMANENT' and f.tablespace_name = t.tablespace_name
                group by t.tablespace_name) t1,
  -- AU: Oracle 11-12 has performance issue with dba_free_space
  -- Original select:
  --               (  select f.tablespace_name, sum (f.bytes) file_free_space
  --                    from dba_free_space f
  --                group by tablespace_name) t2
               (  select a.tablespace_name, sum (a.bytes) file_free_space
                    FROM   SYS.dba_data_files b, SYS.dba_free_space a
                    WHERE a.tablespace_name = b.tablespace_name
                    GROUP BY a.tablespace_name, b.tablespace_name) t2
         where t1.tablespace_name = t2.tablespace_name(+)
       )
  cross JOIN   ( SELECT LEVEL k FROM dual CONNECT BY LEVEL <= 5 ) k
  cross join v$database d
db.size: select 'db[filesize]',(
          (select sum(bytes) from v$datafile) +
          (select sum(bytes) from v$tempfile) +
          (select sum(file_size_blks*block_size) from v$controlfile) +
          (select sum(bytes * members) from v$log)
          ) totalsize from dual
          union all
          select 'db[usedbytes]',  sum(u.allocated_space*t.blocksize)
          from V$FILESPACE_USAGE u join sys.ts$  t
             on (u.tablespace_id = t.ts#)
expu: select 'expu[,'|| username||',expiring]' key, (expiry_date - sysdate)*24*3600 value
	from dba_users s
	where account_status IN ( 'OPEN', 'EXPIRED(GRACE)' )
	-- and expiry_date > sysdate
	-- and expiry_date < (sysdate + 30)
  union all
  select 'ustat[,'||account_status||',count]' key, count(*) value
  from dba_users
  group by account_status
alertlog: select 'inst['||i.instance_name||',log]', d.value||'/alert_'||i.instance_name||'.log' from gv$instance i, gv$diag_info d
           where i.inst_id = d.inst_id and d.name = 'Diag Trace'
instname: select 'zbxdb[connect,instance_name]', instance_name from v$instance
fullversion: select 'full_version', banner from v$version where banner like 'Oracle Database%'
[checks_720m]
minutes: 720
version: select 'inst['||instance_name||',version]', version from gv$instance
lastpatch: select  'db[last_patch_hist]', ACTION||':'||NAMESPACE||':'||VERSION||':'||ID||':'||COMMENTS
        from sys.registry$history
        where action_time = (select max(action_time) from sys.registry$history)
features: select  'feature['||c.name||','||f.name||',inuse]', decode(currently_used,'TRUE',0,'FALSE',1,2)
          from dba_feature_usage_statistics f
          join v$database c on (c.dbid = f.dbid)
          join dba_registry r on (r.version = f.version)
          where r.comp_id = 'CATALOG'
          and   f.last_sample_period <> 0