primary.21.cfg

# vim: syntax=sql
[auto_discovery_1000]
minutes: 1000
expu.lld: select decode(s.con_id, 0, d.name, p.name) "{#PDB}", username "{#USERNAME}"
            from cdb_users s
            , v$containers p
            , v$database d
            where p.con_id(+) = s.con_id
            and account_status IN('OPEN', 'EXPIRED(GRACE)', 'OPEN & IN ROLLOVER')
            and expiry_date > sysdate
            and expiry_date < (sysdate + 30)
ustat.lld: select decode(s.con_id, 0, d.name, p.name) "{#PDB}", account_status "{#STATUS}"
           from (select con_id, account_status, count(*) cnt from cdb_users group by con_id, account_status) s
               , v$containers p
               , v$database d
            where s.con_id = p.con_id(+)
features.lld: select  c.name "{#PDB}", f.name "{#NAME}"
              from cdb_feature_usage_statistics f join v$containers c on (f.con_id = c.con_id)
              join cdb_registry r on (    r.con_id = c.con_id
                                      and 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$pdbs union select name from v$database where cdb = 'NO'

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 decode (s.con_id,0, d.name, p.name) "{#PDB}", tablespace_name "{#TS_NAME}"
            from cdb_tablespaces s
          , v$containers p
          , v$database d
          where p.con_id(+) = s.con_id
          and contents = 'PERMANENT'

t_ts.lld: select decode (s.con_id,0, d.name, p.name) "{#PDB}", tablespace_name "{#TS_NAME}"
            from cdb_tablespaces s
          , v$containers p
          , v$database d
          where p.con_id(+) = s.con_id
          and contents = 'TEMPORARY'

u_ts.lld: select decode (s.con_id,0, d.name, p.name) "{#PDB}", tablespace_name "{#TS_NAME}"
            from cdb_tablespaces s
          , v$containers p
          , v$database d
          where p.con_id(+) = s.con_id
          and contents = 'UNDO'

service.lld: select decode (s.con_id,0, d.name, pdb.name) "{#PDB}", i.instance_name "{#INST_NAME}",
                    nvl(s.name,s.network_name) "{#SERVICE_NAME}"
                    from gv$services s join gv$instance i
                    on ( s.inst_id = i.inst_id)
                    cross join v$database d
                    join gv$containers pdb
                    on (s.con_id = pdb.con_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 v.name "{#PDB}", c.owner "{#SCHEMA}"
            from cdb_objects c join v$containers v
            on (c.con_id = v.con_id)
            group by v.name, c.owner, c.status
[startup]
minutes: 0
version: select 'inst['||instance_name||',version]', version_full from v$instance
lastpatch: select  'db[last_patch_hist]', ACTION||':'||NAMESPACE||':'||VERSION||':'||ID||':'||COMMENTS||':'||BUNDLE_series status
        from sys.registry$history
        where action_time = (select max(action_time) from sys.registry$history)
        and   rownum < 2
[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,'READ ONLY WITH APPLY',4,'MIGRATE',5, 0)
             from (
                    select name, open_mode from v$pdbs
                     union
                    select name, open_mode from v$database where cdb = 'NO'
                  )
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'
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['||decode (s.con_id,0, d.name, pdb.name)||','||i.instance_name||','|| s.service_name||',sess]' ,count(*)
               from gv$session s join gv$instance i
                 on (   s.inst_id = i.inst_id)
                                 left join v$containers pdb
                 on (   s.con_id = pdb.con_id)
                     cross join v$database d
                 group by i.instance_name, s.service_name, decode (s.con_id,0, d.name, pdb.name)
obj.stat: with all_status as
      (
      select 'INVALID' status from dual
      union all
      select 'VALID' status from dual
      )
      , schemas as
      (
      select distinct s.con_id, s.owner, a.status
        from cdb_objects s
          cross join all_status a
       )
       select 'objects['||v.name||','|| a.owner||','|| a.status||',count]', count(obj.status)
       from schemas a
       left join cdb_objects obj
       on (a.status = obj.status
           and a.owner = obj.owner
               and a.con_id = obj.con_id)
               join v$containers v
               on (v.con_id = a.con_id)
               group by a.status, a.owner, v.name

u_ts: with undoexpirations as (
  SELECT *
          FROM
          (
          SELECT tablespace_name, status, bytes, con_id
          FROM
              cdb_undo_extents u ) pivot ( SUM(bytes) AS used_space FOR
          status IN ('UNEXPIRED' AS unexpired,
                 'EXPIRED'   AS expired,
                 'ACTIVE'    AS active) )
  )
  , files as (
  select tbs.con_id, tbs.tablespace_name, tbs.retention, sum(nvl(bytes,1)) filesize,
  sum(nvl(case when df.autoextensible = 'NO' then df.bytes
               else greatest(df.bytes, df.maxbytes)
          end,1)) file_max_size
  from cdb_tablespaces tbs left join cdb_data_files df
  on (tbs.con_id          = df.con_id
  and tbs.tablespace_name = df.tablespace_name
  )
  where tbs.contents = 'UNDO'
  group by tbs.con_id, tbs.tablespace_name, tbs.retention
  )
  , metrics as (
  select f.con_id, decode(f.con_id, 0, d.name, c.name) con_name
  , f.tablespace_name, f.filesize, f.file_max_size, f.retention
  , exp.unexpired_used_space, exp.expired_used_space, exp.active_used_space
  ,case when f.retention = 'GUARANTEE'
            then  NVL(exp.active_used_space,0)+NVL(exp.unexpired_used_space,0)
       when  f.retention =  'NOGUARANTEE'
            then NVL(exp.active_used_space,0)
  end used_space
  from files f
  left join undoexpirations exp
  on (f.con_id          = exp.con_id
  and f.tablespace_name = exp.tablespace_name
  )
  cross join v$database d
  join v$containers c on (c.con_id = f.con_id)
  )
  select 'u_ts['||m.con_name||','||m.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 filesize
       when k = 2 then file_max_size
       when k = 3 then used_space
       when k = 4 then round((filesize - used_space)/decode(filesize,0,1,filesize)  * 100,2)
       when k = 5 then round((file_max_size - used_space)/decode(file_max_size,0,1,file_max_size) * 100,2)
  end value
  from metrics m
  cross JOIN   ( SELECT LEVEL k FROM dual CONNECT BY LEVEL <= 5 ) k
  order by 1

t_ts:
  select /*+noparallel*/ 't_ts['||decode (s.con_id,0, d.name, p.name)||','||s.TABLESPACE||',filesize]', s.totalspace
    from (select   round (sum (d.bytes))  AS totalspace,
                   d.tablespace_name tablespace, con_id
              from cdb_temp_files d
          group by d.tablespace_name, d.con_id) s
          , v$containers p
         , v$database d
         where p.con_id(+) = s.con_id
   union all
  select   't_ts['||decode (s.con_id,0, d.name, p.name)||','||s.TABLESPACE_name||',maxsize]', maxbytes
        from (select f.con_id, f.tablespace_name, sum(maxbytes) maxbytes
        from (select case when autoextensible = 'NO'
                               then bytes
                     else
                      case when bytes > maxbytes
                               then bytes
                      else          maxbytes
                      end
                     end maxbytes, tablespace_name, con_id
                from cdb_temp_files) f
            , cdb_tablespaces t
       where t.contents = 'TEMPORARY'
         and  f.tablespace_name = t.tablespace_name
         and  f.con_id = t.con_id
       group by f.tablespace_name, f.con_id
       )s
       , v$containers p
         , v$database d
         where p.con_id(+) = s.con_id
  union all
  select 't_ts['||decode (s.con_id,0, d.name, p.name)||','||s.tablespace_name||',usedbytes]', bytes
   from ( select t.con_id, t.tablespace_name, nvl(sum(u.blocks*t.block_size),0) bytes
    from gv$sort_usage u right join
       cdb_tablespaces t
           on ( u.tablespace = t.tablespace_name and u.con_id = t.con_id )
             where   t.contents = 'TEMPORARY'
               group by t.tablespace_name, t.con_id
     )s
     , v$containers p
         , v$database d
         where p.con_id(+) = s.con_id
         union all
  select 't_ts['||decode (tf.con_id,0, d.name, p.name)||','||tf.tablespace_name||',pctfree]' key, round(((tf.bytes-(nvl(tu.blocks,0)*ts.block_size))/tf.bytes)*100,2) value
  from
      (
        select con_id, tablespace_name, sum(bytes) bytes, count(*) c
        from cdb_temp_files
        group by con_id, tablespace_name
      ) tf left join
      (
        select con_id, tablespace, sum(blocks) blocks, count(*) c
        from gv$sort_usage
        group by con_id, tablespace
      ) tu
  on (   tf.con_id = tu.con_id
     and tf.tablespace_name = tu.tablespace
     )
     join cdb_tablespaces ts
  on (   tf.con_id = ts.con_id
     and tf.tablespace_name = ts.tablespace_name
     )
     left join v$containers p
  on (  tf.con_id = p.con_id)
     cross join v$database d
  where ts.contents = 'TEMPORARY'
  union all
  select 't_ts['||decode (tf.con_id,0, d.name, p.name)||','||tf.tablespace_name||',pctfreeMAX]' key, round(((tf.bytes-(nvl(tu.blocks,0)*ts.block_size))/tf.bytes)*100,2) value
  from
      (
        select con_id, tablespace_name, sum(maxbytes) bytes, count(*) c
        from (select case when autoextensible = 'NO'
                               then bytes
                     else
                      case when bytes > maxbytes
                               then bytes
                      else          maxbytes
                      end
                     end maxbytes, tablespace_name, con_id
                from cdb_temp_files) f
        group by con_id, tablespace_name
      ) tf left join
      (
        select con_id, tablespace, sum(blocks) blocks, count(*) c
        from gv$sort_usage
        group by con_id, tablespace
      ) tu
  on (   tf.con_id = tu.con_id
     and tf.tablespace_name = tu.tablespace
     )
     join cdb_tablespaces ts
  on (   tf.con_id = ts.con_id
     and tf.tablespace_name = ts.tablespace_name
     )
     left join v$containers p
  on (  tf.con_id = p.con_id)
     cross join v$database d
  where ts.contents = 'TEMPORARY'

arl_dest: select 'arl_dest['|| i.instance_name||','||d.dest_name||',status]', ''||decode (d.status,'VALID',0,'DEFERRED',1,'ALTERNATE',2,'ERROR',3,'DISABLED',4,'BAD PARAM',5,'FULL',6,7)
            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['||s.con_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 bytes
  FROM   ( --
         SELECT   decode (t.con_id,0, d.name, p.name) con_name
         ,        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
         ,        SUM(NVL(( SELECT   SUM(a.bytes)
                            FROM     cdb_free_space a
                            WHERE    a.tablespace_name = t.tablespace_name
                            and      a.con_id         = t.con_id
                            AND      a.file_id         = f.file_id
                            AND      a.relative_fno    = f.relative_fno
                          ), 0)) file_free_space
         FROM     cdb_tablespaces t
         JOIN     cdb_data_files f
         ON     ( f.tablespace_name = t.tablespace_name and f.con_id = t.con_id )
         cross join v$database d
         left join v$containers p on (t.con_id = p.con_id)
         WHERE    t.CONTENTS = 'PERMANENT'
         GROUP BY t.tablespace_name, decode (t.con_id,0, d.name, p.name)
       ) s
  cross JOIN   ( SELECT LEVEL k FROM dual CONNECT BY LEVEL <= 5 ) k
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(bytes) from cdb_segments

expu: select 'expu['||decode (s.con_id,0, d.name, p.name)||','|| username||',expiring]' key, (nvl(expiry_date,(sysdate+100)) - sysdate)*24*3600 value
	from cdb_users s
	, v$containers p
	, v$database d
	where p.con_id(+) = s.con_id
	and account_status IN ( 'OPEN', 'EXPIRED(GRACE)', 'OPEN & IN ROLLOVER')
	-- and expiry_date > sysdate
	-- and expiry_date < (sysdate + 30)
  union all
  select 'ustat['||decode (s.con_id,0, d.name, p.name)||','|| s.account_status||',count]' key, s.cnt value
    from (select con_id, account_status, count(*) cnt from cdb_users group by con_id, account_status ) s
      , v$containers p
      , v$database d
      where s.con_id = p.con_id(+)
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)
          and rownum < 2
features: select  'feature['||c.name||','||f.name||',inuse]', decode(currently_used,'TRUE',0,'FALSE',1,2)
          from cdb_feature_usage_statistics f
          join v$containers c on (f.con_id = c.con_id and f.dbid = c.dbid)
          join cdb_registry r on (    r.con_id = c.con_id
                                  and r.version = f.version)
          where r.comp_id = 'CATALOG'