3 Replies Latest reply on Jul 9, 2020 5:10 AM by MortenBraten

    18c XE excessive logging to Windows Event Log

    MortenBraten

      Oracle 18c XE

      Windows Server 2016

      audit_trail = db (default value after installation)

       

      According to the docs (see, for example, https://docs.oracle.com/database/121/NTQRF/monitor.htm#NTQRF080 ) , on Windows the "Event number 34 specifies an audit trail event. These events are recorded if the parameter AUDIT_TRAIL is set to db (true) or os in the initialization parameter file. Option os enables systemwide auditing and causes audited records to be written to Event Viewer. Option db enables systemwide auditing and causes audited records to be written to the database audit trail (table SYS.AUD$). Some records, however, are written to Event Viewer."

       

      In short, it says "some records" are written to the Windows event log, but in my case I am seeing thousands of records for Event 34 in the Event Viewer (around 6,000 records per day).

       

      Here are a few random examples from the last minutes:

       

      Audit trail: LENGTH: '662' ACTION :[415] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */ substrb(dump("FLAGS",16,0,64),1,240) d_2016218103, rowidtochar(rowid) rwid from "SYS"."SEQ$" t where rowid in (chartorowid('AAAABkAABAAAARhAAA'),chartorowid('AAAABkAABAAAARiABb')) order by "FLAGS"' DATABASE USER:[3] 'SYS' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[8] 'XXX' STATUS:[1] '0' DBID:[10] '3836604047' SESSIONID:[8] '22608505' USERHOST:[8] 'XXX' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' .

       

      Audit trail: LENGTH: '1458' ACTION :[1210] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */ substrb(dump(substrb("SPARE4",1,64),16,0,64),1,240) d_3715991230, rowidtochar(rowid) rwid from "SYS"."OPTSTAT_HIST_CONTROL$" t where rowid in (chartorowid('AAAAKxAABAAAEixAAE'),chartorowid('AAAAKxAABAAAEixAAG'),chartorowid('AAAAKxAABAAAEixAAH'),chartorowid('AAAAKxAABAAAEixAAI'),chartorowid('AAAAKxAABAAAEixAAJ'),chartorowid('AAAAKxAABAAAEixAAK'),chartorowid('AAAAKxAABAAAEixAAL'),chartorowid('AAAAKxAABAAAEixAAM'),chartorowid('AAAAKxAABAAAEixAAN'),chartorowid('AAAAKxAABAAAEixAAO'),chartorowid('AAAAKxAABAAAEixAAP'),chartorowid('AAAAKxAABAAAEixAAR'),chartorowid('AAAAKxAABAAAEixAAS'),chartorowid('AAAAKxAABAAAEixAAW'),chartorowid('AAAAKxAABAAAEixAAY'),chartorowid('AAAAKxAABAAAEixAAZ'),chartorowid('AAAAKxAABAAAEixAAa'),chartorowid('AAAAKxAABAAAEixAAd'),chartorowid('AAAAKxAABAAAEixAAe'),chartorowid('AAAAKxAABAAAEixAAf'),chartorowid('AAAAKxAABAAAEixAAg'),chartorowid('AAAAKxAABAAAEixAAh'),chartorowid('AAAAKxAABAAAEixAAj'),chartorowid('AAAAKxAABAAAEixAAm')) order by substrb("SPARE4",1,64)' DATABASE USER:[3] 'SYS' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[8] 'XXX' STATUS:[1] '0' DBID:[10] '3836604047' SESSIONID:[8] '22608505' USERHOST:[8] 'XXX' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' .

       

      Audit trail: LENGTH: '658' ACTION :[411] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */ substrb(dump("TS#",16,0,64),1,240) d_2918363161, rowidtochar(rowid) rwid from "SYS"."CLU$" t where rowid in (chartorowid('AAAAACAABAAAACRAAA'),chartorowid('AAAAACAABAAAAVIAAA')) order by "TS#"' DATABASE USER:[3] 'SYS' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[8] 'XXX' STATUS:[1] '0' DBID:[10] '3836604047' SESSIONID:[8] '22608505' USERHOST:[8] 'XXX' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' .

       

      Audit trail: LENGTH: '679' ACTION :[432] 'select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */ substrb(dump("SNAPSHOT_ID",16,0,64),1,240) d_3680256565, rowidtochar(rowid) rwid from "SYS"."EXP_STAT$" t where rowid in (chartorowid('AAAALPAAEAAALELAAA'),chartorowid('AAAALPAAEAAAPDpACP')) order by "SNAPSHOT_ID"' DATABASE USER:[3] 'SYS' PRIVILEGE :[4] 'NONE' CLIENT USER:[0] '' CLIENT TERMINAL:[8] 'XXX' STATUS:[1] '0' DBID:[10] '3836604047' SESSIONID:[8] '22608505' USERHOST:[8] 'XXX' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3' .

       

       

      The question is, how can I reduce the level of logging? Excessive logging could have a performance impact, as well as filling up the log and potentially flushing out more important entries, and the sheer number of informational entries make it difficult to monitor the Windows Event logs for more important (Oracle-related) events.