database_health_check.sql
-- ------------------------------------------------------------------------------------- File Name : database_health_check.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database health check and performance monitoring script-- Purpose : Proactive database monitoring and health assessment for maintenance and troubleshooting-- Call Syntax : @F:\DBA\Scripts\database_health_check.sql (check_level)-- Parameters : check_level - Level of detail for health check (BASIC, STANDARD, DETAILED)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENSET VERIFY OFFSET TIMING ONCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_health_check_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_DETAILED.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ONTIMING START health_check
PROMPT Starting Database Health Check Script...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Timestamp: &DATE1 &TIME1PROMPT Check Level: &1PROMPT
-- Validate check level parameterDECLARE v_check_level VARCHAR2(20) := UPPER('&1');BEGIN IF v_check_level NOT IN ('BASIC', 'STANDARD', 'DETAILED') THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid check level. Use: BASIC, STANDARD, or DETAILED'); END IF; DBMS_OUTPUT.PUT_LINE('Health check level: ' || v_check_level);END;/
-- ------------------------------------------------------------------------------------- DATABASE VERSION AND BASIC INFO: Display database version and configuration-- -----------------------------------------------------------------------------------
SPOOL &output_filename
PROMPT PROMPT 1. DATABASE VERSION AND BASIC INFORMATIONPROMPT ==========================================
COLUMN name FORMAT A20COLUMN value FORMAT A50COLUMN description FORMAT A70
PROMPT Database Version and Instance Info:PROMPT
SELECT name, value, descriptionFROM v$parameterWHERE name IN ('db_name', 'db_unique_name', 'db_domain', 'compatible')UNION ALLSELECT 'Instance Status' as name, status as value, 'Current instance status' as descriptionFROM v$instanceUNION ALLSELECT 'Database Version' as name, version as value, 'Oracle database version' as descriptionFROM v$instance;
-- ------------------------------------------------------------------------------------- TABLESPACE USAGE: Check tablespace utilization and free space-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TABLESPACE USAGE AND STORAGEPROMPT ================================
COLUMN tablespace_name FORMAT A35COLUMN total_mb FORMAT 9,999,999,999COLUMN used_mb FORMAT 9,999,999,999COLUMN free_mb FORMAT 9,999,999COLUMN pct_used FORMAT 999.99COLUMN status FORMAT A10
SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / 1024 / 1024) AS used_mb, ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024) AS free_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used, dt.statusFROM dba_data_files dfLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idJOIN dba_tablespaces dt ON df.tablespace_name = dt.tablespace_nameGROUP BY df.tablespace_name, dt.statusHAVING ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) > 80 OR ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024) < 100ORDER BY pct_used DESC;
-- ------------------------------------------------------------------------------------- PERFORMANCE METRICS: Check key performance indicators-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. PERFORMANCE METRICSPROMPT =======================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20
PROMPT Key Performance Metrics:PROMPT
SELECT metric_name, value, metric_unitFROM v$sysmetricWHERE metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'User Commits Per Sec', 'User Transactions Per Sec', 'Physical Reads Per Sec', 'Physical Writes Per Sec')AND group_id = 2ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- SESSION AND PROCESS INFO: Monitor current sessions and processes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. ACTIVE SESSIONS AND PROCESSESPROMPT =================================
COLUMN username FORMAT A20COLUMN program FORMAT A35COLUMN machine FORMAT A25COLUMN status FORMAT A10COLUMN count_sessions FORMAT 999
PROMPT Active Sessions by User:PROMPT
SELECT NVL(username, 'BACKGROUND') as username, status, COUNT(*) as count_sessionsFROM v$sessionGROUP BY username, statusORDER BY count_sessions DESC;
-- ------------------------------------------------------------------------------------- WAIT EVENTS: Analyze current wait events (DETAILED level only)-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. TOP WAIT EVENTSPROMPT =================================
COLUMN event FORMAT A40COLUMN total_waits FORMAT 999,999,999COLUMN time_waited FORMAT 999,999,999 SELECT event, total_waits, time_waitedFROM v$system_eventWHERE wait_class != 'Idle'ORDER BY time_waited DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY USAGE: Check memory allocation and usage (DETAILED level only)-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. MEMORY USAGE DETAILSPROMPT =================================
COLUMN component FORMAT A30COLUMN current_size_mb FORMAT 999,999COLUMN min_size_mb FORMAT 999,999COLUMN max_size_mb FORMAT 999,999 SELECT component, ROUND(current_size/1024/1024) as current_size_mb, ROUND(min_size/1024/1024) as min_size_mb, ROUND(max_size/1024/1024) as max_size_mbFROM v$memory_dynamic_componentsWHERE current_size > 0ORDER BY current_size DESC;
-- ------------------------------------------------------------------------------------- ALERTS AND ISSUES: Check for critical alerts and issues-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. CRITICAL ALERTS AND ISSUES SUMMARYPROMPT ======================================
COLUMN message_type FORMAT A30COLUMN message_level FORMAT A10COLUMN message_text FORMAT A100
SELECT 'Tablespace Alert' as message_type, CASE WHEN combined_percentage > 95 THEN 'CRITICAL' WHEN combined_percentage > 85 THEN 'HIGH' WHEN combined_percentage > 75 THEN 'MEDIUM' ELSE 'LOW' END as message_level, 'Tablespace ' || tablespace_name || ': ' || ROUND(combined_percentage, 1) || '% capacity reached' || ' (' || ROUND(pct_used_current, 1) || '% of current, ' || ROUND(pct_used_max, 1) || '% of max)' as message_textFROM ( SELECT df.tablespace_name, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used_current, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) * 100, 2) AS pct_used_max, CASE WHEN MAX(df.autoextensible) = 'YES' THEN -- For autoextend tablespaces, use the percentage of max space ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) * 100, 2) ELSE -- For fixed tablespaces, use current percentage ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) END AS combined_percentage FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_id GROUP BY df.tablespace_name)WHERE combined_percentage > 70
UNION ALL
SELECT 'Session Alert' as message_type, 'MEDIUM' as message_level, 'Active sessions: ' || COUNT(*) as message_textFROM v$sessionWHERE status = 'ACTIVE'HAVING COUNT(*) > 50;-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and display timing-- -----------------------------------------------------------------------------------
TIMING STOPCLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE HEALTH CHECK COMPLETEDPROMPT ========================================PROMPT Check Level: &1PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Completion Time: &DATE1 &TIME1
PROMPT PROMPT Note: Review any alerts above and investigate HIGH/CRITICAL priority issuesPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENSET VERIFY OFFSET TIMING ONCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_health_check_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_DETAILED.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ONTIMING START health_check
PROMPT Starting Database Health Check Script...PROMPT ========================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Timestamp: &DATE1 &TIME1PROMPT Check Level: &1PROMPT
-- Validate check level parameterDECLARE v_check_level VARCHAR2(20) := UPPER('&1');BEGIN IF v_check_level NOT IN ('BASIC', 'STANDARD', 'DETAILED') THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid check level. Use: BASIC, STANDARD, or DETAILED'); END IF; DBMS_OUTPUT.PUT_LINE('Health check level: ' || v_check_level);END;/
-- ------------------------------------------------------------------------------------- DATABASE VERSION AND BASIC INFO: Display database version and configuration-- -----------------------------------------------------------------------------------
SPOOL &output_filename
PROMPT PROMPT 1. DATABASE VERSION AND BASIC INFORMATIONPROMPT ==========================================
COLUMN name FORMAT A20COLUMN value FORMAT A50COLUMN description FORMAT A70
PROMPT Database Version and Instance Info:PROMPT
SELECT name, value, descriptionFROM v$parameterWHERE name IN ('db_name', 'db_unique_name', 'db_domain', 'compatible')UNION ALLSELECT 'Instance Status' as name, status as value, 'Current instance status' as descriptionFROM v$instanceUNION ALLSELECT 'Database Version' as name, version as value, 'Oracle database version' as descriptionFROM v$instance;
-- ------------------------------------------------------------------------------------- TABLESPACE USAGE: Check tablespace utilization and free space-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TABLESPACE USAGE AND STORAGEPROMPT ================================
COLUMN tablespace_name FORMAT A35COLUMN total_mb FORMAT 9,999,999,999COLUMN used_mb FORMAT 9,999,999,999COLUMN free_mb FORMAT 9,999,999COLUMN pct_used FORMAT 999.99COLUMN status FORMAT A10
SELECT df.tablespace_name, ROUND(SUM(df.bytes) / 1024 / 1024) AS total_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / 1024 / 1024) AS used_mb, ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024) AS free_mb, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used, dt.statusFROM dba_data_files dfLEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_idJOIN dba_tablespaces dt ON df.tablespace_name = dt.tablespace_nameGROUP BY df.tablespace_name, dt.statusHAVING ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) > 80 OR ROUND(NVL(SUM(fs.bytes), 0) / 1024 / 1024) < 100ORDER BY pct_used DESC;
-- ------------------------------------------------------------------------------------- PERFORMANCE METRICS: Check key performance indicators-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. PERFORMANCE METRICSPROMPT =======================
COLUMN metric_name FORMAT A40COLUMN value FORMAT 999,999,999.99COLUMN metric_unit FORMAT A20
PROMPT Key Performance Metrics:PROMPT
SELECT metric_name, value, metric_unitFROM v$sysmetricWHERE metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'User Commits Per Sec', 'User Transactions Per Sec', 'Physical Reads Per Sec', 'Physical Writes Per Sec')AND group_id = 2ORDER BY metric_name;
-- ------------------------------------------------------------------------------------- SESSION AND PROCESS INFO: Monitor current sessions and processes-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. ACTIVE SESSIONS AND PROCESSESPROMPT =================================
COLUMN username FORMAT A20COLUMN program FORMAT A35COLUMN machine FORMAT A25COLUMN status FORMAT A10COLUMN count_sessions FORMAT 999
PROMPT Active Sessions by User:PROMPT
SELECT NVL(username, 'BACKGROUND') as username, status, COUNT(*) as count_sessionsFROM v$sessionGROUP BY username, statusORDER BY count_sessions DESC;
-- ------------------------------------------------------------------------------------- WAIT EVENTS: Analyze current wait events (DETAILED level only)-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. TOP WAIT EVENTSPROMPT =================================
COLUMN event FORMAT A40COLUMN total_waits FORMAT 999,999,999COLUMN time_waited FORMAT 999,999,999 SELECT event, total_waits, time_waitedFROM v$system_eventWHERE wait_class != 'Idle'ORDER BY time_waited DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY USAGE: Check memory allocation and usage (DETAILED level only)-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. MEMORY USAGE DETAILSPROMPT =================================
COLUMN component FORMAT A30COLUMN current_size_mb FORMAT 999,999COLUMN min_size_mb FORMAT 999,999COLUMN max_size_mb FORMAT 999,999 SELECT component, ROUND(current_size/1024/1024) as current_size_mb, ROUND(min_size/1024/1024) as min_size_mb, ROUND(max_size/1024/1024) as max_size_mbFROM v$memory_dynamic_componentsWHERE current_size > 0ORDER BY current_size DESC;
-- ------------------------------------------------------------------------------------- ALERTS AND ISSUES: Check for critical alerts and issues-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. CRITICAL ALERTS AND ISSUES SUMMARYPROMPT ======================================
COLUMN message_type FORMAT A30COLUMN message_level FORMAT A10COLUMN message_text FORMAT A100
SELECT 'Tablespace Alert' as message_type, CASE WHEN combined_percentage > 95 THEN 'CRITICAL' WHEN combined_percentage > 85 THEN 'HIGH' WHEN combined_percentage > 75 THEN 'MEDIUM' ELSE 'LOW' END as message_level, 'Tablespace ' || tablespace_name || ': ' || ROUND(combined_percentage, 1) || '% capacity reached' || ' (' || ROUND(pct_used_current, 1) || '% of current, ' || ROUND(pct_used_max, 1) || '% of max)' as message_textFROM ( SELECT df.tablespace_name, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) AS pct_used_current, ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) * 100, 2) AS pct_used_max, CASE WHEN MAX(df.autoextensible) = 'YES' THEN -- For autoextend tablespaces, use the percentage of max space ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(CASE WHEN df.autoextensible = 'YES' THEN df.maxbytes ELSE df.bytes END) * 100, 2) ELSE -- For fixed tablespaces, use current percentage ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes) * 100, 2) END AS combined_percentage FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name AND df.file_id = fs.file_id GROUP BY df.tablespace_name)WHERE combined_percentage > 70
UNION ALL
SELECT 'Session Alert' as message_type, 'MEDIUM' as message_level, 'Active sessions: ' || COUNT(*) as message_textFROM v$sessionWHERE status = 'ACTIVE'HAVING COUNT(*) > 50;-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and display timing-- -----------------------------------------------------------------------------------
TIMING STOPCLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE HEALTH CHECK COMPLETEDPROMPT ========================================PROMPT Check Level: &1PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Completion Time: &DATE1 &TIME1
PROMPT PROMPT Note: Review any alerts above and investigate HIGH/CRITICAL priority issuesPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------