On 10/26/05, Stephan Eichenlaub <stephan@xxxxxxxxxxxxxx> wrote: > Hi, > > every once in a while (maybe once in two months) a customer has > connection problems to his production database. > > Version is 8.1.7. Setup is a database server (linux) and a windows > server that runs the application (and several clients connecting to the > application but that's of no importance here). > > The connection failure only seems to occur at night. After maintenance > the database is started up again, then has about 30 minutes of idle > time, gets a tnsping 10 to wake up the connection - and right after the > tnsping sqlplus gets an error. > In the batch these commands are issued right after another: > > ------------- start here ------------ > tnsping mySID 10 >> mylog > sqlplus -s system/password@mySID @switch_logfile.sql >> mylog > ------------- end here ------------- > > The result usually is that things work like intended but sometimes it > doesn't. The logfile mylog shows: > > ------------- start here ------------ > > TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on > 25-OKT-2005 22:00:00 > > (c) Copyright 1997 Oracle Corporation. All rights reserved. > > Attempting to contact > (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.101.211)(PORT=1521)) > OK (40 msec) > OK (40 msec) > OK (0 msec) > OK (0 msec) > OK (0 msec) > OK (0 msec) > OK (0 msec) > OK (0 msec) > OK (0 msec) > OK (0 msec) > ERROR: > ORA-01034: ORACLE not available > ORA-27101: shared memory realm does not exist > Linux Error: 2: No such file or directory > > ------------------ end here ------------- > > The tnsping seems to have helped making the connection error happen less > often - but apparently it's not the solution to the problem as in the > example above it reports 10 fine pings - and right after that the > connection can't be established. > > What could be the reason, where would you start looking? > In the oracle logfiles on the database server nothing shows up, not even > a connection attempt. > Strangely enough, I haven't found a recent sqlnet.log on the machine > that runs the script. > > Stephan Eichenlaub > > -- > ----------------------------------------------------------------------- > Stephan Eichenlaub xx49-261-9141500 > St.-Josef-Platz 10 xx49-162-9068888 > D-56068 Koblenz http://www.eichenlaub.net > ----------------------------------------------------------------------- Stephan, tnsping succeeds implies that the tns listener is available. sqlplus connection fails implies that no such instance is available. the listener being up and the database instance being up are 2 very different concepts. Perhaps a cold backup is running during that time interval? Check v$instance (if you have privileges) and see when the instance was last started. Sounds like a database in noarchivelog mode that only uses offline backups. hth. Paul -- //www.freelists.org/webpage/oracle-l