Suspend & Resume 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 us 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. The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when we issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, if someone starts a new instance another instance is being suspended, the new instance will not be suspended.

Using the ALTER SYSTEM RESUME statement to resume normal database operations. The SUSPEND and RESUME commands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and we issue an ALTER SYSTEM SUSPEND statement from instance 1, then we can issue a RESUME statement from instance 1, 2, or 3 with the same effect. The suspend/resume feature is useful in systems that allow us to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If we use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then we can use the suspend/resume feature to facilitate the split. 

The suspend/resume feature is not a suitable substitute for normal shutdown operations, because copies of a suspended database can contain uncommitted updates.

The following statements illustrate suspend and resume usage. The V$INSTANCE view is queried to confirm database status.

     SQL>    alter system suspend;
               System altered.

     SQL>   select database_status from V$instance;
  

             DATABASE_STATUS
               ------------------------
                 SUSPENDED

       SQL>  alter system resume ;
                System altered.

      SQL> select database_status from v$instance ;
        

             DATABASE_STATUS
             -------------------------
              ACTIVE