Quiesce 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 be login into the database. 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.
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.
Enabled the Quiescing of Oracle Database:
Sql> alter system quiesce restricted;
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.”