Oracle Database Production Hardening Checklist - Best Practice

 

✅ Oracle Database Production Hardening Checklist (RHEL)

---

1️⃣ OS & Kernel Hardening

🔒 OS Configuration

• Dedicated server / VM for Oracle only
• Correct RHEL version certified for Oracle
• Minimal packages installed (no GUI, dev tools)
• NTP / chrony configured and synced

chronyc tracking

---

⚙ Kernel Parameters

Verify:

sysctl -a | egrep "shm|sem|fs.aio|max_map_count"

Key settings:

• kernel.shmmni, shmmax, shmall
• kernel.sem sized for workload
• fs.aio-max-nr sufficient
• vm.swappiness = 1
• vm.zone_reclaim_mode = 0

✅ Persist in /etc/sysctl.conf

---

❌ Transparent Huge Pages (THP)

• THP set to never

cat /sys/kernel/mm/transparent_hugepage/enabled

---

2️⃣ CPU & NUMA Hardening

🧠 NUMA Validation

lscpu | grep NUMA

numactl --hardware

• NUMA detected and considered
• Oracle memory interleaving enabled
• No CPU pinning unless justified

Recommended:

numactl --interleave=all

---

🔥 CPU Oversubscription

• No CPU quota throttling (VM / container)
• vCPU ≤ physical CPU
• Hyper‑threading understood and planned

---

3️⃣ Memory Hardening

📦 SGA & PGA

• SGA < per‑NUMA node memory
• PGA target sized (avoid PGA spills)
• HugePages configured (if applicable)

Check HugePages:

grep Huge /proc/meminfo

---

🚨 Swap

• Swap enabled but minimal
• No Oracle paging to swap

vmstat 1 5

---

4️⃣ Storage & IO Hardening

💾 Disk Separation (Mandatory)

• Datafiles
• Redo logs
• Archive logs / FRA
• Temp
• Backups

No sharing of redo + data.

---

⏱ IO Latency Targets

IO Type	Target
Redo writes	< 5 ms
Data reads	< 15 ms
Temp IO	< 20 ms

Verify:

iostat -xm 1 5

---

🧩 ASM (If Used)

• Diskgroup redundancy defined
• Rebalance power controlled
• No mixed latency tiers in same DG

---

5️⃣ Oracle Parameter Hardening

✅ Mandatory Parameters

show parameter processes;

show parameter sessions;

show parameter open_cursors;

Ensure:

• open_cursors ≥ 2–3× app need
• processes sized for peak
• sessions = processes * 1.5

---

⚡ Performance Safety

• cursor_sharing = FORCE (only if needed)
• result_cache_mode evaluated
• optimizer_adaptive_features controlled
• parallel_degree_policy understood

---

6️⃣ Security & Access Hardening

🔐 OS Level

• Oracle user non‑login shell (if allowed)
• SSH key‑based access
• No password reuse
• Root access logged

---

🔑 Database Level

• Password profiles enforced
• Default accounts locked
• Strong SYS password
• ADMIN roles minimized

SELECT username FROM dba_users WHERE account_status!='OPEN';

---

7️⃣ Resource Management (Very Important)

🎛 Oracle Resource Manager

• Enabled in production
• Separate OLTP / Batch consumers
• CPU runaway prevention

SHOW PARAMETER resource_manager_plan;

---

8️⃣ Backup & Recovery Hardening

🛡 RMAN

• Daily incremental
• Weekly full
• Archive log backups
• Controlfile autobackup ON

SHOW ALL;

---

🔁 Restore Testing

• Restore tested quarterly
• PITR validated
• Backup success monitored

---

9️⃣ Monitoring & Alerting

📊 OS Monitoring

• CPU, load
• Memory pressure
• IO latency
• Filesystem usage

---

🧠 Oracle Monitoring

• AWR enabled
• ASH accessible
• Tablespace growth alerts
• Session thresholds

---

🔍 Baselines

• CPU baseline captured
• IO latency baseline captured
• AWR baseline saved post go‑live

---

🔟 Patch & Lifecycle Management

🧩 Oracle

• Quarterly RU applied
• OPatch version updated
• Patch rollback plan ready

---

🧩 OS

• Kernel patches tested
• Reboot procedure documented
• Firmware audited (if bare metal)

---

1️⃣1️⃣ High Availability & DR

• Data Guard configured (if required)
• DG lag alerts
• Switchover tested
• DR RTO/RPO documented

---

1️⃣2️⃣ Documentation & Audit Readiness

• SOPs (CPU, IO, Outage)
• RCA template
• Architecture diagram
• Capacity forecast
• CMDB updated

---

✅ Final “GO‑LIVE” Gate Criteria

✔ Stable CPU baseline
✔ Disk latency within SLA
✔ RMAN recoverable
✔ Resource controls enabled
✔ Security controls enforced

---

📌 Pro Tip (Real‑World)

Most production outages happen due to lack of resource controls, not lack of hardware.

Oracle Database CPU Performance Troubleshooting

 

Oracle Database CPU Performance Troubleshooting (RHEL)

