Database Health Check Script

-- DBA Health Check Script for Oracle Database

-- Step 1: Check Database Instance and Version

-- Check Oracle Database version and instance status

SELECT * FROM v$version;

SELECT instance_name, status FROM v$instance;

-- Step 2: Check Database Status

-- Ensure that the database is open and running

SELECT status FROM v$database;

-- Step 3: Check Tablespace Usage

-- Check for space usage and growth of tablespaces

SELECT tablespace_name, 

       total_blocks * 8 / 1024 / 1024 AS "Total_MB",

       free_blocks * 8 / 1024 / 1024 AS "Free_MB",

       (total_blocks - free_blocks) * 8 / 1024 / 1024 AS "Used_MB",

       ROUND(((total_blocks - free_blocks) * 100) / total_blocks, 2) AS "Usage(%)"

FROM v$temp_space_header;

SELECT tablespace_name, 

       ROUND(SUM(bytes) / 1024 / 1024, 2) AS "Used_MB",

       ROUND(SUM(maxbytes) / 1024 / 1024, 2) AS "Max_MB",

       ROUND((SUM(bytes) / SUM(maxbytes)) * 100, 2) AS "Used(%)"

FROM dba_data_files

GROUP BY tablespace_name;

-- Step 4: Check for Expired or Invalid Objects

-- It's important to check for invalid objects and make sure everything is valid

SELECT owner, object_type, object_name

FROM dba_objects

WHERE status = 'INVALID';

-- Step 5: Check for Locked Sessions

-- This will show if any sessions are being locked, which could affect performance

SELECT sid, serial#, username, osuser, status, machine, program

FROM v$session

WHERE blocking_session IS NOT NULL;

-- Step 6: Check for Long Running Queries

-- Monitor long running queries which could be resource hogs

SELECT s.sid, s.serial#, s.username, q.sql_text, s.status, s.osuser, s.program

FROM v$session s, v$sql q

WHERE s.sql_id = q.sql_id

AND s.status = 'ACTIVE'

AND s.username IS NOT NULL

ORDER BY s.sid;

-- Step 7: Check Redo Log Status

-- It's important to monitor redo logs for any potential issues

SELECT member, status FROM v$logfile;

-- Step 8: Check Oracle Alert Log for Errors

-- Check the alert log for recent errors and important messages

-- Check the alert log using the following:

-- $ tail -n 100 $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/alert_<instance_name>.log

-- Step 9: Check Backup Status

-- Ensure that backups are scheduled and have been successfully completed

SELECT * FROM dba_backup_set WHERE status = 'COMPLETED' ORDER BY completion_time DESC FETCH FIRST 5 ROWS ONLY;

-- Step 10: Check the Listener Status

-- Ensure that the listener is up and running

SELECT * FROM dba_listener_status;

-- Step 11: Check for Database Jobs and their Status

-- You should check for any long-running or failed jobs in Oracle DBMS_SCHEDULER

SELECT job_name, status, last_start_date, next_run_date

FROM dba_scheduler_jobs;

-- Step 12: Check for Tables with Excessive Rows (Potentially Large Tables)

-- Identify tables with very large numbers of rows that may need partitioning or optimization

SELECT table_name, num_rows

FROM all_tables

WHERE owner NOT IN ('SYS', 'SYSTEM') 

AND num_rows > 1000000  -- Adjust the threshold based on your environment

ORDER BY num_rows DESC;

-- Step 13: Check for Fragmentation in Tablespaces

-- Check for tablespaces with high fragmentation or those requiring cleanup

SELECT tablespace_name, 

       ROUND(SUM(bytes) / 1024 / 1024, 2) AS "Free_MB",

       ROUND(SUM(maxbytes) / 1024 / 1024, 2) AS "Max_MB",

       ROUND((SUM(bytes) / SUM(maxbytes)) * 100, 2) AS "Used(%)"

FROM dba_free_space

GROUP BY tablespace_name;

-- Step 14: Check System Resource Usage

-- Check CPU, memory, and IO usage on the server to ensure no system resource issues

-- This would need to be done on the OS level, not directly through SQL.

-- For Linux:

-- $ top -n 1

-- $ vmstat 1 5

-- $ iostat

-- Step 15: Check for Datafile Growth

-- Check datafiles for growth and ensure there's enough space

SELECT file_name, ROUND(bytes / 1024 / 1024 / 1024, 2) AS "Size_GB"

FROM dba_data_files;

-- Step 16: Check for Pending Table/Index/Partition Stats

-- Ensure that statistics are up-to-date to improve query performance

SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE owner = 'SCHEMA_NAME' AND (last_analyzed IS NULL OR num_rows = 0);

-- Step 17: Check Database Users and Profiles

-- Monitor active database users and their profiles

SELECT username, profile FROM dba_users;

-- Step 18: Check for High Watermark for Undo Segments

-- Check undo segments and high-water mark in undo tablespace

SELECT tablespace_name, file_id, block_id, blocks

FROM v$rollstat;

-- Step 19: **Monitor Index Health**

-- Check for missing indexes on frequently queried tables (tables with no indexes at all)

SELECT owner, table_name, COUNT(*) AS missing_indexes

FROM dba_tables t

LEFT JOIN dba_indexes i ON t.table_name = i.table_name AND t.owner = i.owner

WHERE i.index_name IS NULL

AND t.owner NOT IN ('SYS', 'SYSTEM')

GROUP BY owner, table_name

ORDER BY missing_indexes DESC;

-- Step 20: **Monitor Fragmented Indexes**

-- Identify fragmented indexes which may need rebuilding or optimization

SELECT index_name, table_name, 

       ROUND((leaf_blocks * 100) / blocks) AS "Fragmentation(%)"

FROM dba_indexes

WHERE table_owner NOT IN ('SYS', 'SYSTEM')

AND ROUND((leaf_blocks * 100) / blocks) > 30  -- Adjust threshold for fragmentation

ORDER BY "Fragmentation(%)" DESC;

-- Step 21: **Monitor Index Usage**

-- Identify indexes that are not being used, potentially candidates for removal

SELECT * 

FROM dba_indexes

WHERE status = 'UNUSED';

-- Step 22: **Gather Database Statistics (GSS)**

-- Regularly gathering statistics helps Oracle optimize query plans and overall performance.

-- This section will gather stats for tables and indexes. Make sure the relevant schema is specified for better control.

-- Use DBMS_STATS to gather stats for all schemas or specific objects:

BEGIN

  -- Gather stats for all schemas in the database

  DBMS_STATS.GATHER_DATABASE_STATS;

  -- Alternatively, gather stats for a specific schema (replace SCHEMA_NAME)

  -- DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

END;

/

-- Step 23: **Index Usage Stats:**

-- Monitor Index usage statistics to identify unused or underperforming indexes

SELECT index_name, 

       object_name, 

       object_type, 

       leaf_blocks, 

       distinct_keys, 

       avg_leaf_blocks_per_key, 

       num_rows

FROM dba_indexes

WHERE table_owner NOT IN ('SYS', 'SYSTEM')

AND (distinct_keys = 0 OR avg_leaf_blocks_per_key > 10);  -- Adjust based on your system needs

-- Step 24: **Monitor Disabled Triggers**

-- Check if there are any triggers that are disabled on any tables. Disabled triggers could indicate issues in the application or unwanted side effects.

SELECT owner, table_name, trigger_name, status

FROM dba_triggers

WHERE status = 'DISABLED'

AND owner NOT IN ('SYS', 'SYSTEM')

ORDER BY owner, table_name;