数据库进入静默状态 以及与restricted介绍

DBA偶然也需要从并发非dba操作中孤立出来，换句话说，隔离并发非DBA事务，查询或PL/SQL语句。隔离的一种方法是取关闭数据库并以restricted模式打开。你也能不中断用户，使系统进入静默状态。在静默状态中，DBA能安全地执行某些操作，这些操作要求从当前非DBA用户中隔离出来操作。

在静默状态下,只有具有DBA权限的用户能够在数据库中执行查询/更新操作, 运行PL/SQL程序, 任何非DBA用户都不能在数据库中执行任何操作.
DBA在执行某些操作时,必须排队其他用户的活动这类操作包括如下两类:
  1.某些操作在执行过程,如果有其他用户访问操作的对象,该操作将会失败. 比如, 当DBA正在为某个表添加一个新的字段时,如果有用户恰好正在使用这个表,添加字段的操作将失败.
  2.某些操作在执行过程中产生的中间结果不应当被其他用户看到.比如,假设DBA要执行一项分为多个步骤的操作:首先导出一个表的数据,然后删除这个表, 最后再重新导入数据建立新的表,以达到重建该表的目的.而如果有某个用户在删除表之后,重建表之前访问这个表,将会得到错误的结果.
  如果没有静默功能, 要保证成功完成上述类型的操作必须首先关闭数据库, 然后再使用受限模式打开数据库. 这样做的代价是很大的, 尤其是在必须保证数据库不间断运行的环境中.而使用数据库进入静默状态可以快速达到相同的目的,但是却并不需要中断数据库的运行.
  处于静默状态时只允许SYS和SYSTEM两个DBA操作操作, 其他用户即使被授予DBA角色或SYSDBA权限也不能够在静默状态的数据库中进行操作. 因此,静默状态是比受限状态更为"干净"的状态.

SQL> alter system quiesce restricted;
alter system quiesce restricted
*
ERROR at line 1:
ORA-25507: resource manager has not been continuously on

SQL> alter system quiesce restricted;
alter system quiesce restricted
*
ERROR at line 1:
ORA-25507: resource manager has not been continuously on

SQL>
SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string
SQL>
SQL> alter system set resource_manager_plan='SYSTEM_PLAN' scope=spfile ;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 4732717392 bytes
Fixed Size                   743760 bytes
Variable Size             738197504 bytes
Database Buffers         3992977408 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.
SQL> alter system quiesce restricted;

System altered.
SQL> select status,active_state from v$instance;

STATUS       ACTIVE_ST
------------ ---------
OPEN         QUIESCED

 

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 only to administrative personnel (not general database users). Use this mode of instance startup when you need to 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 SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.

The following command starts an instance (and mounts and opens the database) in restricted mode:

STARTUP RESTRICT

You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;
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 only to administrative personnel (not general database users). Use this mode of instance startup when you need to 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 SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.
The following command starts an instance (and mounts and opens the database) in restricted mode:
STARTUP RESTRICT

You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.
Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:
ALTER SYSTEM DISABLE RESTRICTED SESSION;