1. Typical CPU Performance Symptoms

• High CPU utilization (near 100%)
• Load average increases rapidly
• Slow query response while disk latency is normal
• AWR shows:
  • DB CPU
  • CPU time
  • resmgr:cpu quantum
• OS %idle very low and %wa low (CPU, not IO)

---

2. Step‑1: Confirm CPU Pressure at OS Level

✅ Check load vs CPU cores

uptime

nproc

lscpu | egrep 'CPU\(s\)|Core|Socket|Thread'

Interpretation

• Load average ≤ CPU cores → system OK
• Load average >> CPU cores → CPU contention

Example:

load average: 48.21, 45.12, 40.90
CPU cores: 24

➡️ CPU saturation confirmed

---

3. Step‑2: Identify CPU Utilization & Wait

✅ top (quick overview)Focus on top line:

top

%Cpu(s): 92.4 us, 3.1 sy, 0.2 ni, 2.0 id, 1.5 wa

CPU‑bound system

• %us + %sy high
• %id near 0
• %wa low

---

✅ vmstat – verify run queue

vmstat 1 10

Key columns:

Column	Meaning
r	Runnable processes
us	User CPU
sy	System CPU
id	Idle
wa	IO wait

If r > CPU cores consistently → CPU contention

---

4. Step‑3: Identify Top CPU Consumers

✅ Per‑process CPU usage

ps -eo pid,ppid,cmd,%cpu,%mem --sort=-%cpu | head -20

Look for:

• Oracle foreground sessions
• Oracle background processes (ora_*)
• Non‑DB processes stealing CPU

---

✅ Real‑time per‑process view

top -H -p $(pgrep -d',' -f ora_)

This shows Oracle threads consuming CPU.

---

5. Step‑4: Per‑CPU & Core Imbalance

✅ mpstat (very important on NUMA)

mpstat -P ALL 1 5

Look for:

• Some CPUs at 100%
• Others mostly idle

➡️ Indicates:

• NUMA imbalance
• CPU affinity or pinning problem

---

6. Step‑5: Context Switching & Syscall Overhead

✅ pidstat – CPU & context switches

pidstat -u -w 1 5

High:

• cswch/s (context switches)
• nvcswch/s

➡️ Too many active sessions or excessive parsing

---

7. Step‑6: Oracle Side – Is Database the CPU Consumer?

✅ CPU usage inside Oracle

SELECT stat_name, value

FROM v$sysstat

WHERE stat_name IN ('CPU used by this session','DB CPU');

Check ratio:

• DB CPU close to total DB time → CPU bottleneck
• IO waits dominant → not CPU issue

---

8. Step‑7: Identify Oracle Sessions Using CPU

✅ Active CPU sessions

SELECT s.sid, s.serial#, s.username,

s.program, s.status,

p.spid OS_PID

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND s.status = 'ACTIVE'

AND s.wait_class = 'CPU'

ORDER BY s.last_call_et DESC;

🔁 Correlate SPID with OS:

top -p <spid>

---

9. Step‑8: SQL Responsible for CPU Usage

✅ Top CPU SQL

SELECT sql_id,

cpu_time/1000000 cpu_sec,

executions,

cpu_time/executions avg_cpu

FROM v$sql

ORDER BY cpu_time DESC

FETCH FIRST 10 ROWS ONLY;

Red flags:

• High avg_cpu
• Low executions but high total CPU
• Cartesian joins
• Missing indexes

---

10. Step‑9: Run Queue vs CPU Throttling

✅ Check cgroups / VM CPU limits

cat /sys/fs/cgroup/cpu/cpu.cfs_quota_us

cat /sys/fs/cgroup/cpu/cpu.cfs_period_us

Quota < total cores → CPU throttling

---

11. Step‑10: Oracle Resource Manager (Very Common)

✅ Check RM activation

SHOW PARAMETER resource_manager_plan;

✅ CPU waits due to RM

SELECT event, total_waits, time_waited/100 time_sec

FROM v$system_event

WHERE event LIKE 'resmgr%';

If high:

• Sessions are being CPU throttled intentionally

---

12. Common CPU Root Causes

Cause	Symptom
Bad execution plan	Single SQL consumes most CPU
Missing index	Full scans
High parse rate	Hard parsing
Too many sessions	Context switching
CPU oversubscription	VM / container CPU cap
NUMA imbalance	Some CPUs overloaded

---

13. Immediate Mitigation Actions

✅ Short‑term:

• Kill runaway sessions
• Reduce parallelism
• Disable unnecessary jobs
• Temporarily increase CPU shares

✅ Medium‑term:

• SQL tuning (indexes, hints)
• Bind variables
• Increase cursor cache
• Enable Result Cache (where valid)

✅ Long‑term:

• SQL baselines
• CPU capacity increase
• NUMA pinning
• Resource Manager redesign

---

14. One‑Shot CPU Triage Command Set

uptime

vmstat 1 5

mpstat -P ALL 1 5

top -H -p $(pgrep -d',' -f ora_)

Oracle:

