Friday, June 29, 2012

Configure OEM in Oracle 11gR2 on Window


The Oracle Enterprise Manager, (OEM) is the standard monitoring tool for Oracle databases . The OEM allows the DBA to easily manage every aspect of the database, even non-traditional tasks like applying patches and scheduling jobs . Sometimes OEM doesn't work due to some issue and  i face such a issues while re-configuring it  and follow this method . Let's have a look on this below issue . All the steps are same for window or linux/unix platform . (just change path and variable where required)

C:\>set ORACLE_SID=orcl
C:\>emca  -deconfig  dbcontrol  db  -repos  drop
STARTED EMCA at Jun 28, 2012 10:52:27 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
Listener port number: 1521
Password for SYS user:
Password for SYS user: xxxx
Do you wish to continue? [yes(Y)/no(N)]: y
Jun 28, 2012 10:52:53 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\app\Neerajs\cfgtoollogs\emca\orcl\emca_2012_06_28_10_52_24.log.
Jun 28, 2012 10:52:55 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jun 28, 2012 10:52:56 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Jun 28, 2012 10:52:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Jun 28, 2012 11:03:43 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 28, 2012 11:03:44 AM

Now, creating  OEM

C:\>sqlplus sys/xxxx@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 11:07:03 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  drop  user  SYSMAN   cascade ; 
 drop  user  SYSMAN   cascade
             *
ERROR at line 1:
ORA-01918: user 'SYSMAN' does not exist

SQL> drop  user  MGMT_VIEW ;
drop  user  MGMT_VIEW
            *
ERROR at line 1:
ORA-01918: user 'MGMT_VIEW' does not exist

SQL>  drop  role MGMT_USER ;
 drop  role MGMT_USER
            *
ERROR at line 1:
ORA-01919: role 'MGMT_USER' does not exist


Drop all the dependent synonyms of  "sysman" 

SQL> spool  drop_synonym.sql 
SQL> SELECT  'drop public synonym '  || synonym_name || ';'   FROM dba_synonyms  WHERE    table_owner = 'SYSMAN';
no rows selected

If we have got  any  output here , then run the below script .

SQL> @drop_synonym.sql 


Let's  Configure EM

C:\>set ORACLE_SID=orcl
C:\>set ORACLE_HOME=C:\app\Neerajs\product\11.2.0\dbhome_1
C:\>emca -config dbcontrol db -repos create

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  i.e, database instance is down and ORA-01031 . We  search on meta-link and find some useful information . So, some possible reasons may be : 

1.) Database may not be up.

2.) Database is started setting environment variable ORACLE_HOME with trailing '/'. Reset  ORACLE_HOME and bounce the database.

3.) Confirm that the database is up.  Connect to the database via sql*plus and do:-

C:\>sqlplus sys/xxxx@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 28 17:25:00 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status,version from v$instance;
INSTANCE_NAME    STATUS          VERSION
----------------         ------------      -----------------
orcl                          OPEN             11.2.0.1.0

4.) 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. 
a.) To check the current ORACLE_HOME :-
set ORACLE_HOME (windows)

b.) To check the $ORACLE_HOME which was set at the time when the database was last started .Connect to sqlplus as SYS as SYSDBA and issue the following:-

SQL> var OHM varchar2(100);
SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;
PL/SQL procedure successfully completed.
SQL> PRINT OHM
OHM
<Oracle_Home which was set at the time that the database was last started will be returned here>

c.) 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 re-started. 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.

5.) 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:-
$ORACLE_HOME/bin>sqlplus sys/<password>@<alias> as sysdba

If the above returns the ora-1031 error, check the following :-

a.) 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 by logging into the database via sql*plus and typing "show parameter remote".

SQL> show parameter remote 
NAME                                                TYPE                VALUE
----------------------------                    ---------            --------------
remote_login_passwordfile            string                EXCLUSIVE

If this parameter is not set to 'EXCLUSIVE', the following command can be run .
SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

b.) For dbconsole 11.2.0.1 and 11.2.0.2 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 is NONE . So, i changed to NTS and finally able to configure the OEM . The rest are as 

C:\>set ORACLE_SID=orcl
C:\>set ORACLE_HOME=C:\app\Neerajs\product\11.2.0\dbhome_1
C:\>emca  -config dbcontrol db -repos create -SID  ORCL   -PORT  1521 -ORACLE_HOME  C:\app\Neerajs\product\11.2.0\dbhome_1

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

Enter the following information:
Listener ORACLE_HOME [ C:\app\Neerajs\product\11.2.0\dbhome_1 ]: C:\app\Neerajs\product\11.2.0\dbhome_1
Password for SYS user:
Password for DBSNMP 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\Neerajs\product\11.2.0\dbhome_1
Local hostname ................ ramtech-199
Listener ORACLE_HOME ................ C:\app\Neerajs\product\11.2.0\dbhome_1
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
Jun 28, 2012 11:51:48 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\app\Neerajs\cfgtoollogs\emca\orcl\emca_2012_06_28_11_51_07.log.
Jun 28, 2012 11:51:51 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jun 28, 2012 12:55:16 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jun 28, 2012 12:55:53 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Jun 28, 2012 1:07:37 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jun 28, 2012 1:07:58 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Jun 28, 2012 1:07:58 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Jun 28, 2012 1:10:46 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Jun 28, 2012 1:10:47 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jun 28, 2012 1:11:26 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jun 28, 2012 1:11:27 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jun 28, 2012 1:14:55 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 28, 2012 1:15:05 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://ramtech-199:5500/em <<<<<<<<<<<
Jun 28, 2012 1:15:48 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: C:/app/neerajs/product/11.2.0/dbhome_1/ramtech-199_orcl/sysman/config
/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 28, 2012 1:15:48 PM


Enjoy    :-) 


4 comments:

Anonymous said...

Hi! Quick question that's completely off topic. Do you know how to
make your site mobile friendly? My website looks weird when viewing
from my apple iphone. I'm trying to find a theme or
plugin that might be able to correct this issue. If you
have any recommendations, please share. With thanks!



Look into my site :: outdoor gyms

Anonymous said...

it appears that this capability has been deleted with oracle 12c?
I can't run emca on a windows 2012 box with oracle 12c ( 12.1.0.1) already installed.

Anonymous said...

Nice entry - resolved my issue. Thank You :)

Trần Đình Tài said...

Thank you so much