How to verify oracle is working and communicating?

[rev.dennis]

What I found out to test oracle connectivity you would do the following from a machine that has Oracle loaded.

 

The three main things to check for when diagnosing remote database connection issues are the machine, the listener, and the database. The utilities that can be used to test each one of these include ping, tnsping, and a database connection.

 

The ping utility is used to test the connectivity to a remote machine. ping will indicate whether a remote server is accessible and responding. If the ping command indicates that a machine cannot be accessed, the other connectivity tests will also fail.

D:> ping asgard

Pinging asgard.zahsystems.com with 32 bytes of data:

Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254

 

Once connectivity to the host is confirmed with ping, the next connection to test is the listener. The tnsping utility is used to determine whether or not an Oracle service can be successfully reached. If a connection can be established from a client to a server (or server to server), tnsping will report the number of milliseconds it took to reach the remote service. If unsuccessful, a network error will be displayed. However, tnsping will only report if the listener process is up and provides no indication of the state of the database.

D:> tnsping

The “net service name” must exist in the tnsnames.ora file (In the example below GRACELANV8_GRA901m is the hostname in tnsnames.ora)

D:> tnsping GRACELANV8_GRA901m 5

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=gracelan)
(PORT=1525)) (CONNECT_DATA= (SID=GRA901m)))
OK (80 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)

 

The result from the tnsping command above shows 80 milliseconds (ms) were required for the first “ping”. During this time period, the alias GRACELANV8_GRA901m from the local tnsnames.ora file was retrieved, a DNS of the host “gracelan” was resolved, and the TNS connect and refuse packets were transported. The second trip took only 10 ms because all of the connection information was already cached.

 

Here is a link to a nice free scanning tool that allows scanning a remote machine for open TCP and UDP ports (no installation required, just launch the .exe)