Quiescing a Database

To put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a quiesced state, in the sense that no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements are running in the system.

SQL> ALTER SYSTEM QUIESCE RESTRICTED;

System altered.

SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;

ACTIVE_ST
---------
QUIESCED

You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view.

select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;
SQL> ALTER SYSTEM UNQUIESCE;

System altered.

SQL> SELECT ACTIVE_STATE FROM V$INSTANCE;

ACTIVE_ST
---------
NORMAL

Note:
In this discussion of quiesce database, a DBA is defined as user SYS or SYSTEM. Other users, including those with the DBA role, are not allowed to issue the ALTER SYSTEM QUIESCE DATABASE statement or proceed after the database is quiesced.

Note:
You cannot perform a cold backup when the database is in the quiesced state, because Oracle Database background processes may still perform updates for internal purposes even while the database is quiesced. In addition, the file headers of online datafiles continue to appear to be accessible. They do not look the same as if a clean shutdown had been performed. However, you can still take online backups while the database is in a quiesced state.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/start004.htm#ADMIN10263

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