Unstoppable Sessions
In some situation, you might see AP server spawned thousands of sessions for no reason, we should take some actions on the spot. In such moment, killing sessions at database-level may not work well for us, so removing all user sessions at OS-level could be an instant remedy for such situation. But how to kill all user sessions instantly?
In this post, we'll introduce a way to kill server processes at OS level to remove sessions from the ground. Subsequently, PMON detects those missing processes, it rolls back their transactions afterwards.
There're some scopes to kill server processes.
Kill All Server Processes
First of all, you must make sure $ORACLE_SID is the right one we want to focus on.
[oracle@test ~]$ echo $ORACLE_SID
ORCL
Then we kill all server processes (non-background process) by selecting processes containing LOCAL in their names. Which means, LOCAL=YES and LOCAL=NO are both selected.
A process with LOCAL=YES means that it's used by a local connection, whereas LOCAL=NO represents an external connection.
[oracle@test ~]$ kill -9 $(ps -ef | grep LOCAL | grep oracle$ORACLE_SID | awk '{print $2}')
This command removes all Oracle server processes.
Kill LOCAL=NO Processes Only
But the thing is, killing all Oracle processes contains LOCAL also kill internal connections from local database server. Most likely, they connected by OS authentication.
For example, batch jobs are all important local sessions, we may not like to kill them. We have talked about this in How to expdp AS SYSDBA without Password.
So a better solution is to choose non-local, external connections to be candidates, which is, processes with LOCAL=NO in names.
[oracle@test ~]$ kill -9 $(ps -ef | grep LOCAL=NO | grep oracle$ORACLE_SID | awk '{print $2}')
We killed only remote user sessions.
Exclude Some Processes from Killing
There could be some remote connections that are important and should not be killed, such as Oracle GoldenGate process and database links. We can exclude those process ID from our command if you are able to identify their processes.
[oracle@test ~]$ kill -9 $(ps -ef | grep LOCAL=NO | grep oracle$ORACLE_SID | awk '{print $2}' | grep -Ev "39921|20919|74610"
In the above command, we excluded 3 process ID from our killing process, which preserves important remote connections.
A scenario that we use the technique to remove session instantly can be found in the post: How to Resolve Oracle SHUTDOWN IMMEDIATE Hangs.