# 📊 Oracle Database Health Monitoring SOP
## 🎯 Objective
To proactively monitor Oracle database health, detect issues early, and ensure high availability, performance, and data integrity across environments (Standalone / RAC / Data Guard).
---
# 🧭 1. Daily Health Check Overview
| Category | Check | Frequency |
| ----------- | --------------------------------- | --------- |
| Instance | Uptime, Status | Daily |
| Database | Role, Open Mode | Daily |
| PDB | Open Status | Daily |
| Backup | Last RMAN Backup | Daily |
| Storage | ASM, Tablespace | Daily |
| Performance | Active Sessions | Daily |
| DR | Data Guard Sync | Daily |
| Integrity | Block Corruption, Invalid Objects | Daily |
---
# 🟢 2. Instance & Database Status
## ✅ Database Uptime
```sql
SELECT
'UPTIME: ' ||
FLOOR(SYSDATE - STARTUP_TIME) || ' days, ' ||
FLOOR(MOD((SYSDATE - STARTUP_TIME) * 24, 24)) || ' hours, ' ||
FLOOR(MOD((SYSDATE - STARTUP_TIME) * 24 * 60, 60)) || ' minutes'
FROM GV$INSTANCE;
```
### ✔️ Expected:
* Should be stable (no frequent restarts)
### 🚨 Alert If:
* Unexpected restart observed
---
## ✅ Database Role & Status
```sql
SELECT database_role, name, db_unique_name, open_mode, log_mode,
flashback_on, protection_mode, protection_level
FROM gv$database;
```
### ✔️ Expected:
* PRIMARY → READ WRITE
* STANDBY → READ ONLY / MOUNTED
---
# 🧩 3. PDB Health Check
## ✅ PDB Status
```sql
SELECT inst_id, con_id, name, open_mode, restricted,
TO_CHAR(open_time,'DD-MON-YYYY HH24:MI:SS') open_time
FROM gv$pdbs;
```
### ✔️ Expected:
* All required PDBs in **READ WRITE**
---
## ✅ PDB Size
```sql
SELECT con_id, name, open_mode,
total_size/1024/1024/1024 "PDB_SIZE_GB"
FROM v$pdbs;
```
---
# 💾 4. Backup Monitoring
## ✅ Last Backup Status
```sql
SELECT
'LAST BACKUP: ' ||
INPUT_TYPE || ' - ' ||
STATUS || ' - ' ||
TO_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI') || ' to ' ||
TO_CHAR(END_TIME, 'DD-MON-YYYY HH24:MI')
FROM V$RMAN_BACKUP_JOB_DETAILS
WHERE ROWNUM = 1;
```
### ✔️ Expected:
* Status = COMPLETED
### 🚨 Alert If:
* FAILED / RUNNING too long / No backup
---
# ⚡ 5. Performance Monitoring
## ✅ Active Sessions
```sql
SELECT count(*), inst_id, status
FROM gv$session
GROUP BY inst_id, status;
```
### ✔️ Expected:
* Balanced ACTIVE vs INACTIVE sessions
### 🚨 Alert If:
* Too many ACTIVE sessions → Possible load issue
---
# 💽 6. Storage Monitoring
## ✅ ASM Diskgroup Usage
```sql
SELECT NAME,
TOTAL_MB/1024 TOTAL_GB,
FREE_MB/1024 FREE_GB,
(FREE_MB/TOTAL_MB)*100 FREE_PER
FROM v$asm_diskgroup;
```
### ✔️ Threshold:
* Free space > 20%
---
## ✅ Tablespace Usage
```sql
SELECT file_name, tablespace_name, bytes/1024/1024 MB, status
FROM dba_data_files;
```
---
## ✅ TEMP Tablespace
```sql
SELECT tablespace_name,
GB_total,
GB_used,
GB_free
FROM ...
```
---
## ✅ UNDO Tablespace
```sql
SELECT tablespace_name,
tot_ts_size_GB,
free_ts_size_GB,
usedspace
FROM ...
```
---
# 🔁 7. Data Guard Monitoring
## ✅ Archive Sync Status
```sql
SELECT a.thread#, b.last_seq, a.applied_seq,
b.last_seq-a.applied_seq ARC_DIFF
FROM ...
```
### ✔️ Expected:
* ARC_DIFF ≈ 0
### 🚨 Alert If:
* Lag increasing
---
## ✅ Data Guard Stats
```sql
SELECT name, value, unit, time_computed
FROM v$dataguard_stats;
```
---
# 🔒 8. Locks & Blocking Sessions
## ✅ Blocking Locks
```sql
SELECT * FROM v$lock WHERE block != 0;
```
### 🚨 Action:
* Identify blocking session
* Kill if required
---
# ⚠️ 9. Database Integrity Checks
## ❌ Block Corruption
```sql
SELECT * FROM v$database_block_corruption;
```
### 🚨 Action:
* Restore block using RMAN
---
## ❌ Invalid Objects
```sql
SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;
```
### 🚨 Action:
```sql
EXEC UTL_RECOMP.RECOMP_SERIAL();
```
---
# 🚨 10. Alert Threshold Summary
| Metric | Warning | Critical |
| ---------------- | ------------ | ------------------- |
| ASM Free Space | < 20% | < 10% |
| Tablespace Usage | > 80% | > 90% |
| Active Sessions | Sudden spike | System slowdown |
| Data Guard Lag | > 5 logs | Continuous increase |
| Backup | Delayed | Failed |
---
##################
If lag increases, you investigate immediately.
Quick Takeaway / Summary