Oracle 19c Database Health Check Script – Complete Oracle DBA Guide
Maintaining a healthy Oracle Database is one of the most important responsibilities of an Oracle Database Administrator (DBA). A proactive database health check helps identify performance bottlenecks, storage issues, invalid objects, tablespace utilization, backup status, and database availability before they become critical problems.
In this article, you'll learn how to create and use an Oracle 19c Database Health Check Script that collects essential database information in a single execution. This guide is designed for Oracle DBAs, System Administrators, Developers, and IT Professionals who want a simple, effective, and reusable script for daily database monitoring.
What is an Oracle Database Health Check?
An Oracle Database Health Check is a routine assessment of your database environment to verify that critical components are operating correctly. Rather than waiting for users to report problems, DBAs can proactively detect issues such as low tablespace, invalid database objects, failed backups, inactive listeners, or excessive session counts.
Regular health checks improve database stability, reduce downtime, and support better capacity planning. They are considered a best practice for production, testing, and development environments.
Why Perform Regular Database Health Checks?
A scheduled health check provides valuable insight into the overall condition of your Oracle Database. It enables administrators to identify and resolve potential issues before they affect application performance or business operations.
- Monitor database availability.
- Check instance status.
- Review database version and uptime.
- Monitor tablespace utilization.
- Detect invalid objects.
- Identify blocking or inactive sessions.
- Verify archive log mode.
- Review backup status.
- Monitor Fast Recovery Area (FRA) usage.
- Collect useful information for daily DBA reports.
Prerequisites
Before running the health check script, ensure the following requirements are met:
- Oracle Database 19c installed.
- SQL*Plus available.
- SYSDBA or a user with required dictionary privileges.
- Access to dynamic performance views (V$ views).
Oracle 19c Database Health Check Script
The following SQL*Plus script gathers key health information from an Oracle 19c database. Save it as health_check.sql and execute it from SQL*Plus.
SET PAGESIZE 1000
SET LINESIZE 200
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
PROMPT ==========================================
PROMPT ORACLE 19C DATABASE HEALTH CHECK REPORT
PROMPT ==========================================
PROMPT
PROMPT DATABASE INFORMATION
PROMPT ---------------------
SELECT name,
db_unique_name,
open_mode,
database_role,
log_mode
FROM v$database;
PROMPT
PROMPT INSTANCE INFORMATION
PROMPT --------------------
SELECT instance_name,
host_name,
version,
status,
startup_time
FROM v$instance;
PROMPT
PROMPT DATABASE UPTIME
PROMPT ----------------
SELECT FLOOR(SYSDATE-startup_time) || ' Days'
AS UPTIME
FROM v$instance;
PROMPT
PROMPT TABLESPACE UTILIZATION
PROMPT ----------------------
SELECT tablespace_name,
ROUND(used_percent,2) USED_PERCENT
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;
PROMPT
PROMPT INVALID OBJECTS
PROMPT ---------------
SELECT owner,
object_type,
COUNT(*) TOTAL
FROM dba_objects
WHERE status='INVALID'
GROUP BY owner, object_type
ORDER BY owner;
PROMPT
PROMPT ACTIVE SESSIONS
PROMPT ---------------
SELECT status,
COUNT(*)
FROM v$session
GROUP BY status;
PROMPT
PROMPT ARCHIVE LOG MODE
PROMPT ----------------
ARCHIVE LOG LIST;
PROMPT
PROMPT FAST RECOVERY AREA
PROMPT ------------------
SELECT
name,
space_limit/1024/1024 "LIMIT_MB",
space_used/1024/1024 "USED_MB",
space_reclaimable/1024/1024 "RECLAIMABLE_MB"
FROM v$recovery_file_dest;
PROMPT
PROMPT DATABASE SIZE
PROMPT -------------
SELECT ROUND(SUM(bytes)/1024/1024/1024,2)
AS DATABASE_SIZE_GB
FROM dba_data_files;
PROMPT
PROMPT HEALTH CHECK COMPLETED
PROMPT ======================
What Does This Script Check?
The script combines several important Oracle data dictionary and dynamic performance views to provide a quick overview of database health.
1. Database Information
The script displays essential database properties including:
- Database Name
- DB Unique Name
- Open Mode
- Database Role
- Archive Log Mode
This information confirms that the database is open and operating in the expected role, especially in Data Guard environments.
2. Instance Information
The instance section reports:
- Instance Name
- Oracle Version
- Host Server
- Current Status
- Startup Time
This helps verify that the instance is running normally and provides the exact version of Oracle Database 19c installed.
3. Database Uptime
Database uptime is calculated using the instance startup time. Unexpectedly low uptime may indicate recent restarts caused by maintenance, patches, server reboots, or unexpected failures.
4. Tablespace Utilization
Monitoring tablespace usage is one of the most critical daily DBA tasks. The script lists all tablespaces along with their current percentage of used space.
Administrators should pay close attention to tablespaces approaching full capacity. Expanding datafiles or enabling autoextend before space runs out can prevent application outages.
5. Invalid Database Objects
Invalid objects often appear after upgrades, deployments, patching activities, or interrupted compilation processes. The report groups invalid objects by owner and object type, making it easier to identify schemas requiring recompilation.
6. Active Database Sessions
The session summary provides a count of ACTIVE and INACTIVE sessions currently connected to the database. A sudden increase in active sessions may indicate increased workload, connection leaks, or long-running transactions that require further investigation.
7. Archive Log Status
For databases operating in ARCHIVELOG mode, verifying archive status is essential for backup and recovery readiness. The report displays the current archive log configuration directly from SQL*Plus.
8. Fast Recovery Area (FRA)
The Fast Recovery Area stores archived redo logs, flashback logs, RMAN backups, and other recovery-related files. Monitoring FRA utilization helps prevent space exhaustion, which can interrupt backups and archive log generation.
9. Database Size
The script calculates the total size of all datafiles to provide a quick estimate of the database storage footprint. This information is useful for capacity planning and growth analysis.
How to Execute the Health Check Script
Running the health check script is straightforward. Save the SQL code from Part 1 as health_check.sql on the database server or your administration workstation.
Step 1: Connect to the Database
sqlplus / as sysdba
Alternatively, connect using a privileged database account.
sqlplus system/password@ORCL
Step 2: Run the Script
SQL> @health_check.sql
SQL*Plus executes each query sequentially and displays the results on the screen.
Generate a Health Check Report
Most Oracle DBAs prefer saving the output into a report for auditing and historical analysis. SQL*Plus provides the SPOOL command for this purpose.
Example
SPOOL health_check_report.txt @health_check.sql SPOOL OFF
The generated report can be archived daily or emailed automatically using operating system scripts.
Sample Output
========================================= ORACLE 19C DATABASE HEALTH CHECK REPORT ========================================= Database Name : ORCL Open Mode : READ WRITE Database Role : PRIMARY Log Mode : ARCHIVELOG Instance Name : ORCL Version : 19.0.0.0.0 Host : dbserver01 Status : OPEN Database Uptime : 42 Days Tablespace Usage SYSTEM 52% SYSAUX 48% USERS 31% TEMP 15% Invalid Objects No Invalid Objects Found Database Size 235.48 GB Health Check Completed Successfully
A report like this gives administrators a quick snapshot of the database without manually running multiple SQL queries.
Additional Health Checks Every Oracle DBA Should Perform
While the basic script covers the most important areas, production databases often require additional monitoring.
Check Blocking Sessions
SELECT blocking_session,
sid,
serial#,
username
FROM v$session
WHERE blocking_session IS NOT NULL;
Blocking sessions can significantly impact application performance and should be investigated promptly.
Check Recently Failed Jobs
SELECT owner,
job_name,
status
FROM dba_scheduler_job_run_details
WHERE status='FAILED'
ORDER BY log_date DESC;
Failed scheduled jobs may indicate issues with backups, maintenance tasks, or business-critical processes.
Monitor RMAN Backup Status
SELECT INPUT_TYPE, STATUS, START_TIME, END_TIME FROM V$RMAN_BACKUP_JOB_DETAILS ORDER BY START_TIME DESC;
Regularly reviewing RMAN backup history ensures that valid backups are available when recovery is required.
Check Temporary Tablespace Usage
SELECT TABLESPACE_NAME, SUM(BYTES_USED)/1024/1024 MB_USED FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME;
Excessive temporary tablespace usage may indicate inefficient SQL execution plans or large sorting operations.
Review Alert Log Regularly
The Oracle Alert Log records significant database events, including startup, shutdown, errors, and background process messages. Reviewing it daily helps identify problems before they affect users.
Automating Daily Health Checks
Executing the script manually every day is practical for small environments but becomes inefficient as the number of databases grows. Automating health checks ensures consistency and saves valuable administration time.
Linux Cron Example
0 7 * * * sqlplus / as sysdba @/home/oracle/scripts/health_check.sql
The above cron job runs the health check every day at 7:00 AM.
Windows Task Scheduler
Windows administrators can create a scheduled task that runs SQL*Plus with the health check script at a specified time each day. The generated report can then be stored in a shared folder or attached to an automated email.
Oracle DBA Best Practices
- Run a health check at least once every day.
- Monitor tablespace utilization proactively.
- Review invalid database objects after deployments.
- Monitor archive log generation.
- Validate RMAN backups regularly.
- Review the Oracle Alert Log daily.
- Apply Oracle Release Updates (RU) according to Oracle recommendations.
- Monitor database growth for capacity planning.
- Document health check results for trend analysis.
- Test recovery procedures periodically.
Frequently Asked Questions (FAQ)
Is this script compatible with Oracle 19c Enterprise Edition?
Yes. The script is designed for Oracle Database 19c and uses standard data dictionary and dynamic performance views commonly available in Enterprise Edition. Some queries may also work in Standard Edition depending on privileges and features in use.
How often should I run a database health check?
For production databases, running the health check daily is recommended. Mission-critical environments may benefit from more frequent monitoring combined with enterprise monitoring tools.
Can I customize the script?
Absolutely. You can add checks for ASM disk groups, Pluggable Databases (PDBs), Data Guard, GoldenGate, wait events, AWR reports, or any environment-specific requirements.
Does this script replace Oracle Enterprise Manager?
No. This script provides a lightweight, quick health assessment. Oracle Enterprise Manager offers comprehensive monitoring, alerting, and performance analysis across enterprise environments.
Conclusion
A well-designed Oracle 19c Database Health Check Script is an essential tool for every Oracle DBA. Instead of manually executing dozens of SQL statements, a single script can collect critical information about database status, storage utilization, invalid objects, active sessions, recovery settings, and overall health.
Regular health checks support proactive database administration, reduce the likelihood of unexpected outages, and provide valuable insights for capacity planning and performance optimization. By scheduling the script to run automatically and reviewing the generated reports, organizations can maintain stable, secure, and reliable Oracle Database environments.
As your infrastructure grows, consider extending the script with additional checks for ASM, RAC, Data Guard, Oracle Multitenant, and backup validation to create a comprehensive daily health report tailored to your environment.
Use this health check script as the foundation for your daily Oracle monitoring routine. As your environment evolves, enhance it with checks for ASM, RAC, Data Guard, Pluggable Databases (PDBs), listener status, and operating system metrics. A proactive monitoring strategy helps identify issues early, improves database availability, and supports long-term performance and reliability.
About the Author
Abdul Wahid Rana is an experienced Oracle Database Administrator specializing in Oracle Database Administration, Oracle E-Business Suite, Oracle Data Guard, RMAN Backup & Recovery, Oracle RAC, Performance Tuning, High Availability Solutions, and production database troubleshooting.
Through this blog, he shares practical Oracle DBA tutorials, real-world troubleshooting guides, SQL scripts, monitoring solutions, and best practices to help database professionals manage Oracle environments with confidence.
If this guide helped you Oracle 19c Database Health Check Script – Complete Oracle DBA Guide, please consider liking, following, and sharing this post with fellow Oracle DBAs. Don't forget to bookmark it for quick access during future troubleshooting!
No comments:
Post a Comment