-- 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;
