Oracle DBA: Daily Checks for Real-World Output

When managing an Oracle environment, database stability and performance are crucial for operational continuity. I have spent years monitoring and optimizing Oracle instances in enterprise contexts, where even brief downtime can have significant repercussions. A reactive approach, intervening only when a problem has already escalated, is unsustainable. This is why I have refined a list of essential daily checks, designed to promptly identify anomalies and prevent service disruptions. These regularly performed checks offer a clear snapshot of the database’s health, allowing proactive action to ensure applications relying on Oracle run without interruption. The goal is not just to keep the database online but to ensure it operates at its full capacity, supporting critical organizational workloads.

Tested on: Oracle Database 19c · SQL*Plus · September 23, 2026

Prerequisites / Test Environment

To perform the described checks, you will need a DBA user with appropriate permissions (e.g., SYSDBA) and access to the database server. The commands have been tested on an Oracle Database 19c instance, accessed via SQL*Plus. It is advisable to perform these checks in a test or development environment before applying them directly to production, to become familiar with the outputs and potential side effects.

1. Instance and Listener Status

The first step is to verify that the Oracle instance is active and the listener is running. Without an active instance, the database is unavailable. Without a listener, no external connections can be established. Read also: Oracle Listener: Configuration and Troubleshooting

To check the instance status, connect to SQL*Plus as SYSDBA and use the STATUS command.

SQL> SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

Typical output:

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ----------- -----------------
ORCL             OPEN         ACTIVE

To check the listener status, execute the lsnrctl status command from the operating system prompt.

lsnrctl status

Typical output (relevant part):

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-SEP-2026 10:30:00

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-SEP-2026 09:00:00
Uptime                    0 days 1 hr. 30 min. 0 sec
...
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

2. Disk Space and Tablespaces

Tablespace exhaustion is a common cause of database blocks and degraded performance. Monitoring available space is crucial to prevent outages. This check should be performed daily, especially in environments with high workloads or rapid data growth. Read also: Oracle RMAN: Production Backup & Recovery Guide (2026)

SQL> SELECT
  df.tablespace_name,
  TRUNC(df.bytes / (1024 * 1024)) AS total_mb,
  TRUNC(SUM(fs.bytes) / (1024 * 1024)) AS free_mb,
  TRUNC((SUM(fs.bytes) / df.bytes) * 100) AS free_percent
FROM
  dba_data_files df,
  dba_free_space fs
WHERE
  df.file_id = fs.file_id (+)
GROUP BY
  df.tablespace_name, df.bytes
ORDER BY
  free_percent;

Typical output:

TABLESPACE_NAME         TOTAL_MB    FREE_MB FREE_PERCENT
-------------------- ---------- ---------- ------------
SYSAUX                     1000        150           15
USERS                       500        300           60
SYSTEM                      700        600           85
TEMP                        200        200          100
UNDOTBS1                   1000       1000          100

A low free_percent value for critical tablespaces like SYSAUX or USERS requires immediate attention.

3. Backups and Alert Log

Valid backups are the guarantee of being able to recover the database in case of disaster. The alert log is the database’s journal, where critical errors, startup/shutdown events, and other vital information are recorded. Checking both daily is an indispensable security practice.

For the status of recent backups (using RMAN):

SQL> SELECT
  session_key,
  input_type,
  status,
  TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
  TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
  elapsed_seconds/60 AS minutes_elapsed
FROM
  V$RMAN_BACKUP_JOB_DETAILS
WHERE
  start_time > SYSDATE - 1
ORDER BY
  start_time DESC;

Typical output:

SESSION_KEY INPUT_TYPE STATUS          START_TIME          END_TIME            MINUTES_ELAPSED
----------- ---------- --------------- ------------------- ------------------- ----------------
12345       DB FULL    COMPLETED       2026-09-22 23:00:00 2026-09-23 01:30:00           150

For the alert log, its location is defined by the DIAGNOSTIC_DEST parameter. You can read the latest messages with a tail or grep command.

tail -f $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log | grep -i "ORA-"

Look for ORA- errors or critical, fatal, error messages.

4. Active Sessions and Locks

Blocked or long-running sessions can indicate bottlenecks or application-level issues. Identifying them promptly allows intervention before they impact a large number of users.

To view active sessions and their wait events:

SQL> SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status,
  s.state,
  sw.event,
  sw.seconds_in_wait
FROM
  V$SESSION s,
  V$SESSION_WAIT sw
