Re: Serial# changing

From: Robert Prendin <rpdata_at_cyberus.ca>
Date: Sat, 22 Jan 2000 22:02:11 GMT
Message-ID: <388a2978.11999748@news>

Saw this a few days ago on 7.3.3.5.1, when PMON is in the process of TRYING to delete a process, for some reason the Serial# keeps changing.....Seems to be a bug... Here's is some more info on it...

Problem Description

You may receive an ORA-30 if you try to kill a session with the command

ALTER SYSTEM KILL SESSION (sid,serial#);

You may notice one of these symptoms:

         The serial# keeps increasing or changing. 
         The session still has locks on an object.

Problem Explanation

Why would you want to issue that command?

The process no longer exists at the os level, but does show up as active in v$session.
The user reboots the client machine without logging off, leaving a shadow process.
That session is holding onto a lock that needs to be released.

ORA-00030, 00000, "user session ID does not exist"

// *Cause: The user session id no longer exists, probably because the

// session was logged out. // *Action: Use a valid session ID.

Solution Description

The reason you cannot kill the session because PMON is already trying to
delete it. This is indicated by the fact that the serial number keeps

changing. When PMON attempts to cleanup a dead session, it will increase the
serial number.

The problem is that PMON may take a long time to clean up the process. If the
process was doing a very large transaction at the time it aborted, then PMON
has to rollback the large transaction.

When PMON finds a dead process, it will try to clean it up. If it makes any
progress, i.e. if it manages to free at least some of the process's resource,
it will repeatedly keep trying to delete the process. When it finally gets to
the point where it can't free up any of the process's resource(i.e. there are
no more free buffers), it will print a message to the trace file and try to
delete that process once a second. The problem is not that PMON is not trying
hard enough to delete the process. The problem is the lack of resources needed
to remove the process. If there are not enough buffers, then the removal of
the process is delayed. This is a free buffer problem in the data cache.

PMON can take anywhere from 5 minutes to over 24 hours to clean up a job. The
impact is that often times the process being cleaned up is holding locks that
prevents others from performing certain operations.

Presently the only workaround is to wait for PMON to clean up the job or to
shutdown and startup the db.

A list of bugs filed on this:

BUG 258727 PMON TRYING TO CLEAN UP PROCESS KILLED AT O/S INCREMENTING SERIAL
NUMBER Since the session had been running a PL/SQL program, the session may have
been rolling back transactions which caused it to take so long to disappear
from the monitor session.

BUG 192228 DEAD PROCESS IS NOT RELEASING ITS LOCKS A process has died for some unknown reason (the client could have switched his
computer off). Then the locks just hung around forever. The shadow process is
no longer there. There is a session ID and a serial# in lock monitor (v$lock),
but no corresponding entry in monitor session (v$session). Tried to alter
system kill session and received an error saying that the session did not
exist. The only workaround is to do a shutdown abort to release the locks.

BUG 232809/186423 PMON FAILED TO DELETE A PROCESS, ORA-30 WHEN KILLING THE
SESSION/GETS ORA 30 WHEN TRYING TO KILL A SESSION WHOSE SESSIONID KEEPS
CHANGING When PMON attempts to cleanup a dead session, it will bump the serial number.
You cannot kill the session because PMON is already trying to delete it.
If, however, the database is doing really performing poorly or nothing is
being cleaned up, try a shutdown abort.

BUG 200455/186423 GETTING ORA-30 WHEN TRYING TO KILL SESSION THAT SHOWS UP IN
V$SESSION V$process and v$session indicates that the session id, os#, user, and table
being locked are staying the same but the serial# is changing. PMON trace
files indicate that it is having difficulties killing the process. The os
process was killed. The fact that the serial number keeps changing indicates
that the session is indeed being reused. The 'alter system kill session'
command takes a session id as well as a session serial #; if the serial #
does not match the current serial #, an ORA-30 is the result. Serial #'s
change when the session logs off and someone logs in and uses the same session.

This is an OLD article from Metalaink, but the problem continues to pop up...

Good Luck,

Robert Prendin
On Fri, 21 Jan 2000 18:44:25 -0800, <terrysutton_at_usa.net> wrote:

>On O 8.0.5, I'm looking at v$session to track a session (actually we want to
>kill the session). The connection logon_time is 4 days ago, and the SID
>stays the same, but the serial# keeps increasing (so we can't kill it,
>because it changes). I've never seen this before; I thought a connection
>retains its SID & serial# for the life of the connection. Can anyone help
>me? TIA
>
>--Terry
>
>
Received on Sat Jan 22 2000 - 16:02:11 CST