More

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

    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

    Recent Articles

    spot_img

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox