Placing a Database into a Quiesced (suspended) State
There are times when a DBA needs to perform work on the database that requires no other ACTIVE sessions. This is very common when a DBA tries to run a package or a modify a table when the application is till running. There are also frequent occurrences where too much IT support folks time is spent in bringing lot of application servers down to perform a deployment or some database maintenance. That is where Quiescing comes in.
When you place a database in quiesced state, non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.
To place a database into a quiesced state, run the following statement:
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state. In an Oracle Real Application Clusters environment, this statement affects all instances, not just the one that issues the statement.
When you issue above statement, you may wait a long time for active sessions to become inactive.?In this 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 to view the quiesce state of an instance:
SQL> select active_state from v$instance;