Enterprise Manager show database status is unavailable

2011/08/03 by 1 Comment

客户的一套11.2.0.1 RAC数据库部署了 Enterprise Manager 11g以方便管理，但系统运行一段时间后EM就变得无法正常使用了，页面显示”the database status is currently unavailable…….”，但实际上数据库和监听都处于正常可用的状态。

sysman_password

作为乙方来说对Enterprise Manager并不太熟悉，虽然EM是一个十分强大的图形化监控工具，但是我们很少有机会在生产环境中用到它，这一点可能和甲方的DBA不同。

针对该EM不可用的问题首先想到了尝试重启dbconsole，以验证该问题是否可重现。

emctl stop  dbconsole
emctl start dbconsole

重启dbconsole后问题依旧,查MOS发现引发该EM不可用问题的可能性有多种，通过$ORACLE_HOME/_/sysman/log/emoms.log的EM相关日志可以进一步定位该问题:

tail -500 emoms.log

 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:39:46,131 [EMUI_14_39_46_/console/aboutApplication] ERROR em.console doGet.363 - java.lang.IllegalStateException: Response has already been committed,
be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
java.lang.IllegalStateException: Response has already been committed, be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
        at com.evermind.server.http.EvermindHttpServletResponse.sendRedirect(EvermindHttpServletResponse.java:1371)
        at oracle.sysman.emSDK.svlt.EMServlet.myDoGet(EMServlet.java:812)
        at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:343)
        at oracle.sysman.eml.app.Console.doGet(Console.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:107)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
        at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:146)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:327)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:39:46,150 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:39:46,157 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:46,182 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:51,352 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:39:51,394 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:51,509 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:39:51,537 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:39:56,757 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:03,574 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:03,603 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:03,729 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:03,800 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:09,328 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:09,361 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:11,946 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,285 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,307 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,321 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:16,368 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:16,437 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,472 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:16,507 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:16,536 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:17,187 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:17,189 [EMUI_14_40_17_/console/aboutApplication] ERROR svlt.PageHandler handleRequest.639 - java.lang.IllegalStateException: Response has already been committed
java.lang.IllegalStateException: Response has already been committed
        at com.evermind.server.http.EvermindHttpServletResponse.resetBuffer(EvermindHttpServletResponse.java:1892)
        at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:249)
        at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
        at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
        at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
        at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
        at oracle.sysman.emSDK.svlt.PageHandler.render(PageHandler.java:1113)
        at oracle.sysman.emSDK.svlt.PageHandler.myRender(PageHandler.java:1173)
        at oracle.sysman.emSDK.svlt.PageHandler.handleRequest(PageHandler.java:597)
        at oracle.sysman.emSDK.svlt.EMServlet.myDoGet(EMServlet.java:787)
        at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:343)
        at oracle.sysman.eml.app.Console.doGet(Console.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:107)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
        at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:146)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:327)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:40:17,189 [EMUI_14_40_17_/console/aboutApplication] ERROR em.console doGet.363 - java.lang.IllegalStateException: Response has already been committed, be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
java.lang.IllegalStateException: Response has already been committed, be sure not to write to the OutputStream or to trigger a commit due to any other action before calling this method.
        at com.evermind.server.http.EvermindHttpServletResponse.sendRedirect(EvermindHttpServletResponse.java:1371)
        at oracle.sysman.emSDK.svlt.EMServlet.myDoGet(EMServlet.java:812)
        at oracle.sysman.emSDK.svlt.EMServlet.doGet(EMServlet.java:343)
        at oracle.sysman.eml.app.Console.doGet(Console.java:157)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.sysman.eml.app.EMRepLoginFilter.doFilter(EMRepLoginFilter.java:107)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
        at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:146)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.BrowserVersionFilter.doFilter(BrowserVersionFilter.java:122)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:102)
        at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:17)
        at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:327)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
        at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
2011-08-02 14:40:21,661 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:21,691 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:21,822 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:21,886 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean03.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:27,141 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:33,941 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:33,975 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:34,092 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:34,124 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean02.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:42,088 [PAFStartupThread] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:42,332 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:42,584 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:42,611 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,594 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,622 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,653 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,683 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,738 [HTTPThreadGroup-6] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,766 [HTTPThreadGroup-6] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean01.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:46,793 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

2011-08-02 14:40:46,822 [HTTPThreadGroup-4] ERROR eml.OMSHandshake processFailure.806 - OMSHandshake failed.(AGENT URL = https://maclean04.askmac.cn:3938/emd/main)(ERROR = INTERNAL_ERROR)(CAUSE =java.sql.SQLException: ORA-28000: the account is locked
)
2011-08-02 14:40:48,244 [HTTPThreadGroup-4] ERROR conn.ConnectionService verifyRepositoryEx.891 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

从以上em日志中可以发现最显著的问题为ORA-28000，显然是EM相关的数据库用户被lock住了。10g中与EM相关的数据库用户包括SYSMAN和DBSNMP，分别查询该2个用户的状态:

SQL> select account_status,lock_date,username from dba_users where username in ('SYSMAN','DBSNMP');

ACCOUNT_STATUS                   LOCK_DATE       USERNAME
-------------------------------- --------------- ------------------------------
OPEN                                             DBSNMP
LOCKED(TIMED)                    02-JUL-11       SYSMAN

发现SYSMAN用户在7月份被锁定了，而且是LOCKED(TIMED)，说明是因为登陆密码不正确多次登陆后被锁的。LOCKED(TIMED)的相关信息以下Note:

DBA_USERS.ACCOUNT_STATUS shows LOCKED(TIMED) instead of LOCKED even if PASSWORD_LOCK_TIME set to unlimited [ID 284344.1]
  	Modified 18-FEB-2011     Type PROBLEM     Status PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 21-Sep-2010
Symptoms
DBA_USERS.ACCOUNT_STATUS shows LOCKED(TIMED) instead of LOCKED for a user with a profile with a PASSWORD_LOCK_TIME
set to unlimited and number of failed logins > FAILED_LOGIN_ATTEMPTS

For eg.

create profile my_profile limit failed_login_attempts 3 password_lock_time unlimited ;

SQL> create user my_user identified by my_user default tablespace users temporary tablespace temp profile my_profile ;

User created.

SQL> grant connect to my_user ;

(after 3 failed login attempts)

USERNAME PROFILE ACCOUNT_STATUS
------------------------------ ------------------------------ --------------
MY_USER MY_PROFILE LOCKED(TIMED)
Cause
Up until Oracle RDBMS version 11.1.0.7, after a number of failed logins > FAILED_LOGIN_ATTEMPTS, the account
status of the user will always be LOCKED(TIMED).
One would only see LOCKED if the account was explicitly locked during create or alter user/role
(ie after using CREATE USER or ALTER USER statements)

eg.

alter user my_lock account lock ;

USERNAME PROFILE ACCOUNT_STATUS
-------------------------------
MY_USER MY_PROFILE LOCKED

Solution
Expected behaviour is
1. Oracle release is  FAILED_LOGIN_ATTEMPTS

2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited:
DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME =
DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

于是我尝试将SYSMAN用户解锁，并重启EM并再次尝试登陆:

SQL> alter user sysman account unlock;
User altered.

重启EM后重新登陆，发现问题仍未解决，再次检查账户信息

SQL> select account_status,lock_date,username from dba_users where username in ('SYSMAN','DBSNMP');

ACCOUNT_STATUS                   LOCK_DATE       USERNAME
-------------------------------- --------------- ------------------------------
OPEN                                             DBSNMP
LOCKED(TIMED)                    02-AUG-11       SYSMAN

发现SYSMAN用户又被LOCK住了，显然是有人在数据库内修改了SYSMAN的密码后，没有重新配置EM中的SYSMAN密码，导致每次EM启动时都会尝试用错误的密码登陆数据库，最终造成SYSMAN用户被LOCKED(TIMED)住，并出现”ORA-28000: the account is locked”错误。

找出根本原因后问题就显得十分简单了，我们只需要关闭EM，并同步数据库和EM中的SYSMAN密码，再重启EM就可以了:

1.
首先关闭EM
export ORACLE_SID=
export ORACLE_UNQNAME=
cd $ORACLE_HOME/bin
./emctl stop dbconsole

2.
修改DB中的SYSMAN密码并解锁用户，并验证登陆正常
alter user sysman identified by  ;

alter user sysman account unlock;

conn sysman/password

3.
更新EM中的SYSMAN密码
export ORACLE_SID=
export ORACLE_UNQNAME=
cd $ORACLE_HOME/bin
./emctl setpasswd dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://maclean.askmac.cn:1158/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.

4.
启动EM
export ORACLE_SID=
export ORACLE_UNQNAME=
cd $ORACLE_HOME/bin
./emctl start dbconsole
Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号