[原創] How to Quiesce a Database

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

文件參考

How to Quiesce a Database

By Alexander Geldutes



Quiesce a database is a new feature in Oracle 9i. It is similar to the restricted mode. When a database in a quiesced mode no one can connect to the database except SYS and SYSTEM and all current user sessions are suspended. As the result in the quiesced mode DBA can perform. some maintenance task.



In order to quiesce a database:

Set the RESOURCE_MANAGER_PLAN parameter to a non-null value.

alter system set resource_manager_plan='SYSPLAN' scope=both;



Connect to the database as SYSDBA using SQL*Plus and issue the ALTER SYSTEM command:

C:>sqlplus /nolog



SQL*Plus: Release 9.0.1.0.1 - Production on Tue Jun 17 15:10:56 2003



(c) Copyright 2001 Oracle Corporation.  All rights reserved.



SQL> connect / as sysdba

Connected.



SQL> alter system quiesce restricted;



System altered.



SQL>



If Resource Manager is not turned on (RESOURCE_MANAGER_PLAN is not set) and you are trying to quiesce a database then you receive the following error:

SQL>  alter system quiesce restricted;

   alter system quiesce restricted

   *

   ERROR at line 1:

   ORA-25507: resource manager has not been continuously on



So do not forget to set the RESOURCE_MANAGER_PLAN parameter to a non-null value. You can check the status of the database by running a query against V$INSTANCE;

SQL> select active_state from v$instance;



ACTIVE_ST

---------

QUIESCED



SQL>



To bring a database back in the fully operational mode, use the following command:

SQL> alter system unquiesce;



System altered.



SQL> select active_state from v$instance;



ACTIVE_ST

---------

NORMAL



SQL>