Interview Question 23 : What troubleshooting steps you will follow when user complaints about connectivity issue ?

 When a user reports a database connectivity issue, I follow a structured, layered troubleshooting approach so I can isolate the problem quickly without jumping to conclusions. The goal is to identify whether the issue is user‑side, network‑side, listener‑side, or database‑side.

Below is my real‑world DBA troubleshooting checklist, explained step by step.

---

Database Connectivity Troubleshooting Steps

---

1️⃣ Gather Basic Information (Do First)

Before touching the system, I ask targeted questions:

• Which application or user is affected?
• Error message (ORA‑error, TNS‑error, timeout, etc.)
• From where is user connecting? (App server, laptop, batch job)
• Is it one user, multiple users, or all users?
• When did it start? Sudden or intermittent?
• Has anything changed recently? (patch, password change, server reboot)

📌 This helps narrow down whether it's a local or global issue.

---

2️⃣ Check if Database Is Up

First, I verify database status on the server:

ps -ef | grep pmon

or

sqlplus / as sysdba

Then:

SELECT status FROM v$instance;

✅ Expected: OPEN

Possible findings:

• DB down → startup required
• DB mounted or restricted → user connections blocked

---

3️⃣ Validate Listener & Network Services

Most connectivity issues are listener or network related.

Check listener status

lsnrctl status

Ensure:

• Listener is running
• Correct SERVICE_NAME is registered
• No errors like:
  • TNS-12541: no listener
  • TNS-12514: listener does not know of service

Restart listener (if safe)

lsnrctl stop

lsnrctl start

---

4️⃣ Test Local Connectivity

To rule out OS/network issues:

sqlplus user/pass@localhost:1521/service

or:

tnsping service_name

✅ If local connection works but remote fails → likely network firewall or routing issue

---

5️⃣ Check Database Service Registration

Inside database:

SHOW PARAMETER service_names;

SELECT name FROM v$services;

If service not registered:

ALTER SYSTEM REGISTER;

Also check:

SHOW PARAMETER local_listener;

---

6️⃣ Review Listener Logs

Listener log path:

$ORACLE_HOME/network/log/listener.log

Look for:

• Connection refusals
• Service handler issues
• High connection rate
• Service UNKNOWN status

---

7️⃣ Verify User Account Status

If only specific users are affected:

SELECT username, account_status

FROM dba_users

WHERE username = 'USER1';

Check for:

• LOCKED
• EXPIRED
• Password changed recently

Unlock if needed:

ALTER USER user1 ACCOUNT UNLOCK;

---

8️⃣ Check Resource & Session Limits

Connectivity can fail if limits are hit:

SELECT resource_name, current_utilization, limit_value

FROM v$resource_limit

WHERE resource_name IN ('processes','sessions');

If limits reached:

• Increase parameters
• Kill runaway sessions
• Restart application tier if required

---

9️⃣ Validate Network & Firewall

If DB and listener look fine:

• Confirm DB port (1521) open
• Check firewall or load balancer changes
• Coordinate with network team

Typical errors:

• ORA-12170: TNS connect timeout
• Random disconnections

---

🔟 Check Application & Connection Pool

For application issues:

• Connection pool exhaustion
• Stale connections
• Incorrect TNS string
• Hardcoded passwords

Fix:

• Restart app
• Correct JDBC/tns configuration
• Clear pool

---

1️⃣1️⃣ Review Alert Log

Always check database alert log:

$ORACLE_BASE/diag/rdbms/*/*/trace/alert*.log

Look for:

• ORA‑errors
• PMON cleanup issues
• Instance restarts
• Network errors

---

Summary Flow (Quick Mental Model)

User → App → Network → Listener → DB → Service → User Account

---

Common Scenarios & Root Causes

Symptom	Likely Cause
All users down	DB or listener down
One user down	Account locked / password
App down, SQL works	Pool / config issue
Intermittent	Network / firewall
ORA‑12514	Service not registered
ORA‑12170	Timeout / network

---

Interview‑Ready Answer (Concise)

“When a user reports connectivity issues, I first gather error details and scope, then verify database status, listener health, service registration, and user account state. I test local connectivity, review listener and alert logs, check resource limits, and finally coordinate with network or application teams if needed.”

---

DBA Best Practice

✅ Always fix root cause, not just restart
✅ Document incident for future prevention
✅ Add monitoring for:

• Listener
• Connection count
• Process/session usage