Jephe Wu - http://linuxtechres.blogspot.com
Environment: Windows 2003 server SE SP2, Oracle 10g standard edition 10.2.0.4.0, Windows is using domain account to authenticate.
Problem: Due to some reason, we cannot login as sys/system/sysman/dbsnmp, we can only use oracle login Windows server and use os authentication(sqlpuls / as sysdba) to check database. Also, Enterprise Manager is not working. We need to drop/recreate EM repository to get EM up so that we can monitor database through EM
Objective: change password for sys/system/sysman/dbsnmp, recreate EM repository for getting EM working, use the latest version of sqldeveloper(3.0) so that we can use menu 'view-DBA' function to manage database also.
Question: For sql developer 3.0, why cannot we use os authentication?
I have successfully tried to use sql developer 2.0 for os authentication, please refer to
Refer to http://linuxtechres.blogspot.com/2011/07/how-to-use-sqldeveloper-with-os.html, but 3.0 doesn't work this way. So we cannot use DBA view function which is very good feature in version 3.0.
Steps:
1. change sys/system/sysman/dbsnmp password for database first.
login Windows, use os authentication
sqlplus / as sysdba
sql> alter user sys identified by "yourpassword";
sql> alter user system identified by "yourpassword";
sql> alter user sysman identified by "yourpassword";
sql> alter user dbsnmp identified by "yourpassword";
make user all above users are open, not expiried and locked.
Your might want to refer to another article at http://linuxtechres.blogspot.com/2010/09/jephe-wu-httplinuxtechres.html
and How to Change DBSNMP Password in Database 10g and 11g Monitored by DB Control [ID 259387.1] in Oracle support.
2. drop/recreate the existing EM repository.
Make sure c:\windows\system32\drivers\etc\hosts contains a line like this even though you can ping FQDN with correct ip without this following line.
1.2.3.4 FQDN hostname
otherwise, you will encouter error message below when cretae EM repository:
May 17, 2006 7:34:39 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfig
Exception: Error instantiating EM configuration files at oracle.sysman.emcp.EMAgentConfig.updateAgentConfigFiles(EMAgentConfig.java:2560)
at oracle.sysman.emcp.EMAgentConfig.performConfiguration(EMAgentConfig.java:1166)
at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:207)
at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:185)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:146)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)
Refer to EMCA Fails With Error "SEVERE: Error instantiating EM configuration files" [ID 370207.1] in Oracle support.
Sometimes, under Linux, without the correct entry in /etc/hosts with the correct ip address, you might encounter error for creating EM repository below:
SEVERE: Failed to allocate port(s) in the specified range(s) for the following process(es): JMS [5540-5559],RMI [5520-5539],Database Control [5500-5519],EM Agent [3938] | [1830-1849]
If you encounter error message like this:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one upper, one lower and
one special character
that's due to internal BUG 4195090. check REPMANAGER AND EMCA FAILS WITH PASSWORD VERIFICATIONFUNCTION [ID 779098.1] from Oracle support
=> change
ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION null;
then put back later after done emca.
c:> set ORACLE_SID=xxxx (important, otherwise, you cannot secure dbconsole after creating repository)
c:> emca -deconfig dbcontrol db -repos drop
type in sid, listener port, passwords etc
c:> emca -config dbcontrol db -repos create
type in sid, listener port, passwords etc.
You will get successfully created EM repository message, and secured dbcontrol successfully, but failed to start up dbcontrol, wait for 15 mins.
it will fail and give the following errors:
Refer to the log file at D:\oracle\10.2.0\db_1\cfgtoollogs\emca\xxxx\emca_2011-08-26_10-32-25-AM.log for more details.
26/08/2011 10:49:38 oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error starting Database Control
at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:646)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:224)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:193)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:184)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:419)
According to Enterprise Manager Database Control Configuration - Recovering From Errors Due to CA Expiry on Oracle Database 10.2.0.4 or 10.2.0.5 [Video] [ID 1222603.1]
and ATTENTION - Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5 - Patch Required from 31-Dec-2010 onwards [ID 1217493.1]
According to above docs, you can either apply Patch 8350262, or disable https for EM as follows:
You can use
emca -config dbcontrol db -repos recreate
to drop and create
3. disable https for EM (Starting from 10.2.0.4 onwards DBConsole uses HTTPS in its URL by Default [ID 747770.1] in Oracle support)
c:> emctl unsecure dbconsole
c:> emctl start dbconsole
access EM at http://FDQN:5501/em/console/
4. use Sqldeveloper 3.0 and EM to monitor/manage database.
-->
Posts
