Oracle Database Quiesce State Detailed

The three states of the database have similarities, and here's a quick summary.

This article describes the state of Quiesce.

RESTRICT, Quiesce and Suspend (i):

When the database is in the QUIESCE state, only the DBA session can operate, and the normal session is in the waiting state, and the normal session can continue only if the database exits the Quiesce state.

Quiesce appears to be similar to restrict, modifying the state of the database so that DBA users can manage to avoid simultaneous access by non-DBA users. But there is a clear difference between the two. First of all, the restrict is to prohibit the landing of ordinary users, but not to the user has logged. If you want to completely disable access for ordinary users, you must either reboot or manually determine the normal session that is already connected, and perform the action of Kill sessions. And Quiesce is not so, by setting the system's Quiesce restricted, all non-DBA users are waiting, and no new operations can be performed until the system exits the QUIESCE state, whether it is a new login or an existing regular user session.

Therefore, the QUIESCE state is very helpful for the 7*24 environment, and for other users, only the waiting time for the operation becomes very long and does not give an error. Of course, QUIESCE has the advantages of restrict, there must be some additional requirements, that is, the database has to be configured with Resource management resource Management.

[Oracle@bjtest ~]$ Sqlplus "/As SYSDBA"

Sql*plus:release 9.2.0.4.0-production on Wednesday April 22 00:25:59 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to:

Oracle9i Enterprise Edition Release 9.2.0.4.0-64bit Production

With the partitioning, OLAP and Oracle Data Mining options

Jserver Release 9.2.0.4.0-production

Sql> Show Parameter Resource

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Enqueue_resources integer 3144

Resource_limit Boolean FALSE

Resource_manager_plan string

Sql> alter system QUIESCE restricted;

Alter system QUIESCE restricted

*

ERROR is on line 1th:

ORA-25507: The resource manager is not always open

If the Explorer is not open, the above ORA-25507 error will appear in 9i.

bash-2.03$ sqlplus "/As SYSDBA"

Sql*plus:release 10.2.0.3.0-production on Friday February 20 00:30:49 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connect to:

Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options

Sql> Show Parameter Resource

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Resource_limit Boolean FALSE

Resource_manager_plan string

Sql> alter system QUIESCE restricted;