Oracle Database Deep Health Check Checklist

Recommended assessment levels

Level	Purpose	Typical scope
L1	Operational health	Availability, space, backup, alert log
L2	DBA health	Objects, jobs, sessions, memory, RMAN, Data Guard
L3	Performance health	AWR, SQL, waits, I/O, redo, RAC, concurrency
L4	Architecture/risk	HA/DR, capacity, security, recoverability, OS/ASM/design

A proper architecture assessment should reach L4.

1. Database / Instance Health

1.1 Database state

Check:

Instance status
Database open mode
Database role
Restricted mode
ARCHIVELOG mode
FORCE LOGGING
Flashback status
Startup time
Database version/RU
CDB status

SELECT

i.instance_name,

i.host_name,

i.version,

i.status,

i.database_status,

i.startup_time,

d.name,

d.open_mode,

d.database_role,

d.log_mode,

d.force_logging,

d.flashback_on

FROM v$instance i

CROSS JOIN v$database d;

Validate

Healthy

Instance OPEN
Primary = READ WRITE
Physical standby normally MOUNTED or READ ONLY WITH APPLY depending on design
No unexpected restart

Investigate

Unexpected startup time
Restricted login
Instance recovery
ARCHIVELOG disabled on a database requiring PITR/DR
FORCE LOGGING disabled where Data Guard/design requires it

2. Oracle Version and Patch Health

Check:

SELECT banner_full

FROM v$version;

SELECT

action_time,

patch_id,

patch_type,

action,

status,

description

FROM dba_registry_sqlpatch

ORDER BY action_time DESC;

Validate:

Database RU level
GI RU level
OPatch inventory
SQL patch inventory
Failed datapatch components
GI/DB home consistency
Out-of-place patching strategy
One-off conflicts
Unsupported database version

Also compare:

$ORACLE_HOME/OPatch/opatch lsinventory

against:

SELECT * FROM dba_registry_sqlpatch;

A mismatch deserves investigation.

3. CDB/PDB Health

Especially important for your multitenant environments.

SELECT

con_id,

name,

open_mode,

restricted,

open_time

FROM v$pdbs

ORDER BY con_id;

Check:

PDB open state
SAVE STATE
Restricted PDBs
PDB startup failures
PDB service configuration
Resource plans
PDB space utilization
Local undo configuration
PDB violations

SELECT *

FROM pdb_plug_in_violations

WHERE status <> 'RESOLVED'

ORDER BY time DESC;

Red flag: unresolved ERROR-level compatibility/plugin violations.

4. Tablespace Health

A health check should consider both percentage utilization and remaining GB.

Example:

SELECT

tablespace_name,

ROUND(used_space * 8192/1024/1024/1024,2) used_gb,

ROUND(tablespace_size * 8192/1024/1024/1024,2) total_gb,

ROUND(used_percent,2) used_pct

FROM dba_tablespace_usage_metrics

ORDER BY used_percent DESC;

A commonly used operational starting point is:

<80% normal
80–90% investigate/growth watch
>90% action

However, do not judge a very large tablespace by percentage alone. A multi-terabyte tablespace at 90% could still have substantial free space.

Check additionally:

Autoextend
MAXSIZE
Remaining filesystem/ASM space
SYSTEM
SYSAUX
UNDO
TEMP
Bigfile tablespaces
Read-only tablespaces
Offline datafiles

5. Datafile Health

SELECT

tablespace_name,

file_name,

bytes/1024/1024/1024 GB,

autoextensible,

maxbytes/1024/1024/1024 max_gb,

status

FROM dba_data_files

ORDER BY tablespace_name, file_name;

Check:

Offline files
Recover status
Autoextend
Maximum file size
Storage headroom underneath autoextend
Uneven datafile growth
Excessive datafile count

Also:

SELECT * FROM v$recover_file;

Ideally no unexpected rows.

6. ASM Health

For ASM environments check:

SELECT

name,

state,

type,

total_mb,

free_mb,

usable_file_mb,

ROUND(100 * (1 - usable_file_mb/NULLIF(total_mb,0)),2) used_pct

FROM v$asm_diskgroup;

Check:

