Recover OEM repository from old schema backup


Things not always go right, and recently I had to recover the OEM repository using a one month old datapump backup. Only SYSMAN schema was corrupted, and I did not touch the other schemas.

I probably still miss doing some things and this post is mostly a note to make it faster for other people going through the process and not having a DB backup…

A full restore of the database would be certainly a much better and efficient solution than the one below. 🙂

To recover the corrupted SYSMAN schema where the OEM repository resides, first I drop it and then I did:

# Restore user
impdp schemas=SYSMAN directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp include=USER

# Restore system grants:
SQL> grant execute on dbms_crypto to sysman;
SQL> grant execute on dbms_lock to sysman;
SQL> grant execute on dbms_rls to sysman;
SQL> grant execute on dbms_aq to sysman;
SQL> grant execute on dbms_aqadm to sysman;
SQL> grant execute on dbms_alert to sysman;

# Restore other object grants 
impdp directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp include=GRANT

# Restore data
impdp schemas=SYSMAN directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp 

# Restart queues
/* select 'exec DBMS_AQADM.START_QUEUE(queue_name => ''SYSMAN.'||name||''');' from dba_queues where enqueue_enabled like '%NO%' and owner='SYSMAN' order by 1; */
SQL> exec dbms_aqadm.start_queue('SYSMAN.MGMT_ADMINMSG_BUS',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_CNTR_QUEUE',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_LOADERJOB_QUEUE',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_EVENT_BUS',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_NOTIFY_Q',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_GROUP_EVENT_Q',true,true);
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.EM_JOB_STATUS_UPD_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_HOST_PING_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_LOADER_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_NOTIFY_INPUT_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_TASK_Q');
SQL> commit;

# Perform partitionion maintenance (as SYSMAN):
SQL> exec gc_interval_partition_mgr.partition_maintenance;
SQL> exec mgmt_audit_admin.add_audit_partition;

Start OEM again:

emctl start oms

Unblock the agents (Setup – Manage Cloud Control – Agents – select blocked agents + “unblock”) and on each monitored host, clear the agent state, restart it and test:

emctl clearstate agent
emctl stop agent
emctl start agent
emctl control agent runCollection $(hostname -f):host Response
emctl status agent

After this, OEM was running again without problems.

Leave a comment

Your email address will not be published.