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再移動也沒什麼區別。