ORA-03113: end-of-file on communication channel

Problem:Users are not able to login into the application,they are getting a message i.e End of file communication channel.

In the sqlnet.log file, the errors could be:

Tracing to file: D:\oracle\ora90\network\ADMIN\TRACE\NEWTRACE.TXT
Tns error struct:
ns main err code: 12560
TNS-12560: TNS:proto adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 126
nt OS err code: 0

Solution:

Try to trace the client and the server connection using:

Place these parameters in the sqlnet.ora file on the client node.

TRACE_LEVEL_CLIENT=16
TRACE_FILE_CLIENT=CLIENT
TRACE_DIRECTORY_CLIENT=full path to directory where you want the trace file created.
TRACE_TIMESTAMP_CLIENT=on
TRACE_UNIQUE_CLIENT=on

Place These parameters in the sqlnet.ora file on the server node.

TRACE_LEVEL_SERVER=16
TRACE_FILE_SERVER=SERVER
TRACE_DIRECTORY_SERVER=full path to directory where you want the trace file created.
TRACE_TIMESTAMP_SERVER=on

Data Collected at client side

From client trace new trace.txt

19-JUN-2008 13:48:13:566 ntt2err: entry
19-JUN-2008 13:48:13:566 ntt2err: soc 264 error – operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
19-JUN-2008 13:48:13:566 ntt2err: exit
19-JUN-2008 13:48:13:566 nttrd: exit
19-JUN-2008 13:48:13:566 nsprecv: transport read error
19-JUN-2008 13:48:13:566 nsprecv: error exit
19-JUN-2008 13:48:13:566 nserror: entry
19-JUN-2008 13:48:13:566 nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=
0, ora[1]=0, ora[2]=0
19-JUN-2008 13:48:13:566 nsrdr: error exit
19-JUN-2008 13:48:13:566 nsdo: nsctxrnk=0
19-JUN-2008 13:48:13:566 nsdo: error exit
19-JUN-2008 13:48:13:566 nioqrc: recv: packet reader error -> translated to IFCR_EOF
19-JUN-2008 13:48:13:566 nioqrs: entry
19-JUN-2008 13:48:13:566 nioqrs: state = interrupted (1)
19-JUN-2008 13:48:13:566 nioqsm: entry
19-JUN-2008 13:48:13:566 nioqsm: Sending break packet (1)…
19-JUN-2008 13:48:13:566 nttwr: entry
19-JUN-2008 13:48:13:566 ntt2err: entry
19-JUN-2008 13:48:13:566 ntt2err: soc 264 error – operation=6, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
19-JUN-2008 13:48:13:566 ntt2err: exit
19-JUN-2008 13:48:13:566 nttwr: exit
19-JUN-2008 13:48:13:566 nserror: entry
19-JUN-2008 13:48:13:566 nserror: nsres: id=5060, op=152, ns=12571, ns2=12560; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
19-JUN-2008 13:48:13:566 nioqsm: send-break: failed to send break…
19-JUN-2008 13:48:13:566 nioqper: error from send-marker
19-JUN-2008 13:48:13:566 nioqper: nr err code: 0
19-JUN-2008 13:48:13:566 nioqper: ns main err code: 12570
19-JUN-2008 13:48:13:566 nioqper: ns (2) err code: 12560
19-JUN-2008 13:48:13:566 nioqper: nt main err code: 530
19-JUN-2008 13:48:13:566 nioqper: nt (2) err code: 53
19-JUN-2008 13:48:13:566 nioqper: nt OS err code: 0
19-JUN-2008 13:48:13:566 nioqsm: exit
19-JUN-2008 13:48:13:566 nioqer: entry
19-JUN-2008 13:48:13:566 nioqer: incoming err = 12152
19-JUN-2008 13:48:13:566 nioqce: entry
19-JUN-2008 13:48:13:566 nioqce: exit
19-JUN-2008 13:48:13:566 nioqer: returning err = 3113
19-JUN-2008 13:48:13:566 nioqer: exit
19-JUN-2008 13:48:13:566 nioqrs: nioqrs: Couldn’t send break. returning 3113
19-JUN-2008 13:48:13:566 nioqrs: exit

As you can see the main error in the client trace file i.e nsprecv: transport read error, so it means there is some problem in the network which cause the connection to terminate.

Action Plan;

For Windows NT

The underlying reasons of these errors are a synchronization error in the TCP/IP layer on NT. To help prevent this kind of errors, a few things can be adjusted to help the synchronization:

A#. TCP.NODELAY parameter
Try adding the below parameter in the client sqlnet.ora :

tcp.nodelay = yes

In most cases, TCP/IP info send across the network is buffered till at least a complete network packet can be send. This means that in certain cases commands are not issued directly, and kept buffered until some other info can be send as well.This has the potential to generate timeouts and errors.To avoid this, this delay can be switched off.

tcp.nodelay = yes

B#. Disabling AUTOMATIC_IPC on Clients

On client PC’s, checking for IPC connections is pointless as there is never a database installed on them. So, in order to save some time during the connections phase, set AUTOMATIC_IPC=OFF in the “SQLNET.ORA” file.

C#. Add the following to the Server and Client sqlnet.ora file

Disable_OOB = ON

D#.Increase TcpMaxDataRetransmissions parameter in the Windows Registry

On Windows NT/2000:

HKEY_LOCAL_MACHINE
SYSTEM
CurrentControlSet
Services
Tcpip
Parameters

add a key “TcpMaxDataRetransmissions” (REG_DWORD) and set its value to 10.
On the Edit menu, click Add Value, and then add the following:
Value Name: TcpMaxDataRetransmissions
Value Type: REG_DWORD – Number
Valid Range: 0 – 0xFFFFFFFF
Default Value: 5 Decimal
New Value: 10 to Decimal

The value may vary as it is dynamically adjusted.

E#:

If you not using NTS authetication for client-server then comment out the SQLNET.AUTHENTICATION_SERVICES parameter in the client side sqlnet.ora.

F#:

Check whether the listener service and database service are started as same domain user / local account as applicable.
If this is a MS AD environment, then try starting with Domain account.

G#:

Check whether there is any firewall between the client and server. if so, check whether the all the ports are reachable for the client. for eg.for the uploaded client trace port used is 1521.

C:\> telnet <server hostname/ ip > 1521

if this fails then the firewall /network device is blocking the connection and hence contact your network / firewall administrator to take necessary actions.