C # troubleshooting of Oracle Database Connection timeout

C # troubleshooting of Oracle Database Connection timeout
 
Creation Time: 2007/08/09
 
Recently, I found a strange problem when using C # To operate Oracle databases. When there is a timeout limit for database sessions, even if the client reconnects to the database, it cannot continue database operations, in addition, no error occurs during connection, only an exception is thrown in the operation after connection.
 
The program itself is very simple. It receives message data from a message-oriented middleware (MOM) and saves it to the database. as this application is a backend service, you need to be careful when handling errors, because background services require stronger robustness and better fault tolerance compared with programs with user interaction (interface.
 
Therefore, when operating the database, if the database connection problem occurs and the message cannot be saved, do not lose the message and try again to connect to the database, and then process the message again.
For other types of errors, such as primary/foreign key problems, data type problems, and other exceptions caused by message data errors, they should be saved to the file and logged, for manual review, and then process the following message data.
 
So the program code alone handles errors such as the ORA-00028 (Session kill) ORA-02396 (exceed idle time) ORA-01012 (not logon) and ORA-12535 (timeout), and exits the message processing function, and re-called in the main function to re-connect to the database and other resources.
 
In the main function of the Service:
While (true)
{Try
{
Processmessage (queuename );
}
Catch (exception E)
{
Logmanager. writelog (201, E. Message );
GC. Collect ();
}

// Sleep
If (Mre. waitone (Cf. time_out, false ))
Break;
}
 
In the message processing function:
Private Static void processmessage (string queuename)
{
// Open Database
String strconn = Cf. db_conn_str;
Oracleconnection OC = new oracleconnection (strconn );
OC. open ();

// Connect to Mom... (omitted)

Do
{
// Read mom message data... (omitted)
Try
{
// Operation for saving the mom message data to the database... (omitted)

STR = msginfo. msgname + "Save to database success .";
Logmanager. writelog (301, STR );
}
Catch (oracleexception E)
{
// In case of: ORA-00028 (Session kill)
// ORA-02396 (exceed idle time)
// ORA-01012 (not logon)
// ORA-12535 (timeout)
If (E. Code = 2396 | E. Code = 1012 | E. Code = 28 | E. Code = 12535)
{
STR = msginfo. msgname + "Save to database fail, error code:" + E. Code. tostring () + ", send message back and re-connect oracle .";
Logmanager. writelog (301, STR );

Break; // quit Loops
}
Else // for other reason, just save data to dump file
{
// Save the mom message data to the file... (omitted)
}
}

Catch (exception E)
{
// Save the mom message data to the file... (omitted)
}
}

} While (true );

OC. Close ();
}

However, in the test, it was found that the ORA-00028 (Session kill) ORA-01012 (not logon) and the ORA-12535 (timeout) can be successfully automatically connected for message processing, in the case of ORA-02396 (exceed idle time), no error was found in the connection, but the save operation failed, resulting in the remaining messages in the database can not be processed normally.

These problems can occur in databases with resource restrictions, because background services are usually connected.
Create profile pai_prof limit
Idle_time 60
Connect_time 480;
 
Alter system set resource_limit = true;

After the program logic errors and database problems are eliminated, the only problem is the connection pool, that is, when the database is reconnected, an existing connection is retrieved from the client connection pool to provide efficiency, however, this connection has timed out. Therefore, the operation still triggers a timeout error after the connection is successful. therefore, you must clear the connection pool and then reconnect. pair. in net framework2.0, the static functions clearpool and clearallpool are added to oracleconnection.
The clearpool method clears a specific connection in the connection pool (specified by parameters ). If the connection is in use during the call, it will be properly marked. When the connection is closed, the connection will be discarded and not saved to the connection pool.
Clearallpools clears all connections in the connection pool. If the connection is in use when this method is called, the connection will be properly marked. When close is called for the connection, the connection will be discarded and not saved to the connection pool.

Therefore, the solution is very simple. You can add a connection pool purge operation in a proper place to clear all exceptions or connection pool timeout. For example:

If (E. Code = 2396)
{
// Clear connection pool and close connection
Oracleconnection. clearpool (OC );

STR = msginfo. msgname + "Save to database fail, error code:" + E. Code. tostring () + ", send message back and re-connect oracle .";
Logmanager. writelog (301, STR );

Break; // quit Loops
}