-- =========================================================
-- Database Health Check Report (HTML)
-- Author  : IFSTechServices.com 
-- Purpose : Generate HTML health check report similar to DB Info
-- Note    : Uses only non-AWR/ASH views (no Diagnostic Pack needed)
-- Connect using sys USER
-- Test on Lower Environment before on production
-- For Any support. Please mail to ifs.services@IFSTechServices.com
-- =========================================================

-- ===== Thresholds (tune these as per your standards) =====
DEFINE TBS_WARN_PCT      = 15        -- Tablespace free % warning threshold
DEFINE FRA_WARN_PCT      = 80        -- FRA usage warn (%)
DEFINE FRA_FAIL_PCT      = 90        -- FRA usage fail (%)
DEFINE UPTIME_WARN_HOURS = 24        -- Warn if instance up time < hours
DEFINE RMAN_WARN_HOURS   = 24        -- Warn if last successful RMAN > hours

SET LINESIZE 2000
SET LONG 200000
SET LONGCHUNKSIZE 200000
SET TRIMSPOOL ON
SET WRAP OFF

SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET PAGESIZE 50000

--SET LINESIZE 220
SET TRIMSPOOL ON

-- HTML ON
SET MARKUP HTML ON SPOOL ON TABLE ON PREFORMAT OFF ENTMAP OFF
SPOOL Database_Health_Check_Report.html

