OEM database – generating huge number of errors

Hello !

Got this issue when checking alert log:

BEGIN EM_LOADERJOB.unlock_target(hextoraw(:1 )); END;
Additional information: hd=0xfc429b90 phd=0x63888408 flg=0x100476 cisid=107 sid=107 ciuid=107 uid=107
2020-01-14T16:36:37.705267-06:00
WARNING: too many parse errors, count=148547900 SQL hash=0x7533e331
PARSE ERROR: ospid=25178, error=6550 for statement:
2020-01-14T16:36:37.705493-06:00

And this repeat each 5 seconds. Around 1 month my alert log reached 100M and got other error –

Alert log is 100.088027954102M, consider using rotate_generic.pl

Solution for this error:

First – check if your package EM_LOADERJOB have the procedure UNLOCK_TARGET

col owner for a25
col object_name for a25
col PROCEDURE_NAME for a35
col OBJECT_TYPE for a25
set lines 200
SELECT Owner,
Object_Name,
Procedure_Name,
Object_Type
FROM DBA_Procedures
WHERE Object_Name = ‘EM_LOADERJOB’
ORDER BY Procedure_Name
/

Sample Output:

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE
————————- ————————- ———————————– ————————-
SYSMAN EM_LOADERJOB DEQUEU_JOB PACKAGE
SYSMAN EM_LOADERJOB LOADERJOB_LOG PACKAGE
SYSMAN EM_LOADERJOB RELEASE_TARGET_LOCK PACKAGE
SYSMAN EM_LOADERJOB SUBMIT_JOB PACKAGE
SYSMAN EM_LOADERJOB UPDATE_DQ_TIME PACKAGE
SYSMAN EM_LOADERJOB UPDATE_END_TIME PACKAGE
SYSMAN EM_LOADERJOB UPDATE_JOB_STATUS PACKAGE
SYSMAN EM_LOADERJOB PACKAGE

If not – Copy the procedure contents from another OEM database.

Follow the steps below:

Please ensure a full database backup is take prior to proceeding
—————————————-

Login in to EM Console:

1a. Navigate to Targets -> Databases -> click on Repository database -> Schema -> Programs -> Package Bodies -> Select schema name as SYSMAN and object name as “EM_LOADERJOB” –> click Search

2b. Here you will see the EM_LOADERJOB package, click Edit.

Find the uploaded EM_LOADERJOB_packge_body attachment, copy it and past it in the “Source” Box and click apply.

3c. On the right side of the page select object_type as “packages” — drop down button –> Select schema name as SYSMAN and object name as “EM_LOADERJOB” –> click Search

3) We can see the EM_LOADERJOB package, click Edit.

Find the uploaded EM_LOADERJOB_packge_definition attachment, copy it and past it in the “Source” Box and click apply.

3. Connect to Repository as SYSMAN and run the below query again.

col owner for a25
col object_name for a25
col PROCEDURE_NAME for a35
col OBJECT_TYPE for a25
set lines 200
SELECT Owner,
Object_Name,
Procedure_Name,
Object_Type
FROM DBA_Procedures
WHERE Object_Name = ‘EM_LOADERJOB’
ORDER BY Procedure_Name
/

Now we can see the unlock_target procedure in the output. If so, Monitor the alert.log and check if the issue remains.