# Oracle Dba Networking Connectivity

> USE THIS SKILL WHENEVER the user mentions the Oracle listener (lsnrctl, listener.ora, listener restart/reload/start/stop, static vs dynamic registration), tnsnames.ora / sqlnet.ora / EZConnect, tnsping, service registration (ALTER SYSTEM REGISTER, service_names, local_listener/remote_listener, PMON/LREG), connectivity testing or "cannot connect to the database", Connection Manager (CMAN), SDU / SEND_BUF / RECV_BUF / network throughput tuning, or ANY of the connection errors ORA-12541 (no listener), ORA-12514 (unknown service), ORA-12505 (unknown SID), ORA-12516/12519/12520 (no handler), ORA-12170 (TNS timeout), ORA-12537/12547 (lost contact), ORA-12560 (protocol adapter), or TNS-03505 (cannot resolve name) — EVEN IF they do not name the specific task. Covers listener lifecycle, listener.ora + tnsnames.ora maintenance, static/dynamic service registration, layered ORA-12xxx diagnosis, and the plan-only Tier-3 runbooks for CMAN setup and network performance/SDU/buffer tuning. Follows the oracle-dba-common contra

- Skill: `shreyas70773/oracle-dba-networking-connectivity` (Agent Skill, multi-file: 20 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-networking-connectivity`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-networking-connectivity/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Productivity
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-networking-connectivity

---

# Oracle DBA - Networking & Connectivity

Oracle Net stack management for Oracle 19c and 23ai (EE; single_instance and Data
Guard; on-prem): the listener lifecycle, `listener.ora` / `tnsnames.ora`
maintenance, static and dynamic service registration, connectivity testing, and
layered ORA-12xxx connection-error diagnosis. Everything self-executing is
**T1 (observe)** or **T2 (reversible, dry-run-default)**. **Connection Manager
(CMAN) setup** and **deep network / SDU / buffer tuning** are **T3** and live only
in `references/runbooks.md` — they print commands for a human and never self-execute.

In-DB SQL (service registration, connection diagnostics) runs through the SQLcl MCP
saved connection **`dba_ai_conn`** ("sqlcl-mcp"). Host operations (`lsnrctl`,
`srvctl`, `tnsping`, editing `*.ora`) cannot go through SQLcl MCP (restrict level 4 =
no host commands) and ship as behaviour-equivalent `.sh` / `.ps1` **scripts** (to
later front a guarded `oracle-dba-ops` MCP).

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Listener restart / reload / start / stop | L1 | T2 | `scripts/listener_restart.sh` / `.ps1` (`restart\|reload\|start\|stop`) | yes — `start` already-up -> noop; `restart` converges to "up"; `stop` already-down -> noop |
| Listener status | L1 | T1 | `scripts/listener_restart.sh status` / `.ps1 status` | yes — read-only (`lsnrctl`/`srvctl status`) |
| tnsping / connectivity test | L1 | T1 | `scripts/tnsping_test.sh` / `.ps1` (`--alias`, `--tcp`, `--connect`) | yes — read-only layered probe (tnsping + optional TCP + DB connect via dba_ai_conn) |
| Listener creation & configuration (incl. STATIC SID_LIST) | L2 | T2 | `scripts/listener_create_config.sh` / `.ps1` (backup + reload) | yes — listener.ora already matching host/port (+SID) -> noop |
| tnsnames.ora maintenance (add/update/remove/list) | L2 | T2 | `scripts/tnsnames_maintenance.sh` / `.ps1` (`add\|update\|remove\|list`) | yes — identical alias -> noop; missing-on-remove -> noop; `list` is T1 read-only |
| Static vs dynamic service registration — status | L2 | T1 | `scripts/service_registration.sh status` / `.ps1 status` + `sql/service_registration_status.sql` | yes — read-only |
| Static vs dynamic service registration — force dynamic (`ALTER SYSTEM REGISTER`) | L2 | T2 | `scripts/service_registration.sh force` / `.ps1 force` + `sql/force_service_registration.sql` | yes — benign re-publish; persists nothing; no-op effect on re-run |
| Connection error diagnosis (ORA-12xxx) — host triage | L2 | T1 | `scripts/connection_diagnose.sh` / `.ps1` (tnsping -> lsnrctl -> TCP -> in-DB) | yes — read-only report (findings never mutate) |
| Connection error diagnosis (ORA-12xxx) — in-DB facts | L2 | T1 | `sql/connection_diagnostics.sql` (via dba_ai_conn) | yes — read-only |
| Connection Manager (CMAN) setup | L3 | **T3** | `references/runbooks.md#cman-setup` | partial — `cman.ora` edits reversible; conceptual setup is one-shot/design |
| Network performance / SDU / buffer tuning | L3 | **T3** | `references/runbooks.md#network-tuning` | partial — each `*.ora` change reversible from backup; tuning is iterative/judgement |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. All in-DB
  work (`sql/service_registration_status.sql`, `sql/force_service_registration.sql`,
  `sql/connection_diagnostics.sql`, and the `--connect` smoke test) runs through it
  via `connect_db` / `Connect-Db`. Never SYS/SYSTEM — except inside a T3 runbook that
  explicitly states `AS SYSDBA`, supplied interactively by the operator. `force`
  needs the `ALTER SYSTEM` privilege on the connected account.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere.** Host tools (`lsnrctl`, `srvctl`, `tnsping`) use OS authentication and
  need no credentials. `listener.ora` / `tnsnames.ora` hold connect descriptors, never
  passwords; an argument that looks like `user/pass@db` or `password=` is rejected
  with `ERR_SECRET` (8).
- **Helpers are sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **`TNS_ADMIN`** must resolve (env `TNS_ADMIN`, else `$ORACLE_HOME/network/admin` via
  `--oracle-home` / `-OracleHome` / `ORACLE_HOME`). On Oracle Restart / Grid
  Infrastructure-managed listeners, set `ODB_USE_SRVCTL=1` (`--srvctl` / `-Srvctl`) so
  CRS does not auto-restart the listener underneath you.
- **Logging**: structured `key=value` lines to the canonical paths
  `/var/log/oracle-dba/networking/networking.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/networking/networking.log` (Windows); skill segment
  `networking`.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. Every config-file edit takes a timestamped `.bak` first.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. T2
scripts default to `--dry-run`; pass `--execute` to act. See each script's `--help`.

### Listener restart / reload / start / stop / status
- **Trigger**: apply a `listener.ora` change (`reload`), recover a down listener
  (`start`), bounce after a hang (`restart`), or audit (`status`).
- **Precheck**: control tool present (`lsnrctl`, or `srvctl` if `ODB_USE_SRVCTL=1`);
  valid listener name; current up/down state probed.
- **Action**: `scripts/listener_restart.sh {status|reload|start|stop|restart} --listener LSNR [--execute]` / `.ps1`. `reload` is the least-disruptive way to apply config (re-reads `listener.ora` without dropping endpoints).
- **Postcheck**: end-state verified — `up` for reload/start/restart, `down` for stop.
- **Rollback note**: a listener is restartable — re-run `start`/`restart`. Established sessions survive a restart; only new inbound connects pause briefly.

### tnsping / connectivity test
- **Trigger**: first responder for "cannot connect" / ORA-12541 / ORA-12514 / ORA-12170; pre-change smoke test.
- **Precheck**: valid `--alias` (or `--connect` for a DB-only test).
- **Action**: `scripts/tnsping_test.sh --alias ALIAS [--tcp] [--connect]` / `.ps1`. Layer 1 tnsping, optional Layer 2 raw TCP to the resolved host:port, optional Layer 3 DB login via `dba_ai_conn`.
- **Postcheck**: n/a — emits `tnsping_ok` / `db_connect_ok` metrics; non-zero exit only if a requested probe failed.
- **Rollback note**: none — read-only.

### Listener creation & configuration
- **Trigger**: stand up a new named listener, change its host/port, or add a STATIC `SID_LIST` (e.g. to expose a service before the instance is OPEN, for tools that need static SID).
- **Precheck**: validated inputs (no `.ora` injection); `TNS_ADMIN` writable; idempotency probe of existing block.
- **Action**: `scripts/listener_create_config.sh --listener LSNR --host H --port P [--static-sid SID --static-service SVC --oracle-home OH] [--execute]` / `.ps1` (`-DbHost`, `-StaticSid`, ...). Backs up `listener.ora`, rewrites the block atomically, `lsnrctl reload`.
- **Postcheck**: `lsnrctl status` answers and (if static) advertises the SID/service.
- **Rollback note**: restore the printed `listener.ora.<ts>.bak` and `lsnrctl reload`.

### tnsnames.ora maintenance
- **Trigger**: add/update a client alias to a (host, port, service), remove a stale alias, or list current aliases.
- **Precheck**: validated alias/host/service/port; `TNS_ADMIN` writable; idempotency probe.
- **Action**: `scripts/tnsnames_maintenance.sh {add|update|remove|list} --alias A [--host H --port P --service S] [--execute]` / `.ps1` (`-DbHost`, ...). Backs up `tnsnames.ora`, rewrites the alias block atomically.
- **Postcheck**: add/update validated with `tnsping <alias>` returning OK.
- **Rollback note**: restore the printed `tnsnames.ora.<ts>.bak`. (No service restart needed — clients re-read the file on next connect.)

### Static vs dynamic service registration
- **Trigger**: confirm how a service is registered (status), or force an immediate dynamic re-registration after a listener restart / `local_listener` change instead of waiting ~60s (force).
- **Precheck**: `dba_ai_conn` reachable; for `force`, instance is `OPEN` and account has `ALTER SYSTEM`.
- **Action**: `scripts/service_registration.sh status` (T1) or `force --execute` (T2) / `.ps1`. `force` runs `ALTER SYSTEM REGISTER` via `sql/force_service_registration.sql`.
- **Postcheck**: instance still `OPEN`; verify on host with `lsnrctl status <listener>` — dynamic services show `READY`/`BLOCKED`, static show `UNKNOWN`.
- **Rollback note**: none needed — `REGISTER` persists nothing and only re-publishes configured services.

### Connection error diagnosis (ORA-12xxx)
- **Trigger**: a client reports ORA-12541/12514/12505/12516/12519/12520/12170/12560 or TNS-03505 and you must localise the failing layer.
- **Precheck**: none (read-only); valid `--alias` / `--listener` if supplied.
- **Action**: `scripts/connection_diagnose.sh --alias A [--listener L] [--tcp] [--no-indb]` / `.ps1`. Probes tnsping -> `lsnrctl status` -> raw TCP -> in-DB facts (`sql/connection_diagnostics.sql`) and prints per-layer findings + interpretation hints.
- **Postcheck**: n/a — surfaces `finding=...` log lines; the run itself exits 0 (it is a report).
- **Rollback note**: none — read-only. Remediate with the listener / registration / tnsnames tasks above, or escalate deep cases to the T3 runbooks.

## Tier-3 runbooks

**Connection Manager (CMAN) setup** and **network performance / SDU / buffer tuning**
are Tier 3 and live in **`references/runbooks.md`**: `#cman-setup`, `#network-tuning`.
These runbooks **PRINT exact `cman.ora` / `sqlnet.ora` / `listener.ora` / `tnsnames.ora`
edits and `cmctl` / `lsnrctl` commands for a human operator and NEVER self-execute.**
They are gated by `require_approval_token` / `Require-ApprovalToken`: an automated
agent's only sanctioned action is to print the relevant section and stop with
`ERR_APPROVAL` (6) until a human supplies a real change ticket/token (`--token <TICKET>`
or `ODB_APPROVAL_TOKEN`; placeholders are rejected). SYSDBA is a T3-only,
interactively-supplied exception. When in doubt, runbook.

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas.