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.