Master Oracle Daily Health Checks: Essential Commands for DB Stability

Regular database health checks are fundamental for stable system operation. While each operations team may have its own inspection framework, many Oracle‑specific checks are universally useful. The following Oracle daily inspection commands help monitor instance status, resource usage, performance, server health, and security.

1. Database Basic Status Check

(1) Instance status – Verify that STATUS is OPEN and DATABASE_STATUS is ACTIVE .

(2) Tablespace status – All tablespaces should show STATUS = ONLINE.

(3) Data file status

select tablespace_name, status from dba_tablespaces;

Resulting STATUS should be AVAILABLE .

(4) Online redo log check

select group#, status, type, member from v$logfile;

There should be three or more rows; STATUS must not be INVALID or DELETED .

(5) Rollback segment check

select segment_name, status from dba_rollback_segs;

All rollback segments should have STATUS = ONLINE.

2. Oracle Resource Usage Check

(1) Initialization parameter limits

select resource_name, max_utilization, initial_allocation, limit_value from v$resource_limit;

If LIMIT_VALUE - MAX_UTILIZATION ≤ 5, the corresponding parameter may need adjustment in the init.ora file.

(2) Session connections

select sid, serial#, username, program, machine, status from v$session;

STATUS

= ACTIVE indicates an active session; excessive connections can consume resources and may require manual cleanup.

(3) Tablespace usage

select f.tablespace_name, a.total, f.free, round((f.free / a.total) * 100) "% Free"
from (
  select tablespace_name, sum(bytes/ (1024*1024)) total
  from dba_data_files group by tablespace_name) a,
(
  select tablespace_name, round(sum(bytes/ (1024*1024))) free
  from dba_free_space group by tablespace_name) f
where a.tablespace_name = f.tablespace_name(+)
order by "% Free";

If free space < 10%, consider adding data files instead of relying on auto‑extend.

(4) SYSTEM tablespace objects

select distinct owner from dba_tables where tablespace_name = 'SYSTEM' and owner not in ('SYS','SYSTEM')
union
select distinct owner from dba_indexes where tablespace_name = 'SYSTEM' and owner not in ('SYS','SYSTEM');

Objects belonging to non‑SYS users should be moved out of SYSTEM.

(5) Extent usage near limits

select segment_name, segment_type, tablespace_name, (extents/max_extents)*100 Percent
from sys.dba_segments
where max_extents <> 0 and (extents/max_extents)*100 >= 95
order by Percent;

Segments approaching their maximum extents need storage parameter adjustments.

(6) Next extent larger than tablespace free chunk

select a.table_name, a.next_extent, a.tablespace_name
from all_tables a, (
  select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) f
where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a, (
  select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) f
where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;

Adjust tablespace parameters when next extent exceeds available free space.

3. Oracle Performance Check

Set SQL*Plus display: set pages 80, set lines 120, col event for a40.

(1) Wait events

select sid, event, p1, p2, p3, wait_time, seconds_in_wait
from v$session_wait
where event not like 'SQL%' and event not like 'rdbms%';

Frequent waits such as latch free, enqueue, buffer busy, or file reads indicate potential problem statements.

(2) Top 5 system wait events

select * from (
  select * from v$system_event where event not like 'SQL%'
  order by total_waits desc)
where rownum <= 5;

(3) Top 10 high‑cost SQL

select * from (
  select parsing_user_id, executions, sorts, command_type, disk_reads, sql_text
  from v$sqlarea order by disk_reads desc)
where rownum < 10;

(4) Top Disk‑Read SQL

select sql_text from (
  select * from v$sqlarea order by disk_reads)
where rownum <= 5;

(5) Deadlock detection

select sid, serial#, username, schemaname, osuser, machine, terminal, program, owner, object_name, object_type, o.object_id
from dba_objects o, v$locked_object l, v$session s
where o.object_id = l.object_id and s.sid = l.session_id;

4. Server CPU, Memory & I/O Checks

(1) CPU usage: top (2) Memory usage: free -m (3) I/O statistics: iostat -k 1 3 (4) System load: uptime (5) Zombie processes

select spid from v$process where addr not in (select paddr from v$session);

(6) Redo log buffer statistics

select name, value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

If redo buffer allocation retries / redo entries exceeds 1%, increase log_buffer.

5. Server Security Checks

(1) System security logs

Successful logins: grep -i accepted /var/log/secure Failed logins:

grep -i inval /var/log/secure && grep -i failed /var/log/secure

(2) Crontab job failures

select job, what, last_date, next_date, failures, broken
from dba_jobs where schema_user = 'system';

(3) Invalid indexes

select index_name, table_name, tablespace_name, status
from dba_indexes where owner = 'system' and status <> 'VALID';

Rebuild invalid indexes with:

alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;