OEM: The database is down. Please specify the host credentials to access database restart and diagnostics tools.

We recently faced this issue on "Oracle Enterprise manager" on one of our testing environments

Oracle Enterprise manager is showing following message as in above image:
The database is down. Please specify the host credentials to access database restart and diagnostics tools.

I debugged it with following steps

1) Check Database is open or Not

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 02:35:33 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

SQL> select status from v$instance;
STATUS
------------
OPEN

2) Check listener is running properly

C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 06-FEB-2015 02:36:16
Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydbhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date                05-FEB-2015 14:11:28
Uptime                    0 days 12 hr. 24 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\myorcl\Database\myorclDB\NETWORK\ADMIN\listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydbhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status READY, has 1 handler(s) for this service...
Service "myorcl" has 1 instance(s).
  Instance "myorcl", status READY, has 1 handler(s) for this service...
Service "myorclXDB" has 1 instance(s).
  Instance "myorcl", status READY, has 1 handler(s) for this service...
The command completed successfully

3) check status of emctl

C:\Windows\system32>emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://mydbhost:5500/em/console/aboutApplication

Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory 
E:\myorcl\Database\myorclDB/mydbhost/sysman/log

4) check emoms.log logs
E:\myorcl\Database\myorclDB\mydbhost_myorcl\sysman\log\emoms.log

java.sql.SQLException: ORA-28000: the account is locked

5) Check Account Status of SYSTEM, SYSMAN and DBSNMP users

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 02:37:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

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

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

By above steps I was sure that Database was functioning normally, the problem was with SYSMAN user being locked. Oracle use SYSMAN user as a default super-user account to administrator Oracle Enterprise manager and stores the Oracle Management Repository in SYSMAN schema. SYSMAN password is stored in encrypted format in files which are used by OEM, We need to unlock SYSMAN user and change password of SYSMAN.

I use following Oracle documentation to change the password of SYSMAN
https://docs.oracle.com/cd/B19306_01/em.102/b40002/repository.htm#i1029558

Here are the steps I follow to resolve the issue

1) Stop Enterprise Manager Database Console

C:\Windows\system32>emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://mydbhost:5500/em/console/aboutApp
lication
The OracleDBConsolemyorcl service is stopping..........
The OracleDBConsolemyorcl service was stopped successfully.

2) Connect to SYS user as SYSDBA

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 02:37:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

SQL>

3) Unlock the SYSMAN account and change password

SQL> alter user sysman account unlock;
User altered.

SQL> alter user SYSMAN identified by "NEW_SYSMAN_PASSWORD";
User altered.

4) locate and open the emoms.properties configuration file, in our case it was at
E:\myorcl\Database\myorclDB\mydbhost_myorcl\sysman\config\emoms.properties

5) change following entries value in the "emoms.properties" file:

    orcle.sysman.eml.mntr.emdRepPwd=NEW_SYSMAN_PASSWORD (in plain text)
    orcle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE (true to false)

6) In some cases you may need to edit "targets.xml" file too, which is located at
E:\myorcl\Database\myorclDB\mydbhost_myorcl\sysman\emd\targets.xml

7) change following entries value in the "targets.xml" file:

        <Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
        <Property NAME="password" VALUE="NEW_SYSMAN_PASSWORD" ENCRYPTED="FALSE"/>

8) Start Enterprise Manager Database Console

C:\Windows\system32>emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://mydbhost:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...The OracleDBConsolemyorcl service is starting..................
The OracleDBConsolemyorcl service was started successfully.

All Done, and Oracle Enterprise Manager is now running properly :)

I really hope you all have enjoyed and learnt/revisited something from this demonstration on the OEM issue and how to change SYSMAN password. Don't forget to put your feedback as comments, they are really valuable.



Related Posts:
- Oracle Installation and DHCP
- Oracle Database 12c New Features for Developers
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
- How to connect sqlplus without tnsnames.ora
- SQLNET: How does Oracle Client connect with Oracle Server