EMCA重建OEM时导致DB停顿在quiescing状态

Created on 04.02.2011
环境:
Oracle版本:
Oracle 10g Release 2  10.2.0.5
OS版本:
Windows Server 2003 Enterprise
摘要:
用EMCA重建OEM时，导致数据库挂起，DB一直处于quiescing状态，除了使用sys和system新的连接无法建立

在生产数据库上重建EM还是具有危险性的，这一点以前没有意识到，因为每一次都很顺利，但这一次真实遇到问题。

用EMCA重建repository之前，实际上每次都会警告，在repository被删除的时候数据库会被置于静默状态。
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: Y
但是从来没有把这个警告当回事。

输入所有的参数之后，repository开始重建，但是一直停顿在下面的提示信息，等待很久后还是如此。
Apr 2, 2011 1:52:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\ora102\cfgtoollogs\emca\OPCON2\emca_2011-04-02_01-46-14-PM.log.
Apr 2, 2011 1:52:15 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Apr 2, 2011 1:52:18 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Apr 2, 2011 1:52:18 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) …

这个时候如果查询v$instance,会发现db一直处于quiescing状态
SQL> select status,active_state from v$instance;

STATUS       ACTIVE_ST
———— ———
OPEN         quiescing

当数据库处于静默状态时，只有SYS和SYSTEM用户能够在数据库中进行操作，以完成某些不希望被打扰的操作。
SQL> alter system quiesce restricted;
使用上面的SQL进入静默状态时，数据库会等待所有非DBA用户session结束，并不允许非DBA用户建立新的session，在这个过程中，v$instance会一直显示quiescing，在进入静默状态后会显示quiesced。
SQL> alter system unquiesce;
上面的语句离开静默状态。

在我的这个例子中，一直处于quiescing说明有活动的session在阻止DB进入静默状态。

Metalink文档152819.1提供了一个SQL用来查询是那个session阻止了DB进入静默状态。
具体内容如下：

Apparent Hang After Issuing ‘ALTER SYSTEM QUIESCE RESTRICTED’ [ID 152819.1]
Modified 22-OCT-2010     Type PROBLEM     Status PUBLISHED

Problem Description
===================

As a DBA user, you issue the following command to put all running instances
into quiesced state:

SQL> alter system quiesce restricted;

It appears to hang and does not return to the prompt.

Solution Description
====================

Find sessions that have open transactions and take appropriate action, either
by killing these session(s) or asking the owner to end the ongoing transactions
by either committing or rolling back.

You may use this query to find open transactions:

select p.spid, s.osuser, s.machine, s.username, s.sid, s.serial#
from v$session s, v$process p
where p.addr = s.paddr
and s.sid in (select sid from v$lock where type = ‘TX’);

Explanation
===========

The ALTER SYSTEM QUIESCE RESTRICTED command puts all running instances into
quiesced state. At the time an ALTER SYSTEM QUIESCE RESTRICTED command is
issued, there might be active non-DBA sessions in the system, such as inside
a transaction or a query. These sessions can proceed until they become inactive
(the transaction or the query completes). Only after all these sessions become
inactive does the ALTER SYSTEM QUIESCE RESTRICTED command return. Active
transactions as SYS or SYSTEM do not block the database going into a quiesce mode.

The wait event for the session is ‘wait for possible quiesce finish’.

References
==========

Note:102925.1  Tracing sessions: waiting on an enqueue

Posted in Oracle,  管理 by admin at April 2nd, 2011.
Tags: emca, oem, Oracle, quiesced mode