标签归档：alter system resume;

以前一直没有注意到oracle有暂定和恢复功能(SUSPEND/RESUME)[从oracle 8i开始有的特性],一下是官方描述:

The Database Suspend/Resume feature provides a mechanism by which all disk I/O 
(datafile, controlfile and file header I/Os) in a database (in all instances) 
can be suspended making it easier to make a copy of the database.  When an 
ALTER SYSTEM SUSPEND command is issued, it will wait for any ongoing instance 
recovery to complete and then set a flag in all running instances to stop all 
new lock and I/O activity.  The command may return before the last I/O is 
issued because the check for the flag might have been before the suspend and 
the I/O might have been issued after the suspend.  So, reads, typically are not
allowed when the database is suspended but may still be active for a period of 
time.  However, this command does ensure that no new I/Os will be issued.  

Once all instances of a database are suspended, a copy of the database can be 
made by making a copy of all the files (i.e. the control file, online logs and 
all data files).  The copy can have uncommitted updates and therefore the only 
way a copy of the database can be used in this scenerio is to do an instance 
recovery and then open it.

The database can be suspended or resumed through an ALTER SYSTEM call.  You can
issue this statement as the user SYSTEM or SYS (the user must have DBA 
privileges).   

The syntax for these two commands is as follows:

    ALTER SYSTEM <options>;

    <options> = SUSPEND | RESUME | <existing options>

The database will remain in the suspended state until the ALTER SYSTEM RESUME 
command is issued.  The database will remain suspended even if the process 
issuing the ALTER SYSTEM SUSPEND command dies or exists.  However, if all 
instances are shutdown and started again, the database is no longer in a 
suspended state.  

The ALTER SYSTEM RESUME command has the effect of blocking the I/O since the 
SUSPEND command.  When the RESUME command is issued, it might cause a burst in 
the I/O, which may take a while to even out.  A message is written to the alert
log everytime the database is suspended or resumed, as shown by the example 
below:

    Mon Nov 29 11:32:22 1999
    Completed: alter database open
    Wed Dec  1 12:56:53 1999
    Starting ORACLE instance (normal)
    Wed Dec  1 22:03:50 1999
    Suspending database following alter system suspend command.
    Wed Dec  1 22:06:14 1999
    Resuming database following alter system resume command.
    Wed Dec  1 22:07:08 1999


The following is an example of using the SUSPEND and RESUME feature:

    SVRMGR> connect system/manager
    Connected.
    SVRMGR> alter system suspend;
    Statement processed.
    SVRMGR> select * from user_source;
    ^X^Cselect * from user_source   -----  (at this stage the statement will 
                                            just hang.  A Ctrl-X Ctrl-C was 
                                            issued to kill the statement)
                  *
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01013: user requested cancel of current operation
    SVRMGR>
    SVRMGR> alter system resume;
    Statement processed.


Considerations and Restrictions:
--------------------------------
- The files in the copy database can not be used as backups of the original 
  database for media recovery.  (If the direct path option is in use at the 
  time, there may be corrupted blocks).

- A new instance cannot be started during the SUSPEND state of the database.  
  If one is started, it will not be included in the SUSPEND process and thus no 
  I/O suspension guarantees are provided in this case.

- Creation of backups or archived logs will not be affected by the 
  ALTER SYSTEM SUSPEND command.

- The two different commands can  be issued from two different instances or 
  processes.

- If the SUSPEND command during execution may fail for some reason yet 
  result in some of the instances being suspended, the command can be issued 
  again since the instances in suspend status will ignore the command.

- Also database queries will hang when the database is in suspend mode

按照描述SUSPEND 操作会挂起所有io,只要涉及到io操作就会挂起,如果操作的所有请求都可以在内存中完成（buffer cache/shared pool等）,那这样的操作是可以直接完成的.

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 14 21:51:53 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system suspend;

System altered.

SQL> select database_status from v$instance;

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

SQL> create table t1 as select * from dba_users;
create table t1 as select * from dba_users
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> create table t_xff as select * from dba_users;
^C
C:\Users\XFF>

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 14 21:53:19 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system resume;

System altered.

SQL> select database_status from v$instance;

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

SQL> create table t_xff as select * from dba_users;

Table created.


SQL>  alter system suspend;

System altered.

SQL> select count(1) from user$;

  COUNT(1)
----------
        94

SQL> select count(1) from t_xff;
^C
C:\Users\XFF>

在某些情况下,可以通过这类操作来挂起数据库,做一些特殊的操作.