Recently i had a database getting ora-04031- (Shared Pool) errors but were NOT receiving any alerts from OEM – 220.127.116.11 (Grid Control). After investigation and looking for low hanging fruit (i.e. Missing Incident rule, Agent not running, Check Community and Oracle-L, etc),i couldn’t see anything that could be causing the Issue. So I resorted to the last resort – Open Oracle Support Call.
Kudo’s to Brandon at Oracle Support who walked me through alert log monitoring.
Lesson #1 – When you select (Under Database), Monitoring/Metric and Collection Settings/Generic Alert Log Error – Select Edit and then you will see the following screen:
Under Alert Log Filter expression – the default is “.*ORA-0*(54|1142|1146)\D.*”. This column is for Errors you want to Ignore and NOT to find – in another words – Oracle Enterprise Manager will ignore any error with those matches / errors entirely.
So only put in this column errors you want to ignore.
#2 – If . you want to search for a particular error – use perl regex type commands and start and end the search with a “.*” (Dot Star). Example if you want to search for ora-04031 – Put in the critical / Warning – “.*ORA-04031.*”.
#3 – Avoid using “Alert Log” checks and use “DB Alert Log Error Status” Instead, which is turned off by default. Brandon from Oracle support said that they improved this check and the “Alert Log” will be deprecated in future releases. BTW, it has all the same metrics as “Alert Log” but code has been upgraded in the “DB Alert Log Error Status” and NOT the “Alert Log” check. Basically does the same thing but with updated code.
#4 To Test and Generate an error with alert log – run the following as “SYS” with the error you want to check for(Im checking for ORA-04031).
SYS-SQL> exec dbms_system.ksdwrt(3,’ORA-04031: This is a test error message for monitoring and can be ignored.’); This will put a record in the alert file that you can test.
#5 – To run the check without waiting for the next collection – run the following from the OEM agent home on the database server, this following command will force a collection:
OS>emctl control agent runCollection <DBNAME>:oracle_database db_alertlog_coll_12 – For db alert log
OS> emctl control agent runCollection <DBNAME>:oracle_database alert_log_rollup_12C – for alert log checks
*** Note make sure you have the right DBNAME – to verify the name – run:
OS> emctl status agent scheduler which will show all the metrics collected for this database and host, then find “db_alertlog_coll”
One caveat – is that if you haven’t checked for a ORA- error in the past, the first time it will generate a OEM ticket for each one found. After that first time, it will only generate for the past duration.
I hope this helps and good luck,