联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
以前一直没有注意到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>
在某些情况下,可以通过这类操作来挂起数据库,做一些特殊的操作.
