Re: [Sqlrelay-discussion] Oracle bounces causing dead connections
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Oracle bounces causing dead connections
|
From: Todd F. <fr...@an...> - 2005-10-11 15:40:31
|
On Tue, Oct 11, 2005 at 11:28:02AM -0400, Firstworks/4access wrote: > What versions of Oracle SQL Relay are you running? >=20 SQLRelay: sqlrelay-0.36.4.tar.gz Oracle: Oracle9i Enterprise Edition Release 9.2.0.6.0 > I just tried to reproduce this problem using Oracle 10g and current SQL > Relay, using a similar setup to yours (including authtier=3D"database"). >=20 > When I bounced my database, the SQL Relay connection daemons recovered > gracefully and I was able to run queries against it again. The odd part was that it appeared that only 1 connection had the problem...= I could run the script 3 at a time and randomly one would have the issue (= because of the connection it was attached to) while the others ran just fin= e. >=20 > The connection daemons analyze each error that comes back from the > database and recognize certain errors as meaning that the database was > bounced. It's possible that I missed one of those errors and that the > connection daemon hasn't realized that the database is down and hasn't > attempted to reconnect to it. Only odd thing I can think of is that instead of just downing the database = the DBA took the listener offline first and then did the shutdown. But that= shouldn't be that uncommon of a procedure. >=20 > If I send you some code with debug in it, can you rebuild/reinstall SQL > Relay and try to reproduce the problem again? I can do that :} I happen to have gotten real good at building it on evil = solaris :} >=20 > Dave > dav...@fi... >=20 > On Thu, 2005-10-06 at 16:26 -0400, Todd Freeman wrote: > > Howdy all! > >=20 > > Am having great fun with sqlrelay... works great... except (you knew t= hat was coming didn't you :} ) when the database bounces I am getting at l= east 1 connection that 'hangs'. > >=20 > > The symptoms are that some of my perl progs just run on fine... but ot= hers connecting to this connection get 'Broken Pipe' > >=20 > > Below are the conf... the logs from the connection and the listener it = says it is debugging to. The only odd thing I see is the 'invalid login/pas= sword' message... (and yes... it is using the same script using the same us= ername and password that the working ones are using) > >=20 > > Any help would be appreciated! Ohh yeah... and ROCK ON! > >=20 > > ##### Relevant conf ##### > > <instance id=3D"oracleTEST" port=3D"9000" socket=3D"/tmp/oracle= TEST.socket" dbase=3D"oracle8" connections=3D"2" maxconnections=3D"4" maxqu= euelength=3D"0" growby=3D"2" ttl=3D"60" endofsession=3D"rollback" sessionti= meout=3D"600" runasuser=3D"nobody" runasgroup=3D"nobody" cursors=3D"20" deb= ug=3D"listener_and_connection" authtier=3D"database"> > > <users> </users> > > <connections> > > <connection connectionid=3D"its_proxy" string= =3D"user=3Dits_proxy;password=3Dsecret;oracle_sid=3DTEST.world;autocommit= =3Dno;" metric=3D"1"/> > > </connections> > > </instance> > > ######################### > >=20 > > ##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9268 ###= ## > > Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25= 739 > > 10/06/2005 16:11:50 EDT connection [9268] : done waiting for listen= er > > 10/06/2005 16:11:50 EDT connection [9268] : releasing announce mutex > > 10/06/2005 16:11:50 EDT connection [9268] : done releasing announce= mutex > > 10/06/2005 16:11:50 EDT connection [9268] : done announcing availabilit= y... > > 10/06/2005 16:11:50 EDT connection [9268] : waiting for client... > > 10/06/2005 16:11:50 EDT connection [9268] : reconnect succeeded > > 10/06/2005 16:11:50 EDT connection [9268] : done waiting for client > > 10/06/2005 16:11:50 EDT connection [9268] : client session... > > 10/06/2005 16:11:50 EDT connection [9268] : getting command... > > 10/06/2005 16:11:50 EDT connection [9268] : done getting command > > 10/06/2005 16:11:50 EDT connection [9268] : authenticate > > 10/06/2005 16:11:50 EDT connection [9268] : authenticate... > > 10/06/2005 16:11:50 EDT connection [9268] : database-based authenti= cation failed: invalid user/password > > 10/06/2005 16:11:50 EDT connection [9268] : ending session.= =2E. > > 10/06/2005 16:11:50 EDT connection [9268] : aborting all bu= sy cursors... > > 10/06/2005 16:11:50 EDT connection [9268] : done aborting a= ll busy cursors > > 10/06/2005 16:11:50 EDT connection [9268] : resetting autoc= ommit behavior... > > 10/06/2005 16:11:50 EDT connection [9268] : setting= autocommit off... > > 10/06/2005 16:11:50 EDT connection [9268] : done se= tting autocommit off... > > 10/06/2005 16:11:50 EDT connection [9268] : done resetting = autocommit behavior... > > ######################### > >=20 > > ##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25739 ##### > > 10/06/2005 16:11:50 EDT listener [25739] : getting authentication... > > 10/06/2005 16:11:50 EDT listener [25739] : done getting authentication > > 10/06/2005 16:11:50 EDT listener [25739] : incrementing session count... > > 10/06/2005 16:11:50 EDT listener [25739] : waiting for exclusive a= ccess... > > 10/06/2005 16:11:50 EDT listener [25739] : done waiting for exclus= ive access... > > 10/06/2005 16:11:50 EDT listener [25739] : 3 > > 10/06/2005 16:11:50 EDT listener [25739] : waiting for the scaler.= =2E. > > 10/06/2005 16:11:50 EDT listener [25739] : done waiting for the sc= aler... > > 10/06/2005 16:11:50 EDT listener [25739] : done incrementing session co= unt > > 10/06/2005 16:11:50 EDT listener [25739] : getting a connection... > > 10/06/2005 16:11:50 EDT listener [25739] : acquiring exclusive shm acce= ss > > 10/06/2005 16:11:50 EDT listener [25739] : done acquiring exclusive shm= access > > 10/06/2005 16:11:50 EDT listener [25739] : waiting for an available con= nection > > 10/06/2005 16:11:50 EDT listener [25739] : done waiting for an availabl= e connection > > 10/06/2005 16:11:50 EDT listener [25739] : handoff=3Dreconnect > > 10/06/2005 16:11:50 EDT listener [25739] : socket=3D/usr/local/fir= stworks/var/sqlrelay/tmp/sockets/156 port=3D53973 > > 10/06/2005 16:11:50 EDT listener [25739] : signalling connection that w= e've read > > 10/06/2005 16:11:50 EDT listener [25739] : done signalling connection t= hat we've read > > 10/06/2005 16:11:50 EDT listener [25739] : releasing exclusive shm acce= ss > > 10/06/2005 16:11:50 EDT listener [25739] : done releasing exclusive shm= access > > 10/06/2005 16:11:50 EDT listener [25739] : done getting a connecti= on > > 10/06/2005 16:11:50 EDT listener [25739] : decrementing busy listeners > > 10/06/2005 16:11:50 EDT listener [25739] : done decrementing busy liste= ners > > ######################### > >=20 > >=20 >=20 >=20 >=20 > ------------------------------------------------------- > This SF.Net email is sponsored by: > Power Architecture Resource Center: Free content, downloads, discussions, > and more. http://solutions.newsforge.com/ibmarch.tmpl > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion --=20 Todd Freeman Ext 6103 .^. Don't fear the penguins! Programming Department /V\ Andrews University // \\ http://www.linux.org/ http://www.andrews.edu/~freeman/ /( )\ http://www.debian.org/ ^^ ^^ |