SELECT sql_id, cpu_time/1000000 cpu_sec

FROM v$sql

ORDER BY cpu_time DESC FETCH FIRST 5 ROWS ONLY;

---

15. CPU vs IO – Golden Rule

Observation	Root Cause
High %us, low %wa	CPU bottleneck
High load, CPUs idle	IO bottleneck
High DB CPU in AWR	SQL tuning required
RM waits	Resource controls

Production Server/Database/Application troubleshooting Runbook for Issue like CPU, Memory, I/o , Kernel

 

0️⃣ Runbook Objectives

This runbook helps you:

✅ Quickly identify CPU, I/O, memory, or process issues
✅ Correlate OS metrics with database / application symptoms
✅ Avoid random commands during incidents
✅ Reach root cause, not just symptom relief

---

1️⃣ Incident Intake (ALWAYS FIRST)

Before touching the system, collect:

Question	Why
What is impacted? (DB, app, batch, login)	Scope
Since when?	Time correlation
All users or subset?	Severity
Any recent changes?	Deploy / patch
Error messages?	Symptom confirmation

📌 Do not skip this step. It saves 30–40% time later.

---

2️⃣ High‑Level System Health Snapshot (30 seconds)

2.1 Uptime & Load

uptime

Focus on:

• Load average vs CPU cores
• Sudden spike timeframe

✅ Load > CPU count → investigate
✅ Load + low CPU → likely I/O wait

---

2.2 Disk Space (quick sanity)

df -h

🚨 Any filesystem ≥ 90% → fix immediately

---

3️⃣ Real‑Time CPU & Memory View (top)

top

3.1 CPU Line Interpretation

%Cpu(s): 12 us, 5 sy, 0 ni, 60 id, 23 wa

Metric	Meaning
us	App/SQL CPU
sy	Kernel CPU
id	Idle
wa	I/O wait

🚨 wa > 15% → jump to I/O section

---

3.2 Process Area

• Sort by CPU: Shift + P
• Sort by MEM: Shift + M

Note:

• PID
• %CPU
• %MEM
• COMMAND

👉 Take PID(s) for next step.

---

4️⃣ Exact Process Diagnosis (ps)

✅ Master Command (use this by default)

ps -eo user,pid,ppid,stat,%cpu,%mem,etime,wchan,comm --sort=-%cpu

What to Look For

Symptom	Indicator
High CPU	%cpu, R state
Hung process	D state
Long running	High etime
I/O wait	wchan = io_schedule
Zombie	Z

---

🔴 Critical: Find Blocked (I/O) Processes

ps -eo pid,stat,wchan,etime,comm | awk '$2 ~ /D/'

If you see:

• ora_*
• java
• mysqld

➡️ Storage / filesystem issue almost certain

---

5️⃣ Kernel Stress View (vmstat)

vmstat 1 5

Key Columns

Column	Meaning
r	Runnable processes
b	Blocked (I/O wait)
si/so	Swap usage
wa	I/O wait

Interpretation Rules

Observation	Meaning
b > 0	Processes stuck in I/O
wa high	Disk latency
si/so > 0	Memory pressure
r > CPU cores	CPU contention

---

6️⃣ Storage Diagnosis (iostat)

iostat -xz 1 5

Critical Metrics

Metric	Bad Threshold
%util	> 80%
await	> 20 ms
await >> svctm	Queueing issue

Conclusions

Pattern	Root Cause
High await + D state	Storage latency
High util	Disk saturation
NFS disks slow	Network / mount issue

🚨 DBWR / LGWR in D state = immediate escalation

---

7️⃣ Memory Focused Check

ps -eo pid,stat,rss,vsz,%mem,comm --sort=-rss | head

If:

• RSS very high
• Swap active

➡️ Tune memory / restart leaking service

---

8️⃣ Oracle / Database‑Specific Quick Checks

8.1 Oracle Processes

ps -eo pid,stat,%cpu,wchan,etime,comm | grep ora_

8.2 Dangerous Signs

Process	Issue
ora_dbw* in D	Datafile I/O
ora_lgwr in D	Redo disk
Many ora_w* in D	Parallel I/O stall

➡️ Do NOT bounce DB blindly

---

9️⃣ Decision Matrix (Very Important)

Observation	Action
High CPU, no D	Tune app/SQL
High wa + D	Storage escalation
Z processes	Restart parent
Swap active	Add memory / reduce usage
Disk full	Cleanup immediately

---

🔔 Escalation Triggers

Escalate to Storage / Infra when:

• D state persists > 5 minutes
• await > 50 ms
• DB background processes blocked

Escalate to App/DB Team when:

• CPU us > 80%
• Single PID dominating CPU
• SQL identified as hot spot

---

✅ One‑Glance Incident Command Set

uptime

top

ps -eo pid,stat,%cpu,%mem,wchan,comm --sort=-%cpu

vmstat 1 5

iostat -xz 1 5

---

🧠 Golden Rule (Remember This)

High load does NOT always mean CPU problem.
D state + wa = storage until proven otherwise.