ORA-00029: session is not a user session
Cause: The session ID specified in an ALTER SYSTEM KILL SESSION command was not a user session (for example, recursive, etc.).
Cause: The session ID specified in an ALTER SYSTEM KILL SESSION command was not a user session (for example, recursive, etc.).
Action: Retry with a user session ID.
Example:
The following is an example scenario for ORA-00029 :
I have a big problem here on my ORACLE 10.2.0.2 P14 on Windows 2003
32-bit.
32-bit.
Something happened at the weekend (and I have no idea what because
nobody was working) that lead to the following:
nobody was working) that lead to the following:
Maximum number of processes (200) exceeded within a couple of minutes
after DB start. No further
connections possible (ORA-00020).
after DB start. No further
connections possible (ORA-00020).
Now, what I researched so far is that there is apparantly a record in
V$LOCK with lmode=6 without username, always with
the same SID and serial# but different thread numbers after each DB
startup. The thread numbers
do not exist in Windows Manager and there is no locked record, latch, no
deadlock or whatever out there.
V$LOCK with lmode=6 without username, always with
the same SID and serial# but different thread numbers after each DB
startup. The thread numbers
do not exist in Windows Manager and there is no locked record, latch, no
deadlock or whatever out there.
I tried to use "ALTER SYSTEM KILL SESSION 'xxxx,x';" but I receive the
ORA-00029 "session is not a user session".
So I tried to use "orakill ". I receive the expected
correct (!) message but all what happens is
that the database is shutdown. After new startup the record still stays
active and blocks everything.
ORA-00029 "session is not a user session".
So I tried to use "orakill ". I receive the expected
correct (!) message but all what happens is
that the database is shutdown. After new startup the record still stays
active and blocks everything.
Not only can no further connection be established (e.g. SAP, BRGUI, OEM,
SQLPLUS) but also my filesystem is filling up
with trace files about ORA-00020 and of course the alert log with
messages about process M000 died and new creation failed
(4 lines each second!)...
SQLPLUS) but also my filesystem is filling up
with trace files about ORA-00020 and of course the alert log with
messages about process M000 died and new creation failed
(4 lines each second!)...
Can anybody help me with that? I have spent now 3 days on this and don't
know wherelese to research further options...
know wherelese to research further options...
And of course I tried to increase the number of processes and sessions,
up to 600 proc. with 1200 sessions...no change
in behavor...just took longer to reach the limit...
up to 600 proc. with 1200 sessions...no change
in behavor...just took longer to reach the limit...
My SGA total size is 1.3GB while windows is booted with 3GB option.
PGA-target is 300M.
PGA-target is 300M.
------------
Verify PMON is running in the background...
select *
from v$session
where type ='BACKGROUND'
AND program like '%PMON%';
Verify PMON is running in the background...
select *
from v$session
where type ='BACKGROUND'
AND program like '%PMON%';
-----------------
Resolution :
I just fixed it! Since I could not get rid of the locked record I tried
this:
First I did a reset on all system user passwords. Then deleted
the remote-login-password file and recreated it. Processes are now
perfectly in the normal range!
Resolution :
I just fixed it! Since I could not get rid of the locked record I tried
this:
First I did a reset on all system user passwords. Then deleted
the remote-login-password file and recreated it. Processes are now
perfectly in the normal range!
0 comments:
Post a Comment