DBA Tips: Is your SYSMAN Account Locked??

http://www.articles.freemegazone.com/oracle-sysman-account-locked.php?ref=1

The default super administrator SYSMAN account is created with the password specified by you when you install Enterprise Manager. Many database administrators like me must have faced the scenario when they are unable to connect to database engine because instead of a normal login screen they get an error message “Enterprise Manager is not able to connect to database instance. The state of the components are listed below……”

Everything is up and running but still your Enterprise Manager can not connect to the database instance. How frustrating it is, right?

In this article I will discuss how to troubleshoot the problem if one day or another you are unable to connect to the database instance as a result of error like this.

Check Enterprise Manager Log File:

In the first step we will check the enterprise manager log file as follows.

[oracle@myserver log]$ pwd

/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD
/sysman/log
[oracle@ myserver log]$ tail -50 emoms.log
.
.
.
2008-02-11 23:12:04,968 [ApplicationServerThread-11]
ERROR app.SessionObjectManager sessionDestroyed.128 -
java.sql.SQLException: ORA-28000: the account is locked
java.sql.SQLException: ORA-28000: the account is locked
.
.
.

We can see that the log file contains ORA-28000 error (ORA-28000: the account is locked). Generally we get ORA-28000 error if the user has consequently entered wrong password for maximum number of times specified by the user profile parameter FAILED_LOGIN_ATTEMPTS or the account is locked by DBA.

Check Database Users:

Now we will check database users.

SYSTEM> select username, account_status from dba_users;

     USERNAME       ACCOUNT_STATUS
   -------------------     -------------------------------
     MGMT_VIEW                    OPEN
     SYS                                    OPEN
     SYSTEM                            OPEN
     DBSNMP                           OPEN
     SYSMAN                   LOCKED(TIMED)

Here we can see that the all other accounts are working fine but the SYSMAN account is locked.

Stop Database Console:

Now we will stop the Database Console

[oracle@myserver dbascripts]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

https://myserver.mydomain:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.


Reset SYSMAN Account:

Once the database console is stopped then we will reset the SYSMAN account.

[oracle@myserver log]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 12 09:51:59 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> alter user sysman identified by <new_password> account unlock;
User altered.

Now our SYSMAN account is successfully reset. Now we need to reset SYSMAN account in the targets.xml file. The targets.xml file is located in $ORACLE_HOME/myserver.mydomain _mySID/sysman/emd

We will modify the properties ‘Username' and ‘Password'.

[oracle@myserver emd]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/emd
[oracle@myserver emd]$ cp targets.xml targets.xml.ori
[oracle@myserver emd]$ vi targets.xml
.
.
.
<Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
<Property NAME="password" VALUE="<new_password>" ENCRYPTED="FALSE"/>
.
.
.

After modifying targets.xml file then we will reset the SYSMAN account in emoms.properties file in $ORACLE_HOME/myserver.my_domain_mySID/sysman.config. We will modify the parameters ‘oracle.sysman.eml.mntr.emdRepPwd' and ‘oracle.sysman.eml.mntr.emdRepPwdEncrypted'.

[oracle@myserver config]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/config
[oracle@myserver config]$ cp emoms.properties emoms.properties.ori
[oracle@myserver config]$ vi emoms.properties
.

.
.
oracle.sysman.eml.mntr.emdRepPwd= <new_password>
.
.
.
oracle.sysman.eml.mntr.emdRepPwdEncrypted= FALSE
.
.
.

Start the Database Console:

At the end we will start the database console.

[oracle@myserver dbascripts]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

https://myserver.mydomain:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ...... started.
-----------------------------------------------------------------------------------------------
Logs are generated in directory
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/log

Here you are... Enjoy your Enterprise Manager!! Read Again!!