Quiesce Oracle Database

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;

Note:
-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.”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s