Solved

ORA-03113 error when quering database behind firewall

Posted on 2002-04-18
8
2,955 Views
Last Modified: 2011-08-18
Hi,

I am trying to query a database behind a firewall. Often when I query I get a proxy server error and the log files says "ORA-03113: end-of-file on communication channel sql
error." After going back and forward a few times with the browser, eventually we succeed to query the database and get the page. Does anyone know what could cause the problem and propose a probable solution?
Thanks in advance.
0
Comment
Question by:steelcsb
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 150 total points
ID: 6951560
i would guess your provider or dialer timed out,
look there
(just if you're mean with browser an internet-browser)

meikl ;-)
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6951584
hi there,

Which platorm: NT, Solaris, Linux -
Use Metalink "Top Tech Docs" to research such questions.  Below insight may be useful

Oracle Connection and Firewalls
-------------------------------
 
When the Oracle client makes a sqlplus connection to the database
(sqlplus userid/password@alias), it will compare the alias name you
supplied in the sqlplus line and look for a match in the tnsnames.ora
file or names server.  Once it obtains the address for the database
server, a connetion attempt is made to the server from the client.
The listener is contacted on the database server and port redirection
can take place depending on the platform, configuration of the init<sid>.ora
file and/or the Oracle product being used.  The OS will obtain a free port
from the OS and send back to the client via the listener the new port
assignment.  The client will then try to connect to the database on a
new port.

A remote Oracle client making a connection to an Oracle database can
fail if there is a firewall installed between the client and the server
if there is port redirection.  The firewall will block the port when
the Oracle client connects to the database and can fail with the client
receiving Oracle error ora-12203 or ora-12535.   The Client connection
failure is due to port redirection from the Windows operating system.  
Port redirection requires the Client to connect to the database using
a different port than originally configured in the configuration ora files.
Oracle MTS on Unix platforms, (without specifying the address with the
ports in the init ora file), Oracle SSL, and NT platforms will cause port
redirection.

A level 16 client trace file can verify if the problem is a firewall
issue.  In the sqlnet.ora file on the client add the followng lines:

trace_level_client  =  16
trace_file_client = client
trace_directory_client = < a valid directory and path > ie:  c:\temp

Save the changes to the sqlnet.ora file and try connecting with sqlplus
to force the error.  This will create the trace file.  Here are several
excerts from a level 16-trace file of what to look for.

The initial packets sent to the listener on port 1521 in trace file.

niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=server1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=v815.world)
(CID=(PROGRAM=D:\V815\BIN\SQLPLUSW.EXE)(HOST=server1)(USER=system))))
nladget: entry
nladget: exit
nscall: entry
nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1521
 

The received packet from the listener telling the client to use 1729 port.

nscon: recving a packet
nsprecv: entry
nsbal: entry
nsbgetfl: entry
nsbgetfl: normal exit
nsmal: entry
nsmal: 44 bytes at 0xb892d0
nsmal: normal exit
nsbal: normal exit
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 232 had bytes read=64
nttrd: exit
nsprecv: 64 bytes from transport
nsprecv: tlen=64, plen=64, type=5
nsprecv: packet dump
nsprecv:00 40 00 00 05 00 00 00  |.@......|
nsprecv:00 36 28 41 44 44 52 45  |.6(ADDRE|
nsprecv:53 53 3D 28 50 52 4F 54  |SS=(PROT|
nsprecv:4F 43 4F 4C 3D 74 63 70  |OCOL=tcp|
nsprecv:29 28 48 4F 53 54 3D 31  |)(HOST=1|
nsprecv:33 38 2E 32 2E 32 31 33  |38.2.213|
nsprecv:2E 36 31 29 28 50 4F 52  |.61)(POR|
nsprecv:54 3D 31 37 32 39 29 29  |T=1729))|    <- port change
nsprecv: normal exit
nscon: got NSPTRD packet
nscon: got 54 bytes connect data
nscon: exit (0)

The client resolving the connection to port 1729.

nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1729
nttbnd2addr: using host IP address: 138.2.213.61
nttbnd2addr: exit
nsc2addr: normal exit

Edit the trace file and you can see the send packets sent from the client
on port 1521 (or your port if different) to the listener.  There will be
receive packets packets returned from the server to the client reflecting
a new port assignment.  Then the client will send packets again from the
client only this time to a different port.  The connection will then fail
at this point in the trace file.

The port that is assigned to the client is randomly chosen by the
operating system and can't be modified.  It can be any free port available
that the server determines is not is use by any other software or hardware.

Once it is determined that the problem is the firewall causing the
connection to fail, the next step is to select a solution to resolve
the issue

Being that Oracle is working correctly, and the firewall is working
correctly, there are several solutions to correct the problem to
allow the clients to connect to the database.


Solution: Firewall Vendor
The first solution is to contact the firewall vendor and see if they have
an upgrade to allow for oracle connectivity with OS port redirection.  If
the firewall software can be upgraded, it is the best solution to follow.


Solution: Connection Manager
The second solution is used for Oracle net8 versions and above, and
requires setting up connection manager (cman) to allow the clients to
connect through a firewall (note:2077721.6 explains cman in more
detail with configuration examples).  Connection Manager is an executable
that can be run from the bin directory, which allows clients to connect
when a firewall is in place between the client and the server.
Connection Manager is similar to a listener.  It reads a cman.ora file,
which contains an address that Connection Manager listens on for incoming
connections, usually default ports of 1610 or 1630.  Connection Manager
starts similar to the listener and will enter a listening state.  The
Oracle client needs to be running net8 or above, and will need to have
the following entered into the tnsnames.ora file.

cmantest =
   (description =
     (address_list =
       (address =                  <- first address is to the cman            
         (protocol=tcp)
         (host=hostname or ip of cman)
         (port=1610)
       )
       (address=                  <- second address is to listener
         (protocol=tcp)
         (host=hostname or ip of listener)
         (port=1521)
       )
     )
     (connect_data = (sid =  sidname))
     (source_route = yes)            <-This tells the client that it is
    )                                using cman and it must take the
                                          first two addresses listed.

 
When the client contacts the connection manager, cman will look in
cache for the second address the client brought with it.  The second
address will point to the host machine where the listener is running.
Cman will then use that address to direct the client to the listener
and then the connection to the database will be made.

There are many documents on metalink, which will explain connection
manager and configuration in more detail.  This note is to explain the
connection process with firewalls, and not to go into detail with
connection manager.


Solution: Use_Shared_Socket
A third solution for NT servers is to add the use_shared_socket = true
into the registry (see 124140.1).  This will allow the OS to share port
1521 and clients will then stay on 1521 when connecting to the database
and will not be port redirected.  A downfall of this option is all connetions
will stay on the listener port and if the listener is stopped or
restarted all the connections will be severed from the database.

Unix
Unix Platforms can have problems connecting to a database through a
firewall if they have implemented Multi Threaded Server (MTS).  MTS
dispatchers will redirect connection ports like NT Platform does as
mentioned above.

Solution: Setting MTS ports
A work around for this is to specify the port in the mts parameters of
the init.ora file (note:1016349.102).  This will allow the dispatcher
to use the port specified and will not select a port a random.  Then make
sure the port is open on the firewall.  The following example will show
the ports set to 2450 and 3125.  Please set these parameters according to
your individual systems.  This solution will also work with NT.

Example
mts_dispatchers="(address=(protocol=tcp)(host=hostname)
                 (port=2450))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)(host=hostname)
                 (port=3125))(dispatchers=1)"


SSL
Using SSL will cause Port redirection.  The work around is to select
and set the ports using MTS in the init.ora, or by setting the Ports
with Connection Manager in the cman.ora file.



RELATED DOCUMENTS
-----------------
note:1016349.102
note:66382.1
note:124140.1

********************

Firewalls, Windows NT, and Redirections:
========================================

On 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 through.  
The resulting error is usually a TNS-12203.

There are two ways to resolve this issue:

