Even though this post is about solving Oracle Database 10g connection problems most of the things discussed here are same or similar for other versions of Oracle databases.
In order to diagnose Oracle database connection problems we need to know about Oracle Network services(Net 8).Oracle Net Services(Net 8) is a service which provides methods for understanding and resolving network problems.It uses logs and trace files to diagnose the problem.
Commonly there are two types of Diagnostics we can do to identify Connection Problems
Server Diagnostics:-To identify if there is connection issue in the database server you need the assistance of DBA or system administrator.Also two things you have to know is
1) Whether other systems are able to connect to the database server ,if it is yes then there is no issue with the server.
2)Whether any server,database or listener configuration has been changed recently ,if it is then there is a possibility of issue at the server side.
If there is a server issue Administrator has to do two types of test
1)Verify the database is running
2) Perform a loopback test
The Server Diagnostics is not our concern since it will be taken care by Administrator
Client Diagnostics:-To diagnose Client connection problems you have to know two things
If you have multiple Oracle homes on your client machine or you are establishing a connection from third Party Application(Visual Studio) to oracle,mention your connection problem in the Comments section,i would address them as much as possible
If the same Oracle Client installation has been done on your network and only your Client is not able to connect to the database server jump to point 5 otherwise see the below
1)Check that you have installed the same protocol support as was installed on the database server.This can be verified with the System Administrator.The Common protocol in unix system are
IPC
BEQ
TCP/IP
SSL
RAW
2)Check base connectivity for underlying network transport.For example for the TCP/IP Protocol check the PING Utility(which is very commonly used).Use the PING utility to test the network connection between client and Database server
3)Verify with the Local PC Support that all Net8 Services software for the client has been installed
4) Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files exist in the correct locations and then check Localized Configuration File Support for further details
5)If other client computers are connecting to the selected Oracle database, back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working computer onto the non-working client
6)Perform further test by verifying Testing Network Connectivity from the Client. While verifying this the TNSPING is not a reliable utility to check connection descriptor but anyhow for testing purpose suppose this is the Tnsnames entry for the database
ORA11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA11)
)
)
you have to go to Command Prompt and type
d:>TNSPING ORA11
to verify the connection service.On success it will give
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =ORA11)))
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)
Also trcroute is not available in Oracle database 10g Windows platform.
7)IF the connection still fails your last option is to enable log and trace files
The Common file where we can enable logging options in client is sqlnet.ora located in <ORACLE_HOME>/NETWORK/ADMIN
The logging parameters and Tracing parameters( TRACE_LEVEL_CLIENT being the most important)for client are
sqlnet.ora Parameter
LOG_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is the current working directory.
LOG_FILE_CLIENT :-Sets the name of the log file for the client. By default the log name is sqlnet.log.
TRACE_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.
TRACE_FILE_CLIENT :-Sets the name of the log file for the client. Sets the name of the trace file for the client. By default the trace file name is sqlnet.trc
TRACE_LEVEL_CLIENT :- This will decide the level of information provided values are
off (equivalent to 0) provides no tracing
user (equivalent to 4) traces to identify user-induced error conditions
admin (equivalent to 6) traces to identify installation-specific problems
support (equivalent to 16) provides trace information for troubleshooting information
In our case we need to troubleshoot hence set the level to SUPPORT unless problem requires the other options
Sample sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
LOG_DIRECTORY_CLIENT=D:\test
TRACE_LEVEL_CLIENT = SUPPORT
So on a concluding note kindlylet us know the background of the problem and copy the contents of the log and trace files in Comments section if you are not able to figure out the Connection Problem yourself.
In order to diagnose Oracle database connection problems we need to know about Oracle Network services(Net 8).Oracle Net Services(Net 8) is a service which provides methods for understanding and resolving network problems.It uses logs and trace files to diagnose the problem.
Commonly there are two types of Diagnostics we can do to identify Connection Problems
Server Diagnostics:-To identify if there is connection issue in the database server you need the assistance of DBA or system administrator.Also two things you have to know is
1) Whether other systems are able to connect to the database server ,if it is yes then there is no issue with the server.
2)Whether any server,database or listener configuration has been changed recently ,if it is then there is a possibility of issue at the server side.
If there is a server issue Administrator has to do two types of test
1)Verify the database is running
2) Perform a loopback test
The Server Diagnostics is not our concern since it will be taken care by Administrator
Client Diagnostics:-To diagnose Client connection problems you have to know two things
If you have multiple Oracle homes on your client machine or you are establishing a connection from third Party Application(Visual Studio) to oracle,mention your connection problem in the Comments section,i would address them as much as possible
If the same Oracle Client installation has been done on your network and only your Client is not able to connect to the database server jump to point 5 otherwise see the below
1)Check that you have installed the same protocol support as was installed on the database server.This can be verified with the System Administrator.The Common protocol in unix system are
IPC
BEQ
TCP/IP
SSL
RAW
2)Check base connectivity for underlying network transport.For example for the TCP/IP Protocol check the PING Utility(which is very commonly used).Use the PING utility to test the network connection between client and Database server
3)Verify with the Local PC Support that all Net8 Services software for the client has been installed
4) Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files exist in the correct locations and then check Localized Configuration File Support for further details
5)If other client computers are connecting to the selected Oracle database, back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working computer onto the non-working client
6)Perform further test by verifying Testing Network Connectivity from the Client. While verifying this the TNSPING is not a reliable utility to check connection descriptor but anyhow for testing purpose suppose this is the Tnsnames entry for the database
ORA11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA11)
)
)
you have to go to Command Prompt and type
d:>TNSPING ORA11
to verify the connection service.On success it will give
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =ORA11)))
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)
Also trcroute is not available in Oracle database 10g Windows platform.
7)IF the connection still fails your last option is to enable log and trace files
The Common file where we can enable logging options in client is sqlnet.ora located in <ORACLE_HOME>/NETWORK/ADMIN
The logging parameters and Tracing parameters( TRACE_LEVEL_CLIENT being the most important)for client are
sqlnet.ora Parameter
LOG_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is the current working directory.
LOG_FILE_CLIENT :-Sets the name of the log file for the client. By default the log name is sqlnet.log.
TRACE_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.
TRACE_FILE_CLIENT :-Sets the name of the log file for the client. Sets the name of the trace file for the client. By default the trace file name is sqlnet.trc
TRACE_LEVEL_CLIENT :- This will decide the level of information provided values are
off (equivalent to 0) provides no tracing
user (equivalent to 4) traces to identify user-induced error conditions
admin (equivalent to 6) traces to identify installation-specific problems
support (equivalent to 16) provides trace information for troubleshooting information
In our case we need to troubleshoot hence set the level to SUPPORT unless problem requires the other options
Sample sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
LOG_DIRECTORY_CLIENT=D:\test
TRACE_LEVEL_CLIENT = SUPPORT
So on a concluding note kindlylet us know the background of the problem and copy the contents of the log and trace files in Comments section if you are not able to figure out the Connection Problem yourself.
Welcome NR :)
ReplyDelete