Hi Mladen,
That MOS article that Jared mentioned has some examples of setting event
created incidents: Monitoring 11g Database Alert Log Errors in Enterprise
Manager 10g and 11g Grid Control (Doc ID 949858.1)
Other than that, there are also some ORADEBUG commands showing how to
create incidents, namely:
SQL> oradebug doc event
...
* Create an incident labeled 'table_missing' when external error
942 is signaled by process id 14534:
alter session set events '942 {process: 14534}
incident(table_missing)';
...
SQL> oradebug doc event action incident
incident
- Create an Incident
Usage
-------
incident( label <string>[32])
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
On Wed, 21 Aug 2019 at 00:59, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
Hi Mikhail!<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Where can see the syntax for alter system set events='<errno> incident'.
Is there a document which describes the syntax?
I was unable to find it.
TIA
On 8/20/19 4:56 PM, Mikhail Velikikh wrote:
It looks like you have not fired any incidents. The key thing in my
message was that incident keyword in the event syntax:
alter system set events '1555 *incident*(sto)';
By doing that, any ORA-1555 will lead to a user-defined incident
generation. That message about the flood-controlled incident, I actually
copied from the instance where I tested ORA-1555 with the exact event
syntax that I used in my message.
I will try to do more tests tomorrow.
On Tue, Aug 20, 2019, 21:38 Jared Still <jkstill@xxxxxxxxx> wrote:
I did some tests, the results were not quite what I expected, but in anyMladen Gogala
case, flood control was not invoked.
Note: hopefully this formatting is preserved by the list server.
Testing ORA-1555 for Flood Control
When setting system events to trap an error and dump a stack trace, there is
the potential for many files to be generated.
Oracle sometimes employs an internal 'flood control' when many errors of the
same type occur to prevent using too many resources to store the same msg
over and over.
This experiment is to see if flood control might be employed when trace
files are forced via 'alter system set events'.
sqlrun was used to start many sessions running the same SQL over and over
https://github.com/jkstill/sqlrun
Enable ORA-1555 events
*
sqlplus -L -s jkstill/XXX@p1 << EOF
set feedback off term off heading off
alter system set events '1555 trace name errorstack level 3, lifetime 2';
exit;
EOF
*
Note: when run without the lifetime argument, default lifetime appears to
be 1. That is, just one event dumped per session. The SQL being executed
*
declare
ora1555 exception;
pragma exception_init(ora1555,-1555);
begin
-- the error will still cause a trace file to be created via the 'set
events' command
-- without causing the script to fail
begin
raise ora1555;
exception
when others then null;
end;
end;
*
When run with 35 sessions, each executing the SQL every .1 seconds for 60
seconds, there was not any flood control employed, both without the
lifetime argument, and with lifetime 2. With a lifetime of 20, there was
still only a single stack trace created per session. Earlier experiments
via sqlplus did generate multiple call stacks via the 'set events', so I
don't know why this is acting differently. In any case, flood control was
not invoked.
On Tue, Aug 20, 2019 at 12:18 PM Jared Still <jkstill@xxxxxxxxx> wrote:
Thank you Mikhail.
I certainly have seen a number of trace files with 'flood controlled' in
them due to a large volume of the same error.
Simple enough to test this, going to go try it now.
On Tue, Aug 20, 2019 at 11:55 AM Mikhail Velikikh <mvelikikh@xxxxxxxxx>
wrote:
Hi Jared,
It is possible to setup an event generated incident like this:
alter system set events '1555 incident(sto)';
Then, a standard flood-control kicks in after a certain number of
incidents (please see the following MOS article for the thresholds:
DIA-49435 When Trying To Create An Incident Packet (Doc ID 1358087.1)).
For instance, in my 19c database I have got five incidents and the
sixth was flood controlled.
Here is a relevant excerpt from the trace file:
DDE: Problem Key 'ORA 700 [EVENT_CREATED_INCIDENT] [1555] [STO]' was
flood controlled (0x2) (incident: 111062)
That is not exactly what is required but it is the closest I can come
up with.
Best regards,
Mikhail Velikikh
On Tue, 20 Aug 2019 at 17:49, Jared Still <jkstill@xxxxxxxxx> wrote:
Hello,
Does anyone know if it is possible to control the total number of
trace files created by 'alter system set events' ?
For example, the following command will cause all sessions to dump
stack traces when there have been at least 2 occurrences (armcount) of
ORA-1555 in a session:
alter system set events '1555 trace name errorstack level 3,
armcount 2, lifetime 2';
After 2 events have occurred (lifetime) then the session will stop
dumping stacks for this error.
What I would like to be able to do is limit this system wide.
Say for instance 3 sessions dump stack traces for ORA-1555.
I would like to limit this to N system wide dumps of stack trace.
So after 3 sessions have created a trace file, no other sessions
should do so.
The reason is that if left unattended, when many session incur
ORA-1555, then this could potentially be quite a few rather large files.
The idea of course is to be able to set and forget, until the next
event.
And yes, I know that TFA could be setup to deal with this, but that is
another story...
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Github: https://github.com/jkstill
--
Database Consultant
Tel: (347) 321-1217
