DBA generally face this issue many times that a user has reported that he or she can’t connect to a database. You know there are many components involved with network connectivity and want to figure out the root cause of the problem.
Use the below steps as guidelines when diagnosing Oracle database network connectivity issues:
1. Use the operating system ping utility to determine whether the remote box is accessible—for example:
$ ping dwdb
dwdb is alive
If ping doesn’t work, work with your system or network administrator to ensure you have server-to-server connectivity in place.
2. Use telnet to see if you can connect to the remote server and port (that the listener is listening on)—for example:
$ telnet ora03 1521
Trying 127.0.0.1...
Connected to ora03.
Escape character is '^]'.
The prior output indicates that connectivity to a server and port is okay. If the
prior command hangs, then contact your SA or network administrator for
further assistance.
3. Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network—for example:
$ tnsping dwrep
..........
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = dwdb1.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DWREP)))
OK (500 msec)
If tnsping can’t contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).
4. Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.
Sometimes the client machine will have multiple TNS_ADMIN locations and
tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is
being used is to rename it and see whether you get a different error when
attempting to connect to the remote database.
Network connectivity issues can be troublesome to diagnose because there are several architectural components that have to be in place for it to work correctly. You need to have the following in place:
If you’re still having issues, examine the client sqlnet.log file and the remote server listener.log file. Sometimes these log files will show additional information that will pinpoint the issue.
Use the below steps as guidelines when diagnosing Oracle database network connectivity issues:
1. Use the operating system ping utility to determine whether the remote box is accessible—for example:
$ ping dwdb
dwdb is alive
If ping doesn’t work, work with your system or network administrator to ensure you have server-to-server connectivity in place.
2. Use telnet to see if you can connect to the remote server and port (that the listener is listening on)—for example:
$ telnet ora03 1521
Trying 127.0.0.1...
Connected to ora03.
Escape character is '^]'.
The prior output indicates that connectivity to a server and port is okay. If the
prior command hangs, then contact your SA or network administrator for
further assistance.
3. Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network—for example:
$ tnsping dwrep
..........
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = dwdb1.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DWREP)))
OK (500 msec)
If tnsping can’t contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).
4. Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.
Sometimes the client machine will have multiple TNS_ADMIN locations and
tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is
being used is to rename it and see whether you get a different error when
attempting to connect to the remote database.
Network connectivity issues can be troublesome to diagnose because there are several architectural components that have to be in place for it to work correctly. You need to have the following in place:
- A functional network
- Open ports from point to point
- Oracle Net correctly installed and configured
- Target database and listener up and running
- Correct navigational information from the client to the target database
If you’re still having issues, examine the client sqlnet.log file and the remote server listener.log file. Sometimes these log files will show additional information that will pinpoint the issue.
No comments:
Post a Comment