This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Mar 18, 2013 9:52 AM by TSharma-Oracle RSS

How to delete these old flooded audit files?

BingLiu Newbie
Currently Being Moderated
we have ORACLE database 11.1, Solaris 10.10, database audit is turned on, there are flooded amount of *.aud files under adump directory, we need keep them for 30 days, then delete them.

but the problem is use this command "find . -name "*.aud" -mtime 30 -print -exec rm -f {} \; " does not work, because too many files (100 per minute).


adump> ls -l |wc -l
2877939

rm *.aud
-bash: /usr/bin/rm: Arg list too long




any idea or command I can use to purge these old files?


Thanks,
  • 1. Re: How to delete these old flooded audit files?
    TSharma-Oracle Guru
    Currently Being Moderated
    Find *"Controlling the Size and Age of the OS Audit Trail"* in the following link. By increasing the size of the Audit file will help you generating less number of files and you can then delete those files with your unix command. This is one way of doing it.

    http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php
  • 2. Re: How to delete these old flooded audit files?
    BingLiu Newbie
    Currently Being Moderated
    Thanks, but this document is for 11R2, we are using 11.1
  • 3. Re: How to delete these old flooded audit files?
    TSharma-Oracle Guru
    Currently Being Moderated
    You should be able to use that in 11.1 also. Check that. Also, there is automatic purge details in the document.
    Also. you should find a reason why these many files are being generated and whether you need that level of auditing.
  • 4. Re: How to delete these old flooded audit files?
    BingLiu Newbie
    Currently Being Moderated
    In that ref file, audit_trail is set to file. But in my case, audit_trai is set to DB already(AUD$ table), flooded file is from audit_sys_operations.


    parameter is like below:


    audit_file_dest     String     /adump
    audit_sys_operations     Boolean     TRUE
    audit_syslog_level     String     
    audit_trail     String     DB
  • 5. Re: How to delete these old flooded audit files?
    TSharma-Oracle Guru
    Currently Being Moderated
    Did you check why are you generating so many files? Why are you logging as sys or doing lot of activities as SYS? Check where the connections are coming from and try to fix that. That would be the real solution.
    Still, if you check "Controlling the Size and Age of the OS Audit Trail" in that link, you can increase the size of the file by setting "Audit file max size" amd Audit file max age". It will allow you to create bigger files resulted in less number of files so you can use your rm{} function to delete that.

    *But again You should check the cause of the Problem first.*
  • 6. Re: How to delete these old flooded audit files?
    BingLiu Newbie
    Currently Being Moderated
    I checked, but could not find any user use sys to do the work. almost all sys activity loged is from ORACLE database itself, I guess ASH/AWR, like below:



    Tue Mar 12 21:10:28 2013
    LENGTH : '2631'
    ACTION :[2504] 'select db.version_time, db.controlfile_change#, db.controlfile_sequence#, dbinc.resetlogs_time, df.last_recid, tf.last_recid, ts.last_reci
    d, rl.last_recid, rm.last_recid, dc.last_recid, al.last_recid, bp.last_recid, do.last_recid, offr.last_recid, pc.last_recid, bs.last_recid, ldbinc.resetlo
    gs_change#, ldbinc.resetlogs_time, ic.last_recid, rt.last_recid, grsp.last_recid, nrsp.last_recid, bcr.last_recid, decode(dc.records_used, 0, 0, dc.last_r
    ecid - dc.records_used + 1), decode(al.records_used, 0, 0, al.last_recid - al.records_used + 1), decode(bp.records_used, 0, 0, bp.last_recid - bp.records_
    used + 1), decode(bs.records_used, 0, 0, bs.last_recid - bs.records_used + 1), decode(do.records_used, 0, 0, do.last_recid - do.records_used + 1), decode(
    offr.records_used, 0, 0, offr.last_recid - offr.records_used + 1), decode(pc.records_used, 0, 0, pc.last_recid - pc.records_used + 1)into :vertime, :cfs
    cn, :cfseq, :rltime, :dfhwm, :tfhwm, :tshwm, :rlhwm, :rmhwm, :dchwm, :alhwm, :bphwm, :dohwm, :offrhwm, :pchwm, :bshwm, :lrstscn, :lrsttime, :ichwm, :rthwm
    , :grsphwm, :nrsphwm, :bcrhwm, :dclwm, :allwm, :bplwm, :bslwm, :dolwm, :offrlwm, :pclwm from v$database db, v$controlfile_record_section df, v$controlfi
    le_record_section tf, v$controlfile_record_section ts, v$controlfile_record_section rt, v$controlfile_record_section rl, v$controlfile_record_section rm,
    v$controlfile_record_section dc, v$controlfile_record_section al, v$controlfile_record_section bp, v$controlfile_record_section do, v$controlfile_record_s
    ection offr, v$controlfile_record_section pc, v$controlfile_record_section bs, v$controlfile_record_section ic, v$controlfile_record_section grsp, v$contr
    olfile_record_section nrsp, v$controlfile_record_section bcr, v$database_incarnation dbinc, v$database_incarnation ldbinc where df.type = 'DATAFILE' and
    tf.type = 'TEMPORARY FILENAME' and ts.type = 'TABLESPACE' and rt.type = 'REDO THREAD' and rl.type = 'REDO LOG' and rm.type = 'RMAN CO
    NFIGURATION' and dc.type = 'DATAFILE COPY' and al.type = 'ARCHIVED LOG' and bp.type = 'BACKUP PIECE' and do.type = 'DELETED OBJECT' and
    offr.type = 'OFFLINE RANGE' and pc.type = 'PROXY COPY' and bs.type = 'BACKUP SET' and ic.type = 'DATABASE INCARNATION' and grsp.type = 'GUAR
    ANTEED RESTORE POINT' and nrsp.type = 'RESTORE POINT' and bcr.type = 'DATABASE BLOCK CORRUPTION' and dbinc.incarnation#=db.recovery_target_incarnat
    ion# and ldbinc.incarnation#=db.last_open_incarnation#'
    DATABASE USER:[1] '/'
    PRIVILEGE :[6] 'SYSDBA'
    CLIENT USER:[5] 'oracle'
    CLIENT TERMINAL:[0] ''
    STATUS:[1] '0'
  • 7. Re: How to delete these old flooded audit files?
    jgarry Guru
    Currently Being Moderated
    http://www.linuxjournal.com/article/6060

    There's also xargs. Something like
    find .|xargs rm
  • 8. Re: How to delete these old flooded audit files?
    BingLiu Newbie
    Currently Being Moderated
    thanks, but we need keep 30 days audit file, how to delete only those older than 30 days?
  • 9. Re: How to delete these old flooded audit files?
    TSharma-Oracle Guru
    Currently Being Moderated
    If this is coming from OEM or db control, you do not need to use SYS user for this. You can DBSNMP which will help you reducing these files.
  • 10. Re: How to delete these old flooded audit files?
    Justin_Mungal Journeyer
    Currently Being Moderated
    DBMS_AUDIT_MGMT is quite useful once you get it working. It's been back-ported to patchsets 10.2.0.5 and 11.1.0.7. Make sure to review +Known Issues When Using: DBMS_AUDIT_MGMT [ID 804624.1]+ if you're planning on using it.
  • 11. Re: How to delete these old flooded audit files?
    EdStevens Guru
    Currently Being Moderated
    BingLiu wrote:
    thanks, but we need keep 30 days audit file, how to delete only those older than 30 days?
    By modifying the "find" command to include the time flag ... exactly like you did in your first posting.

    Joel was simply showing that you could use xargs instead of -exec. It wasn't meant to be a final solution.

    Edited by: EdStevens on Mar 16, 2013 7:21 AM
  • 12. Re: How to delete these old flooded audit files?
    marksmithusa Journeyer
    Currently Being Moderated
    jgarry wrote:
    http://www.linuxjournal.com/article/6060

    There's also xargs. Something like
    find .|xargs rm
    +1

    When we first realise/remember that we're creating ridiculous amounts of audit files and we haven't implemented purging, we use xargs too. We often find the other method fails because of the limit on the number of the files we can process.
  • 13. Re: How to delete these old flooded audit files?
    BingLiu Newbie
    Currently Being Moderated
    Thanks, I will test to see if this command work on Solaris 10.
  • 14. Re: How to delete these old flooded audit files?
    BingLiu Newbie
    Currently Being Moderated
    I am not 100% sure where those statements(run as sys user) came from, so I guess it is sys internal job like ASH/AWR. But I am sure that OEM agent is using DBSNMP. As long as any user has sysdba role or sysoper role, its activity(statements) will be recorded, no matter you login as sys or not.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points