WHERE
  s.sid = sw.sid
  AND s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY
  sw.seconds_in_wait DESC;

Typical output:

SID    SERIAL# USERNAME PROGRAM                   STATUS   STATE            EVENT                               SECONDS_IN_WAIT
----- -------- -------- ------------------------- -------- ---------------- ----------------------------------- ----------------
123      45678 APP_USER  JDBC Thin Client          ACTIVE   WAITING          SQL*Net message from client                     5
124      90123 APP_BATCH BATCH_JOB                 ACTIVE   WAITING          db file sequential read                       120

To identify locks:

SQL> SELECT
  l.session_id AS blocking_sid,
  s.serial# AS blocking_serial#,
  s.username AS blocking_user,
  s.program AS blocking_program,
  l.locked_mode AS lock_mode,
  o.object_name,
  o.object_type,
  (SELECT sid FROM V$SESSION WHERE blocking_session = l.session_id) AS blocked_sid
FROM
  V$LOCK l
  JOIN V$SESSION s ON l.session_id = s.sid
  JOIN DBA_OBJECTS o ON l.id1 = o.object_id
WHERE
  l.block = 1;

Typical output:

BLOCKING_SID BLOCKING_SERIAL# BLOCKING_USER BLOCKING_PROGRAM          LOCK_MODE OBJECT_NAME OBJECT_TYPE BLOCKED_SID
------------ ---------------- ------------- ------------------------- --------- ----------- ----------- -----------
123          45678            APP_USER      JDBC Thin Client          3         DATA_TABLE  TABLE          124

Common Errors and Troubleshooting

• Listener not started: If lsnrctl status fails or the listener is not in a READY state, try restarting it with lsnrctl start or lsnrctl stop followed by lsnrctl start. Check the listener.log file for specific errors.
• Tablespace nearly full: If a tablespace is at its limit, you can add a new datafile (ALTER TABLESPACE USERS ADD DATAFILE '/path/to/datafile.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;) or resize an existing datafile if it’s not at its maximum (ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 500M;).
• Backup failed: Check RMAN logs and operating system logs. Often, the cause is insufficient space, permission issues, or incorrect configuration. Ensure the Oracle user has write permissions to the backup destination.
• Blocked sessions: Identify the blocking session and the locked object. Often, a missing commit or rollback is the cause. If necessary, terminate the blocking session with ALTER SYSTEM KILL SESSION 'sid,serial#'; but be careful, as this could lead to a long rollback and impact other transactions. Always consult the application team before terminating a session.

FAQ — Frequently Asked Questions

How often should I perform these checks?

Ideally, these checks should be performed at the beginning of each workday. For particularly critical or high-workload environments, some checks (like session status or space utilization) might benefit from more frequent monitoring, even every few hours. Automation via scripts and monitoring tools is highly recommended to lighten the DBA’s load.

Can I automate these checks?

Absolutely. Most of these SQL and shell commands can be integrated into scripts (e.g., Bash, Python) and scheduled via cron on Linux or Task Scheduler on Windows. Outputs can be redirected to log files or sent via email for quick review. Professional monitoring tools like Oracle Enterprise Manager (OEM) or third-party solutions offer comprehensive dashboards and automatic alerts.

What should I do if I find a serious problem?

In case of serious problems (e.g., instance down, critical tablespaces full, failed backups), the first action is to consult internal documentation for troubleshooting and escalation procedures. If no specific procedure exists, investigate the alert log, trace files, and operating system logs. Contact the development team or application vendors if the problem appears related to application code or configuration. Timely communication is critical.

Are these checks sufficient for security?

No, these checks focus on database availability and performance. Database security requires a much broader set of controls and practices, including user and privilege management, regular patching, data encryption, auditing, and monitoring for suspicious activities. This set of checks is a starting point for operational management, not a substitute for a comprehensive security strategy. Oracle Database Security Guide

Conclusions with Operational Takeaways

Implementing a daily Oracle check routine is a non-negotiable practice for any DBA aiming for system proactivity and stability. The real-world outputs provided in this guide are the starting point for building your personalized checklist. Remember that automation is a valuable ally, but human oversight remains irreplaceable for interpreting alerts and making informed decisions. A well-monitored database is a resilient database, capable of supporting the most stringent operational needs and preventing disasters before they fully materialize.

Sources

• Oracle Database Security Guide 19c
• SQL*Plus User’s Guide and Reference 19c
• Oracle Database Administrator’s Guide 19c

Updated: September 2026