ORACLE 受限RESTRICT模式,只读READ ONLY模式,静默QUIESCE模式,延迟SUSPEND模式解释

一、受限RESTRICT模式
关于ORACLE RESTRICT模式，以及START OPEN RECOVER 




说明如下：
Restricting Access to an Instance at Startup
You can start an instance, and optionally mount and open a database, in restricted 
mode so that the instance is available onlyto administrative personnel (not general 
database users). Use this mode of instance startup when you must accomplish one of 
the following tasks:
■ Perform an export or import of data
■ Perform a data load (with SQL*Loader)
■ Temporarily prevent typical users from using data
■ Perform certain migration or upgrade operations
Typically, all users with the CREATE SESSIONsystem privilege can connect to an open 
database. Opening a database in restricted mode allows database access only to users 
with both the CREATE SESSIONand RESTRICTED SESSIONsystem privilege. Only 
database administrators should have the RESTRICTED SESSIONsystem privilege. 




关于如上文档，说明受限模式可以再你想做一些维护的时候，而不想其他非restrict权限用户登录的时候使用。
可以和STARTUP进行搭配启动实例
startup [nomount|mount|open} restrict;
而ORACLE RESTART也可以使用
srvctl start database -d db_unique_name-o restrict




同时在正常数据库使用期间也可以使用
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
 
System altered
 
SQL> ALTER SYSTEM enable RESTRICTED SESSION;
 
System altered




来启用和禁用。但是注意经过测试，这个操作知识影响随后的会话。已经连接的会话不受影响，通过查看V$INSTANCE的
LOGINS字段可以查看其状态




二、只读READ ONLY模式


如下描述：
Opening a database in read-only mode enables you to query an open database while 
eliminating any potential for online data content changes. While opening a database in 
read-only mode guarantees that data file and redo log files are not written to, it does 
not restrict database recovery or operations that change the state of the database 
without generating redo. For example, you can take data files offline or bring them 
online since these operations do not affect data content.
可以看到实际上READ ONLY打开数据库后并不是限制所有的操作，而是关于修改数据和写入日志
文件的操作时不允许，相反的是允许的。V$DATABASE的OPEN_MODE字段显示其状态。
做测试如下：
1、增加表空间
SQL> create tablespace jjj datafile size 10m ;
create tablespace jjj datafile size 10m
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access




2、增加日志文件
SQL> alter database add logfile group 11;


Database altered.




3、OFFLINE数据文件


SQL> alter database datafile 8 offline;


Database altered.


SQL> alter database  datafile 8 online;


Database altered.


而关于使用READ ONLY 模式。文档只给出了
在MOUNT阶段下。使用如下命令打开数据库


alter database open read only;
alter database open read write;
意思是如果想改变模式，需要SHUTDOWN IMMEDIATE然后
startup mount 下修改


三、静默QUIESCE模式
如下描述：
   Occasionally you might want to put a database in a state that allows only DBA 
transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a 
quiesced state, in the sense that no ongoing non-DBA transactions, queries, fetches, or 
PL/SQL statements are running in the system. 
  In this discussion of quiesce database, a DBA is defined as user SYS or SYSTEM. 
Other users, including those with the DBA role, are not allowed to issue the 
ALTER SYSTEM QUIESCE DATABASE statement or proceed after the database is quiesced. 
  If a user issues a SQL query in an attempt to force an inactive session to
become active, the query will appear to be hung. When the database is later unquiesced,
the session is resumed, and the blocked action is processed.
  Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED
statement completes, and the database is ina quiesced state. In an Oracle Real 
Application Clusters environment, this statement affects all instances, not just the one 
that issues the statement.
  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.
  select bl.sid, user, osuser, type, program
   from v$blocking_quiesce bl, v$session se
   where bl.sid = se.sid;
  当你需要做一些维护的时候，可能需要只有SYS或者SYSTEM用户登录，你可以使用这种模式.其他即使
拥有DBA角色的用户也是受到限制的。它不仅会让新的非SYS和SYSTEM用户登录HANGH住，而且登录的会话
如果再想执行语句也会被HANG住。注意改变为静默模式需要等待当前所有的ACTIVE会话完成其操作，可能
需要较长的时间，次期间虽然静默模式并未完成，其效果已经产生新的登录和ACTIVE已有的会话是会HANG住的。
可以通过V$BLOCKING_QUIESCE来查看哪些会话堵塞了改变静默模式。同时RAC环境中他影响全部节点，而非本实例。
当然本操作也会由于事物而被堵塞，在一个事物未COMMIT或者ROLLBACK的时候其会话状态任然为NOACTIVE，
但是也会堵塞本操作。 V$INSTANCE ACTIVE_STATE可以查看其状态
  更改方法 
  ALTER SYSTEM QUIESCE RESTRICTED;
  ALTER SYSTEM UNQUIESCE;
  
可以简单的测试如下：
开启一个非SYSTEM,SYSDBA用户事物
SQL> delete test10;
 
93 rows deleted
而不提交，另外开启SYSDBA权限会话改变为静默模式
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
....一直HANG主
等待事件为wait for possible quiesce finish


提交这个事物
commit;
改变模式成功，如果再想查询数据，则开始等待
select * from test
一直HANG
其等待事件为resmgr:become active 


四、延迟SUSPEND模式
描述如下：
  The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to data files (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. 
   The suspend/resume feature is not a suitable substitute for normal shutdown 
operations, because copies of a suspended database can contain uncommitted updates.
SUSPEND模式会阻挡所有的I/O，所有的读写都会处于HANG的状态，而这种模式在移动文件的时候比较
有用，而他不能代替传统的SHUTDOWN操作或者备份操作，因为他包含UNCOMMIT的事物。RAC下影响全节点，
V$INSTANCE的DATABASE_STATUS可以查看其状态
其改变方式如下：


ALTER SYSTEM SUSPEND;
ALTER SYSTEM RESUME;


做一个测试
ALTER SYSTEM SUSPEND;
后
select * from test;
drop table test;
alter system switch logfile;
操作均被堵塞，其等待事件为：
 writes stopped by instance recovery or database suspension

在做一个测试在LINUX 使用这种方法移动数据文件。
先发起
SQL> ALTER SYSTEM SUSPEND;
然后建立一个目录
mkdir TESTXUEXI 
然后拷贝我的数据文件目录到这个目录
cp -R xuexi TESTXUEXI/
然后MV 以前的目录
mv xuexi xuexitest
然后建立一个连接
ln -s TESTXUEXI/xuexi/ xuexi
 xuexi -> TESTXUEXI/xuexi/
这样我们算完成了。可以恢复
ALTER SYSTEM RESUME;
这样我们继续查询我们的数据，但是实际底层的文件已经是用的复制的那一份。这种方法
可以再空间不够的时候使用，但是这和SHUTDOWN DATABASE再移动也没什么区别。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1270941/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-1270941/