Oracle DBA Tips Corner

     Return to the Oracle DBA Tips Corner.

click me  

How to Recreate the Database Control Repository - (Oracle10g R1)

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. On UNIX Systems
  3. On Windows Systems
  4. Further Reading


Overview

This article provides steps-by-step details on how to remove and recreate the Database Control Repository in a 10g R1 database.


On UNIX Systems

  1. Run the script $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager to remove the Database Control Repository.

    NOTE: Type RepManager without any arguments to get a list of available options.
    NOTE: You will need to have the database passwords for SYS and SYSMAN to drop the Database Control Repository! 

    $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager -connect TESTDB -action drop
Enter SYS user's password : change_on_install
Enter repository user name : SYSMAN
Enter repository user password : sysman_password
etting temporary tablespace from database...
Found temporary tablespace: TEMP
Checking SYS Credentials ... OK.
Dropping the repository..
Quiescing DB ... Failed.
Checking for Repos User ... Exists.
Repos User exists..
Clearing EM Contexts ... OK.
Dropping EM users ...
Done.
Dropping Repos User ... Done.
Dropping Roles/Synonymns/Tablespaces ... Done.
Unquiescing DB ... Done.
Dropped Repository Successfully.

  2. After deleting the Database Control Repository, you will need to remove the configuration files. This is done by using the emca script with the -x option as shown in the following example: 
    emca -x TESTDB

STARTED EMCA at Fri May 13 20:01:28 EDT 2005
May 13, 2005 8:01:28 PM oracle.sysman.emcp.EMConfig stopOMS
INFO: Stopping the DBConsole ...
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Fri May 13 20:01:35 EDT 2005

  3. Finally, you can recreate both the Database Control schema objects and configuration files by running the emca utility and answering the prompts. For my example, the database is using Automatic Storage Management (ASM), so I will be using the -a option to emca:

    NOTE: You will need to have the database passwords for SYS and DBSNMP to create the Database Control Repository!

    NOTE: One of the prompts in the creation phase asked for the SYSMAN password. Whatever you type for this prompt is what the creation assistant will use when creating the SYSMAN database account.

    NOTE: Another popular type of OEM Database Control configuration is a RAC configuration that uses ASM. To generate this type of configuration, use the following: 
    emca -c -a

    emca -a

STARTED EMCA at Fri May 13 20:05:20 EDT 2005
Enter the following information about the database to be configured
Listener port number: 1521
Database SID: TESTDB
Service name: TESTDB.IDEVELOPMENT.INFO
Email address for notification: jhunter@idevelopment.info
Email gateway for notification: relay.idevelopment.info
ASM ORACLE_HOME [ /u01/app/oracle/product/10.1.0/db_1 ]: /u01/app/oracle/product/10.1.0/db_1
ASM SID [ +ASM ]: +ASM
ASM port [ 1521 ]: 1521
ASM user role [ SYSDBA ]: SYSDBA
ASM user name [ SYS ]: SYS
ASM user password: change_on_install
Password for dbsnmp: dbsnmp
Password for sysman: sysman
Password for sys: change_on_install
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/10.1.0/db_1
Enterprise Manager ORACLE_HOME ................ /u01/app/oracle/product/10.1.0/db_1

Database host name ................ linux3
Listener port number ................ 1521
Database SID ................ TESTDB
Service name ................ TESTDB.IDEVELOPMENT.INFO
Email address for notification ............... jhunter@idevelopment.info
Email gateway for notification ............... relay.pair.com
ASM ORACLE_HOME ................ /u01/app/oracle/product/10.1.0/db_1
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM user name ................ SYS

-----------------------------------------------------------------
Do you wish to continue? [yes/no]: yes
May 13, 2005 8:06:37 PM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file /u01/app/oracle/product/10.1.0/db_1/sysman/emdrep/config/repository.variables ...
May 13, 2005 8:11:05 PM oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository ...
May 13, 2005 8:11:05 PM oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
May 13, 2005 8:11:09 PM oracle.sysman.emcp.EMConfig addPortEntries
INFO: Updating file /u01/app/oracle/product/10.1.0/db_1/install/portlist.ini ...
May 13, 2005 8:11:09 PM oracle.sysman.emcp.EMConfig updateEmdProps
INFO: Updating file /u01/app/oracle/product/10.1.0/db_1/sysman/config/emd.properties ...
May 13, 2005 8:11:09 PM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: targets.xml file is updated successfully
May 13, 2005 8:11:09 PM oracle.sysman.emcp.EMConfig updateEmomsProps
INFO: Updating file /u01/app/oracle/product/10.1.0/db_1/sysman/config/emoms.properties ...
May 13, 2005 8:11:09 PM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: emoms.properties file is updated successfully
May 13, 2005 8:11:13 PM oracle.sysman.emcp.EMConfig startOMS
INFO: Starting the DBConsole ...
May 13, 2005 8:13:17 PM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
May 13, 2005 8:13:17 PM oracle.sysman.emcp.EMConfig perform
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://linux3:5500/em <<<<<<<<<<<
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Fri May 13 20:13:17 EDT 2005

  4. Access to the new repository is http://<Machine Name>:5500/em/ 
    http://linux3.idevelopment.info:5500/em/


On Windows Systems

The procedures for recreating the Database Control Repository in Windows is identical to that on UNIX platforms.

  1. Run the script %ORACLE_HOME%\sysman\admin\emdrep\bin\RepManager to remove the Database Control Repository. 
    %ORACLE_HOME%\sysman\admin\emdrep\bin\RepManager -connect TESTDB -action drop
<Answer screen prompts>
    NOTE:
    The RepManager script in 10.1.0.2.0 does not work correctly on Windows. For this reason, you will have to manually drop the SYSMAN schema and all management objects. The RepManager script should be fully functional in release 10.1.0.3.0

    Logon SQL*Plus as user SYS or SYSTEM, and drop the SYSMAN account and mangement 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;

  2. After deleting the Database Control Repository, you will need to remove the configuration files. This is done by using the emca script with the -x option as shown in the following example: 
    emca -x TESTDB

  3. Finally, you can recreate both the Database Control schema objects and configuration files by running the emca utility and answering the prompts.

    NOTE: You will need to have the database passwords for SYS and DBSNMP to create the Database Control Repository!
    NOTE: One of the prompts in the creation phase asked for the SYSMAN password. Whatever you type for this prompt is what the creation assistant will use when creating the SYSMAN database account. 

    emca

  4. Access to the new repository is: 
    http://<Machine Name>:5500/em/


Further Reading

Additional information on emca command-line options can be found in the Oracle Enterprise Manager 10g Advanced Configuration Guide. This guide is available from OTN at the following address http://download-west.oracle.com/docs/pdf/B12013_01.pdf.

Copyright (c) 1998-2010 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Wednesday, 23-Aug-2006 18:36:08 EDT
Page Count: 48242