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

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.