Home > Administration > Quiescing Oracle Database

Quiescing Oracle Database

Hi Friends,

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.

Talip Hakan Öztürk

  1. November 17, 2013 at 10:56 pm

    Hello, after reading this remarkable post i am also delighted to share my knowledge here with friends.

  1. No trackbacks yet.

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

%d bloggers like this: