QUIESCE / SUSPEND vs startup restrict

We can QUIESCE and SUSPEND the database , This helps in performing the maintainance oprations.

You nee need to restart the database to open it in restricted mode.

QUIESCE:quiescing the database.
Pre-req enable the Database Resource Manager
RESOURCE_MANAGER_PLAN : Defined in init.ora

SQL> alter system quiesce restricted;
System altered.
- Only current command completed.
- No new connections allowed.
-New queries or attempted logins will appear to hang until you unquiesce
the database.
- select Active_State from V$INSTANCE; --This will tell you the status.
The Active_State
NORMAL (unquiesced),
QUIESCING (active non-
DBA sessions are still running),
QUIESCED

To unquiesce the database:

SQL> alter system unquiesce;
system altered.

This will be logged in alert_log file.

2010-04-11 11:25:55.146000 +01:00
Database in quiesce mode
2010-04-11 11:28:07.898000 +01:00
Database out of quiesce mode


Suspending the database:

-A suspended database performs no I/O to its datafiles and control files
-allow the database to be backed up without I/O interference


SQL> alter system suspend;
System altered.

SQL> alter system resume;
System altered.

Monitoring:
select Database_Status from V$INSTANCE;
SUSPENDED or ACTIVE.

The suspend command suspends the database, and is not specific to an instance. Therefore, in an Oracle Real Application Clusters environment, if the suspend command is entered on one system, then internal locking mechanisms will propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, do not start a new instance while you suspend another instance, since the new instance will not be suspended.



In both cases, Sys and System are allowed to connect. All other users will be in hung state if they try to connect.


Enjoy.....and Hope this will save time as no shutdowns required for Maintainance (Small example may be, A data fix )