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

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

0
118

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 Database and ASM instances, number of ACIVE/INACTIVE connections, REDO log generation etc. plays an important role in identifying database current status.

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_Report1.html

TTITLE CENTER ========= ‘Report Dated’ ==========

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;
select sysdate from dual;

TTITLE CENTER ====’DB AND SERVER Details asBelow’====

set head on
set lines 200 pages 500
col name for a15
col DB_UNIQUE_NAME for a20
col HOST_NAME for a20

select i.inst_id, name, open_mode,i.status, database_role,protection_mode, db_unique_Name, instance_name, host_name, logins from gv$database d, gv$instance i
where i.inst_id=d.inst_id;

TTITLE CENTER =====’ DATA GUARD STATUS ‘====

col DESTINATION format a40
SELECT inst_id,dest_id,DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#,SYNCHRONIZATION_STATUS,SYNCHRONIZED, ERROR
FROM GV$ARCHIVE_DEST_STATUS
WHERE STATUS <> ‘DEFERRED’ AND STATUS <> ‘INACTIVE’
order by INST_ID,DEST_ID
/

TTITLE CENTER ====’ Redo Log Group SWITCH HISTORY, HOUR Wise ‘=====

SET linesize 300 pagesize 1000 feedback off echo off trimspool on
COL h0 format 9999
COL h1 format 9999
COL h2 format 9999
COL h3 format 9999
COL h4 format 9999
COL h5 format 9999
COL h6 format 9999
COL h7 format 9999
COL h8 format 9999
COL h9 format 9999
COL h10 format 9999
COL h11 format 9999
COL h12 format 9999
COL h13 format 9999
COL h14 format 9999
COL h15 format 9999
COL h16 format 9999
COL h17 format 9999
COL h18 format 9999
COL h19 format 9999
COL h20 format 9999
COL h21 format 9999
COL h22 format 9999
COL h23 format 9999

SELECT TRUNC(first_time) “Date”, TO_CHAR(first_time, ‘Dy’) “Day”,
COUNT(1) “Total”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’00’, 1, 0)) “h0”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’01’, 1, 0)) “h1”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’02’, 1, 0)) “h2”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’03’, 1, 0)) “h3”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’04’, 1, 0)) “h4”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’05’, 1, 0)) “h5”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’06’, 1, 0)) “h6”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’07’, 1, 0)) “h7”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’08’, 1, 0)) “h8”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’09’, 1, 0)) “h9”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’10’, 1, 0)) “h10”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’11’, 1, 0)) “h11”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’12’, 1, 0)) “h12”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’13’, 1, 0)) “h13”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’14’, 1, 0)) “h14”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’15’, 1, 0)) “h15”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’16’, 1, 0)) “h16”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’17’, 1, 0)) “h17”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’18’, 1, 0)) “h18”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’19’, 1, 0)) “h19”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’20’, 1, 0)) “h20”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’21’, 1, 0)) “h21”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’22’, 1, 0)) “h22”,
SUM(DECODE(TO_CHAR(first_time, ‘hh24′), ’23’, 1, 0)) “h23”
FROM v$log_history where TRUNC(first_time)>sysdate-15
GROUP BY TRUNC(first_time), TO_CHAR(first_time, ‘Dy’)
ORDER BY 1;

TTITLE CENTER =====’ Daily Archive generation with Size ‘=====

SELECT TRUNC(first_time) “Date”,thread#,COUNT() “Redo Generated”, count()*(select bytes/1024/1024/1024 from v$log where status=’CURRENT’ and thread#=1) “Size in GB”
FROM v$log_history where TRUNC(first_time)>sysdate-30
GROUP BY TRUNC(first_time),thread#
ORDER BY 1,2;


SET ECHO ON
SET MARKUP HTML ON OFF
SPOOL OFF

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

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