Fix for Oracle Errors ORA-51106, ORA-51108, ORA-48101, ORA-48102, ORA-12012, ORA-48223

To fix the Oracle errors as ORA-51106, ORA-51108, ORA-48101, ORA-48102, ORA-12012 , ORA-48223, need to drop and create HMA schema using the procedure.

Below is how the Oracle Error looks like.

Message=Alert Log Message: Errors in file /u01/app/oracle/diag/rdbms/prim/prim/trace/trace/prim_j001_2001.trc: ORA-12012: error on auto execute of job "SYS"."DRA_REEVALUATE_OPEN_FAILURES" ORA-51108: unable to access diagnostic repository - retry command ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51014] [HM_FINDING] ORA-48223: Interrupt Requested - Fetch Aborted - Return Code [51014] [HM_FINDING] ORA-06512: at "SYS.DBMS_IR", line 522 raised 1 times 
Metric=User-Defined Numeric Metric 

Solution:

prim-->(prim) /u01/app/oracle/diag/rdbms/prim/prim/metadata> ls -alhtr HM_*.ams

-rw-r----- 1 oracle dba  64K Aug 27  2013 HM_FDG_SET.ams

-rw-r----- 1 oracle dba  64K Sep 12 22:50 HM_RECOMMENDATION.ams

-rw-r----- 1 oracle dba  73M Sep 13 02:13 HM_MESSAGE.ams

-rw-r----- 1 oracle dba 139M Sep 13 02:13 HM_INFO.ams

-rw-r----- 1 oracle dba 124M Sep 13 02:13 HM_FINDING.ams

-rw-r----- 1 oracle dba 2.1M Sep 13 02:13 HM_RUN.ams

prim-->(prim) /u01/app/oracle/diag/rdbms/prim/prim/metadata> sqlplus –S / as sysdba

SQL> exec dbms_hm.drop_schema;

PL/SQL procedure successfully completed.

SQL> !ls -alhtr HM_*.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:13 HM_RUN.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:13 HM_MESSAGE.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:13 HM_INFO.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:13 HM_FINDING.ams

SQL> exec dbms_hm.create_schema;

PL/SQL procedure successfully completed.

SQL> !ls -alhtr HM_*.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:14 HM_RUN.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:14 HM_MESSAGE.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:14 HM_INFO.ams

-rw-r----- 1 oracle dba 464K Sep 13 02:14 HM_FINDING.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:16 HM_RECOMMENDATION.ams

-rw-r----- 1 oracle dba  64K Sep 13 02:16 HM_FDG_SET.ams