forked from ikzelf/zbxdb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
standby.19.cfg
207 lines (198 loc) · 8.54 KB
/
standby.19.cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
# vim: syntax=sql
[auto_discovery_60]
minutes: 60
t_ts.lld: select distinct decode (s.con_id,0, d.name, p.name) "{#PDB}", s.name "{#TS_NAME}"
from V$TABLESPACE s
, v$containers p
, v$database d
, v$tempfile t
where p.con_id(+) = s.con_id
and t.ts# = s.ts#
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'
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)
[startup]
minutes: 0
version: select 'inst['||instance_name||',version]', version_full from v$instance
[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
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
t_ts:
select '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'
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)
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_60m]
minutes: 60
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'
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
instname: select 'zbxdb[connect,instance_name]', instance_name from v$instance
fullversion: select 'full_version', banner from v$version where banner like 'Oracle Database%'