Network Troubleshooting

Overview

Inevitably in the life of a DBA you end up having to check if a device, server or database is available through a network. What follows are time tested techniques to debug a network connection issues.

Procedure

• Can the server be reached?
  ping <hostname or IP>
  ping mydbsvr
• Can the port be reached?
  telnet <ip or hostname> <portnumber>
  telnet mydbsvr 1521

  • If successful you will get a blank screen (waiting for telnet commands).
  • If not successful it will look like this:
    Connecting to mydbsvr ... Could not open connection to host...

• Can you connect via Oracle's TNS (Transparent Network Substrate)?
  tnsping <tnsnames.ora service name>
  %ORACLE_HOME%\bin\tnsping oradb1
• Can you connect via Oracle's sqlplus?
  sqlplus.exe <username>/<password>@<tnsnames.ora service name>
  %ORACLE_HOME%\bin\sqlplus.exe scott/tiger@oradb1

Suggestions

• Turn off any network firewall rules that are interfering.
• Turn off any host firewalls that are interfering.
• Turn off any any virus scanning options that blocking the port (1521 etc.).
• Paping.exe can be used to ping ports much easier than using telnet or when telnet is disabled.

Direct Connection Examples

 sqlplus sys/go@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)
    (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradb)(INSTANCE_NAME=oradb1)
    (SERVER=DEDICATED)))' as sysdba

 sqlplus sys/go@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)
    (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradb)(INSTANCE_NAME=oradb2)
    (SERVER=DEDICATED)))' as sysdba

 sqlplus sys/go@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lnx03)
    (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradb_sb)(INSTANCE_NAME=oradb_sb)
    (SERVER=DEDICATED)))' as sysdba

Lines split for ease of viewing.