QUIESCE and UNQUIESCE the Oracle Database
Quiesce a Database will help the DBA to performed their administrative task without user inter vision in the database. Like Schema changes are especially hard to make while users are doing live transactions in the database. In Quiesce, Only member of sysdba group can performed the activities. Default only sys or system are having privileges to login and performed operation.
Quiescing a database gives you the opportunity to put the database in a single-user mode without having to ever shut the database down.
When we fired the command to change database into Quiesce State then Oracle wait for complete all the running transaction before it successfully change the state to Quiesce. Once database is in Quiesce state, then all other transaction is hold/waiting until it turned back in Unquiesce state.
Note: If User already connected and fire any command then user has to wait before acquire any lock, user is in hang state until DBA make database in unquiesce state
Benefit for Quiesce database:
In OLTP Environment, if you want to performed DDL Operation and acquired lock on the object then DBA need to make the database in Quiesce state to performed database maintenance/administrative task.
Check database is Quiesce State
Query the ACTIVE_STATE column of the V$INSTANCE view
select active_State from v$instance;
NORMAL: Normal unquiesced state.
QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed
Enabled the Quiescing of Oracle Database:
Sql> ALTER SYSTEM QUIESCE RESTRICTED;
Determine the sessions that are blocking the quiesce operation
select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;
Unquiesce database after performed your administrative operations:
Sql>ALTER SYSTEM UNQUIESCE;
-In RAC,Instance cannot open at time of database is in quiesce state.
ORA-25503:cannot open database because database is being quiesced
-Application user is go in waiting until unquiesce is done.
-All inactive sessions are prevented from issuing any database commands until the database is unquiesced.
-All new login attempts will be queued. A user trying to log in during the time the database is in a quiesced state won’t get an error message. Rather, his or her login attempts will seem to “hang.”