Diskgroup mounted
Disk failures
Rebalance operation
Usable space
ASM redundancy
Failure-group distribution
OCR/Voting disk placement
FRA/data separation where architecture requires it

SELECT * FROM v$asm_operation;

Unexpected long-running rebalance activity needs review.

7. TEMP Health

Check current TEMP configuration:

SELECT

tablespace_name,

file_name,

bytes/1024/1024/1024 gb,

autoextensible,

maxbytes/1024/1024/1024 max_gb

FROM dba_temp_files;

Investigate:

TEMP exhaustion
Large TEMP consumers
SQL spilling repeatedly to TEMP
Sort/hash pressure
Parallel queries consuming TEMP
ETL/reporting peaks

TEMP problems should also be correlated with PGA sizing rather than solved only by adding TEMP.

8. UNDO Health

SELECT

begin_time,

end_time,

undoblks,

txncount,

maxquerylen,

nospaceerrcnt,

ssolderrcnt

FROM v$undostat

ORDER BY begin_time DESC

FETCH FIRST 24 ROWS ONLY;

Look especially at:

NOSPACEERRCNT
SSOLDERRCNT
MAXQUERYLEN
Undo retention
Long-running transactions
Flashback requirements

Repeated ORA-01555 should trigger workload/undo-retention analysis, not blindly increasing UNDO.

9. Redo Log Architecture

Check:

SELECT

group#,

thread#,

sequence#,

bytes/1024/1024 size_mb,

members,

status,

archived

FROM v$log

ORDER BY thread#, group#;

and:

SELECT

group#,

member,

type,

status

FROM v$logfile

ORDER BY group#;

Validate:

Correct multiplexing
Group sizes
Group counts
RAC thread configuration
Member failures
Log switch frequency

Ideally review:

SELECT

TO_CHAR(first_time,'YYYY-MM-DD HH24') hour,

COUNT(*) switches

FROM v$log_history

WHERE first_time > SYSDATE - 7

GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24')

ORDER BY 1;

Frequent switches can indicate undersized redo logs or unusually high redo generation.

10. FRA Health

SELECT

name,

space_limit/1024/1024/1024 limit_gb,

space_used/1024/1024/1024 used_gb,

space_reclaimable/1024/1024/1024 reclaimable_gb,

number_of_files

FROM v$recovery_file_dest;

Also:

SELECT *

FROM v$flash_recovery_area_usage;

Check:

Archive logs
Backups
Flashback logs
Guaranteed restore points
Reclaimable space
FRA growth rate

A stuck archive process combined with FRA exhaustion can ultimately affect database availability.

11. Alert Log / ADR Health

This is one of the highest-value checks.

Search for:

ORA-

TNS-

Fatal

Incident

Errors in file

Corrupt

Archiver

ORA-00600

ORA-07445

ORA-04031

ORA-01555

ORA-01652

ORA-01653

ORA-19809

ORA-00257

SQL:

SELECT

originating_timestamp,

message_text

FROM v$diag_alert_ext

WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR

ORDER BY originating_timestamp DESC;

Do not treat every ORA message equally. Classify findings into:

Critical / Major / Warning / Informational

12. Invalid Objects

SELECT

owner,

object_type,

COUNT(*) invalid_count

FROM dba_objects

WHERE status = 'INVALID'

GROUP BY owner, object_type

ORDER BY invalid_count DESC;

Compare to baseline.

Investigate:

Newly invalid objects
Application packages
SYS/SYSTEM invalid objects
Materialized views
Synonyms/dependencies

Avoid automatically assuming every invalid object is an outage risk.

13. Unusable Indexes

SELECT owner, index_name, table_name, status

FROM dba_indexes

WHERE status <> 'VALID';

SELECT index_owner, index_name, partition_name, status

FROM dba_ind_partitions

WHERE status <> 'USABLE';

Particularly important after:

Partition maintenance
ETL
Large data loads
Direct-path operations
Maintenance activities

14. Statistics Health

Given your recent focus on stale statistics, I would make this an explicit health-check category.

SELECT

owner,

table_name,

num_rows,

last_analyzed,

stale_stats

FROM dba_tab_statistics

WHERE owner NOT IN ('SYS','SYSTEM')

