I was thinking now since a long time, how would it be possible to scan Oracle Alert Log to see if there are problems (ctirical or non-critical) in my database.
After several brainstorming session with myself (;-)) I came up with an SQL solution. I named it as “Alert Reader”.
It was tricky at the beginning, because I don’t wanted to create any db object. So first I needed to find a way to read Oracle Alert Log on database level. First I though to scan directly the Alert Log. After checking the Oracle dictionary I found 2 potential Oracle internal views. Well, reading an Oracle view is still easier then reading a textfile (at least for me).
As soon as I found these views, I started to evaluate them. Looking the structure and checking the meanings and possible values of the columns.
Some days past, and the basics were ready. Well, the easiest part was that, I struggled all the time with syntax errors. I have to say, hurting as hell to try to debug SQL code. Even with Oracle SQl Developer I recognized that the error message does not refer to the correct line in the code. So for every single problem I had to read the program from the beginning to the end… But it worth every minute!
Well, I know. In SAP, we have RZ20,DBACOCKPIT and Solution Manager Monitoring. So it may be only the 4th option to check the Alert Log.
But still, I think and hope there are some places where this script could be used.
The tool/script can be located in KBA https://i7p.wdf.sap.corp/sap/support/notes/2177050.
Also I am happy to hear your opinion and don’t be shy if you have some tips to improve it.