-- ===== Page Header / Style =====
set head off
SELECT
    '<h1 style="background:#4b0082;color:white;padding:10px;width:max-content;">'
    || '<b>Oracle Database Health Check</b></h1>'
    || '<div style="margin:6px 0 16px 0;"><b>Generated On:</b> '
    || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
    || '</div>'
    || '<style>
         .ok{color:green;font-weight:bold;}
         .warn{color:#ff8c00;font-weight:bold;}
         .fail{color:red;font-weight:bold;}
         .section{color:#4b0082;padding:6px 8px;margin:12px 0 6px 0;border-left:6px solid #4b0082;background:#f5f0fa;}
         table{border-collapse:collapse;}
         th,td{padding:6px 10px;}
       </style>'
FROM dual;
set head on

-- Use a consistent table style
SET MARKUP HTML ON TABLE "border='1' cellpadding='5' cellspacing='0'"

PROMPT <div class="section"><h2><b>Environment</b></h2></div>

SELECT banner_full AS "Database Edition / Version" FROM v$version
WHERE banner_full LIKE 'Oracle Database%';

SELECT
  d.dbid,
  d.name              AS "Database Name",
  d.platform_name     AS "Platform",
  d.log_mode          AS "Archive Mode",
  CASE d.cdb WHEN 'YES' THEN 'CONTAINER DATABASE' ELSE 'NON-CDB' END AS "Container"
FROM v$database d;

SELECT
  host_name        AS "Host",
  instance_name    AS "Instance",
  version          AS "RDBMS Version",
  status           AS "Instance Status",
  to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') AS "Startup Time",
  ROUND((SYSDATE - startup_time)*24,2)           AS "Hours Up"
FROM v$instance;

PROMPT <div class="section"><h2><b>Health Summary (Pass/Warn/Fail)</b></h2></div>

-- Robust version without WITH/CTE to avoid SQL*Plus parsing issues
SELECT 'Instance Uptime' AS "Check",
       CASE WHEN hours_up < &UPTIME_WARN_HOURS
            THEN '<span class="warn">WARN</span>'
            ELSE '<span class="ok">OK</span>' END AS "Status",
       TO_CHAR(ROUND(hours_up,2)) || ' hours' AS "Details"
FROM (
  SELECT (SYSDATE - startup_time)*24 AS hours_up
  FROM v$instance
)
UNION ALL
SELECT 'FRA Usage',
       CASE
         WHEN fra_pct >= &FRA_FAIL_PCT THEN '<span class="fail">FAIL</span>'
         WHEN fra_pct >= &FRA_WARN_PCT THEN '<span class="warn">WARN</span>'
         ELSE '<span class="ok">OK</span>'
       END,
       TO_CHAR(NVL(fra_pct,0)) || '%' AS details
FROM (
  SELECT CASE WHEN space_limit > 0
              THEN ROUND(space_used*100/space_limit,1)
              ELSE 0 END AS fra_pct
  FROM v$recovery_file_dest
)
UNION ALL
SELECT 'Low Free Tablespaces (< '||TO_CHAR(&TBS_WARN_PCT)||'%)',
       CASE WHEN cnt > 0 THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       TO_CHAR(cnt) || ' tablespace(s)'
FROM (
  SELECT COUNT(*) AS cnt
  FROM (
    SELECT df.tablespace_name,
           ROUND(NVL(fs.bytes,0) * 100 / df.bytes, 1) AS pct_free
    FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df
    LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) fs
      ON fs.tablespace_name = df.tablespace_name
    WHERE df.tablespace_name NOT IN (
      SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY'
    )
  )
  WHERE pct_free < &TBS_WARN_PCT
)
UNION ALL
SELECT 'Datafiles Autoextend OFF',
       CASE WHEN cnt > 0 THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       TO_CHAR(cnt) || ' file(s)'
FROM (SELECT COUNT(*) AS cnt FROM dba_data_files WHERE autoextensible = 'NO')
UNION ALL
SELECT 'Invalid Objects (non-SYS)',
       CASE WHEN cnt > 0 THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       TO_CHAR(cnt) || ' object(s)'
FROM (SELECT COUNT(*) AS cnt FROM dba_objects WHERE status = 'INVALID' AND owner NOT IN ('SYS','SYSTEM'))
UNION ALL
SELECT 'Scheduler Jobs in BROKEN/FAILED state',
       CASE WHEN cnt > 0 THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       TO_CHAR(cnt) || ' job(s)'
FROM (SELECT COUNT(*) AS cnt FROM dba_scheduler_jobs WHERE state IN ('BROKEN','FAILED'))
UNION ALL
SELECT 'Legacy DBMS_JOB (BROKEN=Y)',
       CASE WHEN cnt > 0 THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       TO_CHAR(cnt) || ' job(s)'
FROM (SELECT COUNT(*) AS cnt FROM dba_jobs WHERE broken = 'Y')
UNION ALL
SELECT 'Last RMAN Success',
       CASE WHEN NVL(hours_since_last_ok, 99999) > &RMAN_WARN_HOURS
            THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       NVL(last_ok_txt, 'No successful backup found') AS details
FROM (
  SELECT MAX(end_time) AS last_ok,
         TO_CHAR(MAX(end_time),'DD-MON-YYYY HH24:MI:SS') || ' (' ||
         ROUND( (SYSDATE - MAX(end_time))*24, 2) || 'h ago)' AS last_ok_txt,
         ROUND( (SYSDATE - MAX(end_time))*24, 2) AS hours_since_last_ok
  FROM v$rman_backup_job_details
  WHERE status IN ('COMPLETED','COMPLETED WITH WARNINGS')
)
UNION ALL
SELECT 'Blocking Sessions Present',
       CASE WHEN cnt > 0 THEN '<span class="warn">WARN</span>' ELSE '<span class="ok">OK</span>' END,
       TO_CHAR(cnt) || ' blocked session(s)'
FROM (SELECT COUNT(*) AS cnt FROM gv$session WHERE blocking_session IS NOT NULL)
ORDER BY 1;

PROMPT <div class="section"><h2><b>PDB Open Modes (if CDB)</b></h2></div>
SELECT name AS "PDB Name", open_mode AS "Open Mode" FROM v$pdbs ORDER BY name;



PROMPT <div class="section"><h2><b>Tablespaces Capacity And Pressure</b></h2></div>
SELECT /* + RULE */
       df.tablespace_name AS "Tablespace",
       ROUND(df.bytes/1024/1024) AS "Size (MB)",
       ROUND(NVL(fs.free,0)/1024/1024) AS "Free (MB)",
       NVL(ROUND((NVL(fs.free,0)/df.bytes)*100,1),0) AS "% Free",
       ROUND((1 - NVL(fs.free,0)/df.bytes)*100,1) AS "% Used"
FROM ( SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name ) df
LEFT JOIN ( SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name ) fs
  ON df.tablespace_name = fs.tablespace_name
UNION ALL
SELECT /* + RULE */
       df.tablespace_name,
       ROUND(df.bytes/1024/1024),
       ROUND(df.bytes_free/1024/1024),
       NVL(ROUND(df.bytes_free*100/df.bytes,1),0),
       ROUND((df.bytes - df.bytes_free)*100/df.bytes,1)
FROM (
  SELECT tsh.tablespace_name,
         SUM(tsh.bytes_used + tsh.bytes_free) AS bytes,
         SUM(tsh.bytes_free) AS bytes_free
  FROM v$temp_space_header tsh
  GROUP BY tsh.tablespace_name
) df
ORDER BY 1;

PROMPT <h3><b>Tablespaces Below &TBS_WARN_PCT.% Free</b></h3>
SELECT * FROM (
  SELECT df.tablespace_name AS "Tablespace",
         ROUND((NVL(fs.free,0)/df.bytes)*100,1) AS "% Free",
         ROUND(df.bytes/1024/1024) AS "Size (MB)",
         ROUND(NVL(fs.free,0)/1024/1024) AS "Free (MB)"
  FROM ( SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name ) df
  LEFT JOIN ( SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name ) fs
    ON df.tablespace_name = fs.tablespace_name
) WHERE "% Free" < &TBS_WARN_PCT
ORDER BY 2;

PROMPT <h3><b>Datafiles (Autoextend)</b></h3>
SELECT tablespace_name AS "Tablespace",
       SUBSTR(file_name,1,80) AS "File Name",
       bytes/1024/1024 AS "Size (MB)",
       autoextensible AS "Autoextend",
       increment_by AS "Next Extent (blocks)",
       maxbytes/1024/1024 AS "Max Size (MB)"
FROM dba_data_files
ORDER BY 1,2;

PROMPT <div class="section"><h2><b>FRA / Archivelogs / Backup</b></h2></div>
SELECT name AS "FRA Name",
       space_limit/1024/1024/1024 AS "Limit (GB)",
       space_used/1024/1024/1024  AS "Used (GB)",
       ROUND(CASE WHEN space_limit>0 THEN (space_used/space_limit)*100 ELSE 0 END,1) AS "Used %"
FROM v$recovery_file_dest;

SELECT log_mode AS "Archive Log Mode" FROM v$database;

SELECT TO_CHAR(first_time,'DD-MON-YYYY HH24:MI:SS') AS "Last Archived Log Time"
FROM (SELECT first_time FROM v$log_history ORDER BY first_time DESC)
WHERE ROWNUM = 1;

SELECT sequence#, applied, completion_time
FROM v$archived_log
WHERE ROWNUM <= 10
ORDER BY sequence# DESC;

PROMPT <h3><b>RMAN Backup Jobs (Last 7 Days)</b></h3>
SELECT TO_CHAR(start_time,'DD-MON HH24:MI') AS "Start",
       TO_CHAR(end_time,'DD-MON HH24:MI')   AS "End",
       input_type,
       status,
       ROUND(elapsed_seconds/60,1) AS "Elapsed (min)"
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;

PROMPT <div class="section"><h2><b>Sessions / Blocking / Long Operations</b></h2></div>
SELECT username, status, COUNT(*) AS "Session Count"
FROM v$session
WHERE username IS NOT NULL
GROUP BY username, status
ORDER BY 1,2;

PROMPT <h3><b>Blocking Session Details</b></h3>
SELECT s.sid, s.serial#, s.username, s.status, s.event,
       s.machine, s.program, s.sql_id,
       s.blocking_session AS "Blocking SID"
FROM gv$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.sid;

PROMPT <h3><b>Long Operations In Progress</b></h3>
SELECT sid, serial#, opname, SOFAR, TOTALWORK,
       ROUND( (sofar/NULLIF(totalwork,0))*100,1 ) AS "Pct Done",
       TIME_REMAINING AS "Sec Remaining"
FROM v$session_longops
WHERE TIME_REMAINING > 0
ORDER BY TIME_REMAINING DESC;

PROMPT <div class="section"><h2><b>Wait Events And Wait Classes</b></h2></div>
SELECT * FROM (
  SELECT event, total_waits, time_waited/100 AS "Time Waited (s)"
  FROM v$system_event
  WHERE wait_class <> 'Idle'
  ORDER BY time_waited DESC
) WHERE ROWNUM <= 10;

SELECT wait_class AS "Wait Class",
       total_waits,
       time_waited/100 AS "Time Waited (s)"
FROM v$system_wait_class
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC;

PROMPT <div class="section"><h2><b>Memory (SGA/PGA)</b></h2></div>
SELECT name, ROUND(value/1024/1024,2) AS "Size (MB)" FROM v$sga ORDER BY name;

SELECT name, ROUND(value/1024/1024,2) AS "Size (MB)"
FROM v$pgastat
WHERE name IN ('total PGA inuse','total PGA allocated');

PROMPT <div class="section"><h2><b>Temp And Undo Usage</b></h2></div>
SELECT tablespace_name AS "Temp TS", SUM(bytes_used/1024/1024) AS "Used (MB)", SUM(bytes_free/1024/1024) AS "Free (MB)"
FROM V$temp_space_header
GROUP BY tablespace_name;

SELECT
    b.tablespace AS "Temp TS",
    ROUND(b.blocks * p.value / 1024 / 1024, 2) AS "Temp Used (MB)",
    a.sid || ',' || a.serial# AS "SID,Serial",
    a.username,
    a.program
FROM v$sort_usage b
JOIN v$session a ON a.saddr = b.session_addr
JOIN v$parameter p ON p.name = 'db_block_size'
ORDER BY 2 DESC;

SELECT
  d.tablespace_name AS "UNDO TS",
  ROUND(SUM(CASE WHEN status='ACTIVE' THEN bytes ELSE 0 END)/1024/1024,1) AS "Active (MB)",
  ROUND(SUM(CASE WHEN status='UNEXPIRED' THEN bytes ELSE 0 END)/1024/1024,1) AS "Unexpired (MB)",
  ROUND(SUM(CASE WHEN status='EXPIRED' THEN bytes ELSE 0 END)/1024/1024,1) AS "Expired (MB)"
FROM dba_undo_extents d
GROUP BY d.tablespace_name;

PROMPT <div class="section"><h2><b>Objects, Stats And Jobs</b></h2></div>
SELECT owner, object_type, COUNT(*) AS "Invalid Count"
FROM dba_objects
WHERE status='INVALID' AND owner NOT IN ('SYS','SYSTEM')
GROUP BY owner, object_type
ORDER BY 3 DESC;

-- Stale stats (12c+; if column exists)
-- If this errors on older versions, comment out
BEGIN
  NULL;
END;
/

SELECT owner, table_name, num_rows, last_analyzed
FROM dba_tab_statistics
WHERE stale_stats = 'YES'
  AND owner NOT IN ('SYS','SYSTEM')
  AND ROWNUM <= 100
ORDER BY owner, table_name;




PROMPT <div class="section"><h2><b>Security And Accounts</b></h2></div>
SELECT username, account_status, lock_date, expiry_date, profile
FROM dba_users
WHERE account_status <> 'OPEN'
ORDER BY username;

PROMPT <div class="section"><h2><b>Parameters Changed From Defaults</b></h2></div>
SELECT name, value, issys_modifiable, isdefault
FROM v$parameter
WHERE isdefault = 'FALSE'
ORDER BY name;

PROMPT <div class="section"><h2><b>I/O Hot Datafiles And Redo Switches</b></h2></div>
SELECT * FROM (
  SELECT b.name AS "Datafile", a.phyrds, a.phywrts, a.readtim, a.writetim
  FROM v$filestat a JOIN v$datafile b ON a.file# = b.file#
  ORDER BY a.readtim + a.writetim DESC
) WHERE ROWNUM <= 10;

SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') AS "Hour",
       COUNT(*) AS "Log Switches"
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')
ORDER BY 1 DESC;

PROMPT <div class="section"><h2><b>Data Guard Destinations (if configured)</b></h2></div>

-- Primary, version-agnostic view: use TYPE (not TARGET) from V$ARCHIVE_DEST_STATUS
SELECT
  s.dest_id,
  s.type,                  -- LOCAL / PHYSICAL / LOGICAL
  s.status,                -- VALID / INACTIVE / ERROR
  s.error,
  s.destination,
  s.archived_thread#,
  s.archived_seq#
FROM v$archive_dest_status s
WHERE s.status IS NOT NULL
ORDER BY s.dest_id;
PROMPT <div class="footer">Report generated by ifstechservices.com</div>

SPOOL OFF
SET MARKUP HTML OFF
SET TERMOUT ON