Quiescing a Database
---------------------

quiescing a database gives you the opportunity to put the database in a single-user mode without having to ever shut the database down.

When a database is put in a quiesced state by the DBA the following conditions apply:
• All inactive sessions are prevented from issuing any database commands until the database is unquiesced.
• All active sessions are allowed to be completed.
• All new login attempts will be queued. A user trying to log in during the time the database is in a quiesced state won’t get an error message. Rather, his or her login attempts will seem to hang.
• Only DBA queries, transactions, and PL/SQL statements will be allowed in the database.

To place the database into a quiesced state, as SYS/SYSTEM:
SQL> ALTER SYSTEM QUIESCE RESTRICTED;

The procedure for quiescing Oracle RAC databases is identical to quiescing a noncluster database. You use the ALTER SYSTEM QUIESCE RESTRICTED statement from one instance.

Later on, when you’ve finished your administrative tasks, you can allow regular access to the database by issuing the following command:
SQL> ALTER SYSTEM UNQUIESCE;

The ALTER SYSTEM QUIESCE RESTRICTED statement may wait a long time for active sessions to become inactive. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view. This view returns only a single column: SID (Session ID). You can join it with V$SESSION to get more information about the session, as shown in the following example:

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


Viewing the Quiesce State of an Instance
----------------------------------------
You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:
- 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.











Suspending a Database
-----------------------
The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

If you want to suspend all I/O operations during some special administrative job, you can suspend the database. All reads from and writes to the data files and control files are prohibited while the database is under suspension. The suspension of activity on a database may be necessary when you want to perform an emergency backup of a tablespace, for example, or specialized chores such as splitting a mirror, which you can’t do in any other way.

The SUSPEND and RESUME commands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and you issue an ALTER SYSTEM SUSPEND statement from instance 1, then you can issue a RESUME statement from instance 1, 2, or 3 with the same effect.

You can suspend and resume a database as follows:
SQL> ALTER SYSTEM SUSPEND;

SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;

SQL> ALTER SYSTEM RESUME;