Tuesday, April 2, 2013

Oracle Database Queiesce Mode

Same outcome like closing a database and opening in restricted mode.
But queiesce mode does not require a shutdown.
To put database into queiesce mode -> ALTER SYSTEM QUIESCE RESTRICTED;

When the database is quiesce mode; non-dba active sessions will continue to run until they become inactive.
On the other hand; an inactive session cannot become active when database is in quiesce mode. These kind of sessions appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

To be able to put the database in quiesce mode , all the active session should be inactive, otherwise "alter system quiesce restricted" command will not return. This command can wait for a long time if active sessions are blocking it.

Active sessions blocking quiesce operation can be identified with the following sql;

select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;

Top put database back to normal -> ALTER SYSTEM UNQUIESCE;

Quiesce State of an Instance can be checked by the following sql;

select active_state from v$instance;

NORMAL: Normal unquiesced state.
QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

No comments :

Post a Comment