Manually drop and recreate Oracle Enterprise Manager 11g Database Console repos

Login as user sys and execute DBMS_AQADM.DROP_QUEUE_TABLE. This will drop the queue table that is located on the sysman schema.

sqlplus "/ as sysdba"

DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);

Shutdown the database and start database in restrict mode to remove dbms_jobs.

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;

Remove dbms_jobs and set em user context to 5

EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

EXEC sysman.setEMUserContext('',5);

Using following PL/SQL block remove all the SYNONYMS that belongs to SYSMAN

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;

/

Drop role mgmt_user and users mgmt_view, sysman with cascade option. Once users are dropped we can disable restricted session. This will lift an instance from restricted mode to normal.

DROP ROLE mgmt_user;

DROP user mgmt_view cascade;

DROP user sysman cascade;

ALTER SYSTEM DISABLE RESTRICTED SESSION;

Make sure SYSMAN is dropped successfully.

select username, account_status, created from dba_users where username in ('SYSMAN');

Creating em repos with the command below.

$ORACLE_HOME/bin/emca -config dbcontrol db -repos create 

In my case I got error message with insufficient privileges. Since password file is missing in my case this error has occured.

Create password file for the database.

cd $ORACLE_HOME/dbs

orapwd file=orapworcl password=oracle_4U entries=5

With new password file, em repos will be created successfully.