Forum Stats

  • 3,733,302 Users
  • 2,246,742 Discussions
  • 7,856,651 Comments

Discussions

How to move LOB segment caused by unified auditing

User_KYWTC
User_KYWTC Member Posts: 17 Green Ribbon
edited November 2020 in Database Security - General

HI Team,


We enabled unified auditing and we are purging records every 3 hours. Even though we purge audit records for every 3 hours, we could see the LOB segment that is present under AUDSYS schema is not releasing the space.


We came to know it is due to the bug 18109788 : CLEANUP OF UNIFIED AUDIT TRAIL DOES NOT RELEASE LOB SEGMENT SPACE . But is there any way to reclaim space occupied by the lob segment? How to move the audsys LOB segment to another tablespace ?


OWNER  SEGMENT_NAME                       SIZE_M

------ ----------------------------- --------------------- ----------

AUDSYS SYS_LOB0000091784C00014$$~             107046.125


Thanks,

Bala

Answers

  • Adrian Godoy Ferro
    Adrian Godoy Ferro Member Posts: 30 Red Ribbon

    Hi Bala,

    due my experience with Unified Auditing, the only way to move the LOB out of sysaux and release the space is to clean the audsys.aud$unified.

    I did in the following way in 12.2 and it worked ok.

    1. create and auxiliary schema with a copy of the AUDSYS.AUD$UNIFIED table (here you can redefine the table using advanced compression including for the LOBs as SECURE FILES instead BASIC FILES as were in original table).
    2. insert as select from AUDSYS.AUD$UNIFIED to <NEW-SCHEMA>.AUD_UNIFIED.
    3. purge the complete AUDSYS.AUD$UNIFIED by using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL

    by using this method, I kept the auditing information in this auxiliary schema and clean the original unified_audit_trail table.

    Regards

    Adrian

  • Gerhold
    Gerhold Member Posts: 2 Blue Ribbon

    Hi Bala,

    You should only move "all" AUDSYS owned segments to another tablespace (in my example TS_TEMP_AUDIT)..

    During that move, all the segments are defragmented.


    But before doing the next steps, I would check the real size of the LOB with


    select dbms_lob.getlength('SYS_LOB0000091784C00014$$~') from dual;


    If you can see that, compared to the size of dba_segments, it's worth to proceed, then do a


    BEGIN

        DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

        AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

        AUDIT_TRAIL_LOCATION_VALUE => 'TS_TEMP_AUDIT');

    END;

    /


    If you have turned on the recyclebin, empty it.


    purge dba_recyclebin;


    Flush the audit trail

    exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;


    and move the segments back to your original tablespace.

    BEGIN

        DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

        AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

        AUDIT_TRAIL_LOCATION_VALUE => '<original tablespace>');

    END;

    /


    Empty the recycle bin again (if enabled) and check, if there are still segments in the TS_TEMP_AUDIT tablespace.

    If not, you can drop it.


    Best regards

    Gerhold

Sign In or Register to comment.