Home Oracle DB Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part2

Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part2

0
172

Database monitoring is an integral part of Database Administrator’s everyday work. We all execute many queries to check health status of the database instances. Status check of ASM Disk Group, ASM Disk Group IO distribution, Database total size , Schema Wise size, Tablespace Size details etc. plays an important role to find out everyday database growth. All queries are written for RAC environment. You may change “gv$” to just “v$” for executing these queries in standalone databases.

Please refer to Part1 of daily reports for more queries in database health check.

Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part1

Following format and queries can be used to get reports in HTML format for the above mentioned topics.

SET HEAD OFF
SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
Spool Daily_Report2.html

TTITLE CENTER ======’ ASM Disk Group Information ‘ =============

SET LINESIZE 200
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD ‘Disk Group|Name’
COLUMN sector_size FORMAT 99,999 HEAD ‘Sector|Size’
COLUMN block_size FORMAT 99,999 HEAD ‘Block|Size’
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD ‘Allocation|Unit Size’
COLUMN state FORMAT a11 HEAD ‘State’
COLUMN type FORMAT a6 HEAD ‘Type’
COLUMN total_mb FORMAT 999,999,999 HEAD ‘Total Size (MB)’
COLUMN used_mb FORMAT 999,999,999 HEAD ‘Used Size (MB)’
COLUMN pct_used FORMAT 999.99 HEAD ‘Pct. Used’

SELECT group_number,
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb – free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/

TTITLE CENTER =======’ ASM Disk Group IO Balancing Information ‘ =======

SELECT dg.group_number “GROUP#”,dg.name,DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1-df.sum_io / total_dg.total_io), -1, 0,
(1-df.sum_io / total_dg.total_io)))) “IO_BALANCED”
FROM
(SELECT d.group_number group_number,SUM (ABS ((d.reads + d.writes) – tot.avg_io)) sum_io
FROM v$asm_disk_stat d,
(SELECT group_number,SUM (reads) + SUM (writes),DECODE (COUNT (), 0, 0, (SUM (reads) + SUM (writes)) / COUNT ()) avg_io
FROM v$asm_disk_stat WHERE header_status = ‘MEMBER’ GROUP BY group_number) tot
WHERE header_status = ‘MEMBER’ AND tot.group_number = d.group_number
GROUP BY d.group_number) df,
(SELECT group_number,
SUM (reads) + SUM (writes) total_io
FROM v$asm_disk_stat
WHERE header_status = ‘MEMBER’
GROUP BY group_number) total_dg,
V$ASM_DISKGROUP dg
WHERE df.group_number = total_dg.group_number
AND df.group_number = dg.group_number;

TTITLE CENTER ===’Schema Wise Used Size excluding Oracle Default schemas ‘===

select owner,sum(bytes/1024/1024/1024) “Used Size in GB” from dba_segments where owner not like ‘%SYS%’ group by owner;

set feed on

TTITLE CENTER ==== ‘DB SIZE ‘ =======

select sum(bytes)/1024/1024/1024 size_in_GB from dba_segments;

set feed on

TTITLE CENTER ====’ LIST OF TABLESPACES AND SIZES Including TEMP’ =====

set echo off
column timecol new_value timestamp
column dbname new_value db_nm
col TABLESPACE_NAME for a25

set linesize 200 pages 500
break on report
compute sum of “Size (M)” on report
compute sum of “Used (M)” on report
column name format a30
column “Size (M)” format 999,999,999.99
column “Used (M)” format 999,999,999.99
column “Used %” format a8

SELECT d.status “Status”, d.tablespace_name “Name”, d.contents “Type”, d.extent_management “Extent Management”,
NVL(a.bytes / 1024 / 1024, 0) “Size (M)”,
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024) “Used (M)”,
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) “Used %”
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’) UNION ALL SELECT d.status “Status”, d.tablespace_name “Name”,
d.contents “Type”, d.extent_management “Extent Management”,
NVL(a.bytes / 1024 / 1024, 0) “Size (M)”, NVL(t.bytes, 0)/1024/1024 “Used (M)”,
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “Used %”
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files
group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from
gv$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND d.extent_management like ‘LOCAL’ AND d.contents like
‘TEMPORARY’
order by 7 desc
/

SET MARKUP HTML OFF
SET ECHO ON
SPOOL OFF