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 occurrences where too much IT support folks time is spent in bringing lot of application servers down to perform a deployment or some database maintenance. 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.
The ALTER SYSTEM QUIESCE RESTRICTED statement may wait a long time for active sessions to become inactive. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view. This view returns only a single column: SID (Session ID). You can join it with V$SESSION to get more information about the session, as shown in the following example:
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.
You can query V$INSTANCE view to see active state .
SQL> select active_state from v$instance;
You can see 3 situation.
NORMAL – Database is normal state (unquiescing).
QUIESCING – Being quiesced, but some non-DBA sessions are still active. There are active sessions
QUIESCED – There is no non-DBA sessions are active.
Once done to release the QUIESCE the DBA runs
ALTER SYSTEM UNQUIESCE;
Difference between quiesce the instance and enable restricted session?
1. You can set the database in quiesced mode by the following command.
alter system quiesce restricted;
After running this command all sessions except the ones that have DBA privileges will hang there.
2. Also you can enable the restricted session like this:
alter system enable restricted session;
After running this command, just the users that have enough privileges can connect to the database.
In quiesce restricted, all running sessions complete their current task, then at the point that they become INACTIVE in v$session, they cannot execute any more SQL until the system is unquiesced. In enable restricted session no new connections can be made, but you will need to kill existing sessions or they will simply continue. Quiescing is much less impact, as your users might only notice the database pauses then resumes. Restricted session I use most often in combination with startup force if I absolutely need to perform some maintenance that cannot be done online, then I disable it and do another startup force and reconnect the apps.
