Detect Archivelog Generation Spikes and Unusual Application Behavior using OEM 13c Metric Extensions

In today's tutorial we are going to create an OEM metric extension to detect spikes in archivelog generation in a database that may be caused by unusual application or user activities.

If there is a sudden rise in archivelog generation due to a bulk data delete or load or similar application activity, a regualer periodical backup may not be enough and the archivelog destination or the FRA may used up to 100% even before the backup job runs and clears the location. To mitigate this kind of risks, DBAs keep a file system threshold based backup job that is triggered from a cron or an event manager program that backs up the archivelogs and clears them upon the file system utilization crosses a certain percentage like 40% or 60%. But in both this cases dbas will never come to know that there was an incident like this and a spike in archivelog generation has taken place.

OEM has given us a few options which are inbuilt and can be found under metric collection and settings as "Recovery area usage %" and "Archive area usage %"

But these metric will trigger an alert even if the root cause is not due the archivelog generation
Our goal here is to make the dba aware of what is happening in his database environment and to notify immediately upon detecting an anomaly. For example, if the application team started a massive data load, or a data purge without notifying the dba team or an user by mistake issued a delete statement without putting a proper where clause or dropped a table causing a massive amount of archivelog to be generated and there may be many more such examples.

All the SQL statements used in tutorial can be downloaded from my google drive using the link this video below.

https://docs.google.com/document/d/1e...

Previous Tutorials on ME:

ME to detect Backup Failure:
   • Step-By-Step Create OEM Metric Extension -...  

ME to detect Long Running SQLs:
   • Step-By-Step Create OEM Metric Extension -...