The first few checks do not require logging in to the database server. Rather, they can be performed remotely via SQL*Plus or SQLcl and OS commands. Performing the initial checks remotely over the network establishes whether all the system components are working.
One quick check to determine whether the remote server is available, the database is up, the network is working, and the listener is accepting incoming connections is to connect via an SQL*Plus client to a remote database over the network.
The initial testing could use a non-DBA account. Here is an example of connecting over the network by providing the user, password, port, host and service name:
$ sqlplus michelle/Pa$$w0rd123@’mmsrv1:1521/mmdb23c’
If a connection can be made, then the remote server is available, and the database and listener are up and working.
At this point and with the questions that were asked, we can verify the connectivity issue has to do with the application or with something other than the database.
If the prior SQL command does not work, try to establish whether the remote server is available. This can be done with a ping command issued to the host:
$ ping mmsrv1
You can also try the IP address in case the Domain Name System (DNS) server is not available.
If the ping works, you should see output such as this:
64 bytes from mmsrv1 (192.168.254.215): icmp_seq=1 ttl=64 time=0.044 ms
If the ping does not work, there is probably an issue with either the network or the database server. If the database server is not available, it is time to contact a system administrator or network administrator.
If the ping does work, then check to see if the remote server is reachable via the port that the listener is listening on. The telnet command can accomplish this:
$ telnet IP <port>
In this example, a network connection is attempted to the server’s IP address on the 1521 port:
$ telnet 192.168.254.215 1521
If the IP address is reachable on the specified port, you should see “Connected to . . .” in the output, like so:
Trying 192.168.254.216… Connected to ora04. Escape character is ‘^]’.
If the telnet command does not work, contact the SA or the network administrator. If the telnet command does work and there is network connectivity to the server on the specified port, then use the tnsping command to test network connectivity to the remote server and database, using Oracle Net.
This example attempts to reach the mmdb23c remote service:
$ tnsping mmdb23c
If successful, the output should contain the OK string, like so:
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MMSRV1) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = mmdb23)))OK (20 msec)
If tnsping works, it means that the remote listener is up and working. It does not necessarily mean that the database is up, so you may need to log in to the database server to investigate further. If tnsping does not work, then the listener or the database is down or hung.
Pluggable databases would work the same with tnsping, and if there are issues, just double-check the listener that all of the expected database, container, and pluggable databases were added as needed.
To further investigate issues, log in directly to the server to perform additional checks, such as a mount point filling up. Ideally, if there was another issue, one of the logs or monitoring scripts reported it; however, the checks on the server might be necessary at this point.
Leave a Reply