Re: 8.1.7 connection problems

On 10/26/05, Stephan Eichenlaub <stephan_at_eichenlaub.net> 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_at_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

--
http://www.freelists.org/webpage/oracle-l