# CHECK FIREWALL, WINDOWS FIREWALL , ANTI-Virus Software First !
ping hostname
tnsping TNS
trcroute TNS
telnet <hostname> <port>
tracert hostname
client side
sqlplus scott/tiger@TNS
&
server side
sqlplus scott/tiger@TNS
cat /etc/hosts
cat /etc/resolv.conf
cat /etc/nsswitch.conf
ipconfig -a
ping 127.0.0.1
$ORACLE_HOME/network/admin/sqlnet.ora
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/log/*
sqlnet.log listener.log
/var/log/messages
/var/adm/messages
errpt -a
ls -ld $ORACLE_HOME
netstat -rn
ps -ef | grep -i tns
lsnrctl status {listener_name}
lsnrvtl services {listener_name}
ulimit -a
1. Complete database alert log.
2. If the database was not restarted from the time of last occurance of the
issue,
select * from v$resource_limit
3. RAM and SWAP configured on the server.
4. ulimit settings for oracle user:
ulimit -aS
ulimit -aH
5. Kernel parameter settings:
/etc/sysctl.conf
dblogin
show parameter cluster_database
show parameter listener
$srvctl config vip -n {nodename}
$lsnrctl status listener
agent.log and the crsd.log ..
crsd agent log and the crsd.log
$crsctl getperm resource ora.LISTENER.lsnr
sql net client trace , Client side tracing is done by adding the following syntax to the client's sqlnet.ora file:
We will need a timestamped matching set of client/listener sqlnet traces while error is reproduced in order to find the root cause of the issue.
++ Enable client sqlnet tracing.
=======================
To do this add the following to client sqlnet.ora:
TRACE_LEVEL_CLIENT=16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_DIRECTORY_CLIENT=path
TRACE_FILE_CLIENT=client
TRACE_TIMESTAMP_CLIENT=ON
replace path with a local directory for the trace files. (for example c:\temp)
Do a test connection from the problematic client and check if the trace files are created.
Upload the traces containing the error to me on metalink.
++ Enable listener sqlnet tracing.
==========================
To do this edit the listener.ora and add,
TRACE_LEVEL_{listener name}=16
TRACE_TIMESTAMP_{listener name}=TRUE
TRACE_DIRECTORY_{listener name}=/tmp {-- this can be any directory other than a top level directory like / or c:\
Replace {listener name} with the name of the listener. For example if your listener was called LISTENER then TRACE_LEVEL_LISTENER=16
You need to restrict the amount of disk space used by the tracing then you must also set,
TRACE_FILELEN_{listener name}=500000 {-- size of the files in K
TRACE_FILENO_{listener name}=10 {-- number of files
This will limit the traces to 10 files of around 500Mb, so 5000Mb in total. When the 10th file is full it will reuse file number one.
You will need to stop/start the listener for this to take effect.
When the problem reproduces please can you upload the listener trace and the listener log.
Trace_level_client=16
Trace_directory_client={path_to_the_trace_directory} # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
trace Local listener or SCAN listeners
TRACE_LEVEL_{listener_name}= 16
TRACE_TIMESTAMP_{listener_name}=on
TRACE_DIRECTORY_{listener_name}={path_to_the_trace_directory}
truss -o /tmp/lisener.out -fae lsnrctl start {listener_name}
Some Useful Note:
Note.444705.1 TroubleShooting Guide For ORA-12514 TNS listener could not resolve SERVICE_NAME given in connect descriptor
Note.761740.1 Technicians Unable To Receive Orders While MWM Components Display ODBC Errors And Are Connected
Note.119007.1 ORA-12560: Administering the Listener on UNIX - Troubleshooting
Note 276812.1 TNS-12542 Error When Executing Batch Jobs or in High Transaction Environment
Note.219208.1 Ext/Pub Client Connection via Connect Manager Fails with TNS-12564
Note.393941.1 Ext/Mod ORA-12564 Reported When Using 10g Connection Manager
Note.1116960.1 ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
Note.550859.1 Abstract TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection.
Note.207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Note.119706.1 Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors
For database links between different Oracle versions connections must be supported in BOTH directions in the matrix found in Note 207303.1
eg: As 9.2 -} 7.3.4 is not supported then database links between these version
are not supported in either direction.
You are trying to connect two versions (client-server) that are not certified (as confirmed by Note 207303.1) and between which exist many technical incompatibilities.
CLIENT -- LISTENER -- SERVER RESULT
8 11.1 8 OK
9 11.1 9 OK
10 11.1 10 OK
11 11.1 11 OK
8 11.2 8 FAILS
9 11.2 9 OK
10 11.2 10 OK
11 11.2 11 OK
9 11.1 8 OK
10 11.1 8 OK
11 11.1 8 OK
9 11.2 8 FAILS
10 11.2 8 FAILS
11 11.2 8 FAILS
The relevant relationship that appears to be at issue is LISTENER and DATABASE. Client version is not a factor.
But if the ultimate outcome is that the 11.2 (11gR2) LISTENER is indicated (though I still haven't seen documentation of this) as not compatible with use on a ORACLE 8i (8.1.7.0) DATABASE, then we'll capture that here and move on. I would, however, like to see some evidence of this, if it is available. I can find notes in the KB about 10gR2's listener not supporting 8i database, and I can find notes about 11gR1 having resolved that regression. But I can find nothing regarding listener/database compatibility that mentions 11gR2, that would explain our results.
Clients should be complied with Servers , For Sever 11.2 the only supported clients are 11.2.0 , 11.1.0 , 10.2.0 : 10g end MUST be at 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. See Note:4511371.8 for more details and finally 10.1.0.5 only with extended support .
On the other Side in order to connect from listener to DB server in a supported way , Listener version should be greater than or equal to the server version .
Note 207303.1 should still be followed.
如何诊断ORA125XX连接问题
以下这个Action Script是我收集的在解决ORA-125XX(如ORA-12560)这类网络链接故障时的一些思路，主要包括 现有的网络配置(client & server side)、监听日志、SQLNET Client trace等信息-- How to troubleshooting ORA-125** connection issues：
# CHECK FIREWALL, WINDOWS FIREWALL , ANTI-Virus Software First !
ping hostname
tnsping TNS
trcroute TNS
telnet <hostname> <port>
tracert hostname
client side
sqlplus scott/tiger@TNS
&
server side
sqlplus scott/tiger@TNS
cat /etc/hosts
cat /etc/resolv.conf
cat /etc/nsswitch.conf
ipconfig -a
ping 127.0.0.1
$ORACLE_HOME/network/admin/sqlnet.ora
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/log/*
sqlnet.log listener.log
/var/log/messages
/var/adm/messages
errpt -a
ls -ld $ORACLE_HOME
netstat -rn
ps -ef | grep -i tns
lsnrctl status {listener_name}
lsnrvtl services {listener_name}
ulimit -a
1. Complete database alert log.
2. If the database was not restarted from the time of last occurance of the
issue,
select * from v$resource_limit
3. RAM and SWAP configured on the server.
4. ulimit settings for oracle user:
ulimit -aS
ulimit -aH
5. Kernel parameter settings:
/etc/sysctl.conf
dblogin
show parameter cluster_database
show parameter listener
$srvctl config vip -n {nodename}
$lsnrctl status listener
agent.log and the crsd.log ..
crsd agent log and the crsd.log
$crsctl getperm resource ora.LISTENER.lsnr
sql net client trace , Client side tracing is done by adding the following syntax to the client's sqlnet.ora file:
We will need a timestamped matching set of client/listener sqlnet traces while error is reproduced in order to find the root cause of the issue.
++ Enable client sqlnet tracing.
=======================
To do this add the following to client sqlnet.ora:
TRACE_LEVEL_CLIENT=16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_DIRECTORY_CLIENT=path
TRACE_FILE_CLIENT=client
TRACE_TIMESTAMP_CLIENT=ON
replace path with a local directory for the trace files. (for example c:\temp)
Do a test connection from the problematic client and check if the trace files are created.
Upload the traces containing the error to me on metalink.
++ Enable listener sqlnet tracing.
==========================
To do this edit the listener.ora and add,
TRACE_LEVEL_{listener name}=16
TRACE_TIMESTAMP_{listener name}=TRUE
TRACE_DIRECTORY_{listener name}=/tmp {-- this can be any directory other than a top level directory like / or c:\
Replace {listener name} with the name of the listener. For example if your listener was called LISTENER then TRACE_LEVEL_LISTENER=16
You need to restrict the amount of disk space used by the tracing then you must also set,
TRACE_FILELEN_{listener name}=500000 {-- size of the files in K
TRACE_FILENO_{listener name}=10 {-- number of files
This will limit the traces to 10 files of around 500Mb, so 5000Mb in total. When the 10th file is full it will reuse file number one.
You will need to stop/start the listener for this to take effect.
When the problem reproduces please can you upload the listener trace and the listener log.
Trace_level_client=16
Trace_directory_client={path_to_the_trace_directory} # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
trace Local listener or SCAN listeners
TRACE_LEVEL_{listener_name}= 16
TRACE_TIMESTAMP_{listener_name}=on
TRACE_DIRECTORY_{listener_name}={path_to_the_trace_directory}
truss -o /tmp/lisener.out -fae lsnrctl start {listener_name}
Some Useful Note:
Note.444705.1 TroubleShooting Guide For ORA-12514 TNS listener could not resolve SERVICE_NAME given in connect descriptor
Note.761740.1 Technicians Unable To Receive Orders While MWM Components Display ODBC Errors And Are Connected
Note.119007.1 ORA-12560: Administering the Listener on UNIX - Troubleshooting
Note 276812.1 TNS-12542 Error When Executing Batch Jobs or in High Transaction Environment
Note.219208.1 Ext/Pub Client Connection via Connect Manager Fails with TNS-12564
Note.393941.1 Ext/Mod ORA-12564 Reported When Using 10g Connection Manager
Note.1116960.1 ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
Note.550859.1 Abstract TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection.
Note.207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Note.119706.1 Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors
For database links between different Oracle versions connections must be supported in BOTH directions in the matrix found in Note 207303.1
eg: As 9.2 -} 7.3.4 is not supported then database links between these version
are not supported in either direction.
You are trying to connect two versions (client-server) that are not certified (as confirmed by Note 207303.1) and between which exist many technical incompatibilities.
CLIENT -- LISTENER -- SERVER RESULT
8 11.1 8 OK
9 11.1 9 OK
10 11.1 10 OK
11 11.1 11 OK
8 11.2 8 FAILS
9 11.2 9 OK
10 11.2 10 OK
11 11.2 11 OK
9 11.1 8 OK
10 11.1 8 OK
11 11.1 8 OK
9 11.2 8 FAILS
10 11.2 8 FAILS
11 11.2 8 FAILS
The relevant relationship that appears to be at issue is LISTENER and DATABASE. Client version is not a factor.
But if the ultimate outcome is that the 11.2 (11gR2) LISTENER is indicated (though I still haven't seen documentation of this) as not compatible with use on a ORACLE 8i (8.1.7.0) DATABASE, then we'll capture that here and move on. I would, however, like to see some evidence of this, if it is available. I can find notes in the KB about 10gR2's listener not supporting 8i database, and I can find notes about 11gR1 having resolved that regression. But I can find nothing regarding listener/database compatibility that mentions 11gR2, that would explain our results.
Clients should be complied with Servers , For Sever 11.2 the only supported clients are 11.2.0 , 11.1.0 , 10.2.0 : 10g end MUST be at 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. See Note:4511371.8 for more details and finally 10.1.0.5 only with extended support .
On the other Side in order to connect from listener to DB server in a supported way , Listener version should be greater than or equal to the server version .
Note 207303.1 should still be followed.
# CHECK FIREWALL, WINDOWS FIREWALL , ANTI-Virus Software First !
ping hostname
tnsping TNS
trcroute TNS
telnet <hostname> <port>
tracert hostname
client side
sqlplus scott/tiger@TNS
&
server side
sqlplus scott/tiger@TNS
cat /etc/hosts
cat /etc/resolv.conf
cat /etc/nsswitch.conf
ipconfig -a
ping 127.0.0.1
$ORACLE_HOME/network/admin/sqlnet.ora
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/log/*
sqlnet.log listener.log
/var/log/messages
/var/adm/messages
errpt -a
ls -ld $ORACLE_HOME
netstat -rn
ps -ef | grep -i tns
lsnrctl status {listener_name}
lsnrvtl services {listener_name}
ulimit -a
1. Complete database alert log.
2. If the database was not restarted from the time of last occurance of the
issue,
select * from v$resource_limit
3. RAM and SWAP configured on the server.
4. ulimit settings for oracle user:
ulimit -aS
ulimit -aH
5. Kernel parameter settings:
/etc/sysctl.conf
dblogin
show parameter cluster_database
show parameter listener
$srvctl config vip -n {nodename}
$lsnrctl status listener
agent.log and the crsd.log ..
crsd agent log and the crsd.log
$crsctl getperm resource ora.LISTENER.lsnr
sql net client trace , Client side tracing is done by adding the following syntax to the client's sqlnet.ora file:
We will need a timestamped matching set of client/listener sqlnet traces while error is reproduced in order to find the root cause of the issue.
++ Enable client sqlnet tracing.
=======================
To do this add the following to client sqlnet.ora:
TRACE_LEVEL_CLIENT=16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_DIRECTORY_CLIENT=path
TRACE_FILE_CLIENT=client
TRACE_TIMESTAMP_CLIENT=ON
replace path with a local directory for the trace files. (for example c:\temp)
Do a test connection from the problematic client and check if the trace files are created.
Upload the traces containing the error to me on metalink.
++ Enable listener sqlnet tracing.
==========================
To do this edit the listener.ora and add,
TRACE_LEVEL_{listener name}=16
TRACE_TIMESTAMP_{listener name}=TRUE
TRACE_DIRECTORY_{listener name}=/tmp {-- this can be any directory other than a top level directory like / or c:\
Replace {listener name} with the name of the listener. For example if your listener was called LISTENER then TRACE_LEVEL_LISTENER=16
You need to restrict the amount of disk space used by the tracing then you must also set,
TRACE_FILELEN_{listener name}=500000 {-- size of the files in K
TRACE_FILENO_{listener name}=10 {-- number of files
This will limit the traces to 10 files of around 500Mb, so 5000Mb in total. When the 10th file is full it will reuse file number one.
You will need to stop/start the listener for this to take effect.
When the problem reproduces please can you upload the listener trace and the listener log.
Trace_level_client=16
Trace_directory_client={path_to_the_trace_directory} # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
trace Local listener or SCAN listeners
TRACE_LEVEL_{listener_name}= 16
TRACE_TIMESTAMP_{listener_name}=on
TRACE_DIRECTORY_{listener_name}={path_to_the_trace_directory}
truss -o /tmp/lisener.out -fae lsnrctl start {listener_name}
Some Useful Note:
Note.444705.1 TroubleShooting Guide For ORA-12514 TNS listener could not resolve SERVICE_NAME given in connect descriptor
Note.761740.1 Technicians Unable To Receive Orders While MWM Components Display ODBC Errors And Are Connected
Note.119007.1 ORA-12560: Administering the Listener on UNIX - Troubleshooting
Note 276812.1 TNS-12542 Error When Executing Batch Jobs or in High Transaction Environment
Note.219208.1 Ext/Pub Client Connection via Connect Manager Fails with TNS-12564
Note.393941.1 Ext/Mod ORA-12564 Reported When Using 10g Connection Manager
Note.1116960.1 ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
Note.550859.1 Abstract TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection.
Note.207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Note.119706.1 Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors
For database links between different Oracle versions connections must be supported in BOTH directions in the matrix found in Note 207303.1
eg: As 9.2 -} 7.3.4 is not supported then database links between these version
are not supported in either direction.
You are trying to connect two versions (client-server) that are not certified (as confirmed by Note 207303.1) and between which exist many technical incompatibilities.
CLIENT -- LISTENER -- SERVER RESULT
8 11.1 8 OK
9 11.1 9 OK
10 11.1 10 OK
11 11.1 11 OK
8 11.2 8 FAILS
9 11.2 9 OK
10 11.2 10 OK
11 11.2 11 OK
9 11.1 8 OK
10 11.1 8 OK
11 11.1 8 OK
9 11.2 8 FAILS
10 11.2 8 FAILS
11 11.2 8 FAILS
The relevant relationship that appears to be at issue is LISTENER and DATABASE. Client version is not a factor.
But if the ultimate outcome is that the 11.2 (11gR2) LISTENER is indicated (though I still haven't seen documentation of this) as not compatible with use on a ORACLE 8i (8.1.7.0) DATABASE, then we'll capture that here and move on. I would, however, like to see some evidence of this, if it is available. I can find notes in the KB about 10gR2's listener not supporting 8i database, and I can find notes about 11gR1 having resolved that regression. But I can find nothing regarding listener/database compatibility that mentions 11gR2, that would explain our results.
Clients should be complied with Servers , For Sever 11.2 the only supported clients are 11.2.0 , 11.1.0 , 10.2.0 : 10g end MUST be at 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. See Note:4511371.8 for more details and finally 10.1.0.5 only with extended support .
On the other Side in order to connect from listener to DB server in a supported way , Listener version should be greater than or equal to the server version .
Note 207303.1 should still be followed.