(i) The first way is to use a firewall that has a SQL*Net proxy built into
    it. The way this works is that the SQL*Net proxy starts another listening
    process (usually on port 1610). This causes the firewall to act as a
    Multi-Protocol Interchange.  So, by using the "tnsnav.ora" file on the
    client, you connect to port 1610 (the firewall). The firewall passes the
    connection to the server. The server gives a redirect to the client. The
    client reconnects to the firewall proxy on port 1610, and the firewall
    passes the connection to the Oracle thread on the wild-card listening
    address. Here's what the connection flow would look like:

        1. 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) The second way to resolve this issue is to upgrade the server to 8.0.x
     and use the USE_SHARED_SOCKET parameter in the registry. With this
     method, it doesn't matter what kind of firewall you have. The syntax
     for this parameter is:

         USE_SHARED_SOCKET = TRUE

     Place the parameter in the registry under HKEY_LOCAL_MACHINE:Software:
     Oracle

     Restart Oracle and the listener 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:

                    +---<O>--------<O>----<O>---<O>--+
                    |                                |
                    |     This square represents     <O>
                    |     a listening socket for     |
                    |     port 1521.                 |
                    <O>                              |
                    |     <O> = oracle thread        <O>
                    |     <L> = listener             |
                    |                                |
                    +-<O>-------<L>--<O>------<O>----+

     The operating system then does a poll() or a select() on the socket to
     test for any data. If any of the threads have data, a signal handler is
     used to contact the application and inform it of the new data.

     The disadvantage of USE_SHARED_SOCKET is that if the listener shuts down,
     all connections are dissolved.


Finally, a very common question concerning the listener and port numbers is
why different port numbers show up in the "listener.log" file.  What you are
seeing is the client's source port and client's source IP address.  Here is
how 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.

Question: Will this be a problem with the firewall?
Answer:   No.  The firewall will restrict incoming connections, but will
          freely let any connection on any port out (which is okay).  
   
Here is what it might look like:

                           Firewall
                <-------------||---------\
                <-------------||---------\\
    [CLIENT]----------------->||          \---[SERVER]
                <-------------||---------//
                <-------------||---------/



*** the end.
0
 

Author Comment

by:steelcsb
ID: 6952839
It is an extranet application, we checked the connections and they were okay.
The message we get is an ora-03113 end-of-file on communication channel sql. We checked the sql timeout settings, and it is still not working. We are going through the document oralcetune sent us. But in that case would it not always not work? Does anyone have any other suggestions, or experiences on this topic?
0
 
LVL 5

Expert Comment

by:sora
ID: 6952891
Oracletune:

Just a friendly suggestion. Why not just post the URL from where you did a copy-paste?


sora
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 6953693
1) A connection between one proxy/firewall in intranet/LAN and another is possible if there is internet connection from both sides.

2) A connection from a client standalone machine to proxy/firewall in intranet/LAN is also ok if internet connection exists.

3) A connection from a standalone client to standalone server is also ok with internet connection.

4) A connection from client other than proxy/firewall in one intranet/LAN to oracle server on proxy server of the other and if both client and server are non proxies/firewalls, the connection has some conditions to be met.

5) The server or client within intranet should have a direct IP and a configuration has to be done for which you can contact your systems/hardware people.

6) The inranet domain and not mail domain should be appended to SID/service/database/instance name to form global database name.

7) The net 8 configuration of client should have local net service name and actual one as GDN i.e. SID + domain.

8) Without any configuration, a direct IP client m/c becomes a valid client if Nortel VPN( virtaul private network connection ) exists to database server.

ORA-03113 end-of-file on communication channel
Cause: An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8, two task, software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down.

Action: If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest
an Oracle internal error that requires the assistance of customer support.

So, I think there was a restart/outage at server end or an adapter problem. Since you are trying to connect via application whose front end is browser, try using a different driver which always helps as a good alternative. A thin driver will not even require a client installtion and net 8 configuration. Just place the drivers in zip, jar etc files in same oracle directory path and keep this classpath and mention the details in application connection pooling.

ex: c:\Oracle\Ora81\jdbc\lib\classes12.zip OR classes111.zip.

This will avoid shadow two-task process associated with a Net8 connect has terminated abnormally.

The third possibility is a network failure. This can occur due to TCP/IP protocol problem which is rare, hardware connection problem or configuration problem.

Another very important reason for your problem can be IP changes. A local IP can connect to local IP. Direct to direct. But only local to only direct OR only direct to only local will not work.

Even in case of other IP combinations between oracle client and server, it is important which is primary and which is secondary. Gateway and DNS may also be responsible for network failure. So, it is a tall order but I hope you can pick your answer from the big range that I am offering.

All the best:)

--- k_murli_krishna
0
 
LVL 5

Expert Comment

by:ORACLEtune
ID: 6953834
thanks sora. will do.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 7044895
steelcsb: Close the question please if you are done with it.
--- k_murli_krishna
0
 

Author Comment

by:steelcsb
ID: 7083767
It was a problem with the tunneling. Thnx for the reactions
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now