Hello,

I'll try to get this issue summed up:
- running Oracle 10.2 on a Solaris 10 server
- running Java on a solaris 10 server:
java version "1.5.0_21"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_21-b01)
Java HotSpot(TM) Server VM (build 1.5.0_21-b01, mixed mode)


I've build a small Java program that retrieves data from the database - nothing exceptionnal.
My issue: the DB is set to be restarted daily, at 2am.
My Java programm is trying to handle that this way:

when doing a query, if it fails, we do this catch:


Java Code:
                } catch (SQLException sqle) {
                        Date date = new Date();
                        if ( sqle.toString().contains("Closed Connection") || sqle.toString().contains("socket") ) { // the connection to Oracle DB was lost - let's try to reconnect first:
                                // First, wait a few sec: if the connection dropped, the DB might be restarting.
                                try {
                                        Thread.currentThread().sleep(10000); // 10sec
                                        System.err.println(date+" Going to reconnect now!");
                                        try {
                                                FNE.close(); // Usually fails, since the connection has been dropped by the DB server anyway ... 
                                        } catch (Exception except) {
                                        }
                                        FNE = DriverManager.getConnection(Url,"USER","PASS");
                                        date = new Date();
                                        System.err.println(date+" Should have reconnected");
                                } catch (SQLException sqle2) {
                                        date = new Date();
                                        System.err.println(date+" Error connecting 2: " + sqle2 );
                                        sqle2.printStackTrace();
                                } catch (Exception except) {
                                        // do nothing ... that's just to please the compiler !
                                }
                         } else { // Unknown error: let's log that !
                                date = new Date();
                                System.err.println(date+" Error connecting : " + sqle );
                                sqle.printStackTrace();
                                System.err.println(date+" Error happened on: " + query);
                        }
                }

ok...it's a bit messy, but I tried adding more and more code/error check, because here is what is happening:

- the DB goes down
- my query fails => it tries to reconnect.
- reconnect fails with this exception:
Error connecting : java.sql.SQLException: Io exception: Got minus one from a read call
- on the Oracle side, I get this error message in the listener.log:
08-JUN-2011 06:58:08 * (CONNECT_DATA=(SID=REMOVED_THE_SID)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=REMOVED_THE_IP)(PORT=35182)) * establish * REMOVED_THE_SID* 12518
Solaris Error: 32: Broken pipe
TNS-00517: Lost contact
TNS-12560: TNS:protocol adapter error
TNS-12547: TNS:lost contact
TNS-12518: TNS:listener could not hand off client connection



Then, I come in in the morning, and the Java program is still trying to reconnect.
I kill the program and restart it => it works fine !

=> I need to handle that properly, for example by destroying every old connection to the DB in my java program, before trying to reconnect (since a stop/start of the program itself solves the issue).
something that would look like:
FNE.DROP_EVERYTHING_AND_EMPTY_MEMORY_FROM_THIS_CON NECTION();
FNE.reset_everything();
FNE = DriverManager.getConnection(....); //reconnect now

any idea on how to do that properly ?




notes:
- in the meantime, I am stop and starting the program via crontab, which works fine ... but I would like to avoid doing that, and also being able to handle a non-planned DB restart
- IMPORTANT: please assume that the DB itself is in perfect state and there is nothing needed there - this is an ideal DB ! the fault comes only from Java programing ! (in short: this DB is controlled by Oracle experts, and I won't be able to change anything on it ! and mostly: I'm assuming they know their job well :))

Thanks for your help,