Quiescing Oracle Database

Hi,

In this article, I will write about quiescing an Oracle Database. Quiescing a database that allows only DBA transactions, queries, fetches, or PL/SQL statements. In is situation there is no non-DBA sessions. DBA is defined as user SYS or SYSTEM. Other users who assigned with the DBA role, are not allowed to issue quiesce command.

To place a database into a quiesced state, connect sql*plus and run the following statement;

SQL> ALTER SYSTEM QUIESCE RESTRICTED;

After quiescing database non-DBA active sessions will continue until become inactive. No inactive sessions are allowed to become active. If an inactive session want to become active then it will appear tobe hung. The session can resume when the database is unquiesced.

If you issue above statement in the RAC environment, then it affects all instances. When you issue above statement, you may wait a long time for active sessions to become inactive. In is situation, you can find sessions that are blocking the quiescing operation by following query;

SQL> select bq.sid, username, osuser, program, machine

from v$blocking_quiesce bq, v$session s

where bq.sid = s.sid;

To restore the database to normal operation, run the following statement;

SQL> ALTER SYSTEM UNQUIESCE;

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 – Quiesced. There is no non-DBA sessions are active.