How to abort specific user sessions in RAC and stop the user to connect again to DB.

User_SZR3U

Hi,

We need to abort specific user sessions every night at 23:00 and stop the user to connect to the DB. Then allow the user to connect to the DB next day at 8:00.

I am new in Oracle. I only know I can kill user session by

alter system kill session 'SID,SERIAL#,@INST_ID' immediate;

But the problem is, KILL SESSION sometimes only mark a session as terminated and roll back ongoing transactions. It does not actually abort the transactions.

Is it possible to KILL SESSION something like abort all their transactions (we allow data loss for this user) ? And is there any way we can restrict the user to connect to DB?

Thank you very much😃

JohnWatson2

ALTER SYSTEM DISCONNECT SESSION '...' IMMEDIATE;

User_SZR3U

https://community.oracle.com/tech/developers/discussion/comment/16845235#Comment_16845235

Thanks John, But alter system disconnect session .. immediate also will rollback data at background, right?

I want session is aborted and any processes in this session killed immediate .

Is it only can done by kill -9 in OS level? Thank you

Solomon Yakobson

No matter what you do (including kill -9) all uncommitted changes done by that session will be rolled back which can take long time. You will end up with a corrupt database otherwise.

SY.

User_SZR3U

Thanks Solomon Yakobson.

Then, How can i prevent the user to connect again to the DB after his sesssion is killed? Thank you.

Billy Verreynne

A kill -9 is okay as Oracle system processes will clean up. DB corruption is unlikely - unless the kill is done of a non-user process. Oracle is pretty robust.

As for disallowing subsequent connections. Do you want to prevent any connections from a user device, or prevent logging in using a specific user schema?

The former can be done by using the firewall-cmd (OL7 and later) and block that IP from connecting to the listener port for an interim period. A cron job at 5pm that implement that as a non-permanent rule, and one at 8am that removes that rule.

The latter can be done by a database job that locks the Oracle user schema in the afternoon, and unlocks it in the morning.

JohnWatson2

https://community.oracle.com/tech/developers/discussion/comment/16845355#Comment_16845355

I would use the Resource Manager. Schedule activation of a Resource Manager plan that sets the active session pool for that user's consumer group to zero.

Solomon Yakobson

Well, first we need to understand your application tearing. Are you using middle tier and all application users connect to database as same Oracle user and by "specific user" you mean application user? If so it can only be done on application side. In addition, unless I missed something, all suggested approaches will prevent new connection attempts. Existing sessions created before 23:00 will stay connected (inactive if using Resource Manager but still connected and therefore possibly locking some objects).

SY.

User_GSQTY

You should stop this user before 23:00 with a margin of the average rollback ; if it takes about 60 minutes in general, then kill that user at 22:00.

To prevent user from login : you need to either lock that user at specifict time and unlock it at specifict time ; or create a login trigger to do so.

L. Fernigrini

I would use a login trigger to refuse connections for that user on the desired time frame, seems simpler than locking/unlocking the account or changing the resource manager limits.