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% normal80–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:
NOSPACEERRCNTSSOLDERRCNTMAXQUERYLEN- 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 readdb file scattered readdirect path readdirect path write templog file synclog file parallel writeenq: TX - row lock contentionbuffer busy waitsread 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_targetpga_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
