Ное. 102011
 

This post is also available in: English

Това е една друга интересна мотика при upgrade от 10g на 11g. По време на автоматизираните тестове забелязахме, че audit trail таблицата се раздува с бясна скорост и една забележима част от IO операциите са именно писане в SYS.AUD$.

Открихме следната нота в Metalink:
Huge/Large/Excessive Number Of Audit Records Are Being Generated In The Database [ID 1171314.1]

1.) Starting with Oracle 11g the BY SESSION clause is obsolete. This is documented in the Database Security Guide :

“ The BY SESSION clause of the AUDIT statement now writes one audit record for every audited event. In previous releases, BY SESSION wrote one audit record for all SQL statements or operations of the same type that were executed on the same schema objects in the same user session. Now, both BY SESSION and BY ACCESS write one audit record for each audit operation. In addition, there are separate audit records for LOGON and LOGOFF events. “

Because of the above change it is expected that the number of the audit records/files will grow considerably.

Засилвайки се към документацията открих следното:
Oracle® Database SQL Language Reference 11g Release 1 (11.1)

BY SESSION

In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. If you specify neither clause, then BY SESSION is the default.

На практика същото пише и в Oracle® Database SQL Language Reference 11g Release 2 (11.2)

Още по-неясно е написаното в Oracle® Database Security Guide 11g Release 2 (11.2)

Benefits of Using the BY ACCESS Clause in the AUDIT Statement

By default, Oracle Database writes a new audit record for every audited event, using the BY ACCESS clause functionality. To use this functionality, either include BY ACCESS in the AUDIT statement, or if you want, you can omit it because it is the default. (As of Oracle Database 11g Release 2 (11.2.0.2), the BY ACCESS clause is the default setting.)

Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements. The benefits of using the BY ACCESS clause in the AUDIT statement are as follows:

Честно ви казвам, тия неща ми звучат като „Ъъъъ… бе нещо се повреди тоя BY SESSION, ама успяхме да го позакрепим, макар че вече работи почти като BY ACCESS“

Трябваше да предефинираме audit политиката си преди upgrade-a. За щастие повечето noaudit операции стават online. Единствено noaudit execute прави гадни заключвания и трябваше да го правим по време на downtime.

Ето и едно полезно query по въпроса:

select 'noaudit select on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where sel like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit execute on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where exe like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit update on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where upd like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit insert on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where ins like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit delete on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where del like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')

И още нещо много важно: не забравяйте да си прегледате какво пише в ALL_DEF_AUDIT_OPTS преди upgrade. Иначе catupgrd ще ви създаде хиляда хиляди обекта с кофти auditing.

 Posted by at 11:55

Sorry, the comment form is closed at this time.