Port 1521 Open on Firewall But Unable to Connect Due to Errors: ORA-12535,TNS-12203 [ID 361284.1]

Port 1521 Open on Firewall But Unable to Connect Due to Errors: ORA-12535,TNS-12203 [ID 361284.1]

修改时间 08-OCT-2008 类型 PROBLEM 状态 PUBLISHED

In this Document
Symptoms
Changes
Cause
Oracle dedicated databases on Windows platforms
Oracle shared-mode databases
Oracle 10g direct handoff feature on Windows platforms
Oracle RAC clusters
Solution
References


Applies to:

Oracle Net Services - Version: 8.1.7.4.0 to 10.2.0.1.0
This problem can occur on any platform.

Symptoms

Firewall is present between the Oracle database and the clients. Port 1521 is open in the firewall.
Client connections time out or fail to complete giving errors like ORA-12535 or TNS-12203.

Changes

  1. Firewall service has been installed between database and clients.
  2. One of these:
  • Database has been switched from dedicated mode to shared mode or client connections were restricted to shared mode.
  • Database has been migrated to RAC setup.

Cause

Oracle Listener usually listens on port 1521. It is a common misconception that if you enable access through the firewall by allowing connections to port 1521, then all SQL*Net clients will be able to connect to Oracle databases. There are some situations, including default configured Oracle databases on Windows platforms and shared-mode Oracle databases and RAC clusters, when this is not trued, due to the redirection mechanism in the listener.

Oracle dedicated databases on Windows platforms

To understand why the connection fails, it is necessary to understand how a SQL*Net connection on Windows works -- see more details in Note 68652.1.

On the Windows platform, when a connection 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.

At this point, 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), firewalls will not let the connection through. The resulting error is usually a TNS-12203 or ORA-12535.

Oracle shared-mode databases

Client connections will need to be handed off by the listener to an already existent shared dispatcher process. Since the listener is not spawning anymore a shadow process it has no mechanism to pass the connection's socket descriptor to the appropriate server process (the dispatcher). So the listener will have to send back the client a REDIRECT address/port combination specific to the chosen dispatcher.

The dispatchers ports are set when they are spawned at database startup (or when adjusting the number of dispatchers). By default the port numbers are chosen (randomly) by the operating system so the redirected client connections will get dropped by the firewall.

Oracle 10g direct handoff feature on Windows platforms

Since Oracle 10g, the built-in direct handoff technology allows the listener to pass the incoming client connection to the local dispatcher or dedicated server process without generating REDIRECT messages; this way the connection will remain on the default listener port. On the other hand, passing client connections to remote dispatchers (found on remote database servers and registered by means of remote_listener database parameter) still require REDIRECTs — see next section.

In order to employ the direct handoff feature you would need to add a DIRECT_HANDOFF_TTC_ statement in LISTENER.ORA. By default this feature is disabled.


Oracle RAC clusters

The RAC cluster load balancing is relies on the listeners on each node being able to reroute client connections to the least loaded node at the moment. This routing is implemented using REDIRECT packets -- the client is redirected to the new node upon connecting to the listener.

Depending on listener services registration data, the clients may be redirected to different ports and/or hosts which may not be permitted by the firewall(s). For example, nodes with shared-mode instances will have the dispatchers registered by default at random ports or the database instances may register with different IP addresses to the listener (see Note 364855.1).

Solution

Here are some alternative solutions to fix the problem in the order of preference.

(1) Use Firewall content-filtering module (SQL*Net proxy)

  • Many commercial firewall/security vendors have implemented content-based modules for the Oracle SQL*Net protocol and they will comport as a SQL*Net level proxy for Oracle database connections; this may also have the advantage of securing the Oracle connections through content-inspection.
  • To implement this solution contact your firewall/network administrator and/or your firewall vendor
  • For mode details see Note 2084440.6 and Note 45226.1

(2) Use Oracle Connection Manager (CMAN)
  • This solution will make the REDIRECT happen inside the firewall and the client will not see it; CMAN comports as a proxy service between the client and the real database listener.
  • For details on how to implement this solution please see Note 2077721.6

(3) Use shared server mode / MTS with certain port ranges to be allowed in the firewall
  • Since only specific ports are allowed to pass through the firewall you may reserve a port range and configure the database in shared mode with the dispatchers listening in that port range. This way the clients will be REDIRECTed to a port range allowed by the firewall
  • For details on how to implement this solution please see Note 1016349.102

(4) Use shared sockets (USE_SHARED_SOCKET) for Windows platforms
  • In order to avoid REDIRECTs, the newest TCP/IP Socket implementation of Windows NT 4.0 (available with Service Pack 3) and Windows 2000 can now make use of the USE_SHARED_SOCKET parameter. This way the entire SQL session will take place in the initial connection to the listener (port 1521).
  • Please be aware that using this option has a downside: restarting the listener will disconnect all running sessions
  • For details on how to implement this solution please see Note 124140.1

Additionally, please be aware that most firewalls apply stateful filtering rules on TCP connections which implies that client connections being idle over a certain time limit will be disconnected. See Note 257650.1 for more details on this subject.

References

NOTE:124140.1 - How to configure USE_SHARED_SOCKET on Windows NT/2000
NOTE:2077721.6 - Installation and Configuration of Net8 Connection Manager (CMAN)
NOTE:2084440.6 - Oracle and Firewalls: Answers to Frequently Asked Questions
NOTE:257650.1 - Resolving Problems with Connection Idle Timeout With Firewall
NOTE:364855.1 - RAC Connection Redirected To Wrong Host/IP ORA-12545
NOTE:397393.1 - External Clients behind NAT Translation / Port Forwarding / Tunneling Fail to Connect to Database (ORA-12535 or ORA-12541)
NOTE:45226.1 - SQL*Net and Firewalls
NOTE:68652.1 - Solving Firewall Problems on Windows



显示相关信息 相关的


产品
  • Oracle Database Products > Oracle Database > Net Services > Oracle Net Services
关键字
CMAN; SHARED SERVER; ORACLE LISTENER; REDIRECT; USE_SHARED_SOCKET; CANNOT ESTABLISH CONNECTION; CONNECTION REFUSED; CONNECTION RESET BY PEER; CONNECTION TIMED OUT
错误
TNS-12203; TNS-12535; ORA-12535
[@more@]
请使用浏览器的分享功能分享到微信等