This post is a continuation of the previous post in this topic .We have already mentioned about how to enable trace and log options on the client.The below information is just for your recap if you have seen the prevoius post and also for those who are familiar about Net services in oracle and want to debug their connection issues
Note:-In this post we have discussed only about Windows Platform
The last option to find out connection issues is to enable log and trace files as below
I)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
TRACE_DIRECTORY_CLIENT=D:\test
TRACE_FILE_CLIENT=trace_file_test
Go to II) If you want to just debug the trace file without much information required or else check the below examples of connection issues which were debugged
A)First and foremost you have enabled the log and trace files and the trace file is not generating in the path mentioned in TRACE_DIRECTORY_CLIENT in sqlnet.ora or in the default path then check the below
1)If there are multiple Oracle homes make sure that the oracle client which you are using to connect is the default Oracle_Home
2) Suppose you have two oracle homes one D:\orant\bin and D:\Oracle10g\bin.
Suppose you want to debug the application using oracle client D:\Oracle10g\bin
then put this entry as the first entry in Environment Variable "PATH".
MyComputer-->Properties-->Advanced-->Environment variable
for example if PATH= D:\orant\bin;D:\Oracle10g\bin;<OTHER PATHS>
change it to
PATH= D:\Oracle10g\bin;D:\orant\bin;<OTHER PATHS>
3) If still Trace file is not generating then mention the backgorund and details in the comment section.
B)You are getting the error "ORA-12154: TNS:could not resolve the connect identifier specified" one possible error may be that your TNS Entry may be wrong if you are using local naming (TNSNAMES.ORA file)
Hence the trace file may contain this
Success:-
nnftrne: Using tnsnames.ora address (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.170.8.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FLEXCUBE.WORLD)
)
) for name CTEST.WORLD
Failure:-
nnftrne: Using tnsnames.ora address (DESCRIPTION = for name CTEST.WORLD
with out having the complete tns entry.
For a better understanding of this error ORA-12154 check the link ORA-12154:TNS:could not resolve the connect identifier specified
C)If you are getting the error "ORA-12545: Connect failed because target host or object does not exist"
the one possible reason may be the Host ip address has some special character in the TNS Entry if you are using local naming(TNSNAMES.ORA)
Hence the trace file may contain this
Failure:-
snlinGetAddrInfo: Name resolution failed for 10.170.8.77,
for more information about this error check ORA-12545: Connect failed because target host or object does not exist
D)if you are getting the error ORA-12541: TNS:no listener
One possible reason might be the host name is mentioned wrongly.
Then the trace file will contain
Success:-
nttcni: trying to connect to socket 412.
snlinGetNameInfo: entry
snlinGetNameInfo: Using numeric form of host's address 10.170.8.77
snlinGetNameInfo: exit
nttcni: connected on ipaddr 10.170.8.77
Failure:-
nttcni: trying to connect to socket 420.
ntt2err: entry
ntt2err: soc 420 error - operation=1, ntresnt[0]=511, ntresnt[1]=61, ntresnt[2]=0
ntt2err: exit
for more informnation on this error check ORA-12541: TNS:no listener
II)These are simple examples of what might contain in the trace file for Connection errors from Client machines
For debugging all kinds of connection errors from client
1) Take the trace file from the client machine in which the Connection is successfull which have same configuration of oracle client and database as the client machine which has connection errors,if the oracle client is 10g or higher then remove the timestamp information
2) Take the trace file from problematic Client machine ,if the oracle client is 10g or higher then remove the timestamp information
3)Compare the two trace files to identify the connection problem and google it for additional information .Also note the trace file for successfull connection might contain additional data which you can ignore.
We hope this information was useful
Note:-In this post we have discussed only about Windows Platform
The last option to find out connection issues is to enable log and trace files as below
I)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
TRACE_DIRECTORY_CLIENT=D:\test
TRACE_FILE_CLIENT=trace_file_test
Go to II) If you want to just debug the trace file without much information required or else check the below examples of connection issues which were debugged
A)First and foremost you have enabled the log and trace files and the trace file is not generating in the path mentioned in TRACE_DIRECTORY_CLIENT in sqlnet.ora or in the default path then check the below
1)If there are multiple Oracle homes make sure that the oracle client which you are using to connect is the default Oracle_Home
2) Suppose you have two oracle homes one D:\orant\bin and D:\Oracle10g\bin.
Suppose you want to debug the application using oracle client D:\Oracle10g\bin
then put this entry as the first entry in Environment Variable "PATH".
MyComputer-->Properties-->Advanced-->Environment variable
for example if PATH= D:\orant\bin;D:\Oracle10g\bin;<OTHER PATHS>
change it to
PATH= D:\Oracle10g\bin;D:\orant\bin;<OTHER PATHS>
3) If still Trace file is not generating then mention the backgorund and details in the comment section.
B)You are getting the error "ORA-12154: TNS:could not resolve the connect identifier specified" one possible error may be that your TNS Entry may be wrong if you are using local naming (TNSNAMES.ORA file)
Hence the trace file may contain this
Success:-
nnftrne: Using tnsnames.ora address (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.170.8.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FLEXCUBE.WORLD)
)
) for name CTEST.WORLD
Failure:-
nnftrne: Using tnsnames.ora address (DESCRIPTION = for name CTEST.WORLD
with out having the complete tns entry.
For a better understanding of this error ORA-12154 check the link ORA-12154:TNS:could not resolve the connect identifier specified
C)If you are getting the error "ORA-12545: Connect failed because target host or object does not exist"
the one possible reason may be the Host ip address has some special character in the TNS Entry if you are using local naming(TNSNAMES.ORA)
Hence the trace file may contain this
Failure:-
snlinGetAddrInfo: Name resolution failed for 10.170.8.77,
for more information about this error check ORA-12545: Connect failed because target host or object does not exist
D)if you are getting the error ORA-12541: TNS:no listener
One possible reason might be the host name is mentioned wrongly.
Then the trace file will contain
Success:-
nttcni: trying to connect to socket 412.
snlinGetNameInfo: entry
snlinGetNameInfo: Using numeric form of host's address 10.170.8.77
snlinGetNameInfo: exit
nttcni: connected on ipaddr 10.170.8.77
Failure:-
nttcni: trying to connect to socket 420.
ntt2err: entry
ntt2err: soc 420 error - operation=1, ntresnt[0]=511, ntresnt[1]=61, ntresnt[2]=0
ntt2err: exit
for more informnation on this error check ORA-12541: TNS:no listener
II)These are simple examples of what might contain in the trace file for Connection errors from Client machines
For debugging all kinds of connection errors from client
1) Take the trace file from the client machine in which the Connection is successfull which have same configuration of oracle client and database as the client machine which has connection errors,if the oracle client is 10g or higher then remove the timestamp information
2) Take the trace file from problematic Client machine ,if the oracle client is 10g or higher then remove the timestamp information
3)Compare the two trace files to identify the connection problem and google it for additional information .Also note the trace file for successfull connection might contain additional data which you can ignore.
We hope this information was useful
No comments:
Post a Comment