[Home] [Help]
PACKAGE BODY: ORACLE_OCM.MGMT_DB_LL_METRICS
Source
1 PACKAGE body MGMT_DB_LL_METRICS AS
2
3 g_config_handle UTL_FILE.FILE_TYPE := NULL;
4 g_version_category VARCHAR2(10) := NULL;
5
6 g_dbID v$database.DBID%TYPE := NULL;
7 g_db_version v$instance.version%TYPE := NULL;
8
9 g_is_cdb VARCHAR2(4) := 'NO';
10
11 METRIC_END_MARKER constant VARCHAR2(4) := ':End';
12 METRIC_BEGIN_MARKER constant VARCHAR2(6) := ':Begin';
13
14 INSTANCE_DELIMITER constant VARCHAR2(1) := ':';
15 CELL_DELIMITER constant VARCHAR2(1) := '&';
16
17 /*
18 Compute the version category
19 */
20 FUNCTION get_version_category RETURN VARCHAR2 IS
21 l_db_version v$instance.version%TYPE;
22 l_temp_version v$instance.version%TYPE;
23 l_compat_vers v$parameter.value%TYPE;
24 l_major_version_ndx NUMBER;
25 BEGIN
26 IF g_db_version IS NULL THEN
27 select version into l_db_version from v$instance;
28 ELSE
29 l_db_version := g_db_version;
30 END IF;
31
32 begin
33 select substr(value,1,5) into l_compat_vers from v$parameter where lower(name) = 'compatible';
34 exception
35 WHEN NO_DATA_FOUND THEN
36 l_compat_vers := SUBSTR(l_db_version,1,5);
37 end;
38
39 l_temp_version := LPAD(l_db_version,10, '0');
40 IF l_temp_version < MIN_SUPPORTED_VERSION THEN
41 return NOT_SUPPORTED_VERSION;
42 END IF;
43
44 IF l_compat_vers = '8.1.7' THEN
45 return VERSION_817;
46 END IF;
47 l_temp_version := SUBSTR(l_db_version,1,4);
48 IF l_temp_version = '10.1' THEN
49 return VERSION_10gR1;
50 END IF;
51 IF l_temp_version= '10.2' THEN
52 return VERSION_10gR2;
53 END IF;
54 IF l_temp_version= '11.1' THEN
55 return VERSION_11gR1;
56 END IF;
57 IF l_temp_version= '11.2' THEN
58 return VERSION_11gR2;
59 END IF;
60 IF l_temp_version= '12.0' OR l_temp_version='12.1' THEN
61 return VERSION_12gR1;
62 END IF;
63 l_temp_version := SUBSTR(l_db_version,1,3);
64 IF l_temp_version = '9.2' THEN
65 return VERSION_9iR2;
66 END IF;
67 l_temp_version := SUBSTR(l_db_version,1,3);
68 IF l_temp_version = '9.0' THEN
69 return VERSION_9i;
70 END IF;
71 l_temp_version := SUBSTR(l_db_version,1,5);
72 IF l_temp_version = '8.1.7' THEN
73 return VERSION_817;
74 END IF;
75 return HIGHER_SUPPORTED_VERSION;
76 END get_version_category;
77
78
79 /* Check for consolidated database */
80 PROCEDURE CHECK_IS_CDB IS
81 BEGIN
82 IF g_version_category = VERSION_12gR1 THEN
83 BEGIN
84 execute immediate 'SELECT UPPER(CDB) FROM V$DATABASE' into g_is_cdb;
85 EXCEPTION
86 WHEN OTHERS THEN
87 null;
88 END;
89 END IF;
90 IF g_is_cdb = 'YES' THEN
91 execute immediate 'alter session set container=CDB$ROOT';
92 END IF;
93 END;
94
95
96 /*
97 Write em_error record
98 */
99 PROCEDURE write_error(p_error_msg VARCHAR2) IS
100 BEGIN
101 UTL_FILE.PUT_LINE(g_config_handle,'em_error=' || p_error_msg);
102 UTL_FILE.FFLUSH(g_config_handle);
103 END write_error;
104
105
106 /*
107 Put marker for the metric
108 */
109 PROCEDURE put_metric_marker(marker in VARCHAR2,
110 metric in VARCHAR2,
111 instance_name in VARCHAR2 default null,
112 cell_name in VARCHAR2 default null )
113 IS
114 BEGIN
115 UTL_FILE.PUT( g_config_handle, metric );
116 IF instance_name is not NULL THEN
117 UTL_FILE.PUT( g_config_handle, INSTANCE_DELIMITER||instance_name);
118 END IF;
119 IF cell_name is not NULL THEN
120 UTL_FILE.PUT( g_config_handle, CELL_DELIMITER||cell_name);
121 END IF;
122 UTL_FILE.PUT_LINE(g_config_handle,marker);
123 END put_metric_marker;
124
125
126 /*
127 Generic function to write results of the query to the config dump file
128 */
129 PROCEDURE write_results(query IN VARCHAR2, separator IN VARCHAR2 default '|')
130 IS
131 l_em_result_cur INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
132 l_col_cnt NUMBER DEFAULT 0;
133 /* increase size to handle escape characters */
134 l_columnValue VARCHAR2(6000);
135 l_status NUMBER ;
136
137 BEGIN
138 BEGIN
139 dbms_sql.parse(l_em_result_cur, query, dbms_sql.native);
140
141 /* define all the columns */
142 FOR i IN 1 .. 255 LOOP
143 BEGIN
144 dbms_sql.define_column(l_em_result_cur, i, l_columnValue, 4000);
145 l_col_cnt := i;
146 EXCEPTION
147 WHEN OTHERS THEN
148 IF (sqlcode = -1007) THEN exit;
149 ELSE
150 RAISE;
151 END IF;
152 END;
153 END LOOP;
154
155 dbms_sql.define_column(l_em_result_cur, 1, l_columnValue, 4000);
156 l_status := DBMS_SQL.EXECUTE (l_em_result_cur);
157
158 LOOP
159 exit when (dbms_sql.fetch_rows(l_em_result_cur) <= 0);
160 UTL_FILE.PUT( g_config_handle, 'em_result=');
161 FOR i IN 1 .. l_col_cnt LOOP
162 IF i != 1 THEN
163 UTL_FILE.PUT( g_config_handle, separator);
164 END IF;
165 dbms_sql.column_value( l_em_result_cur, i, l_columnValue );
166 /* replace seperators with escaped separators */
167 l_columnValue := replace(l_columnValue,'#','##');
168 l_columnValue := replace(l_columnValue,separator,'#'|| separator);
169 UTL_FILE.PUT( g_config_handle, l_columnValue );
170 END LOOP;
171 UTL_FILE.NEW_LINE(g_config_handle );
172 END LOOP;
173
174 dbms_sql.close_cursor(l_em_result_cur);
175
176 EXCEPTION
177 WHEN UTL_FILE.INVALID_FILEHANDLE
178 OR UTL_FILE.INVALID_OPERATION
179 OR UTL_FILE.WRITE_ERROR THEN
180 IF DBMS_SQL.IS_OPEN(l_em_result_cur) = TRUE THEN
181 dbms_sql.close_cursor(l_em_result_cur);
182 END IF;
183 RAISE;
184 WHEN OTHERS THEN
185 IF DBMS_SQL.IS_OPEN(l_em_result_cur) = TRUE THEN
186 dbms_sql.close_cursor(l_em_result_cur);
187 END IF;
188 /*
189 On any non-utl file exceptions, log as em_error
190 for the metric.
191 */
192 write_error('SQLERRM: ' || SQLERRM || ' SQLCODE: ' || SQLCODE);
193 END;
194 END write_results;
195
196
197 /*
198 Generic function to write to the config dump file
199 */
200 PROCEDURE write_metric(metric IN VARCHAR2, query IN VARCHAR2, instance_name in VARCHAR2 default null,
201 cell_name in VARCHAR2 default null, separator IN VARCHAR2 default '|')
202 IS
203 l_end_done BOOLEAN DEFAULT FALSE;
204 BEGIN
205 put_metric_marker(METRIC_BEGIN_MARKER,metric,instance_name, cell_name);
206 write_results(query,separator);
207 put_metric_marker(METRIC_END_MARKER,metric,instance_name, cell_name);
208 l_end_done := TRUE;
209 UTL_FILE.FFLUSH(g_config_handle);
210 EXCEPTION
211 WHEN OTHERS THEN
212 IF NOT l_end_done THEN
213 put_metric_marker(METRIC_END_MARKER,metric,instance_name, cell_name);
214 UTL_FILE.FFLUSH(g_config_handle);
215 END IF;
216 RAISE;
217 END write_metric;
218
219
220 /*
221 Private procedure
222 Collect metric=db_init_params
223 */
224 procedure collect_db_init_params IS
225 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
226 BEGIN
227 FOR inst_id_row in l_res_cur LOOP
228 write_metric('db_init_params',
229 'SELECT name, '||
230 ' case '||
231 ' when name=''filesystemio_options'' and value like ''asynch%'' then ''asynch'' '||
232 ' when name=''filesystemio_options'' and value like ''none%'' then ''none'''||
233 ' when name=''filesystemio_options'' and value like ''directIO%'' then ''directIO'''||
234 ' when name=''filesystemio_options'' and value like ''setall%'' then ''setall'''||
235 ' when name=''filesystemio_options'' then '' '''||
236 ' else value '||
237 ' end,'||
238 ' isdefault FROM gv$parameter ' ||
239 ' WHERE name != ''resource_manager_plan'' '||
240 ' AND inst_id = ' || inst_id_row.inst_id,inst_id_row.instance_name);
241 END LOOP;
242 END collect_db_init_params;
243
244
245 /*
246 Private procedure
247 Collect metric=cdb_init_params
248 */
249 procedure collect_cdb_init_params IS
250 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
251 BEGIN
252 FOR inst_id_row in l_res_cur LOOP
253 write_metric('cdb_init_params',
254 'SELECT pdb, '||
255 ' name, '||
256 ' CASE '||
257 ' WHEN name=''filesystemio_options'' and value like ''asynch%'' then ''asynch'' '||
258 ' WHEN name=''filesystemio_options'' and value like ''none%'' then ''none'' '||
259 ' WHEN name=''filesystemio_options'' and value like ''directIO%'' then ''directIO'' '||
260 ' WHEN name=''filesystemio_options'' and value like ''setall%'' then ''setall'' '||
261 ' WHEN name=''filesystemio_options'' then '' '' '||
262 ' ELSE value END value, '||
263 ' isdefault '||
264 'FROM '||
265 ' (SELECT * FROM '||
266 ' (WITH '||
267 ' override AS (SELECT name, value, isdefault, con_id '||
268 ' FROM gv$system_parameter '||
269 ' WHERE con_id != 0 AND inst_id = ' || inst_id_row.inst_id ||'), '||
270 ' pdbs AS (SELECT DISTINCT con_id, name pdb '||
271 ' FROM gv$containers WHERE con_id != 2 and inst_id = '|| inst_id_row.inst_id ||') '||
272 ' SELECT p.name, p.value, p.isdefault, pdb.pdb '||
273 ' FROM gv$system_parameter p, pdbs pdb '||
274 ' WHERE name NOT IN '||
275 ' (SELECT name '||
276 ' FROM override o '||
277 ' WHERE o.con_id = pdb.con_id) '||
278 ' AND p.con_id = 0 '||
279 ' UNION '||
280 ' SELECT name,value,isdefault,pdb.pdb '||
281 ' FROM override o, pdbs pdb '||
282 ' WHERE pdb.con_id = o.con_id) '||
283 ') WHERE name != ''resource_manager_plan'' order by 1,2', inst_id_row.instance_name);
284 END LOOP;
285 END;
286
287 /*
288 Private procedure
289 collect metric=cdb_pdb_over_params
290 */
291 procedure collect_cdb_pdb_over_params IS
292 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
293 BEGIN
294 FOR inst_id_row in l_res_cur LOOP
295 write_metric('cdb_pdb_over_params',
296 'SELECT distinct pdb.pdb,' ||
297 ' p.name,' ||
298 ' case ' ||
299 ' when p.name=''filesystemio_options'' and p.value like ''asynch%'' then ''asynch''' ||
300 ' when p.name=''filesystemio_options'' and p.value like ''none%'' then ''none''' ||
301 ' when p.name=''filesystemio_options'' and p.value like ''directIO%'' then ''directIO''' ||
302 ' when p.name=''filesystemio_options'' and p.value like ''setall%'' then ''setall'' ' ||
303 ' when p.name=''filesystemio_options'' then '' ''' ||
304 ' else p.value end,' ||
305 ' p.isdefault' ||
306 ' from gv$system_parameter p, (select DISTINCT con_id, name pdb from gv$containers where con_id != 2) pdb' ||
307 ' where p.con_id = pdb.con_id' ||
308 ' and name != ''resource_manager_plan'' ' ||
309 ' and inst_id = ' || inst_id_row.inst_id,inst_id_row.instance_name);
310 END LOOP;
311 END collect_cdb_pdb_over_params;
312
313
314 /*
315 Private procedure
316 Collect metric=db_asm_disk
317 */
318 procedure collect_db_asm_disk IS
319 BEGIN
320 IF g_version_category = VERSION_10gR1
321 OR g_version_category = VERSION_10gR2
322 OR g_version_category = VERSION_11gR1
323 OR g_version_category = VERSION_11gR2
324 OR g_version_category = VERSION_12gR1
325 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
326 write_metric('db_asm_disk',
327 'select inst_id, group_number, disk_number, header_status, path '||
328 'from gv$asm_disk where group_number > 0 and header_status != ''MEMBER'' ');
329 END IF;
330 END collect_db_asm_disk;
331
332
333 /*
334 Private procedure
335 Collect metric=autotask_client
336 */
337 procedure collect_autotask_client IS
338 BEGIN
339 IF g_version_category = VERSION_11gR1
340 OR g_version_category = VERSION_11gR2
341 OR g_version_category = VERSION_12gR1
342 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
343 write_metric('db_autotask_client',
344 'select client_name, status from DBA_AUTOTASK_CLIENT where lower(client_name) = ''sql tuning advisor'' ');
345 END IF;
346 END collect_autotask_client;
347
348
349 /*
350 Private procedure
351 Collect metric=db_components
352 */
353 procedure collect_db_components IS
354 BEGIN
355 IF g_version_category <> VERSION_817 THEN
356 IF g_version_category = VERSION_9iR2
357 OR g_version_category = VERSION_9i THEN
358 write_metric('db_components',' select '' '' namespace, comp_id, comp_name, version, status, schema from sys.dba_registry');
359 ELSE
360 write_metric('db_components',' select namespace, comp_id, comp_name, version, status, schema from sys.dba_registry');
361 END IF;
362 END IF;
363 END collect_db_components;
364
365
366 /*
367 Private procedure
368 Collect metric=db_invobj_cnt
369 */
370 procedure collect_db_invobj_cnt IS
371 BEGIN
372 write_metric('db_invobj_cnt',' select owner, count(*) from sys.dba_objects where status = ''INVALID'' group by owner ');
373 END collect_db_invobj_cnt;
374
375
376 /*
377 Private procedure
378 Collect metric=db_scheduler_jobs
379 */
380 procedure collect_db_scheduler_jobs IS
381 BEGIN
382 IF g_version_category = VERSION_10gR2
383 OR g_version_category = VERSION_10gR1 THEN
384 write_metric('db_scheduler_jobs',' select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from sys.dba_scheduler_jobs where job_name=''GATHER_STATS_JOB''');
385 END IF;
386 END collect_db_scheduler_jobs;
387
388
389 /*
390 Private procedure
394 l_max_rate NUMBER := 16*1024;
391 Collect metric=db_scnInfo
392 */
393 procedure collect_db_scnInfo IS
395 l_seconds_1988 NUMBER := 0;
396 l_maximum_scn NUMBER;
397 l_base_year NUMBER := 1988;
398 l_db_version VARCHAR2(10);
399 l_patch NUMBER;
400
401 l_current_scn NUMBER;
402 l_headroom NUMBER;
403 l_growth_24hrs NUMBER;
404 l_scn_calls NUMBER;
405 l_dblink_out NUMBER;
406 l_dblink_in NUMBER;
407 l_max_jump NUMBER ;
408 l_max_jump_time VARCHAR2(32);
409 l_created VARCHAR2(32);
410 l_reject_thresh NUMBER;
411
412 c_time_16 NUMBER;
413 c_time_32 NUMBER;
414
415 l_end_done BOOLEAN := FALSE;
416 l_sql_text VARCHAR2(2000);
417 l_sql_piece VARCHAR2(200);
418 l_is_gmt VARCHAR2(10);
419
420 BEGIN
421 select 16*1024*24*60*60, 32*1024*24*60*60 into c_time_16, c_time_32 from dual;
422
423 SELECT LPAD(version, 10, '0') into l_db_version from v$instance;
424 IF (l_db_version = '11.2.0.2.0' OR l_db_version = '11.2.0.3.0') THEN
425 l_sql_text :=
426 'SELECT count(*) from dba_registry_history ' ||
427 'where comments=''CPUJan2012'' OR comments=''CPUApr2012''';
428 -- presence of this parameter indicates CPU patch is installed.
429 EXECUTE IMMEDIATE l_sql_text INTO l_patch;
430 -- if no rows, then patch is not installed on 11.2.0.2 and 11.2.0.3
431 IF (l_patch = 0) THEN
432 l_max_rate := 32*1024;
433 l_base_year := 2009;
434 select 4*1024*1024*1024*2575 into l_seconds_1988 from dual;
435 END IF;
436 END IF;
437 -- current_scn, intrinsic change (not rate!)
438 IF (l_db_version > '10.0.0.0.0') THEN
439 l_sql_text :=
440 'SELECT d.current_scn, svalue ' ||
441 'FROM v$database d, ' ||
442 '(SELECT sum(s.value) svalue from v$sysstat s WHERE s.name in (''calls to kcmgas'', ''redo writes''))';
443 EXECUTE IMMEDIATE l_sql_text INTO l_current_scn, l_scn_calls;
444 ELSE
445 l_sql_text :=
446 'select dbms_flashback.get_system_change_number from dual';
447 EXECUTE IMMEDIATE l_sql_text INTO l_current_scn;
448 l_sql_text :=
449 'SELECT sum(s.value) svalue from v$sysstat s WHERE s.name in (''calls to kcmgas'', ''redo writes'')';
450 EXECUTE IMMEDIATE l_sql_text INTO l_scn_calls;
451 END IF;
452
453 -- this formula comes from kcmbts
454 SELECT
455 (((((( (to_number(to_char(sysdate,'YYYY'))-l_base_year)* 12
456 +(to_number(to_char(sysdate,'MM'))-1)) * 31
457 + (to_number(to_char(sysdate,'DD'))-1)) * 24
458 + (to_number(to_char(sysdate,'HH24')))) * 60
459 + (to_number(to_char(sysdate,'MI' )))) * 60
460 + (to_number(to_char(sysdate,'SS' ))))
461 * l_max_rate) + l_seconds_1988
462 INTO l_maximum_scn FROM dual;
463
464 -- scn_headroom
465 IF (l_max_rate = 32768) THEN
466 IF (l_current_scn > l_seconds_1988) THEN
467 SELECT to_char(((l_maximum_scn - l_current_scn) / c_time_32),'99999D99')
468 INTO l_headroom FROM dual;
469 ELSE
470 SELECT to_char((((l_maximum_scn - l_seconds_1988) / c_time_32) +
471 ((l_seconds_1988 - l_current_scn) / c_time_16)),'99999D99')
472 INTO l_headroom FROM dual;
473 END IF;
474 ELSE
475 SELECT to_char(((l_maximum_scn - l_current_scn) / c_time_16),'99999D99')
476 INTO l_headroom FROM dual;
477 END IF;
478
479 -- outgoing DB links
480 SELECT count(*) INTO l_dblink_out FROM DBA_DB_LINKS;
481
482 -- incoming DB links
483 SELECT count(*) INTO l_dblink_in FROM dba_audit_trail
484 WHERE action_name='LOGON' AND comment_text LIKE '%DBLINK_INFO%' AND timestamp > (sysdate-1) ORDER BY timestamp;
485
486 -- max jump size, average (total) growth rate, max jump timestamp
487 IF (l_db_version > '10.0.0.0.0') THEN
488 BEGIN
489 EXECUTE IMMEDIATE 'select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME=''Flashback Timestamp TimeZone''' INTO l_is_gmt;
490 EXCEPTION
491 WHEN OTHERS THEN
492 l_is_gmt := '';
493 END;
494 IF (l_is_gmt = 'GMT') THEN
495 l_sql_piece := 'WHERE time_dp > sys_extract_utc(systimestamp)-1';
496 ELSE
497 l_sql_piece := 'WHERE time_dp > sysdate-1';
498 END IF;
499 l_sql_text :=
500 'SELECT scn_per_sec, round(avg_scn_per_sec), to_char(time_stamp, ''YYYY-MM-DD HH24:MI:SS'') ' ||
501 'FROM ' ||
502 '( SELECT time_stamp, scn_per_sec, avg(scn_per_sec) over() avg_scn_per_sec, max(scn_per_sec) over() max_scn_per_sec ' ||
503 'FROM ' ||
504 '( SELECT time_dp time_stamp, scn, ' ||
505 'round ((scn - lag(scn,1) over(ORDER BY time_dp))/ ' ||
506 '(60*60*24 * (time_dp - lag(time_dp,1) over(ORDER BY time_dp))), ' ||
507 '0) scn_per_sec ' ||
508 'FROM sys.smon_scn_time ' ||
509 l_sql_piece ||
510 ') ' ||
511 'WHERE scn_per_sec IS NOT NULL order by time_stamp desc ' ||
512 ') ' || -- remove first entry which will have null because of lag()
513 'WHERE scn_per_sec = max_scn_per_sec and rownum = 1';
517 SELECT to_char(created,'YYYY-MM-DD HH24:MI:SS') INTO l_created FROM v$database;
514 EXECUTE IMMEDIATE l_sql_text INTO l_max_jump, l_growth_24hrs, l_max_jump_time;
515 END IF;
516 -- db creation time
518
519 -- reject threshold (may not exist, catch that)
520 BEGIN
521 SELECT value INTO l_reject_thresh FROM v$parameter WHERE name='_external_scn_rejection_threshold_hours';
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524 l_reject_thresh := -1;
525 END;
526
527 BEGIN
528 put_metric_marker(METRIC_BEGIN_MARKER,'db_scnInfo',null,null);
529 UTL_FILE.PUT_LINE( g_config_handle,
530 'em_result=' || l_current_scn ||'|'|| l_headroom ||'|'|| l_growth_24hrs ||'|'|| l_scn_calls ||'|'|| l_dblink_out ||
531 '|'|| l_dblink_in ||'|'|| l_max_jump ||'|'|| l_max_jump_time ||'|'|| l_created ||'|'|| l_reject_thresh );
532
533 put_metric_marker(METRIC_END_MARKER,'db_scnInfo',null,null);
534 l_end_done := TRUE;
535 UTL_FILE.FFLUSH(g_config_handle);
536 EXCEPTION
537 WHEN OTHERS THEN
538 IF NOT l_end_done THEN
539 put_metric_marker(METRIC_END_MARKER,'db_scnInfo',null,null);
540 UTL_FILE.FFLUSH(g_config_handle);
541 END IF;
542 RAISE;
543 END;
544 END collect_db_scnInfo;
545
546
547 /*
548 Private procedure
549 Collect metric=db_redoLogs
550 */
551 procedure collect_db_redoLogs IS
552 BEGIN
553 write_metric('db_redoLogs',
554 ' SELECT l.group# group_num, ' ||
555 /*
556 Comment this column as it changes frequently
557 ' NLS_INITCAP(l.status) status, ' ||
558 */
559 ' '''', ' ||
560 ' l.members members, ' ||
561 ' lf.member file_name, ' ||
562 /*
563 Comment this column as it changes frequently
564 ' NLS_INITCAP(l.archived) archived, ' ||
565 */
566 ' '''', ' ||
567 ' l.bytes logsize, ' ||
568 /*
569 Comment this column as it changes frequently
570 ' l.sequence# sequence_num, ' ||
571 */
572 ' '''', ' ||
573 /*
574 Comment this column as it changes frequently
575 ' l.first_change# first_change_scn, ' ||
576 */
577 ' '''', ' ||
578 ' l.thread# as thread_num , lf.type type' ||
579 ' FROM v$log l, ' ||
580 ' v$logfile lf ' ||
581 ' WHERE l.group# = lf.group#');
582 END collect_db_redoLogs;
583
584
585 /*
586 Private procedure
587 Collect metric=db_datafiles
588 */
589 procedure collect_db_datafiles IS
590 l_status_clause1 VARCHAR2(100);
591 l_status_clause2 VARCHAR2(100);
592 BEGIN
593 IF g_version_category = VERSION_9iR2 THEN
594 l_status_clause1 := 'vdf.status status, ';
595 l_status_clause2 := 'vtf.status status, ';
596 ELSIF g_version_category = VERSION_10gR1
597 OR g_version_category = VERSION_10gR2 THEN
598 l_status_clause1 := 'ddf.online_status status, ';
599 l_status_clause2 := 'vtf.status status, ';
600 ELSIF g_version_category = VERSION_11gR1
601 OR g_version_category = VERSION_11gR2
602 OR g_version_category = VERSION_12gR1
603 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
604 l_status_clause1 := 'ddf.online_status status, ';
605 l_status_clause2 := 'dtf.status status, ';
606 END IF;
607
608 write_metric('db_datafiles',
609 'SELECT /*+ ORDERED */ ' ||
610 ' REPLACE(REPLACE(ddf.file_name, chr(10), ''''), chr(13), '''') file_name, ' ||
611 l_status_clause1 ||
612 ' ddf.tablespace_name tablespace_name,'||
613 ' '''', ' ||
614 ' ddf.autoextensible autoextensible, '||
615 ' ddf.increment_by increment_by, ' ||
616 ' ddf.maxbytes max_file_size, ' ||
617 ' vdf.create_bytes, ' ||
618 ' ''NA'' os_storage ' ||
619 'FROM v$datafile vdf, ' ||
620 'sys.dba_data_files ddf ' ||
621 'WHERE (vdf.file# = ddf.file_id) ' ||
622 'UNION ALL ' ||
623 'SELECT /*+ ORDERED */ ' ||
624 ' REPLACE(REPLACE(dtf.file_name, chr(10), ''''), chr(13), '''') file_name, '||
625 l_status_clause2 ||
626 ' dtf.tablespace_name tablespace_name,'||
627 ' '''', ' ||
628 ' dtf.autoextensible autoextensible, '||
629 ' dtf.increment_by increment_by, ' ||
630 ' dtf.maxbytes max_file_size, ' ||
631 ' vtf.create_bytes, ' ||
632 ' ''NA'' os_storage ' ||
633 'FROM v$tempfile vtf, ' ||
634 ' sys.dba_temp_files dtf ' ||
635 'WHERE (dtf.file_id = vtf.file#)');
636
637 END collect_db_datafiles;
638
639 /*
640 Private procedure
641 Collect metric=cdb_datafiles
642 */
643 procedure collect_cdb_datafiles IS
644 BEGIN
645 write_metric('cdb_datafiles',
646 'SELECT /*+ ORDERED NO_PARALLEL(ddf) */ pdb.pdb,' ||
647 ' REPLACE(REPLACE(ddf.file_name, chr(10), ''''), chr(13), ''''), ' ||
648 ' ddf.online_status,' ||
649 ' ddf.tablespace_name,' ||
650 ' '''',' ||
651 ' ddf.autoextensible,' ||
652 ' ddf.increment_by,' ||
653 ' ddf.maxbytes,' ||
654 ' vdf.create_bytes,' ||
655 ' ''NA'' ' ||
656 'FROM sys.cdb_data_files ddf, v$datafile vdf, ' ||
657 '(select DISTINCT con_id, name pdb from gv$containers where con_id != 2 ) pdb ' ||
658 'WHERE (vdf.file# = ddf.file_id) and ddf.con_id = pdb.con_id and vdf.con_id = pdb.con_id ' ||
659 'UNION ALL ' ||
663 ' dtf.tablespace_name,' ||
660 'SELECT /*+ NO_PARALLEL(dtf) */ pdb.pdb,' ||
661 ' REPLACE(REPLACE(dtf.file_name, chr(10), ''''), chr(13), ''''),' ||
662 ' dtf.status,' ||
664 ' '''',' ||
665 ' dtf.autoextensible,' ||
666 ' dtf.increment_by,' ||
667 ' dtf.maxbytes,' ||
668 ' vtf.create_bytes,' ||
669 ' ''NA'' ' ||
670 'FROM sys.cdb_temp_files dtf, v$tempfile vtf, ' ||
671 '(select DISTINCT con_id, name pdb from gv$containers where con_id != 2) pdb ' ||
672 'WHERE (dtf.file_id = vtf.file#) and dtf.con_id = pdb.con_id and vtf.con_id = pdb.con_id');
673 END;
674
675 /*
676 Private procedure
677 Collect metric=db_tablespaces
678 */
679 procedure collect_db_tablespaces IS
680 l_sql_db_tablespaces VARCHAR2(4000);
681 l_segspace VARCHAR2(100) := ' dtp.segment_space_management,';
682 l_blocksize VARCHAR2(100) := ' dtp.block_size,';
683 l_bigfile VARCHAR2(100) := ' dtp.bigfile ';
684 BEGIN
685 -- build the query with the appropriate subclauses based on DB versions
686 IF g_version_category = VERSION_817 THEN
687 l_segspace := ' '''', ';
688 l_blocksize := ' '''', ';
689 l_bigfile := ' '''', ';
690 ELSIF g_version_category = VERSION_9iR2
691 OR g_version_category = VERSION_9i THEN
692 l_segspace := ' dtp.segment_space_management,';
693 l_blocksize := ' dtp.block_size,';
694 l_bigfile := ' '''', ';
695 ELSIF g_version_category = VERSION_10gR2
696 OR g_version_category = VERSION_10gR1
697 OR g_version_category = VERSION_11gR1
698 OR g_version_category = VERSION_11gR2
699 OR g_version_category = VERSION_12gR1
700 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
701 l_segspace := ' dtp.segment_space_management,';
702 l_blocksize := ' dtp.block_size,';
703 l_bigfile := ' dtp.bigfile ';
704 END IF;
705
706 l_sql_db_tablespaces := 'SELECT ' ||
707 ' dtp.tablespace_name,' ||
708 ' dtp.status,' ||
709 ' dtp.contents,' ||
710 ' dtp.extent_management,' ||
711 ' dtp.allocation_type,' ||
712 ' dtp.logging,' ||
713 ' '''',' || -- this column in GC metric changes too often
714 ' dtp.initial_extent,' ||
715 ' dtp.next_extent,' ||
716 ' dtp.pct_increase,' ||
717 ' dtp.max_extents,' ||
718 ' '''',' || -- this column in GC metric changes too often
719 ' dtp.min_extents,' ||
720 ' dtp.min_extlen,' ||
721 l_segspace ||
722 l_blocksize ||
723 l_bigfile ||
724 ' FROM '||
725 ' sys.dba_tablespaces dtp';
726
727 -- collect the metric, given the decisions made above
728 write_metric('db_tablespaces',l_sql_db_tablespaces);
729
730 END collect_db_tablespaces;
731
732 /*
733 Private procedure
734 Collect metric=cdb_tablespaces
735 */
736 procedure collect_cdb_tablespaces IS
737 BEGIN
738 -- collect the metric
739 write_metric('cdb_tablespaces',
740 'with pdbview AS' ||
741 '(SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
742 'SELECT /*+ NO_PARALLEL(dtp) */ pdb.pdb,' ||
743 'dtp.tablespace_name,' ||
744 'dtp.status,' ||
745 'dtp.contents,' ||
746 'dtp.extent_management,' ||
747 'dtp.allocation_type,' ||
748 'dtp.logging,' ||
749 ''''',' ||
750 'dtp.initial_extent,' ||
751 'dtp.next_extent,' ||
752 'dtp.pct_increase,' ||
753 'dtp.max_extents,' ||
754 ''''',' ||
755 'dtp.min_extents,' ||
756 'dtp.min_extlen,' ||
757 'dtp.segment_space_management,' ||
758 'dtp.block_size,' ||
759 'dtp.bigfile ' ||
760 'FROM sys.cdb_tablespaces dtp, pdbview pdb ' ||
761 'WHERE NOT (dtp.extent_management LIKE ''LOCAL'' AND ' ||
762 'dtp.contents LIKE ''TEMPORARY'') AND ' ||
763 'dtp.con_id = pdb.con_id ' ||
764 'UNION ALL ' ||
765 'SELECT /*+ NO_PARALLEL(dtp) */ pdb.pdb,' ||
766 'dtp.tablespace_name,' ||
767 'dtp.status,' ||
768 'dtp.contents,' ||
769 'dtp.extent_management,' ||
770 'dtp.allocation_type,' ||
771 'dtp.logging,' ||
772 ''''',' ||
773 'dtp.initial_extent,' ||
774 'dtp.next_extent,' ||
775 'dtp.pct_increase,' ||
776 'dtp.max_extents,' ||
777 ''''',' ||
778 'dtp.min_extents,' ||
779 'dtp.min_extlen,' ||
780 'dtp.segment_space_management,' ||
781 'dtp.block_size,' ||
782 'dtp.bigfile ' ||
783 'FROM sys.cdb_tablespaces dtp, pdbview pdb ' ||
784 'WHERE dtp.extent_management LIKE ''LOCAL'' AND ' ||
785 'dtp.contents LIKE ''TEMPORARY'' AND ' ||
786 'dtp.con_id = pdb.con_id');
787 END;
788 /*
789 Private procedure
790 Collect metric=db_controlfiles
791 */
792 procedure collect_db_controlfiles IS
793 BEGIN
794 write_metric('db_controlfiles',
795 'SELECT cf.name file_name, ' ||
796 ' db.controlfile_type status, ' ||
797 ' to_char(db.controlfile_created,''YYYY-MM-DD HH24:MI:SS'') creation_date, ' ||
798 /*
799 Comment this column as it changes frequently
800 ' db.controlfile_sequence# sequence_num, ' ||
801 */
802 ' '''', ' ||
803 /*
804 Comment this column as it changes frequently
805 ' db.controlfile_change# change_num, ' ||
806 */
807 ' '''', ' ||
808 /*
812 ' '''', ' ||
809 Comment this column as it changes frequently
810 ' to_char(db.controlfile_time,''YYYY-MM-DD HH24:MI:SS'') mod_date ' ||
811 */
813 /*
814 Comment this column as it is unavailable from db
815 ' os_storage_entity '
816 */
817 ' ''NA'' ' ||
818 ' FROM v$controlfile cf, ' ||
819 ' v$database db ' );
820
821 END collect_db_controlfiles;
822
823
824 /*
825 Private procedure
826 Collect metric=db_rollback_segs
827 */
828 procedure collect_db_rollback_segs IS
829 BEGIN
830 write_metric('db_rollback_segs',
831 'SELECT ' ||
832 ' drs.segment_name rollname, ' ||
833 ' drs.status status, ' ||
834 ' drs.tablespace_name tablespace_name, ' ||
835 ' rs.extents extents, ' ||
836 /*
837 Comment this column as per George
838 ' rs.rssize rollsize, ' ||
839 */
840 ' '''', ' ||
841 ' drs.initial_extent initial_size, ' ||
842 ' drs.next_extent next_size, ' ||
843 ' drs.max_extents maximum_extents, ' ||
844 ' drs.min_extents minimum_extents, ' ||
845 ' drs.pct_increase pct_increase, ' ||
846 ' rs.optsize optsize, ' ||
847 ' rs.aveactive aveactive, ' ||
848 ' rs.wraps wraps, ' ||
849 ' rs.shrinks shrinks, ' ||
850 ' rs.aveshrink aveshrink, ' ||
851 /*
852 Comment this column as its a volatile data
853 ' rs.hwmsize hwmsize ' ||
854 */
855 ' '''' ' ||
856 ' FROM sys.dba_rollback_segs drs, ' ||
857 ' v$rollstat rs ' ||
858 ' WHERE drs.segment_id = rs.usn (+) ' ||
859 ' and substr(drs.segment_name,1,7) != ''_SYSSMU''');
860 END collect_db_rollback_segs;
861
862
863 /*
864 Private procedure
865 Collect metric=cdb_rollback_segs
866 */
867 procedure collect_cdb_rollback_segs IS
868 BEGIN
869 write_metric('cdb_rollback_segs',
870 'SELECT /*+ NO_PARALLEL(drs) */ pdb.pdb pdb_name, '||
871 ' drs.segment_name rollname, ' ||
872 ' drs.status status, ' ||
873 ' drs.tablespace_name tablespace_name, ' ||
874 ' rs.extents extents, ' ||
875 /*
876 Comment this column as per George
877 ' rs.rssize rollsize, ' ||
878 */
879 ' '''', ' ||
880 ' drs.initial_extent initial_size, ' ||
881 ' drs.next_extent next_size, ' ||
882 ' drs.max_extents maximum_extents, ' ||
883 ' drs.min_extents minimum_extents, ' ||
884 ' drs.pct_increase pct_increase, ' ||
885 ' rs.optsize optsize, ' ||
886 ' rs.aveactive aveactive, ' ||
887 ' rs.wraps wraps, ' ||
888 ' rs.shrinks shrinks, ' ||
889 ' rs.aveshrink aveshrink, ' ||
890 /*
891 Comment this column as its a volatile data
892 ' rs.hwmsize hwmsize ' ||
893 */
894 ' '''' ' ||
895 ' FROM sys.cdb_rollback_segs drs, (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) pdb, ' ||
896 ' v$rollstat rs ' ||
897 ' WHERE drs.segment_id = rs.usn (+) ' ||
898 ' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' ||
899 ' and drs.con_id = pdb.con_id' );
900 END collect_cdb_rollback_segs;
901
902
903 /*
904 Private procedure
905 Collect metric=db_sga
906 */
907 procedure collect_db_sga IS
908 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
909 BEGIN
910 FOR inst_id_row in l_res_cur LOOP
911 write_metric('db_sga',
912 ' select sganame,sgasize ' ||
913 ' from ' ||
914 ' ((SELECT ''Shared Pool (MB)'' sganame, ' ||
915 ' ROUND(NVL(sum(bytes)/1024/1024,0)) sgasize ' ||
916 ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
917 ' AND pool = ''shared pool'') ' ||
918 ' UNION ' ||
919 ' (SELECT ''Buffered Cache (MB)'' sganame, ' ||
920 ' ROUND(NVL(bytes/1024/1024,0)) sgasize ' ||
921 ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
922 ' AND ((name = ''db_block_buffers'' AND pool IS NULL ) OR name = ''buffer_cache'')) ' ||
923 ' UNION ' ||
924 ' (SELECT ''Large Pool (KB)'' "NAME", ' ||
925 ' ROUND(NVL(sum(bytes)/1024,0)) "SIZE" ' ||
926 ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
927 ' AND pool = ''large pool'') ' ||
928 ' UNION ' ||
929 ' (SELECT ''Java Pool (MB)'' "NAME", ' ||
930 ' ROUND(NVL(sum(bytes)/1024/1024,0)) "SIZE" ' ||
931 ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
932 ' AND pool = ''java pool'') ' ||
933 ' UNION ' ||
934 ' (SELECT ''Fixed SGA (KB)'' "NAME", ' ||
935 ' ROUND(NVL(value/1024,0)) "SIZE" ' ||
936 ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
937 ' AND name=''Fixed Size'') ' ||
938 ' UNION ' ||
939 ' (SELECT ''Variable SGA (MB)'' "NAME", ' ||
940 ' ROUND(NVL(value/1024/1024,0)) "SIZE" ' ||
941 ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
942 ' AND name=''Variable Size'') ' ||
943 ' UNION ' ||
944 ' (SELECT ''Redo Buffers (KB)'' "NAME", ' ||
945 ' ROUND(NVL(value/1024,0)) "SIZE" ' ||
946 ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
947 ' AND name=''Redo Buffers'') ' ||
948 ' UNION ' ||
949 ' (SELECT ''Total SGA (MB)'' "NAME", ' ||
950 ' ROUND(NVL(sum(bytes)/1024/1024,0)) "SIZE" ' ||
951 ' FROM gv$sgastat WHERE INST_ID = ' || inst_id_row.inst_id ||
952 ') ' ||
956 ' FROM gv$sga WHERE INST_ID = ' || inst_id_row.inst_id ||
953 ' UNION ' ||
954 ' (SELECT ''Maximum SGA (MB)'' "NAME", ' ||
955 ' ROUND(NVL(sum(value)/1024/1024,0)) "SIZE" ' ||
957 ')) ' ||
958 ' ORDER BY sgasize ' , inst_id_row.instance_name);
959 END LOOP;
960 END collect_db_sga;
961
962
963 /*
964 Private procedure
965 Collect metric=db_license
966 */
967 procedure collect_db_license IS
968 BEGIN
969 write_metric('db_license',
970 'SELECT sessions_max, ' ||
971 'sessions_warning, ' ||
972 /*
973 Comment this column as per George
974 'sessions_current , ' ||
975 */
976 ' '''', ' ||
977 ' sessions_highwater, ' ||
978 ' users_max ' ||
979 ' FROM v$license ');
980 END collect_db_license;
981
982
983 /*
984 Private procedure
985 Collect metric=db_options
986 */
987 procedure collect_db_options IS
988 l_data_mining VARCHAR2(500);
989 BEGIN
990 IF g_version_category = VERSION_817
991 OR g_version_category = VERSION_9i THEN
992 l_data_mining := 'select ''ORACLE_DATA_MINING'' as name, ''FALSE'' as selected ';
993 ELSIF g_version_category = VERSION_9iR2
994 OR g_version_category = VERSION_10gR1
995 OR g_version_category = VERSION_10gR2
996 OR g_version_category = VERSION_11gR1
997 OR g_version_category = VERSION_11gR2
998 OR g_version_category = VERSION_12gR1
999 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
1000 l_data_mining := ' select ''ORACLE_DATA_MINING'' as name, decode((select status from dba_registry where comp_id=''ODM''), ''VALID'', ''TRUE'', ''FALSE'') as selected ';
1001 ELSE
1002 RETURN;
1003 END IF;
1004
1005 write_metric('db_options',
1006 ' select ''INTER_MEDIA'' as name, decode((select username from dba_users where username=''ORDSYS''), ''ORDSYS'', ''TRUE'', ''FALSE'') as selected ' ||
1007 ' from dual ' ||
1008 ' union ' ||
1009 ' select ''SPATIAL'' as name, decode((select username from dba_users where username=''MDSYS''), ''MDSYS'', ''TRUE'', ''FALSE'') as selected ' ||
1010 ' from dual ' ||
1011 ' union ' ||
1012 ' select ''OLAP'' as name, decode((select username from dba_users where username=''OLAPSYS''), ''OLAPSYS'', ''TRUE'', ''FALSE'') as selected ' ||
1013 ' from dual ' ||
1014 ' union ' ||
1015 ' select ''ORACLE_TEXT'' as name, decode((select username from dba_users where username=''CTXSYS''), ''CTXSYS'', ''TRUE'', ''FALSE'') as selected ' ||
1016 ' from dual ' ||
1017 ' union ' ||
1018 ' select ''ULTRA_SEARCH'' as name, decode((select username from dba_users where username=''WKSYS''), ''WKSYS'', ''TRUE'', ''FALSE'') as selected ' ||
1019 ' from dual ' ||
1020 ' union ' ||
1021 ' select ''LABEL_SECURITY'' as name, decode((select username from dba_users where username=''LBACSYS''), ''LBACSYS'', ''TRUE'', ''FALSE'') as selected ' ||
1022 ' from dual ' ||
1023 ' union ' ||
1024 ' select ''SAMPLE_SCHEMA'' as name, decode((select count(*) from dba_users where username IN(''HR'',''PM'',''QS'',''SH'',''OE'')), 0, ''FALSE'', ''TRUE'') as selected ' ||
1025 ' from dual ' ||
1026 ' union ' ||
1027 ' select ''JSERVER'' as name, decode((select count(*) from sys.obj$ where type#=29), 0, ''FALSE'', ''TRUE'') as selected ' ||
1028 ' from dual ' ||
1029 ' union ' ||
1030 l_data_mining ||
1031 ' from dual ' ||
1032 ' union ' ||
1033 ' select ''XDB'' as name, decode((select username from dba_users where username=''XDB''), ''XDB'', ''TRUE'', ''FALSE'') as selected ' ||
1034 ' from dual ' ||
1035 ' union ' ||
1036 ' select ''EM_REPOSITORY'' as name, decode((select username from dba_users where username=''SYSMAN''), ''SYSMAN'', ''TRUE'', ''FALSE'') as selected ' ||
1037 ' from dual ');
1038
1039 END collect_db_options;
1040
1041
1042 /*
1043 Private procedure
1044 Collect metric=statspack_config
1045 */
1046 procedure collect_statspack_config IS
1047 BEGIN
1048 write_metric('statspack_config',
1049 'select '||
1050 ' (select decode(count(*),1,''YES'',''NO'') FROM sys.obj$ o, sys.user$ u '||
1051 ' WHERE u.name = ''PERFSTAT'' AND o.owner# = u.user# AND o.name = ''STATSPACK'' '||
1052 ' AND o.type# = 11 AND o.status = 1) is_installed, '||
1053 ' (select nvl(INTERVAL,'''') from dba_jobs '||
1054 ' where what like ''statspack.snap%'' and SCHEMA_USER=''PERFSTAT'' and rownum = 1) freq from dual');
1055 END collect_statspack_config;
1056
1057
1058 /*
1059 Private procedure
1060 Collect metric=db_users
1061 */
1062 procedure collect_db_users IS
1063 BEGIN
1064 write_metric('db_users',
1065 'select USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, '||
1066 'to_char(CREATED,''YYYY-MM-DD HH24:MI:SS'') creation_time, PROFILE,'||
1067 'to_char(EXPIRY_DATE,''YYYY-MM-DD HH24:MI:SS'') expire_time from dba_users');
1068 END collect_db_users;
1069
1070
1071 /*
1072 Private procedure
1073 Collect metric=db_users
1074 */
1075 procedure collect_cdb_users IS
1076 BEGIN
1077 write_metric('cdb_users',
1078 'WITH pdbs AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) '||
1079 'SELECT /*+ NO_PARALLEL(u) */ pdb, '||
1080 ' USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, '||
1081 ' to_char(CREATED,''YYYY-MM-DD HH24:MI:SS'') ,' ||
1082 ' PROFILE, ' ||
1083 ' to_char(EXPIRY_DATE,''YYYY-MM-DD HH24:MI:SS'') ' ||
1084 ' FROM cdb_users u, pdbs p '||
1085 ' WHERE p.con_id = u.con_id');
1089 /*
1086 END collect_cdb_users;
1087
1088
1090 Private procedure
1091 Collect metric=backup_config
1092 */
1093 /*
1094 procedure collect_backup_config IS
1095 BEGIN
1096 write_metric('backup_config',
1097 'select DEVICE_TYPE, ''DATAFILE'' from v$backup_datafile d, V$BACKUP_PIECE p '||
1098 'where d.SET_COUNT=p.SET_COUNT and d.SET_STAMP=p.SET_STAMP and p.STATUS =''A'' '||
1099 'and d.FILE# != 0 '||
1100 'union all '||
1101 'select p.DEVICE_TYPE,''REDOLOG'' from V$BACKUP_REDOLOG r, V$BACKUP_PIECE p '||
1102 'where r.SET_COUNT=p.SET_COUNT and r.SET_STAMP=p.SET_STAMP and p.STATUS =''A''');
1103 END collect_backup_config;
1104 */
1105
1106
1107 /*
1108 Private procedure
1109 Collect metric=ha_info
1110 */
1111 procedure collect_ha_info IS
1112 BEGIN
1113 IF g_version_category = VERSION_817 OR
1114 g_version_category = VERSION_9i THEN
1115 write_metric('ha_info',
1116 'SELECT dbid, log_mode FROM v$database');
1117 END IF;
1118
1119 IF g_version_category = VERSION_9iR2 THEN
1120 write_metric('ha_info',
1121 'SELECT dbid, log_mode, force_logging, database_role FROM v$database');
1122 END IF;
1123
1124 IF g_version_category = VERSION_10gR1
1125 OR g_version_category = VERSION_10gR2
1126 OR g_version_category = VERSION_11gR1
1127 OR g_version_category = VERSION_11gR2
1128 OR g_version_category = VERSION_12gR1
1129 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
1130 write_metric('ha_info',
1131 'SELECT dbid, log_mode, force_logging, database_role, flashback_on, supplemental_log_data_min FROM v$database');
1132 END IF;
1133 END collect_ha_info;
1134
1135
1136 /*
1137 Private procedure
1138 Collect metric=ha_rman_config
1139 */
1140 procedure collect_ha_rman_config IS
1141 BEGIN
1142 IF g_version_category = VERSION_9i
1143 OR g_version_category = VERSION_9iR2
1144 OR g_version_category = VERSION_10gR1
1145 OR g_version_category = VERSION_10gR2
1146 OR g_version_category = VERSION_11gR1
1147 OR g_version_category = VERSION_11gR2
1148 OR g_version_category = VERSION_12gR1
1149 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
1150 write_metric('ha_rman_config',
1151 'select ''CONTROLFILE AUTOBACKUP'',nvl((select value from v$rman_configuration where name=''CONTROLFILE AUTOBACKUP''),'''') from dual ' ||
1152 'union ' ||
1153 'select name, value from v$rman_configuration where name=''CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE'' and value like ''DISK%'' ' );
1154 END IF;
1155 END collect_ha_rman_config;
1156
1157 /*
1158 Private procedure
1159 Collect metric=db_dbNInstanceInfo
1160 */
1161 procedure collect_db_dbNInstanceInfo IS
1162
1163 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
1164
1165 l_word_length NUMBER;
1166 l_is_64bit VARCHAR2(1);
1167 l_banner_bitrelstr VARCHAR2(4000);
1168 l_banner_bitrelstr_clause VARCHAR2(4000);
1169 l_banner_split_clause VARCHAR2(4000) ;
1170 l_supplemental_log VARCHAR2(100);
1171 l_dbproperties_select VARCHAR2(100);
1172 l_dbproperties_from VARCHAR2(100);
1173 l_dbproperties_where VARCHAR2(100);
1174 l_dv_status VARCHAR2(1000);
1175
1176 BEGIN
1177
1178 -- l_supplemental_log, dbproperties strings
1179 IF g_version_category = VERSION_817 OR g_version_category = VERSION_9i THEN
1180 l_supplemental_log :=', null as SUPPLEMENTAL_LOG ';
1181 l_dbproperties_select := ' ''SYSTEM'' default_temp_tablespace, ';
1182 l_dbproperties_from := '';
1183 l_dbproperties_where := '';
1184 ELSIF g_version_category = VERSION_9iR2
1185 OR g_version_category = VERSION_10gR1
1186 OR g_version_category = VERSION_10gR2
1187 OR g_version_category = VERSION_11gR1
1188 OR g_version_category = VERSION_11gR2
1189 OR g_version_category = VERSION_12gR1
1190 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
1191 l_supplemental_log := ',a.supplemental_log_data_min as SUPPLEMENTAL_LOG ';
1192 l_dbproperties_select := ' p.property_value default_temp_tablespace, ';
1193 l_dbproperties_from := ', database_properties p';
1194 l_dbproperties_where := ' AND p.property_name = ''DEFAULT_TEMP_TABLESPACE'' ';
1195 END IF;
1196
1197 IF g_version_category = VERSION_817 THEN
1198 l_dv_status := ' , NULL as dv_status_code ';
1199 ELSIF g_version_category = VERSION_9i
1200 OR g_version_category = VERSION_9iR2
1201 OR g_version_category = VERSION_10gR1
1202 OR g_version_category = VERSION_10gR2
1203 OR g_version_category = VERSION_11gR1
1204 OR g_version_category = VERSION_11gR2
1205 OR g_version_category = VERSION_12gR1
1206 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
1207 l_dv_status := ' , case when (select count(*) from dba_users where username =''DVSYS'' and user_id = 1279990) > 0 ' ||
1208 ' then case when (select value from v$option where parameter =''Oracle Database Vault'') =''TRUE'' ' ||
1209 ' then 1 ' || -- Enabled
1210 ' else 0 ' || --Disabled
1211 ' end ' ||
1212 ' when (select count(*) from dba_users where username =''DVSYS'' and user_id = 1279990) = 0 ' ||
1216 ' end ' ||
1213 ' then case when (select value from v$option where parameter =''Oracle Database Vault'') = ''TRUE'' ' ||
1214 ' then -1 ' || -- Not Configured
1215 ' else -2 ' || -- Not Installed
1217 'end ' ||
1218 ' dv_status_code ';
1219 END IF;
1220
1221 -- the word length denotes 32 or 64-bit
1222 select length(addr)*4 into l_word_length from v$process where rownum=1;
1223 IF l_word_length = 64 THEN
1224 l_is_64bit := 'Y';
1225 ELSE
1226 l_is_64bit := 'N';
1227 END IF;
1228
1229 -- The portion after the - in the banner is given by
1230 -- substr(banner, instr(banner, '-') + 2)
1231 l_banner_bitrelstr := 'substr(banner, instr(banner, ''-'') + 2)';
1232 l_banner_bitrelstr_clause :=
1233 ' , ''' || l_is_64bit || ''' , ' ||
1234 ' substr(' || l_banner_bitrelstr || ', instr(' || l_banner_bitrelstr || ', '' '') + 1) ';
1235 IF g_version_category = VERSION_817 THEN
1236 l_banner_split_clause :=
1237 ' , substr(banner,instr(banner,''Oracle8i''),8) , ' ||
1238 ' nvl(trim(substr(banner,instr(banner,''Oracle8i'') + 8,instr(banner,'' Release'') ' ||
1239 ' - instr(banner,''Oracle8i'') - 8 )),decode(instr(banner,''Personal''),0,'''',''Personal'')) , ' ;
1240 ELSIF g_version_category = VERSION_9i
1241 OR g_version_category = VERSION_9iR2 THEN
1242 l_banner_split_clause :=
1243 ' , substr(banner,instr(banner,''Oracle9i''),8) , ' ||
1244 ' nvl(trim(substr(banner,instr(banner,''Oracle9i'') + 8,instr(banner,'' Release'') ' ||
1245 ' - instr(banner,''Oracle9i'') - 8 )),decode(instr(banner,''Personal''),0,'''',''Personal'')) , ';
1246 ELSIF g_version_category = VERSION_10gR1
1247 OR g_version_category = VERSION_10gR2
1248 OR g_version_category = VERSION_11gR1
1249 OR g_version_category = VERSION_11gR2
1250 OR g_version_category = VERSION_12gR1 THEN
1251 l_banner_split_clause :=
1252 ' , substr(banner,instr(banner,''Oracle Database ''),19), ' ||
1253 'nvl(trim(substr(banner,instr(banner,''Oracle Database '')+19,instr(banner,'' Release'')' ||
1254 ' - instr(banner,''Oracle Database '')-19 )), decode(instr(banner,''Personal''),0,'''',''Personal'')), ' ;
1255 END IF;
1256
1257 FOR inst_id_row in l_res_cur LOOP
1258 write_metric('db_dbNInstanceInfo',
1259 'SELECT '||
1260 ' a.name database_name, ' ||
1261 ' e.global_name global_name, ' ||
1262 ' b.banner banner, ' ||
1263 ' c.host_name host_name, ' ||
1264 ' c.instance_name instance_name, ' ||
1265 ' to_char(c.startup_time,''YYYY-MM-DD HH24:MI:SS'') startup_time, ' ||
1266 ' decode(c.logins,''RESTRICTED'',''YES'',''NO'') logins, ' ||
1267 ' a.log_mode log_mode, ' ||
1268 ' decode(a.open_mode,''READ ONLY'',''YES'',''NO'') open_mode, ' ||
1269 ' nlsp1.value characterset, ' ||
1270 ' nlsp2.value national_characterset, ' ||
1271 l_dbproperties_select ||
1272 ' to_char(a.created,''YYYY-MM-DD HH24:MI:SS'') created ' ||
1273 l_banner_split_clause ||
1274 ' c.version' ||
1275 l_banner_bitrelstr_clause ||
1276 l_supplemental_log ||
1277 l_dv_status ||
1278 ' FROM gv$database a, ' ||
1279 ' gv$version b, ' ||
1280 ' gv$instance c, ' ||
1281 ' global_name e,' ||
1282 ' gv$nls_parameters nlsp1 , ' ||
1283 ' gv$nls_parameters nlsp2 ' ||
1284 l_dbproperties_from ||
1285 ' WHERE b.banner LIKE ''%Oracle%'' ' ||
1286 ' AND nlsp1.parameter = ''NLS_CHARACTERSET'' ' ||
1287 ' AND nlsp2.parameter = ''NLS_NCHAR_CHARACTERSET'' ' ||
1288 l_dbproperties_where ||
1289 ' AND a.INST_ID = ' || inst_id_row.inst_id ||
1290 ' AND b.INST_ID = ' || inst_id_row.inst_id ||
1291 ' AND c.INST_ID = ' || inst_id_row.inst_id ||
1292 ' AND nlsp1.INST_ID = ' || inst_id_row.inst_id ||
1293 ' AND nlsp2.INST_ID = ' || inst_id_row.inst_id
1294 ,inst_id_row.instance_name);
1295 END LOOP;
1296
1297 END collect_db_dbNInstanceInfo;
1298
1299 /*
1300 Private procedure
1301 Collect metric=cdb_dbNInstanceInfo
1302 */
1303 procedure collect_cdb_dbNInstanceInfo IS
1304 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
1305 BEGIN
1306 FOR inst_id_row in l_res_cur LOOP
1307 write_metric('cdb_dbNInstanceInfo',
1308 'with pdbview AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
1309 'SELECT /*+ NO_PARALLEL(e) NO_PARALLEL(p) NO_PARALLEL(u) */ pdb.pdb, ' ||
1310 'a.name, ' ||
1311 'e.property_value, ' ||
1312 'b.banner, ' ||
1313 'c.host_name, ' ||
1314 'c.instance_name, ' ||
1315 'to_char(c.startup_time,''YYYY-MM-DD HH24:MI:SS''), ' ||
1316 'decode(c.logins,''RESTRICTED'',''YES'',''NO''), ' ||
1317 'a.log_mode, ' ||
1318 'case when pdb.con_id=0 then ' ||
1319 'decode(a.open_mode,''READ ONLY'',''YES'',''NO'') ' ||
1323 'nlsp1.value, ' ||
1320 'else ' ||
1321 'decode(vpb.OPEN_MODE,''READ ONLY'',''YES'',''NO'') ' ||
1322 'end, ' ||
1324 'nlsp2.value, ' ||
1325 'p.property_value, ' ||
1326 'to_char(a.created,''YYYY-MM-DD HH24:MI:SS''), ' ||
1327 'substr(banner,instr(banner,''Oracle Database ''),19), ' ||
1328 'nvl(trim(substr(banner,instr(banner,''Oracle Database '')+19,instr(banner,'' Release'') - instr(banner,''Oracle Database '') - 19)),decode(instr(banner,''Personal''),0,'''',''Personal'')), ' ||
1329 'c.version dbversion, ' ||
1330 'DECODE(addr.word_len, 64, ''Y'', ''N''), ' ||
1331 'substr(substr(banner, instr(banner, ''-'') + 2), instr(substr(banner, instr(banner, ''-'') + 2),'' '')+1), ' ||
1332 'a.supplemental_log_data_min, ' ||
1333 'case when (select count(*) from cdb_users u where u.username =''DVSYS'' and u.user_id = 1279990 and u.con_id = pdb.con_id) > 0 then ' ||
1334 'case when (select value from v$option o where o.parameter =''Oracle Database Vault'' and (o.con_id = pdb.con_id or o.con_id = 0)) = ''TRUE'' ' ||
1335 'then 1 ' || -- Enabled
1336 'else 0 ' || --Disabled
1337 'end ' ||
1338 'when (select count(*) from cdb_users u where u.username =''DVSYS'' and u.user_id = 1279990 and u.con_id = pdb.con_id) = 0 then ' ||
1339 'case when (select value from v$option o where parameter =''Oracle Database Vault'' and (o.con_id = pdb.con_id or o.con_id = 0)) = ''TRUE'' ' ||
1340 'then -1 ' || -- Not Configured
1341 'else -2 ' || -- Not Installed
1342 'end ' ||
1343 'end ' ||
1344 'FROM gv$database a, ' ||
1345 'gv$version b, ' ||
1346 'gv$instance c, ' ||
1347 'CDB_PROPERTIES e, ' ||
1348 'gv$nls_parameters nlsp1, ' ||
1349 'gv$nls_parameters nlsp2, ' ||
1350 'CDB_PROPERTIES p, ' ||
1351 '(select length(addr)*4 word_len from gv$process where rownum=1) addr, ' ||
1352 'pdbview pdb, ' ||
1353 'gv$pdbs vpb ' ||
1354 'WHERE b.banner LIKE ''%Oracle%'' ' ||
1355 'AND nlsp1.parameter = ''NLS_CHARACTERSET'' ' ||
1356 'AND nlsp2.parameter = ''NLS_NCHAR_CHARACTERSET'' ' ||
1357 'AND e.property_name = ''GLOBAL_DB_NAME'' ' ||
1358 'AND p.property_name = ''DEFAULT_TEMP_TABLESPACE'' ' ||
1359 'AND (a.con_id = pdb.con_id OR a.con_id = 0) ' ||
1360 'AND (b.con_id = pdb.con_id OR b.con_id = 0) ' ||
1361 'AND (c.con_id = pdb.con_id OR c.con_id = 0) ' ||
1362 'AND (nlsp1.con_id = pdb.con_id OR nlsp1.con_id = 0) ' ||
1363 'AND (nlsp2.con_id = pdb.con_id OR nlsp2.con_id = 0) ' ||
1364 'AND e.con_id = pdb.con_id ' ||
1365 'AND p.con_id = pdb.con_id ' ||
1366 'AND vpb.con_id(+) = pdb.con_id' ||
1367 ' AND a.INST_ID = ' || inst_id_row.inst_id ||
1368 ' AND b.INST_ID = ' || inst_id_row.inst_id ||
1369 ' AND c.INST_ID = ' || inst_id_row.inst_id ||
1370 ' AND nlsp1.INST_ID = ' || inst_id_row.inst_id ||
1371 ' AND nlsp2.INST_ID = ' || inst_id_row.inst_id ||
1372 ' AND vpb.INST_ID = ' || inst_id_row.inst_id
1373 ,inst_id_row.instance_name);
1374
1375 END LOOP;
1376 END;
1377
1378
1379 -- write the record for an option, if its found to be installed
1380 -- 'OCM ' is appended to the Option Name, so that to distinguish it from other
1381 -- mechanism of collections like db feature usage statistics.
1382 procedure write_option_record(p_name VARCHAR2,p_install_sql VARCHAR2,
1383 p_usage_sql VARCHAR2,
1384 p_info_sql VARCHAR2 DEFAULT null,p_version_sql VARCHAR2 DEFAULT null)
1385 IS
1386 l_isInstalled INTEGER := 0;
1387 l_isUsed VARCHAR2(5) :='FALSE';
1388 TYPE cur_type IS REF CURSOR;
1389 l_featureInfoCur cur_type;
1390 l_feature_row VARCHAR2(4000) := NULL;
1391 l_feature_info VARCHAR2(4000) := NULL;
1392 l_size INTEGER := 4000;
1393 l_row_separator VARCHAR2(1) := ';';
1394 l_option_version v$instance.version%TYPE := NULL;
1395 BEGIN
1396 --check if installed
1397 IF p_install_sql is NOT NULL THEN
1398 BEGIN
1399 execute immediate 'select 1 from dual where exists (' || p_install_sql || ')' into l_isInstalled;
1400 EXCEPTION
1401 WHEN NO_DATA_FOUND THEN
1402 null;
1403 WHEN OTHERS THEN
1404 -- error out exception that is not
1405 -- 00942: table or view does not exist
1406 -- 00904: invalid identifier
1407 -- 01031: insufficient privileges
1408 IF ( sqlcode != -942 AND sqlcode != -904
1409 AND sqlcode != -1031) THEN
1410 write_error(p_name || ' install_sql=[' ||
1411 p_install_sql ||'] SQLERRM: ' || SQLERRM
1412 || ' SQLCODE: ' || SQLCODE);
1413 END IF;
1414
1415 -- if insufficient priv, set the used to unknown
1416 -- and place the error in the feature info
1417 IF ( sqlcode = -1031) THEN
1418 l_isUsed := 'UNK';
1419 l_feature_info := 'i:' || SQLCODE;
1420 END IF;
1421 END;
1422 END IF;
1423
1424 --check if used
1425 BEGIN
1426 execute immediate 'select ''TRUE'' from dual where exists (' || p_usage_sql || ')' into l_isUsed;
1427 EXCEPTION
1428 WHEN NO_DATA_FOUND THEN
1429 null;
1430 WHEN OTHERS THEN
1431 -- error out exception that is not
1435 IF ( sqlcode != -942 AND sqlcode != -904
1432 -- 00942: table or view does not exist
1433 -- 00904: invalid identifier
1434 -- 01031: insufficient privileges
1436 AND sqlcode != -1031) THEN
1437 write_error(p_name || ' usage_sql=[' ||
1438 p_usage_sql ||'] SQLERRM: ' || SQLERRM
1439 || ' SQLCODE: ' || SQLCODE);
1440 END IF;
1441
1442 -- if insufficient priv, set the used to unknown
1443 -- and place the error in the feature info
1444 IF ( sqlcode = -1031) THEN
1445 l_isUsed := 'UNK';
1446 l_feature_info := 'u:'||SQLCODE;
1447 END IF;
1448 END;
1449
1450 --if the option is used it is installed too by default.
1451 IF l_isUsed = 'TRUE' then
1452 l_isInstalled := 1;
1453 END IF;
1454
1455 -- the option is being used and the sql to get its
1456 -- information is not null
1457 IF l_isUsed = 'TRUE' AND p_info_sql IS NOT NULL THEN
1458 -- feature is used, get feature details
1459 l_feature_info :='';
1460 BEGIN
1461 OPEN l_featureInfoCur FOR p_info_sql;
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN
1464 null;
1465 WHEN OTHERS THEN
1466 -- Capture any exception in info sql
1467 -- error out exception that is not
1468 -- 00942: table or view does not exist
1469 -- 00904: invalid identifier
1470 -- 01031: insufficient privileges
1471 IF ( sqlcode != -942 AND sqlcode != -904
1472 AND sqlcode != -1031) THEN
1473 write_error(p_name || ' feature_info_sql=[' ||
1474 p_info_sql ||'] SQLERRM: ' || SQLERRM
1475 || ' SQLCODE: ' || SQLCODE);
1476 END IF;
1477
1478 -- if insufficient priv set the used to unknown
1479 -- and place the error in the feature info
1480 IF ( sqlcode = -1031) THEN
1481 l_feature_info := 'fi:' ||SQLCODE;
1482 END IF;
1483 END;
1484
1485 BEGIN
1486 LOOP
1487 FETCH l_featureInfoCur INTO l_feature_row;
1488 EXIT WHEN l_featureInfoCur%NOTFOUND;
1489 l_feature_info := l_feature_info || l_feature_row
1490 || l_row_separator ;
1491 l_feature_row := NULL;
1492 END LOOP;
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 IF l_feature_row IS NOT NULL THEN
1496 -- save the data 3 chars less than the limit
1497 l_feature_info := substr(l_feature_info || l_feature_row || l_row_separator ,0,l_size - 3);
1498 -- save the trailing elipses to indicate that more data was present
1499 l_feature_info := l_feature_info || '...';
1500 END IF;
1501 END;
1502 END IF;
1503
1504 -- write a record for this option, if its installed or there
1505 -- is an entry in the option feature
1506 IF l_isInstalled = 1 OR l_feature_info IS NOT NULL THEN
1507 -- get the option version if a version sql is present
1508 IF p_version_sql is NOT NULL THEN
1509 BEGIN
1510 execute immediate p_version_sql into l_option_version;
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 l_option_version := g_db_version;
1514 END;
1515 ELSE
1516 l_option_version := g_db_version;
1517 END IF;
1518
1519 -- replace '|' char in feature into with '#'
1520 write_results(
1521 'select ' ||
1522 '''' || g_dbID || ''', ' || -- DBID
1523 '''' || substr('OCM ' || p_name,0,64) || ''',' || -- NAME
1524 '''' || l_option_version || ''',' || -- Version
1525 'decode(''' || l_isUsed || ''',''TRUE'',''1'',''''),' || -- Detected Usages
1526 '1,' || -- Total Samples
1527 '''' || l_isUsed || ''' ,' || -- Currently Used
1528 ''''',' || -- First Usage Date
1529 ''''',' || -- Last Usage Date
1530 ''''',' || -- Aux Count
1531 ''''',' || -- Last Sample Date
1532 ''''',' || -- Last Sample Period
1533 '''' || replace(l_feature_info,'|','#') || '''' || -- Feature Info
1534 ' from dual');
1535 END IF;
1536
1537 EXCEPTION
1538 WHEN OTHERS THEN
1539 --capture any error
1540 write_error(p_name || ' SQLERRM: ' || SQLERRM
1541 || ' SQLCODE: ' || SQLCODE);
1542 END write_option_record;
1543
1544 /*
1545 --- TEMPLATE FUNCTION FOR DB Option collection
1546 Write <OPTION_NAME> option
1547 Provide three sqls:
1548 1. check for install
1549 2. check for usage
1550 3. get option information for validation
1551 */
1552 /*
1553 PROCEDURE write_<OPTION_NAME>
1554 IS
1555 l_isInstalledSQL VARCHAR2(500) :=
1556 '<OPTION INSTALL CHECK SQL:
1557 returns a row if installed, otherwise none>';
1558 l_isUsedSQL VARCHAR2(500) :=
1559 '<OPTION USAGED CHECK SQL:
1560 returns a row if installed, otherwise none>';
1561 l_infoSQL VARCHAR2(500) :=
1562 '<OPTION INFO SQL:
1563 may return multiple rows of one or more columns.
1564 Data from this is collapsed into a line
1565 with a row separator as validation data for this option
1566 >';
1567 BEGIN
1568 write_option_record(<OPTION_NAME>,l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1569 end write_<OPTION_NAME>
1570 */
1571
1572
1573 /*
1574 Write RAC option
1578 3. get option information for validation
1575 Provide three sqls:
1576 1. check for install
1577 2. check for usage
1579 */
1580 PROCEDURE write_rac
1581 IS
1582 l_isInstalledSQL VARCHAR2(500) :=
1583 'SELECT 1 from v$option where parameter=''Real Application Clusters'' and value =''TRUE'' and rownum = 1';
1584 l_isUsedSQL VARCHAR2(500) :=
1585 'select 1 from (select count(*) CNT from gv$instance where rownum <=2) where CNT >= 2';
1586 l_infoSQL VARCHAR2(500) := NULL;
1587 BEGIN
1588 write_option_record('RAC',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1589 end write_rac;
1590
1591
1592 /*
1593 Write Label Security option
1594 Provide three sqls:
1595 1. check for install
1596 2. check for usage
1597 3. get option information for validation
1598 */
1599 PROCEDURE write_lsec
1600 IS
1601 l_isInstalledSQL VARCHAR2(500) :=
1602 'SELECT 1 from v$option where parameter=''Oracle Label Security'' and value =''TRUE'' and rownum = 1';
1603 l_isUsedSQL VARCHAR2(500) :=
1604 'select 1 from lbacsys.lbac$polt where owner <> ''SA_DEMO'' and rownum = 1';
1605 l_infoSQL VARCHAR2(500) := NULL;
1606 BEGIN
1607 write_option_record('Label Security',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1608 end write_lsec;
1609
1610
1611 /*
1612 Write Data Mining option
1613 Provide three sqls:
1614 1. check for install
1615 2. check for usage
1616 3. get option information for validation
1617 */
1618 PROCEDURE write_data_mining
1619 IS
1620 l_isInstalledSQL VARCHAR2(500) :=
1621 'SELECT 1 from v$option where parameter like ''%Data Mining'' and value =''TRUE'' and rownum = 1';
1622 l_isUsedSQL VARCHAR2(500) :=
1623 'select 1 from odm.odm_mining_model where rownum = 1';
1624 l_infoSQL VARCHAR2(500) := NULL;
1625 BEGIN
1626 write_option_record('ORACLE DATA MINING',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1627 end write_data_mining;
1628
1629
1630 /*
1631 Write Data Vault
1632 1. check for install
1633 2. check for usage
1634 3. get option information for validation
1635 */
1636 PROCEDURE write_data_vault
1637 IS
1638 l_isInstalledSQL VARCHAR2(500) :=
1639 'SELECT 1 from (Select count(*) CNT from dba_users where username in (''DVSYS'',''DVF'') ) where CNT = 2';
1640 l_isUsedSQL VARCHAR2(500) :=
1641 'select 1 from dba_users where username = ''DVSYS'' and user_id = 1279990';
1642 l_infoSQL VARCHAR2(500) := NULL;
1643 BEGIN
1644 write_option_record('Database Vault',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1645 end write_data_vault;
1646
1647
1648 /*
1649 Write Audit Vault
1650 1. check for install
1651 2. check for usage
1652 3. get option information for validation
1653 */
1654 PROCEDURE write_audit_vault
1655 IS
1656 l_isInstalledSQL VARCHAR2(500) := NULL;
1657 l_isUsedSQL VARCHAR2(500) :=
1658 'SELECT 1 from dba_users where username = ''AVSYS'' and rownum = 1';
1659 l_infoSQL VARCHAR2(500) := NULL;
1660 BEGIN
1661 write_option_record('Audit Vault',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1662 end write_audit_vault;
1663
1664
1665 /*
1666 Write Content Database
1667 1. check for install
1668 2. check for usage
1669 3. get option information for validation
1670 */
1671 PROCEDURE write_content_db
1672 IS
1673 l_isInstalledSQL VARCHAR2(500) :=
1674 'SELECT 1 from dba_users where username = ''CONTENT'' and rownum = 1';
1675 l_isUsedSQL VARCHAR2(500) :=
1676 -- odm_document contains more than 9004 rows
1677 'select 1 from (select count(*) CNT from content.odm_document where rownum <= 9005 ) where CNT > 9004';
1678 l_infoSQL VARCHAR2(500) := NULL;
1679 BEGIN
1680 write_option_record('Content Database',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1681 end write_content_db;
1682
1683
1684 /*
1685 Write Records Database
1686 1. check for install
1687 2. check for usage
1688 3. get option information for validation
1689 */
1690 PROCEDURE write_records_db
1691 IS
1692 l_isInstalledSQL VARCHAR2(500) :=
1693 'SELECT 1 from dba_users where username = ''CONTENT'' and rownum = 1';
1694 l_isUsedSQL VARCHAR2(500) :=
1695 'select 1 from content.odm_record where rownum = 1';
1696 l_infoSQL VARCHAR2(500) := NULL;
1697 BEGIN
1698 write_option_record('Records Database',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
1699 end write_records_db;
1700
1701
1702 /*
1703 Write OEM
1704 1. check for install
1705 2. check for usage
1706 3. get option information for validation
1707 */
1708 PROCEDURE write_oem
1709 IS
1710 cursor1 integer;
1711 v_count number(1) := 0;
1712 v_schema dba_tables.owner%TYPE;
1713 v_version varchar2(10);
1714 v_component varchar2(20);
1715 l_rows_processed integer;
1716 CURSOR schema_array IS
1717 SELECT '"' || owner || '"'
1718 FROM dba_tables WHERE table_name = 'SMP_REP_VERSION';
1719
1720 CURSOR schema_array_v2 IS
1721 SELECT owner
1722 FROM dba_tables WHERE table_name = 'SMP_VDS_REPOS_VERSION';
1723 BEGIN
1724 BEGIN
1725 SELECT COUNT(*)
1726 INTO v_count FROM ( SELECT DISTINCT program FROM
1727 v$session WHERE
1728 upper(program) LIKE '%XPNI.EXE%'
1729 OR upper(program) LIKE '%VMS.EXE%'
1730 OR upper(program) LIKE '%EPC.EXE%'
1731 OR upper(program) LIKE '%TDVAPP.EXE%'
1732 OR upper(program) LIKE 'VDOSSHELL%'
1736 OR upper(program) LIKE '%XPAUTUNE%'
1733 OR upper(program) LIKE '%VMQ%'
1734 OR upper(program) LIKE '%VTUSHELL%'
1735 OR upper(program) LIKE '%JAVAVMQ%'
1737 OR upper(program) LIKE '%XPCOIN%'
1738 OR upper(program) LIKE '%XPKSH%'
1739 OR upper(program) LIKE '%XPUI%');
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 null;
1743 END;
1744
1745 IF v_count = 0 THEN
1746 BEGIN
1747
1748 OPEN schema_array;
1749 OPEN schema_array_v2;
1750
1751 cursor1:=dbms_sql.open_cursor;
1752
1753 LOOP -- this loop steps through each valid schema.
1754 FETCH schema_array INTO v_schema;
1755 EXIT WHEN schema_array%notfound;
1756 dbms_sql.parse(cursor1,'select c_current_version, c_component from '||v_schema||'.smp_rep_version', dbms_sql.native);
1757 dbms_sql.define_column(cursor1, 1, v_version, 10);
1758 dbms_sql.define_column(cursor1, 2, v_component, 20);
1759
1760 l_rows_processed:=dbms_sql.execute ( cursor1 );
1761
1762 LOOP -- to step through cursor1 to find console version.
1763 if dbms_sql.fetch_rows(cursor1) >0 then
1764 dbms_sql.column_value (cursor1, 1, v_version);
1765 dbms_sql.column_value (cursor1, 2, v_component);
1766 if v_component = 'CONSOLE' then
1767 --Found a schema that has a repository version
1768 v_count := v_count + 1;
1769 exit;
1770 end if;
1771 else
1772 --Did not find any row
1773 exit;
1774 end if;
1775 END LOOP;
1776 END LOOP;
1777
1778 LOOP -- this loop steps through each valid V2 schema.
1779 FETCH schema_array_v2 INTO v_schema;
1780 EXIT WHEN schema_array_v2%notfound;
1781
1782 v_count := v_count + 1;
1783 --( 'Schema '||rpad(v_schema,15)|| ' has a repository version 2.x' );
1784 END LOOP;
1785
1786 dbms_sql.close_cursor (cursor1);
1787 close schema_array;
1788 close schema_array_v2;
1789 EXCEPTION
1790 WHEN OTHERS THEN
1791 null;
1792 END;
1793 END IF;
1794
1795 IF v_count > 0 THEN
1796 write_option_record('OEM 9i',NULL,'select 1 from dual');
1797 END IF;
1798 end write_oem;
1799
1800
1801 /*
1802 Write Spatial
1803 1. check for install
1804 2. check for usage
1805 3. get option information for validation
1806 */
1807 PROCEDURE write_spatial
1808 IS
1809 l_isInstalledSQL VARCHAR2(500) :=
1810 'select 1 from dual where sdo_version IS NOT NULL';
1811 l_isUsedSQL VARCHAR2(500) :=
1812 'select 1 from (select count(*) CNT from ALL_SDO_GEOM_METADATA where owner <> ''MDSYS'' and rownum =1) where CNT >0';
1813 l_infoSQL VARCHAR2(500) :=
1814 'select count(*) from ALL_SDO_GEOM_METADATA where owner <> ''MDSYS''';
1815 l_versionSQL VARCHAR2(50) := 'select sdo_version from dual';
1816 BEGIN
1817 write_option_record('Spatial',l_isInstalledSQL,l_isUsedSQL,l_infoSQL,l_versionSQL);
1818 end write_spatial;
1819
1820
1821 /*
1822 Write Partitioning
1823 1. check for install
1824 2. check for usage
1825 3. get option information for validation
1826 */
1827 PROCEDURE write_partitioning
1828 IS
1829 l_isInstalledSQL VARCHAR2(500) :=
1830 'SELECT 1 from v$option where parameter = ''Partitioning'' and value =''TRUE'' and rownum = 1';
1831 l_isUsedSQL VARCHAR2(4000) :=
1832 'select 1 from (select sum(tot) CNT from
1833 ( select count(*) tot
1834 from
1835 ( select owner, table_name
1836 from dba_tables
1837 where partitioned=''YES''
1838 -- list of schemas to be excluded
1839 and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
1840 minus
1841 select change_table_schema, change_table_name
1842 from change_tables )
1843 union all
1844 select count(*) tot
1845 from dba_indexes di
1846 where partitioned=''YES''
1847 and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
1848 and not exists
1849 ( select change_table_schema, change_table_name
1850 from change_tables ct
1851 where di.table_owner = ct.change_table_schema
1852 and di.table_name = ct.change_table_name))) where CNT > 0';
1853
1854 l_infoSQL VARCHAR2(5000) :=
1855 ' select num||'':''||idx_or_tab||'':''||user_id||'':''||ptype||'':''||subptype||'':''||
1856 pcnt||'':''||subpcnt||'':''||
1857 pcols||'':''||subpcols||'':''||idx_flags||'':''||
1858 idx_type||'':''||idx_uk||''|'' my_string
1859 from (select * from
1860 (select /*+ full(o) */ dense_rank() over
1861 (order by decode(i.bo#,null,p.obj#,i.bo#)) NUM,
1862 decode(o.type#,1,''I'',2,''T'',null) IDX_OR_TAB,
1863 u.user# USER_ID,
1864 decode(p.parttype, 1, ''RANGE'', 2, ''HASH'', 3,
1865 ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') PTYPE,
1866 decode(mod(p.spare2, 256), 0, null, 2, ''HASH'', 3,
1867 ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') SUBPTYPE,
1868 p.partcnt PCNT,
1869 mod(trunc(p.spare2/65536), 65536) SUBPCNT,
1870 p.partkeycols PCOLS,
1871 mod(trunc(p.spare2/256), 256) SUBPCOLS,
1872 decode(p.flags, 0, null, decode(mod(p.flags,3),0,''LP'',1,''L'',2,''GP'', null)) IDX_FLAGS,
1876 9, ''DOMAIN'') IDX_TYPE,
1873 decode(i.type#, 1, ''NORMAL''|| decode(bitand(i.property, 4), 0, '''', 4, ''/REV''),
1874 2, ''BITMAP'', 3, ''CLUSTER'', 4, ''IOT - TOP'',
1875 5, ''IOT - NESTED'', 6, ''SECONDARY'', 7, ''ANSI'', 8, ''LOB'',
1877 decode(i.property, null,null,
1878 decode(bitand(i.property, 1), 0, ''NONUNIQUE'', 1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
1879 from sys.partobj$ p, sys.obj$ o, sys.user$ u, sys.ind$ i
1880 where o.obj# = i.obj#(+)
1881 and o.owner# = u.user#
1882 and p.obj# = o.obj#
1883 and u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
1884 -- fix bug 3074607 - filter on obj$
1885 and o.type# in (1,2,19,20,25,34,35)
1886 -- exclude change tables
1887 and o.obj# not in ( select obj# from sys.cdc_change_tables$)
1888 -- exclude local partitioned indexes on change tables
1889 and i.bo# not in ( select obj# from sys.cdc_change_tables$)
1890 union all
1891 -- global nonpartitioned indexes on partitioned tables
1892 select dense_rank() over (order by decode(i.bo#,null,p.obj#,i.bo#)) NUM,
1893 ''I'' IDX_OR_TAB,
1894 u.user# USER_ID,
1895 null,null,null,null,cols PCOLS,null,
1896 ''GNP'' IDX_FLAGS,
1897 decode(i.type#, 1, ''NORMAL''||
1898 decode(bitand(i.property, 4), 0, '''', 4,
1899 ''/REV''),
1900 2, ''BITMAP'', 3, ''CLUSTER'', 4,
1901 ''IOT - TOP'',
1902 5, ''IOT - NESTED'', 6, ''SECONDARY'', 7,
1903 ''ANSI'', 8, ''LOB'',
1904 9, ''DOMAIN'') IDX_TYPE,
1905 decode(i.property, null,null,
1906 decode(bitand(i.property, 1),
1907 0, ''NONUNIQUE'',
1908 1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
1909 from sys.partobj$ p, sys.user$ u, sys.obj$ o, sys.ind$ i
1910 where p.obj# = i.bo#
1911 -- exclude global nonpartitioned indexes on change tables
1912 and i.bo# not in ( select obj# from sys.cdc_change_tables$)
1913 and o.owner# = u.user#
1914 and p.obj# = o.obj#
1915 and p.flags =0
1916 and bitand(i.property, 2) <>2
1917 and u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS''))
1918 order by num, idx_or_tab desc)';
1919 BEGIN
1920 IF g_version_category = VERSION_817 THEN
1921 l_isUsedSQL :=
1922 'select 1 from (select sum(tot) CNT from
1923 ( select count(*) tot
1924 from dba_tables
1925 where partitioned=''YES''
1926 and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
1927 union all
1928 select count(*) tot
1929 from dba_indexes
1930 where partitioned=''YES''
1931 and owner not in (''SYS'',''SYSTEM'',''SH'',''MDSYS''))) where CNT > 0';
1932
1933 l_infoSQL :=
1934 'select num||'':''||idx_or_tab||'':''||user_id||'':''||ptype||'':''||subptype||'':''
1935 ||pcnt||'':'' ||subpcnt||'':''||
1936 pcols||'':''||subpcols||'':''||idx_flags||'':''||
1937 idx_type||'':''||idx_uk||''|'' my_string from (select * from
1938 (select /*+ full(o) */ dense_rank() over
1939 (order by decode(i.bo#,null,p.obj#,i.bo#)) NUM,
1940 decode(o.type#,1,''I'',2,''T'',null) IDX_OR_TAB,
1941 u.user# USER_ID,
1942 decode(p.parttype, 1, ''RANGE'', 2, ''HASH'', 3,
1943 ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') PTYPE,
1944 decode(mod(p.spare2, 256), 0, null, 2, ''HASH'', 3,
1945 ''SYSTEM'', 4, ''LIST'', ''UNKNOWN'') SUBPTYPE,
1946 p.partcnt PCNT,
1947 mod(trunc(p.spare2/65536), 65536) SUBPCNT,
1948 p.partkeycols PCOLS,
1949 mod(trunc(p.spare2/256), 256) SUBPCOLS,
1950 decode(p.flags,0,null,decode(mod(p.flags,3),0,
1951 ''LP'',1,''L'', 2,''GP'' ,null)) IDX_FLAGS,
1952 decode(i.type#, 1, ''NORMAL''||
1953 decode(bitand(i.property, 4), 0,
1954 '''', 4, ''/REV''),
1955 2, ''BITMAP'', 3, ''CLUSTER'', 4, ''IOT - TOP'',
1956 5, ''IOT - NESTED'', 6, ''SECONDARY'', 7, ''ANSI'',
1957 8, ''LOB'', 9, ''DOMAIN'') IDX_TYPE,
1958 decode(i.property, null,null,
1959 decode(bitand(i.property, 1), 0,
1960 ''NONUNIQUE'',
1961 1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
1962 from sys.partobj$ p, sys.obj$ o, sys.user$ u, sys.ind$ i
1963 where o.obj# = i.obj#(+)
1964 and o.owner# = u.user#
1965 and p.obj# = o.obj#
1966 and u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS'')
1967 -- fix bug 3074607 - filter on obj$
1968 and o.type# in (1,2,19,20,25,34,35)
1969 union all
1973 u.user# USER_ID,
1970 select dense_rank() over (order by decode(i.bo#,null,p.obj#,
1971 i.bo#)) NUM,
1972 ''I'' IDX_OR_TAB,
1974 cast(null as varchar2(20)) c0,
1975 cast(null as varchar2(20)) c1,
1976 cast(null as number) c2,
1977 cast( null as number) c3,
1978 cols PCOLS,
1979 cast(null as number) c4,
1980 ''GNP'' IDX_FLAGS,
1981 decode(i.type#, 1, ''NORMAL''||
1982 decode(bitand(i.property, 4), 0, '''', 4,
1983 ''/REV''),
1984 2, ''BITMAP'', 3, ''CLUSTER'', 4,
1985 ''IOT - TOP'',
1986 5, ''IOT - NESTED'', 6, ''SECONDARY'', 7,
1987 ''ANSI'', 8, ''LOB'',
1988 9, ''DOMAIN'') IDX_TYPE,
1989 decode(i.property, null,null,
1990 decode(bitand(i.property, 1), 0, ''NONUNIQUE'', 1, ''UNIQUE'', ''UNDEFINED'')) IDX_UK
1991 from sys.partobj$ p, sys.user$ u, sys.obj$ o, sys.ind$ i
1992 where p.obj# = i.bo#
1993 and o.owner# = u.user#
1994 and p.obj# = o.obj#
1995 and p.flags =0
1996 and bitand(i.property, 2) <>2
1997 and u.name not in (''SYS'',''SYSTEM'',''SH'',''MDSYS''))
1998 order by num, idx_or_tab desc)';
1999 END IF;
2000
2001 write_option_record('Partitioning',l_isInstalledSQL,l_isUsedSQL,
2002 l_infoSQL);
2003 end write_partitioning;
2004
2005
2006 /*
2007 Write OLAP option
2008 Provide three sqls:
2009 1. check for install
2010 2. check for usage
2011 3. get option information for validation
2012 */
2013 PROCEDURE write_olap
2014 IS
2015 l_isInstalledSQL VARCHAR2(500) :=
2016 'SELECT 1 from v$option where parameter=''OLAP'' and value =''TRUE'' and rownum = 1';
2017 l_isUsedSQL VARCHAR2(500) :=
2018 'SELECT 1 FROM olapsys.dba$olap_cubes '||
2019 ' WHERE OWNER <> ''SH'' and rownum = 1 '||
2020 ' UNION ALL '||
2021 'SELECT 1 FROM '||
2022 ' (SELECT count(*) CNT FROM dba_aws '||
2023 ' where upper(AW_NAME) NOT IN '||
2024 ' (''EXPRESS'', ''CWMTOECM'', ''AWMD'', ''AWREPORT'', ''AWCREATE10G'', ''AWXML'')'||
2025 ' ) where CNT > 0';
2026 l_infoSQL VARCHAR2(500) :=
2027 'SELECT OWNER || ''-'' || AW_NAME FROM DBA_AWS ';
2028 BEGIN
2029 write_option_record('OLAP',l_isInstalledSQL,l_isUsedSQL,l_infoSQL);
2030 end write_olap;
2031
2032
2033 PROCEDURE write_db_feature_usage IS
2034 BEGIN
2035 write_results(
2036 'SELECT DBID,NAME, VERSION, DETECTED_USAGES, TOTAL_SAMPLES, CURRENTLY_USED, '||
2037 'to_char(FIRST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') FIRST_USAGE, '||
2038 'to_char(LAST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_USAGE, '||
2039 'AUX_COUNT, '||
2040 'to_char(LAST_SAMPLE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_SAMPLE, '||
2041 'LAST_SAMPLE_PERIOD, '||
2042 'replace((nvl(TO_CHAR(substr(FEATURE_INFO,0,4000)),'''')),''|'',''#'') FEATURE_DETAIL '||
2043 'FROM DBA_FEATURE_USAGE_STATISTICS where version in ( select max(version) from DBA_FEATURE_USAGE_STATISTICS )' );
2044
2045 end write_db_feature_usage;
2046
2047
2048 /*
2049 Private procedure
2050 Collect metric=db_feature_usage
2051 */
2052 procedure collect_db_feature_usage IS
2053 l_sql VARCHAR2(4000);
2054 l_end_done BOOLEAN DEFAULT FALSE;
2055 BEGIN
2056 put_metric_marker(METRIC_BEGIN_MARKER,'db_feature_usage',null);
2057 IF g_version_category = VERSION_817
2058 OR g_version_category = VERSION_9i
2059 OR g_version_category = VERSION_9iR2 THEN
2060 write_partitioning;
2061 write_oem;
2062 write_olap;
2063 write_rac;
2064 write_lsec;
2065 write_data_mining;
2066 write_data_vault;
2067 write_audit_vault;
2068 write_content_db;
2069 write_records_db;
2070 write_spatial;
2071 END IF;
2072
2073 IF g_version_category = VERSION_10gR1
2074 OR g_version_category = VERSION_10gR2
2075 OR g_version_category = VERSION_11gR1
2076 OR g_version_category = VERSION_11gR2
2077 OR g_version_category = VERSION_12gR1
2078 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2079 write_data_vault;
2080 write_audit_vault;
2081 write_content_db;
2082 write_records_db;
2083 write_db_feature_usage;
2084 END IF;
2085 put_metric_marker(METRIC_END_MARKER,'db_feature_usage',null);
2086 l_end_done := TRUE;
2087 EXCEPTION
2088 WHEN OTHERS THEN
2089 IF NOT l_end_done THEN
2090 put_metric_marker(METRIC_END_MARKER,'db_feature_usage',null);
2091 END IF;
2092 RAISE;
2093 END collect_db_feature_usage;
2094
2095
2096 PROCEDURE write_cdb_feature_usage IS
2097 BEGIN
2098 write_results(
2099 'with pdbview AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
2103 ' AUX_COUNT, ' ||
2100 'SELECT pdb.pdb, DBID, NAME, VERSION, DETECTED_USAGES, TOTAL_SAMPLES, CURRENTLY_USED, ' ||
2101 ' to_char(FIRST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') FIRST_USAGE, ' ||
2102 ' to_char(LAST_USAGE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_USAGE, ' ||
2104 ' to_char(LAST_SAMPLE_DATE,''YYYY-MM-DD HH24:MI:SS'') LAST_SAMPLE, ' ||
2105 ' LAST_SAMPLE_PERIOD, ' ||
2106 ' replace((nvl(TO_CHAR(substr(FEATURE_INFO,0,4000)),'''')),''|'',''#'') FEATURE_DETAIL ' ||
2107 'FROM CDB_FEATURE_USAGE_STATISTICS cfus, pdbview pdb ' ||
2108 'where cfus.version in ' ||
2109 ' (select max(version) from CDB_FEATURE_USAGE_STATISTICS where con_id = pdb.con_id) ' ||
2110 ' and cfus.con_id = pdb.con_id ');
2111 end write_cdb_feature_usage;
2112
2113
2114 /*
2115 Private procedure
2116 Collect metric=cdb_feature_usage
2117 */
2118 procedure collect_cdb_feature_usage IS
2119 l_sql VARCHAR2(4000);
2120 l_end_done BOOLEAN DEFAULT FALSE;
2121 BEGIN
2122 put_metric_marker(METRIC_BEGIN_MARKER,'cdb_feature_usage',null);
2123 write_cdb_feature_usage;
2124 put_metric_marker(METRIC_END_MARKER,'cdb_feature_usage',null);
2125 l_end_done := TRUE;
2126 EXCEPTION
2127 WHEN OTHERS THEN
2128 IF NOT l_end_done THEN
2129 put_metric_marker(METRIC_END_MARKER,'cdb_feature_usage',null);
2130 END IF;
2131 RAISE;
2132 END collect_cdb_feature_usage;
2133
2134 /*
2135 Private procedure
2136 Collect metric=high_water_mark_stats
2137 */
2138 procedure collect_high_water_mark_stats IS
2139 BEGIN
2140 IF g_version_category = VERSION_10gR1
2141 OR g_version_category = VERSION_10gR2
2142 OR g_version_category = VERSION_11gR1
2143 OR g_version_category = VERSION_11gR2
2144 OR g_version_category = VERSION_12gR1
2145 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2146 write_metric('high_water_mark_stats',
2147 'SELECT s.DBID, s.NAME, s.VERSION, s.HIGHWATER, s.LAST_VALUE '||
2148 ' FROM DBA_HIGH_WATER_MARK_STATISTICS s, V$DATABASE d '||
2149 ' where s.version in ( select max(version) from dba_high_water_mark_statistics ) ' ||
2150 ' and s.dbid = d.dbid');
2151 END IF;
2152 END collect_high_water_mark_stats;
2153
2154
2155 /*
2156 Private procedure
2157 Collect metric=cdb_high_water_mark_stats
2158 */
2159 procedure collect_cdb_hwm_stats IS
2160 BEGIN
2161 write_metric('cdb_high_water_mark_stats',
2162 'WITH pdbs AS (SELECT DISTINCT con_id, name pdb FROM gv$containers where con_id != 2) ' ||
2163 'SELECT p.pdb, s.DBID, s.NAME, s.VERSION, s.HIGHWATER, s.LAST_VALUE ' ||
2164 'FROM CDB_HIGH_WATER_MARK_STATISTICS s, V$DATABASE d, pdbs p ' ||
2165 'WHERE s.version in ( SELECT max(version) FROM dba_high_water_mark_statistics ) ' ||
2166 'AND s.dbid = d.dbid ' ||
2167 'AND s.con_id = p.con_id');
2168 END collect_cdb_hwm_stats;
2169
2170
2171 /*
2172 Private procedure
2173 Collect metric=db_cpu_usage
2174 */
2175 procedure collect_db_cpu_usage IS
2176 BEGIN
2177 IF g_version_category = VERSION_10gR2
2178 OR g_version_category = VERSION_11gR1
2179 OR g_version_category = VERSION_11gR2
2180 OR g_version_category = VERSION_12gR1
2181 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2182 write_metric('db_cpu_usage',
2183 'SELECT * FROM (SELECT DBID,VERSION ,to_char(TIMESTAMP,''YYYY-MM-DD HH24:MI:SS''), CPU_COUNT, CPU_CORE_COUNT, CPU_SOCKET_COUNT '||
2184 'FROM DBA_CPU_USAGE_STATISTICS '||
2185 'where version in (select max(version) from DBA_CPU_USAGE_STATISTICS) ORDER BY Timestamp desc) WHERE ROWNUM <= 1 ');
2186 END IF;
2187 END collect_db_cpu_usage;
2188
2189
2190 /*
2191 Private procedure
2192 Collect metric=oracle_pdb_list, oracle_pdb_targets
2193 */
2194 procedure collect_pdb_list IS
2195 BEGIN
2196 write_metric('oracle_pdb_list',
2197 'select /*+ NO_PARALLEL(dp) */ '||
2198 'sys_context(''USERENV'',''CON_NAME'') as NAME, con_uid as CON_UID, sys_context(''USERENV'',''DB_UNIQUE_NAME'') as SERVICE_NAME '||
2199 'from dba_pdbs dp where dp.pdb_name = ''PDB$SEED'' '||
2200 'union '||
2201 'select /*+ NO_PARALLEL(a) NO_PARALLEL(b) */ a.pdb_name as NAME, a.con_uid as CON_UID, NVL(b.NAME,a.pdb_name) as SERVICE_NAME '||
2202 'from dba_pdbs a LEFT JOIN cdb_services b ON a.pdb_name = b.pdb '||
2203 'where a.pdb_name <> ''PDB$SEED'' and '||
2204 '(b.name is null or regexp_like(b.name,''^''||a.pdb_name||''$|^''||a.pdb_name||''\.'', ''i'')) ORDER BY name');
2205 write_metric('oracle_pdb_targets',
2206 'select /*+ NO_PARALLEL(dp) */ '||
2207 'sys_context(''USERENV'',''CON_NAME'') as NAME, con_uid, sys_context(''USERENV'',''DB_UNIQUE_NAME'') as SERVICE_NAME, ''YES'' as IS_ROOT '||
2208 'from dba_pdbs dp where dp.pdb_name = ''PDB$SEED'' '||
2209 'union '||
2210 'select /*+ NO_PARALLEL(a) NO_PARALLEL(b) */ a.pdb_name as NAME, a.con_uid, NVL(b.NAME,a.pdb_name) as SERVICE_NAME, ''NO'' as IS_ROOT '||
2211 'from dba_pdbs a LEFT JOIN cdb_services b ON a.pdb_name = b.pdb '||
2212 'where a.pdb_name <> ''PDB$SEED'' and '||
2213 '(b.name is null or regexp_like(b.name,''^''||a.pdb_name||''$|^''||a.pdb_name||''\.'', ''i'')) ORDER BY name');
2214 END collect_pdb_list;
2218 Collect metric=oracle_cdb_services
2215
2216 /*
2217 Private procedure
2219 */
2220 procedure collect_cdb_services IS
2221 CURSOR l_res_cur IS select inst_id,instance_name from gv$instance;
2222 BEGIN
2223 FOR inst_id_row in l_res_cur LOOP
2224 write_metric('cdb_services',
2225 'WITH pdbs AS (SELECT DISTINCT con_id, name pdb, inst_id FROM gv$containers where con_id != 2) ' ||
2226 'select /*+ NO_PARALLEL(s) */ p.pdb, name, network_name, TO_CHAR(creation_date, ''YYYY-MM-DD HH24:MI:SS''), '||
2227 'failover_method, failover_type, failover_retries, failover_delay,min_cardinality, max_cardinality, '||
2228 'goal, dtp, enabled, aq_ha_notifications, clb_goal, edition '||
2229 'from sys.cdb_services s, pdbs p WHERE s.con_id = p.con_id and p.inst_id = ' || inst_id_row.inst_id
2230 ,inst_id_row.instance_name);
2231
2232 END LOOP;
2233 END collect_cdb_services;
2234
2235 /************************************************
2236 * BEGIN Cell Metrics
2237 ***********************************************/
2238
2239 procedure collect_cell_list IS
2240 l_sql VARCHAR2(4000);
2241 BEGIN
2242 IF g_version_category = VERSION_11gR2
2243 OR g_version_category = VERSION_12gR1
2244 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2245 l_sql := 'select replace(cc.cellname, '':'', ''_'') , cell.name, cell.id, cell.cellVersion, cell.releaseVersion' ||
2246 ' from v$cell_config cc, ' ||
2247 ' xmltable ' ||
2248 '(''/cli-output/cell'' passing xmltype(cc.confval) ' ||
2249 ' columns ' ||
2250 ' name varchar2(256) path ''name'', ' ||
2251 ' id varchar2(256) path ''id'', ' ||
2252 ' cellVersion varchar2(256) path ''cellVersion'', ' ||
2253 ' releaseVersion varchar2(256) path ''releaseVersion'' ' ||
2254 ') cell where cc.conftype=''CELL''';
2255 write_metric('cell_list', l_sql);
2256 END IF;
2257 END collect_cell_list;
2258
2259 /*
2260 Private procedure
2261 Collect metric=cell_config
2262 */
2263 procedure collect_cell_config IS
2264 l_ip_cur INTEGER;
2265 l_ip_var VARCHAR2(256);
2266 l_res INTEGER;
2267 l_sql VARCHAR2(4000);
2268 BEGIN
2269 IF g_version_category = VERSION_11gR2
2270 OR g_version_category = VERSION_12gR1
2271 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2272 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2273 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2274 DBMS_SQL.NATIVE);
2275 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2276 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2277 LOOP
2278 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2279 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2280 l_sql := 'select cell.* ' ||
2281 ' from ' ||
2282 ' v$cell_config cc, ' ||
2283 ' xmltable ' ||
2284 ' (''/cli-output/cell'' passing xmltype(cc.confval) ' ||
2285 ' columns ' ||
2286 ' name varchar2(256) path ''name'', ' ||
2287 ' bmcType varchar2(256) path ''bmcType'', ' ||
2288 ' cellVersion varchar2(256) path ''cellVersion'', ' ||
2289 ' cpuCount varchar2(256) path ''cpuCount'', ' ||
2290 ' fanCount varchar2(256) path ''fanCount'', ' ||
2291 ' id varchar2(256) path ''id'', ' ||
2292 ' interconnectCount varchar2(256) path ''interconnectCount'', ' ||
2293 ' iormBoost varchar2(256) path ''iormBoost'', '||
2294 ' ipaddress1 varchar2(256) path ''ipaddress1'', ' ||
2295 ' ipaddress2 varchar2(256) path ''ipaddress2'', ' ||
2296 ' ipaddress3 varchar2(256) path ''ipaddress3'', ' ||
2297 ' ipaddress4 varchar2(256) path ''ipaddress4'', ' ||
2298 ' kernelVersion varchar2(256) path ''kernelVersion'', ' ||
2299 ' makeModel varchar2(256) path ''makeModel'', ' ||
2300 ' metricHistoryDays varchar2(256) path ''metricHistoryDays'', ' ||
2301 ' powerCount varchar2(256) path ''powerCount'' ' ||
2302 ' ) cell where ' ||
2303 ' cc.conftype=''CELL'' and cellname='''|| l_ip_var || '''';
2304 write_metric('cell_config', l_sql, null, replace(l_ip_var, ':', '_') , '|' );
2305 END IF;
2306 END LOOP;
2307 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2308 END IF;
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2312 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2313 END IF;
2314 END collect_cell_config;
2315
2316
2317 procedure collect_griddisk_config IS
2318 l_ip_cur INTEGER;
2319 l_ip_var VARCHAR2(256);
2320 l_res INTEGER;
2321 l_sql VARCHAR2(4000);
2322 BEGIN
2323 IF g_version_category = VERSION_11gR2
2324 OR g_version_category = VERSION_12gR1
2325 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2326 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2327 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2328 DBMS_SQL.NATIVE);
2329 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2330 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2331 LOOP
2332 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2333 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2334 l_sql := 'select gds.name, gds.availableTo, gds.cellDisk, gds.gdcomment, ' ||
2335 'gds.creationTime, gds.errorCount, gds.id, round(gds.offset/1048576), ' ||
2336 'round(gds.gdsize/1048576), gds.status ' ||
2337 ' from ' ||
2338 ' v$cell_config cc, ' ||
2339 ' xmltable ' ||
2340 ' (''/cli-output/griddisk'' passing xmltype(cc.confval) ' ||
2344 ' cellDisk varchar2(256) path ''cellDisk'', ' ||
2341 ' columns ' ||
2342 ' name varchar2(256) path ''name'', ' ||
2343 ' availableTo varchar2(256) path ''availableTo'', ' ||
2345 ' gdcomment varchar2(4000) path ''comment'', ' ||
2346 ' creationTime varchar2(256) path ''creationTime'', ' ||
2347 ' errorCount varchar2(256) path ''errorCount'', ' ||
2348 ' id varchar2(256) path ''id'', ' ||
2349 ' offset varchar2(256) path ''offset'', ' ||
2350 ' gdsize varchar2(256) path ''size'', ' ||
2351 ' status varchar2(256) path ''status'' ' ||
2352 ' ) gds where cc.conftype=''GRIDDISKS'' and cellname='''|| l_ip_var || '''';
2353 write_metric('cell_griddisk_config', l_sql,
2354 null, replace(l_ip_var, ':', '_'), '|' );
2355 END IF;
2356 END LOOP;
2357 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2358 END IF;
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2362 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2363 END IF;
2364 END collect_griddisk_config;
2365
2366
2367 procedure collect_celldisk_config IS
2368 l_ip_cur INTEGER;
2369 l_ip_var VARCHAR2(256);
2370 l_res INTEGER;
2371 l_sql VARCHAR2(4000);
2372 BEGIN
2373 IF g_version_category = VERSION_11gR2
2374 OR g_version_category = VERSION_12gR1
2375 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2376 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2377 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2378 DBMS_SQL.NATIVE);
2379 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2380 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2381 LOOP
2382 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2383 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2384 l_sql :=
2385 ' select cds.name, cds.cdcomment, cds.creationTime, cds.deviceName, ' ||
2386 ' cds.devicePartition, cds.errorCount,round(cds.freeSpace/1073741824, 2), cds.id, cds.lun, ' ||
2387 ' cds.raidLevel,round(cds.cdsize/1073741824, 2), cds.status ' ||
2388 ' from ' ||
2389 ' v$cell_config cc, ' ||
2390 ' xmltable ' ||
2391 ' (''/cli-output/celldisk'' passing xmltype(cc.confval) ' ||
2392 ' columns ' ||
2393 ' name varchar2(256) path ''name'', ' ||
2394 ' cdcomment varchar2(4000) path ''comment'', ' ||
2395 ' creationTime varchar2(256) path ''creationTime'', ' ||
2396 ' deviceName varchar2(256) path ''deviceName'', ' ||
2397 ' devicePartition varchar2(256) path ''devicePartition'', ' ||
2398 ' errorCount varchar2(256) path ''errorCount'', ' ||
2399 ' freeSpace varchar2(256) path ''freeSpace'', ' ||
2400 ' id varchar2(256) path ''id'', ' ||
2401 ' lun varchar2(256) path ''lun'', ' ||
2402 ' raidLevel varchar2(256) path ''raidLevel'', ' ||
2403 ' cdsize varchar2(256) path ''size'', ' ||
2404 ' status varchar2(256) path ''status'' ' ||
2405 ' ) cds where cc.conftype=''CELLDISKS'' and cellname='''|| l_ip_var || '''';
2406 write_metric('cell_celldisk_config', l_sql,
2407 null, replace(l_ip_var, ':', '_'), '|' );
2408 END IF;
2409 END LOOP;
2410 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2411 END IF;
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2415 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2416 END IF;
2417 END collect_celldisk_config;
2418
2419
2420 procedure collect_lun_config IS
2421 l_ip_cur INTEGER;
2422 l_ip_var VARCHAR2(256);
2423 l_res INTEGER;
2424 l_sql VARCHAR2(4000);
2425 BEGIN
2426 IF g_version_category = VERSION_11gR2
2427 OR g_version_category = VERSION_12gR1
2428 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2429 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2430 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2431 DBMS_SQL.NATIVE);
2432 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2433 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2434 LOOP
2435 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2436 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2437 l_sql :=
2438 ' select luns.name, luns.cellDisk, luns.deviceName, luns.id, luns.isSystemLun, ' ||
2439 ' luns.lunAutoCreate, round(luns.lunSize/1073741824, 2), luns.physicalDevices, ' ||
2440 ' luns.raidLevel, luns.status ' ||
2441 ' from ' ||
2442 ' v$cell_config cc, ' ||
2443 ' xmltable ' ||
2444 ' (''/cli-output/lun'' passing xmltype(cc.confval) ' ||
2445 ' columns ' ||
2446 ' name varchar2(256) path ''name'', ' ||
2447 ' cellDisk varchar2(256) path ''cellDisk'', ' ||
2448 ' deviceName varchar2(256) path ''deviceName'', ' ||
2449 ' id varchar2(256) path ''id'', ' ||
2450 ' isSystemLun varchar2(256) path ''isSystemLun'', ' ||
2451 ' lunAutoCreate varchar2(256) path ''lunAutoCreate'', ' ||
2452 ' lunSize varchar2(256) path ''lunSize'', ' ||
2453 ' physicalDevices varchar2(256) path ''physicalDevices'', ' ||
2454 ' raidLevel varchar2(256) path ''raidLevel'', ' ||
2455 ' status varchar2(256) path ''status'' ' ||
2456 ' ) luns where cc.conftype=''LUNS'' and cellname='''|| l_ip_var || '''';
2457 write_metric('cell_lun_config', l_sql,
2458 null, replace(l_ip_var, ':', '_'), '|' );
2459 END IF;
2460 END LOOP;
2461 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2462 END IF;
2463 EXCEPTION
2464 WHEN OTHERS THEN
2465 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2466 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2470
2467 END IF;
2468 END collect_lun_config;
2469
2471 procedure collect_physicaldisk_config IS
2472 l_ip_cur INTEGER;
2473 l_ip_var VARCHAR2(256);
2474 l_res INTEGER;
2475 l_sql VARCHAR2(4000);
2476 BEGIN
2477 IF g_version_category = VERSION_11gR2
2478 OR g_version_category = VERSION_12gR1
2479 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2480 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2481 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2482 DBMS_SQL.NATIVE);
2483 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2484 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2485 LOOP
2486 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2487 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2488 l_sql :=
2489 ' select pds.name, pds.id, pds.luns, pds.physicalInsertTime, ' ||
2490 ' round (pds.physicalSize/1073741824, 2), pds.status ' ||
2491 ' from ' ||
2492 ' v$cell_config cc, ' ||
2493 ' xmltable ' ||
2494 ' (''/cli-output/physicaldisk'' passing xmltype(cc.confval) ' ||
2495 ' columns ' ||
2496 ' name varchar2(256) path ''name'', ' ||
2497 ' id varchar2(256) path ''id'', ' ||
2498 ' luns varchar2(256) path ''luns'', ' ||
2499 ' physicalInsertTime varchar2(256) path ''physicalInsertTime'', ' ||
2500 ' physicalSize varchar2(256) path ''physicalSize'', ' ||
2501 ' status varchar2(256) path ''status'' ' ||
2502 ' ) pds where cc.conftype like ''PHYSICAL%'' and cellname='''|| l_ip_var || '''';
2503 write_metric('cell_physicaldisk_config', l_sql,
2504 null, replace(l_ip_var, ':', '_'), '|' );
2505 END IF;
2506 END LOOP;
2507 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2508 END IF;
2509 EXCEPTION
2510 WHEN OTHERS THEN
2511 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2512 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2513 END IF;
2514 END collect_physicaldisk_config;
2515
2516
2517 procedure collect_iorm_config IS
2518 l_ip_cur INTEGER;
2519 l_ip_var VARCHAR2(256);
2520 l_res INTEGER;
2521 l_sql VARCHAR2(4000);
2522 BEGIN
2523 IF g_version_category = VERSION_11gR2
2524 OR g_version_category = VERSION_12gR1
2525 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2526 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2527 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2528 DBMS_SQL.NATIVE);
2529 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2530 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2531 LOOP
2532 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2533 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2534 l_sql :=
2535 ' select iorm.* ' ||
2536 ' from ' ||
2537 ' v$cell_config cc, ' ||
2538 ' xmltable ' ||
2539 ' (''/cli-output/interdatabaseplan'' passing xmltype(cc.confval) ' ||
2540 ' columns ' ||
2541 ' name varchar2(256) path ''name'', ' ||
2542 ' catPlan varchar2(256) path ''catPlan'', ' ||
2543 ' dbPlan varchar2(256) path ''dbPlan'', ' ||
2544 ' status varchar2(256) path ''status'' ' ||
2545 ' ) iorm where cc.conftype =''IORM'' and cellname='''|| l_ip_var || '''';
2546 write_metric('cell_iorm_config', l_sql,
2547 null, replace(l_ip_var, ':', '_'), '|' );
2548 END IF;
2549 END LOOP;
2550 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2551 END IF;
2552 EXCEPTION
2553 WHEN OTHERS THEN
2554 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2555 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2556 END IF;
2557 END collect_iorm_config;
2558
2559 procedure collect_patches IS
2560 l_ip_cur INTEGER;
2561 l_ip_var VARCHAR2(256);
2562 l_res INTEGER;
2563 l_sql VARCHAR2(4000);
2564 BEGIN
2565 IF g_version_category = VERSION_11gR2
2566 OR g_version_category = VERSION_12gR1
2567 OR g_version_category = HIGHER_SUPPORTED_VERSION THEN
2568 l_ip_cur := DBMS_SQL.OPEN_CURSOR;
2569 DBMS_SQL.PARSE(l_ip_cur, 'select distinct cellname from v$cell_config',
2570 DBMS_SQL.NATIVE);
2571 DBMS_SQL.DEFINE_COLUMN(l_ip_cur, 1, l_ip_var, 256);
2572 l_res := DBMS_SQL.EXECUTE(l_ip_cur);
2573 LOOP
2574 IF DBMS_SQL.FETCH_ROWS(l_ip_cur)>0 THEN
2575 DBMS_SQL.COLUMN_VALUE(l_ip_cur, 1, l_ip_var);
2576 l_sql := 'select cell.* ' ||
2577 ' from ' ||
2578 ' v$cell_config cc, ' ||
2579 ' xmltable ' ||
2580 ' (''/cli-output/cell'' passing xmltype(cc.confval) ' ||
2581 ' columns ' ||
2582 ' releaseTrackingBug varchar2(256) path ''releaseTrackingBug'' ' ||
2583 ' ) cell where ' ||
2584 ' cc.conftype=''CELL'' and cellname='''|| l_ip_var || '''';
2585 write_metric('cell_patches', l_sql,
2586 null, replace(l_ip_var, ':', '_') , '|' );
2587 END IF;
2588 END LOOP;
2589 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2590 END IF;
2591 EXCEPTION
2592 WHEN OTHERS THEN
2593 IF DBMS_SQL.IS_OPEN(l_ip_cur) THEN
2594 DBMS_SQL.CLOSE_CURSOR(l_ip_cur);
2595 END IF;
2596 END collect_patches;
2597
2598 /************************************************
2599 * END Cell Metrics
2600 ***********************************************/
2601
2602
2603 /*
2604 get the destination file name
2605 */
2606 FUNCTION get_dest_file_name(p_ext IN VARCHAR2 DEFAULT '.ll') RETURN VARCHAR2 IS
2610 SELECT name into l_db_name FROM v$database ;
2607 l_db_name v$database.name%TYPE;
2608 l_par v$instance.PARALLEL%TYPE;
2609 BEGIN
2611 /*
2612 Check if the database is running in RAC mode.
2613 If so, name the file as <db_name>-RAC.ll
2614 */
2615 select PARALLEL into l_par from v$instance;
2616 IF l_par = 'YES' THEN
2617 RETURN l_db_name || '-RAC'||p_ext;
2618 END IF;
2619
2620 RETURN l_db_name || p_ext;
2621 END get_dest_file_name;
2622
2623 /**
2624 Write file header
2625 */
2626 PROCEDURE write_file_header
2627 IS
2628 l_db_characterset VARCHAR2(20);
2629 l_vers v$instance.version%TYPE;
2630 l_comp_cnt NUMBER;
2631 l_checkXMLdb VARCHAR2(500);
2632 BEGIN
2633 select value into l_db_characterset from NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET';
2634 UTL_FILE.PUT_LINE(g_config_handle,'META_VER=' || ORACLE_DATABASE_META_VER);
2635 UTL_FILE.PUT_LINE(g_config_handle,'TIMESTAMP=' || TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
2636 UTL_FILE.PUT_LINE(g_config_handle,'NLS_CHARACTERSET=' || l_db_characterset);
2637 -- If 11+, check for XML DB before calling UTL_INADDR package, otherwise go ahead
2638 select LPAD(version,10,'0') into l_vers from v$instance;
2639 IF l_vers >= '11.0.0.0.0' THEN
2640 l_checkXMLdb :=
2641 'select count(*) from dba_registry where COMP_NAME = ''Oracle XML Database'' ' ||
2642 'and STATUS = ''VALID''' ;
2643 -- check for XML DB installed
2644 execute immediate l_checkXMLdb into l_comp_cnt;
2645 ELSE
2646 l_comp_cnt := 1;
2647 END IF;
2648 IF l_comp_cnt > 0 THEN
2649 -- wrap with exception block
2650 BEGIN
2651 UTL_FILE.PUT_LINE(g_config_handle,'IP_ADDRESS=' || UTL_INADDR.GET_HOST_ADDRESS);
2652 UTL_FILE.PUT_LINE(g_config_handle,'HOSTNAME=' || UTL_INADDR.GET_HOST_NAME);
2653 EXCEPTION
2654 WHEN OTHERS THEN NULL;
2655 END;
2656 END IF;
2657 END;
2658
2659 /**
2660 Write file footer
2661 */
2662 PROCEDURE write_file_footer
2663 IS
2664 BEGIN
2665 UTL_FILE.PUT_LINE(g_config_handle,'_CCR_EOF_');
2666 END;
2667
2668
2669 /*
2670 The implementation procedure which does the collection.
2671 */
2672 procedure collect_config_metrics_impl IS
2673 l_asm_instance_name VARCHAR2(4000) ;
2674 BEGIN
2675 write_file_header();
2676
2677 IF g_version_category = VERSION_10gR1 OR
2678 g_version_category = VERSION_10gR2 THEN
2679 execute immediate 'select * from (select instance_name ' ||
2680 ' from (select '' '' instance_name from dual ' ||
2681 ' union all ' ||
2682 ' select instance_name from v$asm_client) ' ||
2683 ' order by instance_name desc) ' ||
2684 ' where rownum = 1 ' into l_asm_instance_name;
2685 IF l_asm_instance_name != ' ' THEN
2686 UTL_FILE.PUT_LINE(g_config_handle,'OSMInstance=' || l_asm_instance_name);
2687 END IF;
2688 END IF;
2689
2690 collect_db_init_params;
2691 collect_db_asm_disk;
2692 collect_autotask_client;
2693 collect_db_components;
2694 collect_db_invobj_cnt;
2695 collect_db_scheduler_jobs;
2696 collect_db_sga;
2697 collect_db_tablespaces;
2698 collect_db_datafiles;
2699 collect_db_controlfiles;
2700 collect_db_redoLogs;
2701 collect_db_rollback_segs;
2702 collect_db_license;
2703 collect_db_options;
2704 collect_db_dbNInstanceInfo;
2705 collect_ha_info;
2706 collect_ha_rman_config;
2707 collect_statspack_config;
2708 -- collect_backup_config;
2709 collect_db_users;
2710
2711 -- cell metrics
2712 collect_cell_list;
2713 collect_cell_config;
2714 collect_griddisk_config;
2715 collect_celldisk_config;
2716 collect_lun_config;
2717 collect_physicaldisk_config;
2718 collect_iorm_config;
2719 collect_patches;
2720
2721 collect_db_scnInfo;
2722
2723 if g_is_cdb = 'YES' then
2724 collect_pdb_list;
2725 collect_cdb_services;
2726 collect_cdb_datafiles;
2727 collect_cdb_init_params;
2728 collect_cdb_pdb_over_params;
2729 collect_cdb_rollback_segs;
2730 collect_cdb_tablespaces;
2731 collect_cdb_users;
2732 collect_cdb_dbNInstanceInfo;
2733 end if;
2734
2735 write_file_footer();
2736
2737 UTL_FILE.FFLUSH(g_config_handle);
2738
2739 END collect_config_metrics_impl;
2740
2741 /*
2742 Puts the config data into the file
2743 By default, this procedure does not raise an exception.
2744 To raise an exception, pass "raise_exp" as TRUE.
2745 */
2746 procedure collect_config_metrics(directory_location IN VARCHAR2,
2747 raise_exp BOOLEAN DEFAULT FALSE) IS
2748 BEGIN
2749 BEGIN
2750 select dbid into g_dbID from v$database;
2751 select version into g_db_version from v$instance;
2752
2753 g_version_category := get_version_category();
2754 IF g_version_category != NOT_SUPPORTED_VERSION THEN
2755 BEGIN
2756 g_config_handle := UTL_FILE.FOPEN(directory_location,get_dest_file_name(),'W',32767);
2757 EXCEPTION
2758 WHEN UTL_FILE.INVALID_FILEHANDLE OR
2759 UTL_FILE.INVALID_PATH OR
2763 RETURN;
2760 UTL_FILE.INVALID_OPERATION OR
2761 UTL_FILE.WRITE_ERROR THEN
2762 -- Just bail out, we cannot open or write to the ll file(s)
2764 END;
2765
2766 execute immediate 'ALTER SESSION SET nls_numeric_characters=". "';
2767
2768 CHECK_IS_CDB;
2769
2770 MGMT_DB_LL_METRICS.collect_config_metrics_impl;
2771
2772 UTL_FILE.FCLOSE(g_config_handle);
2773 END IF;
2774 EXCEPTION
2775 WHEN OTHERS THEN
2776 IF UTL_FILE.IS_OPEN (g_config_handle) = TRUE THEN
2777 UTL_FILE.FCLOSE(g_config_handle);
2778 END IF;
2779 g_config_handle := null;
2780 g_version_category := NULL;
2781 -- Raise exception only if explicitly asked for, by passing TRUE
2782 -- for "raise_exp" parameter.
2783 IF (raise_exp) THEN
2784 RAISE;
2785 END IF;
2786 END;
2787 END collect_config_metrics;
2788
2789 /*
2790 Puts the statistics config data into the file
2791 By default, this procedure does not raise an exception.
2792 To raise an exception, pass "raise_exp" as TRUE.
2793 */
2794 procedure collect_stats_metrics(directory_location IN VARCHAR2,
2795 raise_exp BOOLEAN DEFAULT FALSE) IS
2796 BEGIN
2797 BEGIN
2798 select dbid into g_dbID from v$database;
2799 select version into g_db_version from v$instance;
2800
2801 g_version_category := get_version_category();
2802 IF g_version_category != NOT_SUPPORTED_VERSION THEN
2803 BEGIN
2804 g_config_handle := UTL_FILE.FOPEN(directory_location,get_dest_file_name('.ll-stat'),'W',32767);
2805 EXCEPTION
2806 WHEN UTL_FILE.INVALID_FILEHANDLE OR
2807 UTL_FILE.INVALID_PATH OR
2808 UTL_FILE.INVALID_OPERATION OR
2809 UTL_FILE.WRITE_ERROR THEN
2810 -- Just bail out, we cannot open or write to the ll file(s)
2811 RETURN;
2812 END;
2813
2814 execute immediate 'ALTER SESSION SET nls_numeric_characters=". "';
2815
2816 CHECK_IS_CDB;
2817
2818 write_file_header();
2819
2820 collect_db_feature_usage;
2821 collect_high_water_mark_stats;
2822 collect_db_cpu_usage;
2823 if g_is_cdb = 'YES' then
2824 collect_cdb_feature_usage;
2825 collect_cdb_hwm_stats;
2826 end if;
2827
2828 write_file_footer();
2829
2830 UTL_FILE.FFLUSH(g_config_handle);
2831 UTL_FILE.FCLOSE(g_config_handle);
2832
2833 END IF;
2834 EXCEPTION
2835 WHEN OTHERS THEN
2836 IF UTL_FILE.IS_OPEN (g_config_handle) = TRUE THEN
2837 UTL_FILE.FFLUSH(g_config_handle);
2838 UTL_FILE.FCLOSE(g_config_handle);
2839 END IF;
2840 g_config_handle := null;
2841 g_version_category := NULL;
2842 -- Raise exception only if explicitly asked for, by passing TRUE
2843 -- for "raise_exp" parameter.
2844 IF (raise_exp) THEN
2845 RAISE;
2846 END IF;
2847 END;
2848 END collect_stats_metrics;
2849
2850 procedure write_db_ccr_file_impl IS
2851 l_hostName v$instance.HOST_NAME%TYPE;
2852 l_dbUniqueName v$parameter.VALUE%TYPE;
2853 l_dbDomain v$parameter.VALUE%TYPE;
2854
2855 l_diagDest v$parameter.VALUE%TYPE;
2856 l_dbName v$parameter.VALUE%TYPE;
2857 l_dbInstanceName v$parameter.VALUE%TYPE;
2858
2859 BEGIN
2860 write_file_header();
2861
2862 select host_name into l_hostName from v$instance;
2863 select value into l_dbUniqueName from v$parameter where name='db_unique_name';
2864 select value into l_dbDomain from v$parameter where name='db_domain';
2865
2866 UTL_FILE.PUT_LINE(g_config_handle,'HOST_NAME=' || l_hostName);
2867 UTL_FILE.PUT_LINE(g_config_handle,'DB_UNIQUE_NAME=' || l_dbUniqueName);
2868 UTL_FILE.PUT_LINE(g_config_handle,'DB_DOMAIN=' || l_dbDomain);
2869
2870 -- diagnostic_dest is property introduced from 11g
2871 -- So, pre 11g db will not have this so prepare for that
2872 BEGIN
2873 select value into l_diagDest from v$parameter where lower(name)='diagnostic_dest';
2874 UTL_FILE.PUT_LINE(g_config_handle,'DIAG_DEST=' || l_diagDest);
2875 EXCEPTION
2876 WHEN NO_DATA_FOUND THEN
2877 null;
2878 END;
2879
2880 select value into l_dbName from v$parameter where name='db_name';
2881 select value into l_dbInstanceName from v$parameter where name='instance_name';
2882
2883 UTL_FILE.PUT_LINE(g_config_handle,'DB_NAME=' || l_dbName);
2884 UTL_FILE.PUT_LINE(g_config_handle,'INSTANCE_NAME=' || l_dbInstanceName);
2885
2886 collect_db_dbNInstanceInfo;
2887 if g_is_cdb = 'YES' then
2888 collect_pdb_list;
2889 end if;
2890 collect_cell_list;
2891
2892 write_file_footer();
2893
2894 END;
2895
2896 /*
2897
2898 By default, this procedure does not raise an exception.
2899 To raise an exception, pass "raise_exp" as TRUE.
2900 */
2901 procedure write_db_ccr_file(directory_location IN VARCHAR2,
2902 raise_exp BOOLEAN DEFAULT FALSE) IS
2903 BEGIN
2904 BEGIN
2905 select dbid into g_dbID from v$database;
2906 select version into g_db_version from v$instance;
2907
2908 g_version_category := get_version_category();
2909 IF g_version_category != NOT_SUPPORTED_VERSION THEN
2910 BEGIN
2911 g_config_handle := UTL_FILE.FOPEN(directory_location,get_dest_file_name('.ccr'),'W',32767);
2912 EXCEPTION
2913 WHEN UTL_FILE.INVALID_FILEHANDLE OR
2914 UTL_FILE.INVALID_PATH OR
2915 UTL_FILE.INVALID_OPERATION OR
2916 UTL_FILE.WRITE_ERROR THEN
2917 -- Just bail out, we cannot open or write to the ll file(s)
2918 RETURN;
2919 END;
2920
2921 execute immediate 'ALTER SESSION SET nls_numeric_characters=". "';
2922
2923 CHECK_IS_CDB;
2924
2925 MGMT_DB_LL_METRICS.write_db_ccr_file_impl;
2926 UTL_FILE.FFLUSH(g_config_handle);
2927 UTL_FILE.FCLOSE(g_config_handle);
2928 END IF;
2929 EXCEPTION
2930 WHEN OTHERS THEN
2931 IF UTL_FILE.IS_OPEN (g_config_handle) = TRUE THEN
2932 UTL_FILE.FFLUSH(g_config_handle);
2933 UTL_FILE.FCLOSE(g_config_handle);
2934 END IF;
2935 g_config_handle := null;
2936 g_version_category := NULL;
2937 -- Raise exception only if explicitly asked for, by passing TRUE
2938 -- for "raise_exp" parameter.
2939 IF (raise_exp) THEN
2940 RAISE;
2941 END IF;
2942 END;
2943 END write_db_ccr_file;
2944
2945 END MGMT_DB_LL_METRICS;