博客访问： 451557
博文数量： 99
博客积分： 1937
博客等级： 上尉
技术积分： 1617
注册时间：
专注数据库开发管理
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
引起这种问题的原因有如下几个：1. 网速不通2.网速过慢3.客户端与服务器端有防火墙，导致listener的返回包穿不过防火墙。根据你的情况是第2种。解决办法为：在服务器端的listener.ora文件中指定：&& CONNECT_TIMEOUT_ = 0最后一种引起ORA-12535错误的原因是由于不正确的设置listener queue size或操作系统中的nofiles参数引起的，如果是这种原因引起的，解决方法如下：1. Increase listener queue size.&2. Increase nofiles value (ulimit -n )3. Restart the listener4.windows系统的放火墙（关闭或将端口1521开通）--------------------------------------------------------------------------ora-12535 : TNS:operation timed out&客户穿越防火墙、路由器PING TNSPING都通SQLPLUS 报 ORA-12535错误：分析： 防火墙/路由器IP地址转换 是关键1、metalink doc：&& 随机端口的&&& listener hang住2、CONNECT_TIMEOUT=0 trace&3、NT下应付这种随机端口的方法：use_shared_socket :(How to configure USE_SHARED_SOCKET on Windows NT/2000)(Firewalls, Windows NT and Redirections)第一种方法：&set a Windows registry value USE_SHARED_SOCKET, put this registry key under your ORACLE registry:\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME&Create the key USE_SHARED_SOCKET and assign the value TRUE.&After that you need to restart the Oracle service and also the Oracle listener, then it should work.&One problem of this setting is if your listener is down, all the connections will be cut. This is different than Oracle on Unix platform works&第二种方法：&需要在MTS模式下（共享模式）Oracle默认是专用模式。&　　经试验发现，如果不在init文件中设参数的话，Oracle仍然会要求一个随机端口和&1521端口来共同通讯，只是这个随机端口，并不随客户端会话和登录的变化而变化，在&没有重启服务器时，是固定的。&　　（试验发现，在专用模式下，每次连接，oracle服务器会按+1方式，提供一个非&1521的端口。） 　　&　　所以，还需要在init.ora文件的最后加上一条参数： 　&　　&mts_dispatchers="(address=(protocol=tcp)(host=myoradb)(port=1521))(dispatchers=1)"&　　&　　这样才真正实现只用一个端口，穿过防火墙&successful configuration can be seen using the 'netstat -a' command from a Command Prompt&&& OR&& Analyze a client trace by setting folloving parameters in the client SQLNET.ORA&&& TRACE_LEVEL_CLIENT= 16&& TRACE_DIRECTORY_CLIENT = && TRACE_FILE_DIRECTORY= &4、connection manager:&其他的可能原因： MTS模式 全局数据库名总结：方法1：SQL*Net proxy 加use_shared_socket方法2：connect manager本地模拟试验基本命令：netstat -an&&&& 查看本机通信情况lsnrctl status 查看监听器情况ping&&&&&&&&&&& 查看本地网络tnsping ***&&&& 查看TNS解析sqlplus&&&&&&&& 查看客户端应用试验一：将本地网络IP地址只允许通过TCP1521端口则本地C:\>ping liqPinging liq [192.168.1.50] with 32 bytes of data:Reply from 192.168.1.50: bytes=32 time<10ms TTL=128C:\>tnsping ora9iAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = liq)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ora9i)))OK（40毫秒）而：SQL> conn system/liq&&& ***************采取操作系统认证可以进入数据库已连接。SQL> conn&&××××××× 走TNSNAME解析 就报错ERROR:ORA-12541: TNS: 没有监听器警告: 您不再连接到 ORACLE。SQL> conn system/liq已连接。SQL>分析：此时可以PING通，TNSPING也通，但是不能用SQLPLUS登陆 与ORA:12535错误问题类似试验二：开放所有端口当然可以一路成功试验三：网络只允许TCP 1521通信 而添加键值USE_SHARED_SOCKET=TRUE到注册表中也可以了！！！！！！！FROM ORALCE METALINKrandom port and redirectionfrom metalink doc: 66382.1On Windows NT, when a connect request comes in to the listener, the listener&spawns an Oracle thread. This thread is a listening thread and is started&on a wild-card address, meaning that the thread is listening for connections&on the current IP address and an unused port number given to the thread by&the networking software. The Oracle thread will contact the listener using&IPC and inform the listener of its listening address, connection load, and&some other status information. The listener sends back to the client a&REDIRECT address. This tells the client to reconnect to the newly spawned&Oracle thread. Since this Oracle thread is on a random port (a range of ports cannot be defined), the firewall will not let the connection throughTNS-12203(i) The first way is to use a firewall that has a SQL*Net proxy built into&&&&& it.connect to proxy and pass connection to listener&&&&&&&& 2. send redirect to client&&&&&&&& 3. connect to redirected address via the proxy&&&&&&&& 4. oracle accepts the connection&&&&&&&&&&&&&&&&&&&&&&&&&&&& firewall&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ||&&&& +------+ <--------2--------||-------2------&& +---------+&&&& |client|&&&&&&&&&&&&&&&&&&& ||&&&&&&&&&&&&&&&& |listener |(port=1521)&&&& +------+ --------1------> proxy ----1------> +---------+&&&&&& A&&& \&&&&&&&&&&&&&&&&&&& /||\&&&&&& |&&&& \---------3-------/ || \-----3------> +---------+&&&&&& |&&&&&&&&&&&&&&&&&&&&&&&& ||&&&&&&&&&&&&&&&& | oracle&& |(port=xxxx)&&&&&& +--------------4---------||-------4------- +---------+(ii)USE_SHARED_SOCKET = TRUE&&&& Place the parameter in the Windows registry under the following locations:&&&&&&(Relases 8.0) or&&&&& (Release 8i or newer)Restart the system for the parameter to take effect.Here's how USE_SHARED_SOCKET works. The listener binds and creates a&&&&&& socket on the address specified in the "listener.ora" file.&& On this&&&&&& socket, there is a LISTEN state active that is used by the listener.&&&&&&& When a new connection comes in to the listener, the listener spawns an&&&&&& Oracle thread on the listening port (i.e. 1521). This happens over and&&&&&& over again so that you have a listener and several established&&&&&& connections using port 1521. Pictorially this scenario would look like&&&&&& this:&&&&&&&&&&&&&&&&&&&& +--------------------+&&&&&&&&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&& |&&&&& This square represents&&&&& &&&&&&&&&&&&&&&&&&&& |&&&&& a listening socket for&&&&& |&&&&&&&&&&&&&&&&&&&& |&&&&& port 1521.&&&&&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&& |&&&&&
= oracle thread&&&&&&&& &&&&&&&&&&&&&&&&&&&& |&&&&&
= listener&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |&&&&&&&&&&&&&&&&&&&& +--------------------+*******************random portFinally, a very common question concerning the listener and port numbers is&why different port numbers show up in the "listener.log" file.&& What you areseeing is the client's source port and client's source IP address.&& Here ishow this relates to your firewall:If I want to make a TCP connection to a server (say with TELNET), I need&to create a socket. To create a socket, I need 4 pieces of information:&a source IP and port, and a destination IP and port.&& So, using TELNET as an&example (the listening port for the TELNET process is 23 on the server):&&&&&&&&&&&&&&& source&&&&&&& destination&&&&&&&&&&&&&& +-----------+---------------+&&&&&&&& IP&&&& |138.2.12.8 |185.45.67.53&&& |&&&&&&&&&&&&&& +-----------+---------------+&&&&&& port&&&& |&&&& xx&&&&& |&&&&& 23&&&&&&&& |&&&&&&&&&&&&&& +-----------+---------------+Notice I have labeled the source port as 'xx'.&& What happens is that the&networking software on the client chooses at random, or in sequential order,&a valid port (between 1024 and 65535) so the client can send and receive data.&This is what you are seeing in the "listener.log" file.from metalink :The net8 connection to a Windows NT/2000 database server normally redirects&& the port number to a random number when a user process connects to a ORACLE&& shadow process. It does not use the TCP/IP port sharing like on UNIX systems where the clients only need to know the TNS listener port. In order to make Oracle connection to work in a firewall environment, the customers had to get a firewall that has a SQL*Net proxy built in or&& with the newest TCP/IP Socket implementation of Windows NT 4.0 (available with&& Service Pack#3) and Windows 2000 this can now be handled by the use of the&& parameter "USE_SHARED_SOCKET".&From metalink :Q: The thing still confuses me is since Oracle will pick up a random port upon handshake via default 1521 port, why do we only need to open 1521 to get sqlplus connect to Oracle, instead of opening a range of ports for subsequent random port's use?&A:Actually&the expected way is to open up a range of ports. Not sure how you are getting away with opening 1521 only. Because even if you open up 1521, the client connection will go through, but once it hits the listener this will fork a new process that will take a random port. this is where the problem resides. Again theoretically this is how it is supposed to work. Not sure if your firewall vendor had added some features that I am not aware of. Can you give us some details on the firewall you are using? And can you do COMPLETE communication when opening 1521?&Here is more information&In theory, it is possible under certain conditions to configure SQL*Net to pass through a firewall without a SQL*Net application proxy. The ability to do this depends on the nature of the firewall itself (not all firewalls support this), the configuration of the server, and the limitations of the operating system.&Firewalls that employ packet filtering may, in general, be configured to allow SQL*Net traffic. Packet filters operate by blocking or allowing communication between machines or networks based on information contained in the IP packet headers. This information includes client and server IP addresses and destination IP port numbers. Note that packet filters themselves don't offer&much security. In order to minimize the security risk to your server,&configuring a packet filter to allow SQL*Net traffic should only be done if you can minimize the 'hole' in the firewall that you are opening up.&Ideally, you would want to restrict incoming connections to a small number of named ports.&For example, you might use one SQL*Net Listener only, listening on port 1521. Note that unless your firewall understands SQL*Net and can verify that the connection coming through to port 1521 really is SQL*Net, you are always taking a chance that the hole through your firewall may be co-opted and used for something other than SQL*Net.&In some server configurations and some operating systems, you cannot easily limit port access in this manner. Systems running multi-threaded servers, pre-spawned servers, or ones that do not support port-sharing require port redirection. That is, while the incoming connection is attempted at port XXXX, for example, the port 'redirects' the incoming connection to a different port&number, say YYYY. The 'redirected' port number may not be known in advance, meaning that in order to allow this type of connection, you'd have to open up the range of ports to which the connection could potentially be redirected.&Opening multiple holes in a firewall gives your firewall the consistency of 'Swiss cheese': lots of holes, meaning lots of potential security breaches. Also see limitations of using Checkpoint's Firewall-1 with SQL*Net, below.&You can, in theory, open up a hole for SQL*Net in some firewalls without using an SQL*Net application proxy. This is not supported in all configurations of the server, in all operating systems, or in all firewalls, and it is not 'secure.'&For more in formation on how you can do this, talk to your firewall vendor&Q:Does the COMPLETE communication mean tnsping, sqlplus or something else? Both tnsping and sqlplus are working once opening 1521.&A: NO reply潜在的危险：CONNECTIONS FAIL WITH ORA-12537 WHEN USE_SHARED_SOCKET IS SET IN 8.1.7BUG: 1566794
阅读(13767) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。oracle的listener无法启动，提示ORA-12535:TNS:操作超时！
本回答由提问者推荐
var sogou_ad_id=731547;
var sogou_ad_height=160;
var sogou_ad_width=690;RAC ORA-12170 ora-12535/tns-12535
现象：开发人员抱怨RAC出现了时连得上时连不上的情况，用SQLPLUS一试，果然有这样的情况：
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
ORA-12170: TNS: 连接超时
警告: 您不再连接到 ORACLE。
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& select instance_name from sys.v_$
INSTANCE_NAME
----------------
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
ORA-12170: TNS: 连接超时
警告: 您不再连接到 ORACLE。
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
SQL& conn system/*******@bjyd
检查两个节点主机和网络，均未开启防火墙：
[oracle@BJYD01 trace]$ su -
[root@BJYD01 ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
Chain FORWARD (policy ACCEPT)
num target prot opt source destination
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
[root@BJYD02 ~]# service iptables status
Firewall is stopped.
[root@BJYD02 ~]#
sqlnet开启16级别的trace:
Fatal NI connect error 12170.
&VERSION INFORMATION:
&TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
&Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
&Time: 10-7?? -:38
&Tracing not turned on.
&Tns error struct:
&ns main err code: 12535
&TNS-12535: TNS: 2&&&3?&&
ns secondary err code: 12560
&nt main err code: 505
&TNS-00505: 2&&&3?&&
nt secondary err code: 60
&nt OS err code: 0
&Client address: &unknown&
[10-7?? -:58:741] ntt2err: entry
[10-7?? -:58:741] ntt2err: soc 284 error - operation=1, ntresnt[0]=505, ntresnt[1]=60, ntresnt[2]=0
[10-7?? -:58:741] ntt2err: exit
[10-7?? -:58:841] nttcni: exit
[10-7?? -:58:841] nttcon: exit
[10-7?? -:58:841] nserror: entry
[10-7?? -:58:841] nserror: nsres: id=0, op=65, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
通常说来，ORA-12535/TNS-12535意味着在client和server之间的通讯时存在超时现象，有以下几种原因：
1.client和server之间设置了防火墙（包括OS层面的和网络层面的）
2.网速较慢导致出现超时
3.网卡故障
4.无法将主机名解释为IP地址也会导致超时
已经知道可能的原因了，那么可以通过排除法进行判断：
1,4经过检查可以排除
2,3都存在可能。
首先我假定2--是网速较慢导致timeout，所以我在在sqlnet.ora中设置
SQLNET.INBOUND_CONNECT_TIMEOUT=180
（180秒我们认为是一个足够大的时间了，在慢速网络中基本可以排除超时的情况）
但设置了这个参数之后，问题依旧，再加之经过网络工程师的检查，没发现网络有异常的现象。
所以，我认为基本可以排除原因2。
那么进一步推测，是原因3导致的吗？
1）我发现了偶尔从节点1到节点2（Public IP）SSH不通的现象：
[oracle@BJYD01 ~]$
[oracle@BJYD01 ~]$ ssh BJYD02
ssh: connect to host BJYD02 port 22: Connection timed out
[oracle@BJYD01 ~]$
[oracle@BJYD01 ~]$ ssh BJYD02
Last login: Thu Jul 11 17:10:49 2013 from BJYD01
[oracle@BJYD02 ~]$
但SSH Private IP没这个问题。
从节点2到节点1SSH无论Public IP还是Private IP都没问题。
2）ifconfig查看，发现节点2的Public IP所在的eth0网卡有较严重的drop包现象（该处未截屏记录下来），其它网卡以及节点1上的2个网卡均正常。
怀疑eth0网卡问题，尝试通过换掉eth0网卡解决问题。
解决方法：
最终将节点2的eth0网卡换掉之后，问题成功解决。
推断根本原因可能是：
1.节点2的eth0网卡老化导致故障；
2.节点2的eth0网口松动。
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'查看: 7320|回复: 9
ora-12535:TNS:操作超时
论坛徽章:1
我的ORACLE8.1.6服务器的服务是正常启动的，监听也显示正常启动的，PING 正常，文件访问也正常
在本机上用PLSQL Developer能正常连接，但是在已测试的7台PC中，其中2台可以正常连接，
其他的则提示：ORA-12535：TNS：操作超时；
用Net8 Configuration Assistant配置也是一样的提示：ORA-12535：TNS：操作超时
这些电脑平时一直都是可以正常连接ORACLE的
请各位高手予以指点
论坛徽章:27
tnsname.ora里配好了没?
论坛徽章:1
一直没修改过,以前可以正常连上
论坛徽章:23
没有在tnsnames.ora里面配连接串吧
论坛徽章:1
什么叫连接串 ?
论坛徽章:1
把可以连接的那台机的TNSNAME.ORA复制过去试试...
论坛徽章:1
我觉得不是客户端的问题,因为以前是可以的,又没有修改过
论坛徽章:1
安装了防火墙?
论坛徽章:27
都能PING通,
可以考虑把能连的计算机的TRANSNAME.ORA复制过去.
论坛徽章:1
奇怪的是--------我把客户端的ip地址改过之后就能连上了
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号出现ora-12535:TNS:超时操作的解决
&今天重装了系统，马上装了oracle
可发现登陆时，老提示ora-12535:TNS:超时操作。
&另外发现listener无法启动,用sqlplus可以登陆oracle,试了很多办法，如关闭防火墙等，原来是装了oracle后，我又改了机器名，所以listener无法监听到机器。解决方法很简单:
...\oracle\ora90\network\ADMIN下打开
tnsnames.ora和listener.ora，将里面的主机名（host）改为当前名称。
后来想进行数据导出，进入oms，但无法搜索到节点，不能配置sysman的首选身份证明，也就无法进行数据导入导出。后来查看服务有一个oracleorahome90agent没有启动，网上查找发现是机器名修改导致，
解决方法：删除%oracle_home%\network\agent\*.Q
，重启服务，服务OK。
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。