AND (stale_stats = 'YES' OR last_analyzed IS NULL)

ORDER BY owner, table_name;

Check:

Stale table statistics
Stale partition statistics
Missing statistics
Global vs partition stats
Incremental statistics
Histogram usage
Modification rate
Auto stats job success
Stats gathering duration
Concurrent statistics jobs

15. Scheduler Health

SELECT

owner,

job_name,

enabled,

state,

failure_count,

last_start_date,

last_run_duration,

next_run_date

FROM dba_scheduler_jobs

WHERE enabled = 'TRUE'

ORDER BY owner, job_name;

Failures:

SELECT

owner,

job_name,

status,

actual_start_date,

run_duration,

error#,

additional_info

FROM dba_scheduler_job_run_details

WHERE status <> 'SUCCEEDED'

ORDER BY actual_start_date DESC;

Look for:

FAILURES
BROKEN jobs
Runtime increase
Jobs not executing
Overlapping jobs
Stats/backup/batch contention

16. Blocking / Locking Health

SELECT

sid,

serial#,

username,

blocking_session,

sql_id,

event,

seconds_in_wait

FROM v$session

WHERE blocking_session IS NOT NULL

ORDER BY seconds_in_wait DESC;

Empty is good at that instant.

Then investigate:

TX row lock contention
TM contention
DDL locks
Long transactions
Hot rows
Application commit behavior

One long transaction can block application activity without necessarily generating an obvious database error.

17. Long-Running SQL

SELECT

sid,

serial#,

username,

sql_id,

event,

wait_class,

last_call_et

FROM v$session

WHERE status = 'ACTIVE'

AND username IS NOT NULL

ORDER BY last_call_et DESC;

Compare against the normal workload baseline rather than adopting an arbitrary “30-minute SQL = bad” rule.

18. Wait Event Analysis

Start with:

SELECT

wait_class,

time_waited,

total_waits

FROM v$system_wait_class

WHERE wait_class <> 'Idle'

ORDER BY time_waited DESC;

Then assess important waits such as:

db file sequential read
db file scattered read
direct path read
direct path write temp
log file sync
log file parallel write
enq: TX - row lock contention
buffer busy waits
read by other session
RAC gc waits

Do not tune purely from cumulative v$system_event. For serious diagnosis use an AWR interval representing the affected period.

19. AWR Deep Health Assessment

This is where I would move from DBA health check to Architect-level performance review.

Review:

Workload

DB Time
DB CPU
Executions
Transactions/s
Logical reads
Physical reads
Redo/sec
Parses
Hard parses

Load profile

Compare:

Per second

Per transaction

Per execution

against historical normal periods.

Top foreground events

Identify whether time is dominated by:

CPU
User I/O
Commit
Concurrency
Cluster
Configuration
Network

SQL assessment

Review top SQL by:

DB Time
CPU
Elapsed time
Buffer gets
Physical reads
Executions
Parse calls
Version count

The key question is not merely “what is the highest SQL?” but:

Why did its contribution change from the normal baseline?

20. Execution Plan Stability

Check important SQL for:

Multiple Plan Hash Values
Sudden plan change
Cardinality errors
New full scans
Join-method changes
Statistics-triggered plan changes
Bind sensitivity
Adaptive cursor behavior
SQL Profile
SQL Plan Baseline

Architecture health should include performance stability, not only current performance.

21. Shared Pool / Cursor Health

Investigate:

Hard parse percentage
Parse-to-execute ratio
Cursor invalidations
Reloads
Version count
ORA-04031
Excessive child cursors

Useful:

SELECT sql_id, version_count

FROM v$sqlarea

WHERE version_count > 20

ORDER BY version_count DESC;

22. SGA Health

SELECT component,

current_size/1024/1024 MB

FROM v$sga_dynamic_components

ORDER BY current_size DESC;

Review:

Buffer cache
Shared pool
Large pool
Java pool
Streams pool
ASMM/AMM configuration

Also compare SGA allocation against:

server RAM
HugePages
NUMA architecture
other instances on host

23. PGA Health

SELECT

name,

ROUND(value/1024/1024,2) MB

FROM v$pgastat;

