Archive

Posts Tagged ‘Oracle EM’

How to (re)configure Oracle Enterprise Manager – unexpected part II.

2013-07-30 1 comment

When I wrote about (re)configuring Oracle Enterprise Manager last time, I wrote something like that: “When you have a problem with creating the Oracle EM via command line (you’ve got the insufficient privileges error) try to use the Database Configuration Assistant“. I think it’s time to be more specific 🙂
Suppose, we did the following:

set ORACLE_SID=orcl
emca -config dbcontrol db -repos create

and get the insufficient privileges error:

STARTED EMCA at Jun 28, 2012 11:08:59 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID:  orcl
Exception in thread "main" oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable.
        at oracle.sysman.emcp.DatabaseChecks.throwDBUnavailableException(DatabaseChecks.java:151)
        at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:144)
        at oracle.sysman.emcp.DatabaseChecks.checkDbAvailability(DatabaseChecks.java:163)
        at oracle.sysman.emcp.DatabaseChecks.getDbServiceName(DatabaseChecks.java:582)
        at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1272)
        at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:574)
        at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:522)

and the emca log contains the below details:

oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
CONFIG: ORA-01031: insufficient privileges
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01031: insufficient privileges
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1655)
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.connect(SQLEngine.java:978)
at oracle.sysman.emcp.util.GeneralUtil.initSQLEngineLoacly(GeneralUtil.java:445)
at oracle.sysman.emcp.util.GeneralUtil.initSQLEngine(GeneralUtil.java:637)
at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(DatabaseChecks.java:118)

From the above logs, we find two hints: database instance is down and ORA-01031. So, some possible reasons may be:
1. Database may not be up – confirm that the database is up. Connect to the database via SQLPLUS

sqlplus / as sysdba

and do

select instance_name, status, version from v$instance;

the output should be similar to that:

INSTANCE_NAME STATUS VERSION
------------- ------ ----------
orcl          OPEN   11.2.0.3.0

2. Confirm whether the ORACLE_HOME environmental variable is correct. This involves comparing the current ORACLE_HOME environmental setting, with the one that was set the last time the database was started.
To check the current ORACLE_HOME (MS Windows):

set ORACLE_HOME

To check the ORACLE_HOME which was set at the time when the database was last started, connect to SQLPLUS (sys as sysdba) and issue the following:

var OHM varchar2(100);
exec dbms_system.get_env('ORACLE_HOME', :OHM);
print OHM;

If the ORACLE_HOME was set wrongly at the time that the database was started (as determined by above steps), then the database will need to be shut down and restarted. To shut the database down, it will be necessary to set the “wrong” ORACLE_HOME (the one which is returned in the OHM output). After the database has been successfully shut down, then the correct ORACLE_HOME should be set, and the database can be re-started.

3. Confirm whether it’s possible to connect to the database as the SYSDBA user remotely, using sqlplus. As part of the repository creation/drop process, the SYS user needs to be able to connect as SYSDBA. The “ORA-01031: insufficient privileges error” is returned when the SYS user is unable to connect as SYSDBA. To test this outside of dbconsole, test a sqlplus remote connection (one which connects via a tnsnames.ora alias) as sysdba, eg:

sqlplus sys/@ as sysdba

If the above returns the ORA-01031 error check whether the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set correctly in the database. For the purposes of configuring dbconsole, it must be set to ‘EXCLUSIVE’. This value can be checked this way:

show parameter remote_login_passwordfile

and the output should be like that

NAME                      TYPE   VALUE
------------------------- ------ ---------
remote_login_passwordfile string EXCLUSIVE

If this parameter is not set to ‘EXCLUSIVE’, the following command can be run:

alter system set remote_login_passwordfile=EXCLUSIVE scope=both;

For dbconsole 11.2.0.1, 11.2.0.2 and 11.2.0.3 check whether the %ORACLE_HOME%\network\admin\sqlnet.ora (or %TNS_ADMIN%\sqlnet.ora) contains the entry:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

In my case the value of SQLNET.AUTHENTICATION_SERVICES was NONE. So, I changed to NTS and think that finally I’ll be able to configure the OEM. Unfortunatelly, still there was an error. So, I started to dig up the logfiles in the %ORACLE_HOME%\_\sysman\log folder and there found the source of the problem (the OracleDBConsolesrvc.log and emdb.nohup are especially useful).

How to (re)configure Oracle Enterprise Manager.

When you’ve installed Oracle Database 10g or 11g, you can’t change the Windows hostname and/or IP address without reinstalling Oracle Enterprise Manager.
These are the steps to reconfigure Oracle EM:

1. Change the %ORACLE_HOME%\network\admin\listener.ora file from an IP address to a machine name.

2. Change the %ORACLE_HOME%\network\admin\tnsnames.ora file from an IP address to a machine name.

3. Change the C:\WINDOWS\System32\drivers\etc\hosts file by adding this line beneath the default localhost line (for the new hostname value):

127.0.0.1       localhost
192.168.111.11  new-hostname

Don’t forget to add the empty line after the last line (sometimes Windows can’t read the last line).

4. Change the Windows hostname.

5. Reboot the machine, and then drop the Oracle EM configuration with the following commands:

set ORACLE_SID=orcl
emca -deconfig dbcontrol db -repos drop

6. You’ll receive the following prompts, enter the Oracle SID, port number and all passwords:

STARTED EMCA at Jul 13, 2008 8:26:42 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y

7. You should then drop the SYSMAN user manually just in case. If the SYSMAN schema hasn’t been droped, then you’ll raise an error when you try to recreate the EM:

CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17
 
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17

Drop the user and dependent on version a few other objects, like:

DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

8. You can then create the EM environment with the following command:

emca -config dbcontrol db -repos create

9. Again, you’ll receive the following prompts, enter the Oracle SID, port number and all passwords:

STARTED EMCA at Jul 13, 2008 8:28:48 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.
 
Enter the following information:
DATABASE SID: orcl
Listener port NUMBER: 1521
Password FOR SYS USER:
Password FOR DBSNMP USER:
Password FOR SYSMAN USER:
Password FOR SYSMAN USER: Email address FOR notifications (optional):
Outgoing Mail (SMTP) server FOR notifications (optional):
-----------------------------------------------------------------
 
You have specified the following settings
 
DATABASE ORACLE_HOME ................ C:\app\Administrator\product\11.1.0\db_1
 
LOCAL hostname ................ new-hostname
Listener port NUMBER ................ 1521
DATABASE SID ................ orcl
Email address FOR notifications ...............
Outgoing Mail (SMTP) server FOR notifications ...............
 
-----------------------------------------------------------------
 
Do you wish TO continue? [yes(Y)/no(N)]: y

When you have a problem with creating the Oracle EM via command line (you’ve got the insufficient privileges error) try to use the Database Configuration Assistant.

That’s should be all.