Thursday, 20 May 2010

Enterprise Manager DB Console set up for Oracle APPS R12 database 11g

Introduction:
In this article we will configure the Enterprise Manager DB console for Oracle 11g database for Oracle APPS R12.

CookBook:
Following points needs to be done.
1- Check for the "sysman" schema in 11g database, if it exist then it means there should be DB Console setup, to check this we can run the following statement,
$ emctl start dbconsole
if this successfull start the DB console then it means the EM DB console has already been configured, and if this statement returns any error, then better to resolve the issues or we can drop and recreate the DB Console. But before dropping the DB Console better to take the backup for the configuration files and database repository.

2- To drop the existing DB Console setup, we can use the following command.

/bin/emca -deconfig dbcontrol db -repos drop

This command will drop both the configuration files and database repository, and if this command is not working for some issues, we can go with manually deletion of the configuration files and database repository. Following commands can be used for manual deletion.

a- To drop the database repository
Step 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYSMAN
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE);

Step 2: Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM 'r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM 'r1.owner'.'r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

b- To drop the configuraiton files

/
/oc4j/j2ee/OC4J_DBConsole__

3- now we can use the following command to create the database repository and configuration files for DB Console.


-bash-3.00$ emca -config dbcontrol db -repos recreate
^C-bash-3.00$
-bash-3.00$
-bash-3.00$
-bash-3.00$ emca -config dbcontrol db -repos create
STARTED EMCA at May 20, 2010 9:32:40 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: ORA
Listener port number: 1581
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 ................ /oracle/oradb/tech_st/11.1.0
Local hostname ................ APPSDBServer.test.com
Listener port number ................ 1581
Database SID ................ ORA
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
May 20, 2010 9:33:13 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/oradb/tech_st/11.1.0/cfgtoollogs/emca/ORA/emca_2010_05_20_09_32_40.log.
May 20, 2010 9:33:16 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 20, 2010 10:16:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
May 20, 2010 10:16:31 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
May 20, 2010 10:23:33 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
May 20, 2010 10:23:41 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
May 20, 2010 10:23:41 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
May 20, 2010 10:24:27 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
May 20, 2010 10:24:27 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
May 20, 2010 10:25:12 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
May 20, 2010 10:25:12 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 20, 2010 10:27:21 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 20, 2010 10:27:21 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://APPSDBServer.test.com:1158/em <<<<<<<<<<<
May 20, 2010 10:27:37 AM 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: /oracle/oradb/tech_st/11.1.0/APPSDBServer.test.com_ORA/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 May 20, 2010 10:27:37 AM

4- Now the EM DB Console can be accessed by the following link.

https://APPSDBServer.test.com:1158/em

2 comments:

Suresh Kumar said...

Hi,

I'm running R12.1.1 on Linux using VMware through winxp machine. I tried to invoke the OEM, i tried starting emctl but failed, so thought of running emca but throws an error java IO Exceptions error. Can you please guide me.

Mehmood Ur Rehman said...

what kind of error messages are you getting, can you paste the errors.