Review:

PGA allocated
Maximum allocated
Over-allocation
Workarea executions
TEMP spill behavior
pga_aggregate_target
pga_aggregate_limit

High TEMP activity + poor optimal workarea execution can point toward PGA/workarea pressure.

24. CPU Health

At database level review:

DB CPU
CPU wait
Run queue
Host CPU
Oracle process CPU
Top SQL CPU
Parallel workload

At OS/AIX level additionally assess:

CPU user

CPU sys

CPU idle

Run queue

Physical CPU

Entitlement

%entc

physc

Virtual CPU

Capped/uncapped

Shared processor pool

SMT

This is especially important for the IBM Power/LPAR environments you've been reviewing.

A database CPU problem may actually be:

Oracle workload → LPAR entitlement constraint → processor pool contention

rather than a database parameter problem.

25. Linux/AIX Memory Health

Check:

Physical RAM

Free/available

Swap

Paging

SGA

PGA

HugePages

NUMA

Filesystem cache

OS processes

For Linux:

free -g

vmstat 1 10

numactl --hardware

grep -i huge /proc/meminfo

For AIX:

svmon -G

vmstat 1 10

lparstat -i

lparstat 1 10

Critical question:

Is Oracle being paged/swapped?

That requires immediate investigation.

26. Storage / I/O Health

Review at least:

Read latency
Write latency
IOPS
Throughput
Queue depth
ASM disk latency
Datafile latency
Redo latency
TEMP latency

27. Commit / Redo Performance

Given the commit-behavior work you've been doing, I would give this its own section.

Correlate:

log file sync

log file parallel write

user commits

redo size

redo writes

log switches

storage latency

commit frequency

Interpretation pattern:

High log file sync + low redo-write latency

→ investigate application commit frequency/concurrency.

High log file sync + high log file parallel write

→ investigate redo I/O path.

Never diagnose commit performance using only one wait event.

28. RAC Health

For RAC check:

All instances online
Services correctly distributed
Interconnect health
Global Cache waits
Instance affinity
Service failover
VIP/SCAN
Clusterware resources
Node eviction history

crsctl stat res -t

srvctl status database -d <db_unique_name>

srvctl status service -d <db_unique_name>

olsnodes -n -s

Database:

SELECT inst_id, instance_name, status FROM gv$instance;

29. Data Guard Health

Essential checks:

SELECT

database_role,

open_mode,

protection_mode,

protection_level,

switchover_status

FROM v$database;

Check:

Transport lag
Apply lag
MRP
Archive gaps
Broker configuration
Standby redo logs
Protection mode
Flashback
Apply rate
DR connectivity

30. RMAN Backup Health

Do not stop at:

Backup completed successfully.

Check:

SELECT

start_time,

end_time,

input_type,

status,

output_bytes/1024/1024/1024 output_gb

FROM v$rman_backup_job_details

ORDER BY start_time DESC;

Assess:

Level 0
Level 1
Archive backup
Controlfile backup
SPFILE backup
Backup duration
Failed jobs
Backup growth
Retention policy
Crosscheck
Obsolete backups

31. Corruption Detection

Check:

SELECT * FROM v$database_block_corruption;

Also include appropriate RMAN validation processes in the recovery assurance program.

Any confirmed corruption should be Critical until scope and recoverability are understood.

32. Security Health

At minimum:

SELECT username, account_status, last_login

FROM dba_users

ORDER BY username;

Check:

Open default accounts
Default passwords
DBA grants
ANY privileges
SYSDBA access
Password profiles
Dormant users
Failed logins
Unified auditing
Audit retention
TDE
Wallet/keystore
Listener exposure

33. Listener / Network Health

Check:

lsnrctl status

lsnrctl services

Review:

Service registration
Handler count
Listener errors
TNS timeouts
Connection storms
DNS dependency
SCAN/VIP for RAC
SQL*Net encryption policy
Firewall idle timeout
Connection-pool behavior

Network problems frequently appear to applications as “database slowness”.

34. Database Links

SELECT owner, db_link, username, host

FROM dba_db_links;

Review:

Invalid links
Obsolete target systems
Security exposure
Password handling
Production → lower environment links
Remote dependency risk

