The problem
The following was observed in an environment where Oracle 10g database was deployed on the server and developers predominantly used TOAD and SQL*Plus for their development work. The clients and database server are separated by a Cisco firewall.
Initial observations
- All connections as sys user using TOAD tend to hang
- Port 1521 was possible to be reached using a telnet client indicating that there is no port block issue
- Sys connections through SQL*Plus worked fine
- All connections as a normal user with lesser privileges using TOAD work fine
- Similar observations were obtained for ALL other databases which were behind the same Cisco firewall. As soon as a database was brought out of the firewall, or when the firewall was bypassed, all connections went through fine regardless of the client software.
Further tests
The following further test was done to narrow down the problem:
- Different types of objects were accessed using the normal user (The connectivity of which was possible using TOAD or SQL*Plus). It was observed that while retrieving any large text object (Large procedure, large package etc.) the session hung even for a normal user.
- This indicates a throughput issue from the client to server. This also relates perfectly to the sys user connection problems using TOAD since TOAD queries numerous metadata tables on the database server at the time of connection when the connecting user is sys.
What the Cisco documentation says
Finally, the issue was tracked down to SQL*Net inspection feature of Cisco firewalls. Here is a brief from Cisco documentation on this feature:
SQL*Net inspection is enabled by default.
The SQL*Net protocol consists of different packet types that the adaptive security appliance handles to make the data stream appear consistent to the Oracle applications on either side of the adaptive security appliance.
The default port assignment for SQL*Net is 1521. This is the value used by Oracle for SQL*Net, but this value does not agree with IANA port assignments for Structured Query Language (SQL). Use the class-map command to apply SQL*Net inspection to a range of port numbers.
Note: Disable SQL*Net inspection when SQL data transfer occurs on the same port as the SQL control TCP port 1521. The security appliance acts as a proxy when SQL*Net inspection is enabled and reduces the client window size from 65000 to about 16000 causing data transfer issues.
Summary
Thus, in such observations of large data transfer related operations not responding properly in a client server Oracle database environment, it is advisable to check if this specific Cisco firewall scenario exists before proceeding with other troubleshooting operations.
