swetha
asked on
ORA-12571, ORA-01001, ORA-03114
Hi,
We use a software called Docent 4.7 that is connected to Orace 7.3 We are having problems with the connectivity
to the database server. When the Docent server is restarted the database connectivity is fine, but in
a 10-15 minutes the connection goes down with one or more of the following error entries in Docent log
files:
1) Database Error: error number: 12571, server: docentdb, severity: 54, message: [SERVERERROR] ORA-12571:
TNS:packet writer failure
2) Database Error: error number: 1001, server: docentdb, severity: 0, message: [SERVERERROR] ORA-01001:
invalid cursor
3) Database Error: error number: 3114, server: docentdb, severity: 0, message: [SERVERERROR] ORA-03114:
not connected to ORACLE
Could someone please help us out with this?
Thanks,
Swetha
We use a software called Docent 4.7 that is connected to Orace 7.3 We are having problems with the connectivity
to the database server. When the Docent server is restarted the database connectivity is fine, but in
a 10-15 minutes the connection goes down with one or more of the following error entries in Docent log
files:
1) Database Error: error number: 12571, server: docentdb, severity: 54, message: [SERVERERROR] ORA-12571:
TNS:packet writer failure
2) Database Error: error number: 1001, server: docentdb, severity: 0, message: [SERVERERROR] ORA-01001:
invalid cursor
3) Database Error: error number: 3114, server: docentdb, severity: 0, message: [SERVERERROR] ORA-03114:
not connected to ORACLE
Could someone please help us out with this?
Thanks,
Swetha
Check the listener.ora file on the server.
Make sure automatic_ipc = off
For some reason this makes the listener unstable. Ours would stay up for about a day to a week.
Give it a try and tell us what happens.
banic
Make sure automatic_ipc = off
For some reason this makes the listener unstable. Ours would stay up for about a day to a week.
Give it a try and tell us what happens.
banic
Man, I'm not awake yet...
After the change you have to cycle the listener.
from a prompt...
>lsnrctl
LSNRCT> stop
LSNRCT> start
LSNRCT> exit
or
LSNRCT> reload
Also, Have a look in your Listener.log
This file might have some juice...
Terry
After the change you have to cycle the listener.
from a prompt...
>lsnrctl
LSNRCT> stop
LSNRCT> start
LSNRCT> exit
or
LSNRCT> reload
Also, Have a look in your Listener.log
This file might have some juice...
Terry
ORA-01001 is "invalid cursor" you can try to debug the code, or even trace it.
If you need more info on tracing, things get a little more wordy.
If you need more info on tracing, things get a little more wordy.
Hi Swetha,
Looks like your connection is getting expired after certain time of inactivity. There may be several reasons for this.
> Verify in your SQLNET.ORA if you have SQLNET.EXPIRE_TIME variable set. If so, comment that out.
> Verify if you have resource_limit set to true (from svrmgrl: show parameter resource_limit.
Then check the default profile for idle_time setting (select * from dba_profiles where profile = 'DEFAULT' and RESOURCE_NAME = 'IDLE_TIME';). If your resource limit is set to TRUE and you have a value other than UNLIMITED for idle_time, then this is what is disconnecting the users.
> Or else may be you are experiencing a problem with your network. Typically, these errors (12571, 1001, 3114) occur due to a loss of network connectivity. However if you are on a large network (WAN), you may need to increase the settings for your TCP/IP registry entry to 15 retries.
If you are using Windows operating system as a client to connect to Oracle, set the following variable in your registry.
For Windows NT:
Go to HKEY_LOCAL_MACHINE/SYSTEM/
For Windows 95:
Go to
HKEY_LOCAL_MACHINE/SYSTEM/
The reason is SQL*Net tries to transmit the packet five times before it detected the network was down. This is a default of TCP/IP on Windows NT and Windows 95.
Possibly the last packet sent is not being received by the server which returns the error 54, connection reset/network is busy. SQL*Net assumes the server is down and closes the connection.
Cheers,
Ramesh
Hi Swetha,
You can also think of the following points,
TCP/IP keepalive on NT
KEEPALIVE is an extension to TCP/IP which enables the closing of
dead connections which are no longer being used.
Problems can occur when the server does not close a connection
after a PC client process has disappeared. This typically happens
when a PC user switches off or reboots their machine while still
connected to Oracle. Note that this is not an Oracle problem, but
a limitation of TCP/IP, which has no way of knowing for sure
whether a remote connection has disappeared.
This feature is enabled by default on NT. Problem can occur however
if the timeout period is too fast for some heavily used or slow
network. In those conditions, the KEEPALIVE registry value can be
used to specify a KEEPALIVE value before a connection gets cut.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
TCP/IP
Parameters
KeepAlive REG_DWORD "number"
A value of '10' is a common value specified for this variable.
Again, this parameter can be useful on both client and server.
Start with the machine generating the error, and if needed, also add
it on the machine on the other side.
TCP/IP timeouts on Windows 95
The same parameter can also be specified on Windows 95. It has the
same functionality, only the location of the parameter in the
registry is different.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Winsock
Parameters
TcpMaxDataRetransmissions REG_DWORD "number"
This parameter is not present in the registry by default. This
means that the first time, this parameter will need to added to
this registry key.
The purpose and behavior of the parameter is the same on the Windows 95
and Windows 98, as on the Windows NT platform.
SDU & TDU parameters
Part of this problem is the sequence of information that gets transmitted.
If there are disruptions in the sequence, the errors ORA-12151 and
ORA-12571 can also appear, alerting the application that not all information
has been send across the network succesfully.
The sequence of information is determined by the amount of data the program
is sending, and the actual size the protocol can send across the network
at a time.
The more data the program wants to send in one 'go', the more sequences and
transport packet split-ups will have to be made.
By default, SQL*Net is using an SDU (Session Data Unit) of 2048 bytes (2Kb)
and a TDU (Transport Data Unit) of 32768 (32Kb) bytes.
On standard Ethernet connections, with no special modifications made, the
SDU is 1500 bytes, while the TDU is 8760 bytes.
With these values, each data request made by SQL*Net will have to be split
up in several smaller packets to actually be able to transmit.
Therefore, to minize the creation the additional packets, it is advised, in
case of these errors, to synchronize the SDU and TDU parameters at the
SQL*Net level with those of the actual network topology used.
These SDU and TDU parameters have to be specified at both the client and
the server configuration files:
TNSNAMES.ORA:
-------------
ORCL.WORLD =
(DESCRIPTION =
(SDU=1500)
(TDU=8760)
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL=TCP)(Host=fooba r)(Port=15 21))
)
(CONNECT_DATA =
(SID = ORCL)
)
)
LISTENER.ORA:
-------------
SID_DESC_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 1500)
(TDU = 8760)
(SID_NAME = ORCL)
)
)
For more information about the SDU and TDU parameter, see Note 44694.1,
Note 44694.1: SQL*Net Packet Sizes (SDU & TDU Parameters)
-- Setting a new TDU size on Windows NT
You can modify the TDU size on NT, via the TcpWindowSize parameter:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Tcpip
Parameters
TcpWindowSize REG_DWORD "number"
Hope this helps you to solve your problem
Sameer
You can also think of the following points,
TCP/IP keepalive on NT
KEEPALIVE is an extension to TCP/IP which enables the closing of
dead connections which are no longer being used.
Problems can occur when the server does not close a connection
after a PC client process has disappeared. This typically happens
when a PC user switches off or reboots their machine while still
connected to Oracle. Note that this is not an Oracle problem, but
a limitation of TCP/IP, which has no way of knowing for sure
whether a remote connection has disappeared.
This feature is enabled by default on NT. Problem can occur however
if the timeout period is too fast for some heavily used or slow
network. In those conditions, the KEEPALIVE registry value can be
used to specify a KEEPALIVE value before a connection gets cut.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
TCP/IP
Parameters
KeepAlive REG_DWORD "number"
A value of '10' is a common value specified for this variable.
Again, this parameter can be useful on both client and server.
Start with the machine generating the error, and if needed, also add
it on the machine on the other side.
TCP/IP timeouts on Windows 95
The same parameter can also be specified on Windows 95. It has the
same functionality, only the location of the parameter in the
registry is different.
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Winsock
Parameters
TcpMaxDataRetransmissions REG_DWORD "number"
This parameter is not present in the registry by default. This
means that the first time, this parameter will need to added to
this registry key.
The purpose and behavior of the parameter is the same on the Windows 95
and Windows 98, as on the Windows NT platform.
SDU & TDU parameters
Part of this problem is the sequence of information that gets transmitted.
If there are disruptions in the sequence, the errors ORA-12151 and
ORA-12571 can also appear, alerting the application that not all information
has been send across the network succesfully.
The sequence of information is determined by the amount of data the program
is sending, and the actual size the protocol can send across the network
at a time.
The more data the program wants to send in one 'go', the more sequences and
transport packet split-ups will have to be made.
By default, SQL*Net is using an SDU (Session Data Unit) of 2048 bytes (2Kb)
and a TDU (Transport Data Unit) of 32768 (32Kb) bytes.
On standard Ethernet connections, with no special modifications made, the
SDU is 1500 bytes, while the TDU is 8760 bytes.
With these values, each data request made by SQL*Net will have to be split
up in several smaller packets to actually be able to transmit.
Therefore, to minize the creation the additional packets, it is advised, in
case of these errors, to synchronize the SDU and TDU parameters at the
SQL*Net level with those of the actual network topology used.
These SDU and TDU parameters have to be specified at both the client and
the server configuration files:
TNSNAMES.ORA:
-------------
ORCL.WORLD =
(DESCRIPTION =
(SDU=1500)
(TDU=8760)
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL=TCP)(Host=fooba
)
(CONNECT_DATA =
(SID = ORCL)
)
)
LISTENER.ORA:
-------------
SID_DESC_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 1500)
(TDU = 8760)
(SID_NAME = ORCL)
)
)
For more information about the SDU and TDU parameter, see Note 44694.1,
Note 44694.1: SQL*Net Packet Sizes (SDU & TDU Parameters)
-- Setting a new TDU size on Windows NT
You can modify the TDU size on NT, via the TcpWindowSize parameter:
HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
Tcpip
Parameters
TcpWindowSize REG_DWORD "number"
Hope this helps you to solve your problem
Sameer
Please update and finalize this old, open question. Please:
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks,
** Mindphaser - Community Support Moderator **
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.
EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.
Thanks,
** Mindphaser - Community Support Moderator **
P.S. Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
No way to know the correct solution. Lacking timely response from swetha or contributing experts, but seeing that there is some useful info here, I recommend:
Refund points and save as a 0-pt PAQ.
DanRollins -- EE database cleanup volunteer
Refund points and save as a 0-pt PAQ.
DanRollins -- EE database cleanup volunteer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Enable tracing at client (CLIENT_TRACE_LEVEL=ADMIN in sqlnet.ora) and see what client logs say after the error.
F.