Working as an Oracle DBA means being the invisible guardian of data availability. Often, your work is only noticed when something goes wrong. And when it does, the impact can be devastating. I recall a Friday evening, just before logging off: a critical alert indicated the SYSTEM tablespace was at 99.8% capacity. Oracle was already in restricted mode, with 200 users locked out and transactions stalled. The cause? A growth of just 2MB per day, consistent for eight months, undetected by ineffective automated monitoring. The primary DBA was on vacation, and the situation required an emergency intervention that ruined the weekend for me and my team. This incident, unfortunately not isolated, underscores the importance of a rigorous daily checklist for any Oracle DBA managing production environments.
This guide provides an essential checklist of 10 daily checks that, when performed correctly, can prevent most common problems, ensuring the stability and performance of your Oracle database. Each point includes specific SQL commands you can integrate into your monitoring scripts or run manually for a quick check. By implementing these practices, you can identify and resolve anomalies before they become critical incidents, maintaining SLAs and user trust. The goal is to transform a potentially lengthy and tedious process into a routine check taking just a few minutes, providing immediate and tangible value.
Prerequisites / Test Environment
To execute the SQL commands presented in this guide, you will need an Oracle user with SELECT privileges on DBA_DATA_FILES, V$SQL, V$SESSION, V$RMAN_BACKUP_JOB_DETAILS, and V$ARCHIVED_LOG views. The ideal test environment is an Oracle Database 12c, 19c, or later instance, with a workload representative of a production environment. For AWR/ASH analysis, diagnostic licenses must be active.
1-2: Space — Tablespace and Datafile Growth in the Last 24h
Space monitoring is the first line of defense against many availability issues. A full tablespace can block transactions, prevent new object creation, or even halt the entire database. It is crucial to check tablespace utilization and datafile growth.
To verify current tablespace usage and maximum configurable size, you can use the following query:
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 percent_used
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY percent_used DESC;This query provides a clear overview of used space and growth potential. Monitoring growth over the last 24 hours is equally crucial for identifying anomalous consumption. You can integrate this logic with historical tables or monitoring tools that track space evolution over time. Approximately 60% of database availability issues in unmonitored environments are related to space exhaustion — Oracle Support Statistics 2025.
3-4: Performance — Top SQL from AWR, Critical Wait Events
Database performance is key to a responsive application. Identifying the most “expensive” queries and critical wait events daily allows you to act proactively. AWR (Automatic Workload Repository) is the primary tool for this analysis.
To find the top SQL by CPU or elapsed time in the last 24 hours, you can query the V$SQL view:
SELECT sql_id, elapsed_time/1000000 elapsed_sec, executions,
ROUND(elapsed_time/NULLIF(executions,0)/1000000,2) avg_sec,
sql_text
FROM v$sql
WHERE last_active_time > SYSDATE-1
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;This query will show you the 10 queries that consumed the most CPU time in the last 24 hours, providing sql_id and the query text for in-depth analysis. If you do not have AWR licenses, you can still use V$SQL for more recent data. Critical wait events can be identified via V$SESSION_WAIT or AWR/ASH reports, indicating bottlenecks on I/O, latches, or locks. A daily analysis of these parameters can reveal performance regressions introduced by new releases or unexpected workloads. 73% of enterprise applications critically depend on database performance — Gartner 2024.
5-6: Alert Log — ORA- Errors, Warnings, Archivelog Status
The alert log is your database’s ship’s log. It contains vital information about critical events, errors, and warnings. Ignoring it is like driving a car without looking at the dashboard warning lights.
Check for ORA- errors or warnings in the alert log. There is no direct SQL query to read the alert log from the database, as it is a text file on the filesystem. You will need to access the server and read the file, typically located in $ORACLE_BASE/diag/rdbms/. You can use Linux commands like tail -f or scripts that search for specific patterns.
Also, check the status of the archivelogs. If the database is in ARCHIVELOG mode, archivelog management is fundamental for recoverability and operational continuity. If the archivelog destination fills up, the database will halt. Use the following query to check its status:
SELECT name, completion_time, status, blocks*block_size/1024/1024 AS size_mb
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
ORDER BY completion_time DESC;This query will give you an overview of archivelogs generated in the last 24 hours, their status, and size, ensuring there are no space issues or corruption.
7-8: Backup — RMAN Verification, Archive Log Destination
Backup is your last line of defense. An unverified backup is a non-existent backup. Every DBA knows that backup reliability is non-negotiable.
Verify the status of RMAN (Recovery Manager) backups to ensure they have completed successfully and are usable. The following query will show you the latest RMAN backups:
SELECT session_key, input_type, status, start_time, end_time, output_bytes/1024/1024 AS output_mb
FROM v$rman_backup_job_details
ORDER BY start_time DESC
FETCH FIRST 5 ROWS ONLY;Check that the status is COMPLETED or COMPLETED WITH WARNINGS (to be investigated). Furthermore, it is essential to monitor the archive log destination. If the destination fills up, the database stops. Ensure that space is adequate or that a cleanup process is active.
9-10: Blocked Sessions, Deadlocks, and Lock Contention
Blocked sessions and deadlocks are symptoms of application-level or database design problems, but they can have an immediate and severe impact on system responsiveness.
To identify blocked sessions, you can use this query:
SELECT s.sid, s.serial#, s.username, s.status, s.program,
(SELECT p.spid FROM v$process p WHERE p.addr = s.paddr) AS os_pid,
s.blocking_session, s.event, s.wait_class
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.blocking_session;This query will help you identify sessions that are blocking other sessions and trace back to the cause. Deadlocks are rare but critical events, often automatically resolved by Oracle but leaving traces in the alert log. Lock contention can be monitored via V$WAIT_STATISTICS or AWR/ASH reports, indicating areas of the database where resources are excessively contended.
SQL Script: Complete Daily Check in 5 Minutes
To automate these checks, you can combine the queries into a single SQL script that can be executed via SQL*Plus or scheduled via cron or dbms_scheduler. This script should generate a concise report highlighting any anomalies, allowing you to maintain control in just a few minutes.
-- Oracle DBA Daily Health Check Script
SET PAGESIZE 9999
SET LINESIZE 200
COLUMN tablespace_name FORMAT A20
COLUMN sql_text FORMAT A60 WRAP
COLUMN status FORMAT A15
COLUMN program FORMAT A30
PROMPT =========================================================
PROMPT == 1. Tablespace Usage (Top 10 by percentage) ==
PROMPT =========================================================
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 percent_used
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY percent_used DESC
FETCH FIRST 10 ROWS ONLY;
PROMPT =========================================================
PROMPT == 2. Top 10 SQL by Elapsed Time (Last 24h) ==
PROMPT =========================================================
SELECT sql_id, elapsed_time/1000000 elapsed_sec, executions,
ROUND(elapsed_time/NULLIF(executions,0)/1000000,2) avg_sec,
SUBSTR(sql_text,1,60) AS sql_text
FROM v$sql
WHERE last_active_time > SYSDATE-1
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
PROMPT =========================================================
PROMPT == 3. Blocked Sessions ==
PROMPT =========================================================
SELECT s.sid, s.serial#, s.username, s.status, s.program,
(SELECT p.spid FROM v$process p WHERE p.addr = s.paddr) AS os_pid,
s.blocking_session, s.event, s.wait_class
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.blocking_session;
PROMPT =========================================================
PROMPT == 4. Last 5 RMAN Backup Jobs ==
PROMPT =========================================================
SELECT session_key, input_type, status, start_time, end_time, output_bytes/1024/1024 AS output_mb
FROM v$rman_backup_job_details
ORDER BY start_time DESC
FETCH FIRST 5 ROWS ONLY;
PROMPT =========================================================
PROMPT == 5. Last 5 Archived Logs (Last 24h) ==
PROMPT =========================================================
SELECT name, completion_time, status, blocks*block_size/1024/1024 AS size_mb
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
ORDER BY completion_time DESC
FETCH FIRST 5 ROWS ONLY;
PROMPT == End of Daily Health Check ==Common Errors and Troubleshooting
One of the most common errors is relying solely on automated monitoring tools without understanding the data they produce. These tools are valuable, but they do not replace the critical eye of a DBA. Another mistake is ignoring warnings in the alert log, considering them less severe than critical errors. Often, a warning is the precursor to a much larger problem.
For troubleshooting, in case of performance issues, always start by analyzing wait events and top SQL. If the problem is space, identify the segments that are growing fastest. For backups, check RMAN logs and destination directories. Remember that the official Oracle documentation (https://docs.oracle.com) is your most reliable resource for in-depth understanding of any aspect.
FAQ — Frequently Asked Questions
What is the ideal frequency for performing these checks?
For critical production environments, a daily check is essential. Some checks, such as backup status and the alert log, should be continuously monitored through automated alerts. However, a daily manual review adds a layer of security and understanding that automation alone cannot provide.
Can I fully automate this checklist?
Yes, most of these queries can be integrated into Bash or Python scripts and scheduled via cron or dbms_scheduler. The output can be emailed or integrated into a monitoring system like Grafana. Automation is fundamental for efficiency, but human review of the results is irreplaceable for interpreting anomalies and preventing false positives.
What is the difference between V$SQL and DBA_HIST_SQLSTAT?
V$SQL contains information about recently executed queries in the Shared Pool buffer and reflects current activity. DBA_HIST_SQLSTAT (part of AWR) contains historical snapshots of query performance, useful for trend analysis and troubleshooting past problems. DBA_HIST_SQLSTAT requires the Diagnostic Pack license, while V$SQL is available by default.
What should I do if a tablespace is almost full?
The first action is to identify the segments consuming the most space. Then, evaluate whether it is possible to add a new datafile (ALTER TABLESPACE ADD DATAFILE) or extend an existing datafile (ALTER DATABASE DATAFILE RESIZE). If it is a temporary or undo tablespace, it can be purged or resized. In extreme cases, you might need to archive or delete data no longer needed.
Conclusions with Operational Takeaways
Proactive management of an Oracle database is not a luxury; it is a necessity. The 10-point checklist presented in this guide, when applied with discipline, can drastically reduce the risk of outages and improve overall performance. Remember that combining automation and human oversight is the winning strategy. Do not wait until your SYSTEM tablespace is at 99.8% to act. Be proactive, not reactive.
Operational Takeaways:
- Implement: Integrate these SQL scripts into your daily workflow.
- Automate: Schedule execution and report delivery to save time.
- Analyze: Do not just read the results; look for patterns, trends, and anomalies.
- Document: Maintain a log of anomalies and corrective actions taken.
Read also: Lynis Security Audit: Linux Hardening Guide 2026
Read also: Oracle RMAN: Production Backup & Recovery Guide (2026)
Read also: Oracle RMAN: Production Backup & Recovery Guide (2026)
Updated: July 2026
