Testing how to quiesce an instance today:
SID 94 > alter system quiesce restricted;
(15 minutes ago on test-db 11.2 )
Ok, it waits for several sessions to finish, right ?
( see Doc
Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. … )
So, we queried V$BLOCKING_QUIESCE and killed all sessions listed there via
alter system kill session ....
and saw them disappear in V$BLOCKING_QUIESCE thereafter.
However, after killing the last one we expected SID 94 to finish the quiesce – command, but: it didn’t.
SQL > select * from v$blocking_quiesce;
no rows selected
SQL > select sw.event
2 from v$session_wait sw
3 where sw.sid=94;
EVENT
——————————–
wait for possible quiesce finish
… for several minutes.
bouncing and restarting the instance and repeating the same experiment but using
alter system disconnect session .... immediate
rather than
alter system kill session ....
gave the expected result.
So, when we want to quiesce our instance we’ll use this algorithm.
(We’ll do that only on a weekend maintenance windows when no interactive user is connected, all batch jobs running will be restartet automatically by our scheduling system when system is unquiesced again.)
But: seems to be a bug to me that V$BLOCKING_QUIESCE showed no entry though quiescing was apparently blocked for minutes.