35. Materialized Views

SELECT

owner,

mview_name,

last_refresh_type,

last_refresh_date,

staleness

FROM dba_mviews;

Check:

Failed refreshes
Stale MVs
Increasing refresh duration
Broken chains/dependencies

36. Capacity / Growth Analysis

A mature health check should answer:

When will we run out of capacity?

Trend:

Database size
Tablespace growth
ASM consumption
FRA growth
Archive generation
Backup size
TEMP peak
Session growth
CPU
memory
IOPS

Forecast separately for:

30 days

90 days

6 months

12 months

Do not simply extrapolate one abnormal day. Use historical workload trends.

37. Configuration / Parameter Review

Capture:

SELECT name, value, isdefault, ismodified

FROM v$parameter

ORDER BY name;

Review non-default parameters.

Pay special attention to:

sga_target

sga_max_size

pga_aggregate_target

pga_aggregate_limit

processes

sessions

open_cursors

undo_retention

db_files

parallel_max_servers

optimizer_features_enable

optimizer_mode

cursor_sharing

filesystemio_options

disk_asynch_io

db_writer_processes

Do not recommend parameter changes simply because a parameter is non-default.

Every change should have:

Finding → Evidence → Impact → Recommendation → Risk → Rollback

38. Dictionary / Component Health

SELECT comp_id,

comp_name,

version,

status

FROM dba_registry

ORDER BY comp_id;

Anything unexpectedly INVALID requires investigation.

39. Architectural Resilience Review

This separates an operational health check from an architecture assessment.

Check:

Single points of failure

Single DB node?
Single ASM failure group?
Single storage path?
Single listener path?
Single backup destination?
Single geographical location?
DR connectivity dependency?

Recovery design

Document:

RPO

RTO

Backup retention

Archive retention

PITR capability

DR topology

Restore test date/result

Failover test

Switchover test

A backup strategy without demonstrated recovery is incomplete.

40. OEM / Monitoring Health

Validate:

Database target status
Host target
ASM
Cluster
Listener
Data Guard
Tablespace threshold
FRA alert
CPU
filesystem
blocking session
backup
archive gap
scheduler failure alerts

Also review:

stale incidents
alert storms
permanently suppressed alerts
incorrect thresholds
monitoring blackout left active

Recommended Final Health Score

Recommend scoring by domain instead of producing one misleading overall percentage.

Area	Suggested status
Availability	Green / Amber / Red
Backup & Recovery	Green / Amber / Red
Data Guard / HA	Green / Amber / Red
Storage	Green / Amber / Red
Performance	Green / Amber / Red
CPU	Green / Amber / Red
Memory	Green / Amber / Red
SQL	Green / Amber / Red
Statistics	Green / Amber / Red
Objects	Green / Amber / Red
Security	Green / Amber / Red
Patch/Version	Green / Amber / Red
Capacity	Green / Amber / Red
Monitoring	Green / Amber / Red

Then assign each finding:

🔴 Critical

Possible outage, corruption, data-loss or recovery failure.

🟠 High

Material production risk requiring remediation.

🟡 Medium

Health degradation or developing capacity/performance issue.

🔵 Low

Optimization or best-practice improvement.

🟢 Healthy

No material issue observed.

Architect-Level Structure

1. Executive Summary

Overall DB health and top risks.

2. Critical Findings

Anything that threatens:

Availability
Data integrity
Recoverability
Security
Performance

3. Current Architecture

Application

↓

Listener / SCAN

↓

Oracle RAC / Single Instance

↓

CDB

├── PDB1

├── PDB2

└── PDB3

↓

ASM

↓

Storage

4. Performance Findings

Each one reported as:

Observation → Evidence → Root-cause hypothesis → Impact → Recommendation

5. Capacity Findings

Current:

DB → Tablespace → ASM → Filesystem → Storage

plus growth projection.

6. HA/DR & Backup

Primary → Data Guard → RMAN → Restore validation

7. Security / Compliance

Accounts, privileges, auditing, TDE, listener/network.

8. Remediation Plan

Classify actions as:

Immediate → 30 days → 90 days → Strategic

Monday, November 2, 2026