Re: set events: control the number of sessions that dump trace
Date: Wed, 21 Aug 2019 10:07:09 +0100
Message-ID: <CALe4HpmazNVmJG9+zm5Zj5eY8hmHbj-=b9msPuvxCOZc3cNWbg_at_mail.gmail.com>
Indeed, the RAISE statement does not generate any incidents. You need to
get a genuine ORA-1555.
Here is what I have done in my 12.2.0.1.190416 database (single instance,
single PDB) using a shared UNDO tablespace:
*1. PDB:*
alter system set events '1555 incident(sto)';
*2. CDB$ROOT (the undo tablespace could be even smaller):*
create undo tablespace small_undo datafile size 100M autoextend on maxsize
500M;
ALTER SYSTEM SET undo_tablespace='SMALL_UNDO' scope=both;
*3. PDB: I was trying to raise ORA-1555 using your code, however, those
attempts did not lead to the error written to the alert log.* When I was
testing that event yesterday, I did not use the RAISE statement with the
EXCEPTION_INIT pragma. I setup certain conditions to get ORA-1555 in my
query. That is quite a significant difference from your tests. The steps
below show how I setup ORA-1555.
*4. PDB:*
create table test_ora1555
as
select lpad('x', 4000, 'x') c
from xmltable('1 to 4000');
*5. PDB: get the current SCN to be used later:*
select current_scn from v$database;
- returned: 47136264
*6. PDB: the code to generate changes quickly overwriting that small undo
tablespace*
begin
for i in 1..4000
loop
update test_ora1555
set c = c;
commit;
end loop;
end;
/
*7. PDB: while 6 is running, I tried to execute the following commands:*
SELECT COUNT(*) FROM TEST_ORA1555 AS OF SCN 47136264;
declare
n number;
begin
SELECT COUNT(*)
INTO n
FROM TEST_ORA1555 AS OF SCN 47136264;
end;
/
After a while, those queries started to return ORA-1555 writing the following to the alert log:
PDB(3):SELECT COUNT(*) FROM TEST_ORA1555 AS OF SCN 47136264
Errors in file
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_10409.trc
(incident=432350) (PDBNAME=PDB):
ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT],
[1555], [STO], [], [], [], [], [], [], [], [], []
ORA-01555: snapshot too old: rollback segment number 15 with name "???" too
small
PDB(3):Incident details in:
/u01/app/oracle/diag/rdbms/orcl/ORCL/incident/incdir_432350/ORCL_ora_10409_i432350.trc
I have got 5 incidents in that hour in total and all subsequent errors were flood-controlled with the following lines written to the trace file:
DDE: Problem Key 'ORA 700 [EVENT_CREATED_INCIDENT] [1555] [STO]' *was flood
controlled* (0x2) (incident: 436026)
ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT],
[1555], [STO], [], [], [], [], [], [], [], [], []
ORA-01555: snapshot too old: rollback segment number 20 with name "???" too
small
DDE: Problem Key 'ORA 700 [EVENT_CREATED_INCIDENT] [1555] [STO]' *was flood
controlled* (0x2) (incident: 436027)
ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT],
[1555], [STO], [], [], [], [], [], [], [], [], []
ORA-01555: snapshot too old: rollback segment number 20 with name
"_SYSSMU20_4031334394$" too small
ADRCI shows that only 5 incidents were generated which is in line with that MOS article that I mentioned earlier: DIA-49435 When Trying To Create An Incident Packet (Doc ID 1358087.1):
adrci> show incident -last 10
ADR Home = /u01/app/oracle/diag/rdbms/orcl/ORCL:
INCIDENT_ID PROBLEM_KEY
CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
*432350 ORA 700 [EVENT_CREATED_INCIDENT] [1555] [STO]
2019-08-21 04:20:34.103000 -04:00432349 ORA 700
[EVENT_CREATED_INCIDENT] [1555] [STO] 2019-08-21
04:20:09.368000 -04:00432348 ORA 700 [EVENT_CREATED_INCIDENT]
[1555] [STO] 2019-08-21 04:20:07.954000 -04:00432347
ORA 700 [EVENT_CREATED_INCIDENT] [1555] [STO]
2019-08-21 04:19:38.463000 -04:00432346 ORA 700
[EVENT_CREATED_INCIDENT] [1555] [STO] 2019-08-21
04:19:05.857000 -04:00*
432341 ORA 4036
2019-08-16 08:21:45.073000 -04:00
432340 ORA 4036
2019-08-16 08:21:44.285000 -04:00
432339 ORA 4036
2019-08-16 08:21:32.372000 -04:00
432338 ORA 4036
2019-08-16 08:21:08.789000 -04:00
428529 ORA 4036
2019-08-12 05:42:19.376000 -04:00
Hope this helps.
On Tue, 20 Aug 2019 at 22:42, Jared Still <jkstill_at_gmail.com> wrote:
> > Used this command: > > alter system set events '1555 incident(forceora1555)'; > > Nothing generated by an ORA-1555, not even a mention in the alert log. > > I found the following note to be an excellent reference: > > Monitoring 11g Database Alert Log Errors in Enterprise Manager 10g and 11g > Grid Control (Doc ID 949858.1) > > > > > 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 > > > > > On Tue, Aug 20, 2019 at 2:05 PM Jared Still <jkstill_at_gmail.com> wrote: > >> On Tue, Aug 20, 2019 at 1:56 PM Mikhail Velikikh <mvelikikh_at_gmail.com> >> 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)'; >>> >>> >> I did not realize that was literal syntax you were referring to. I will >> give it another try. >> >> >
<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>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 21 2019 - 11:07:09 CEST