Wednesday, November 9, 2011

Quiescing a Database

There are times when a DBA needs to perform work on the database that requires no other ACTIVE sessions. This is very common is development databases, where a DBA tries to run a package or a modify a table when the application is till running.
There are also frequent occurances where too much IT support folks time is spent in bringing lot of application servers down to perform a deployment or some database maintainence. That is where Quiescing comes in.
To put a database(run on one instance and it affects all the instances of the RAC database).


ALTER SYSTEM QUIESCE RESTRICTED;



Quiescing puts the database in mode that does not allow any further connections to become ACTIVE state. It would however wait for all ACTIVE connections to become INACTIVE or terminate. Gladly Oracle provides a query to find sessions which are stopping it from enter into QUIESCE mode.


select bq.sid, ss.user, ss.osuser, ss.type, ss.program
from v$blocking_quiesce bq, v$session ss
where bq.sid = ss.sid;



Once in QUIESCE mode, DBA connections are the only ones that are allowed to be in ACTIVE state. The DBA can then easily lock any object to perform his tasks.

Once done to release the QUIESCE;
the DBA runs


ALTER SYSTEM UNQUIESCE;



What ever you do DBAs, for heaven sake just dont bounce that db unnecessarily.