Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to analyze and solve the ORA-03135 error report. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Recently, it is found that the database connection is disconnected from time to time in a business application system in the production environment, and the problem of normal connection occurs after a second request.

The specific error message in the system log is as follows: ORA-03135: connection lost contact\ nprocess ID: 9349\ nsession ID: 2834 serial number: 64475 ".

Oracle version: 11.2.0.4

Log information:

Error message: ORA-03135: connection lost

Stack information: at Oracle.ManagedDataAccess.Client.OracleException.HandleError (OracleTraceLevel level, OracleTraceTag tag, Exception ex)

At OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse (Accessor [] & defineAccessors, Accessor [] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64 [] scnFromExecution, Boolean& bAllPureInputBinds, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64 [] & rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1 & implicitRSList, Boolean bLOBArrayFetchRequired)

At OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader (String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64 [] scnForExecution, Int64 [] & scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)

At Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader (Boolean requery, Boolean fillRequest, CommandBehavior behavior)

At Oracle.ManagedDataAccess.Client.OracleDataAdapter.Fill (DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

There are many reasons for ORA-03135, and the problems are likely to be caused by network devices, operating systems and databases, most likely due to network and firewall configuration.

Solution:

After communicating with the network team engineer, it is confirmed that the long connection setting is not opened before fire prevention, and the problem is solved after it is turned on.

Supplement the concepts of long connections and short connections:

The concept of ① persistent connection

The persistent connection feature is used to set the ultra-long retention time of a specific data flow, so that the session connection retention time of the data stream is not limited by the global aging time. In fact, there is a contradiction between this special service and the current implementation mechanism of stateful firewall in the industry.

In order to ensure the security of the internal network, the default duration of each session on the firewall is relatively short. For example, by default, the retention time of TCP is 1200s and the retention time of UDP is 120s.

Normally, when the interval between two consecutive messages of a TCP session arriving at the firewall is greater than the holding time of the session, in order to ensure the security of the network, the firewall will delete the corresponding session information from the session table. After the subsequent message arrives at the firewall, the firewall discards the message according to its own forwarding mechanism, resulting in an interruption of the connection. In practical applications, users need to query the data on the server, and these query intervals are much larger than the default session persistence time of TCP/UDP. At this point, you need to keep the TCP connection on the firewall for a relatively long time. When the message of a session does not reach the firewall for a long time and then arrives again, it can still pass through the firewall. This technology is called long connection.

The concept of ② short connection

Some applications initiate connections frequently, and if they do not shorten their session persistence time, the number of sessions in the firewall will soar, thus dragging down the firewall. Maintaining too many sessions is not necessary for the firewall. on the contrary, when too many system resources are spent on session persistence, it will damage the ability to generate sessions per second, which is an equally important performance indicator. Setting an excessively high number of sessions reduces the ability to generate sessions per second, and the result is to retain some virtual numbers of sessions that will never be used.

Therefore, we can shorten the holding time of some sessions according to the actual needs of the network application environment, so as to reduce the workload of the firewall and improve the network performance.

References for long and short connections:

Https://www.cnblogs.com/lcplcpjava/p/6581179.html

Tips on knowledge:

Log in to the oracle server by oracle local login: sqlplus "/ as sysdba". You don't need a user name and password to log in.

Disable method:

Under 1.linux

Go to the directory $ORACLE_HOME/network/admin

Cd $ORACLE_HOME/network/admin

Vi sqlnet.ora

In the file sqlnet.ora, add one line to it:

Sqlnet.authentication_services= (none)

If the application reports "ORA-03135 connection lost" from time to time, the problem may be related to the sqlnet.ora setting parameter SQLNET.EXPIRE_TIME

In the file sqlnet.ora, add one line (in minutes):

SQLNET.EXPIRE_TIME = 10

Under 2.windows

SQLNET.AUTHENTICATION_SERVICES= (NTS) is based on operating system verification

SQLNET.AUTHENTICATION_SERVICES= (NONE) is based on Oracle authentication

SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS) is the coexistence of the two.

The above is the editor for you to share how to carry out ORA-03135 error analysis to solve, if there happen to be similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.