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 NotC:\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 ConsoleC:\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 was not 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.
really useful, thank you.
ReplyDelete