7 Replies Latest reply on Sep 20, 2019 11:29 AM by AndrewSayer

    Managing AUD$ without DBMS_AUDIT_MGMT

    3346177
    3346177 Sep 9, 2019 11:14 AM

      Hello community,

       

      I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on DB version 12c (12.2.0.1.0).

      With time (in 3 years) my AUD$ table grew to 500GB in size, especially because of it's 2 CLOB columns (SQLBIND and SQLTEXT).

      I want to shrink  storage consumed by AUD$ to minimum, without losing any audit data.

       

      I came up with the following plan:

       

      1. CREATE TABLESPACE TS_AUDIT_HISTORY

      2. CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY

      3. CREATE  partitioned TABLE AUDIT_USER.AUDIT_HISTORY_TABLE in tablespace TS_AUDIT_HISTORY with:

      - 3.1. COMPRESS HIGH on LOB's  (SQLBIND and SQLTEXT)

      - 3.2. COMPRESS HIGH on all partitions

      4. INSERT INTO AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; COMMIT;

      5. TRUNCATE TABLE SYS.AUD$

       

       

       

      I haven't implemented my plan on any Production environment yet, since almost everyone advices against any other solution but DBMS_AUDIT_MGMT - which I don't want to use because it doesn't compress table's data / CLOB's).

      But I have implemented my plan on a Development environment and results were amazing - After compressing I got from  500GB -> 50GB which is like 90% compress ratio !

       

       

      My questions:

       

      A) How 'smart' is to truncate SYSTEM's table AUD$, can it have any other impact besides the fact that user's won't be able to log in?
      B) When I will be TRUNCATEing AUD$, table will be locked, which will prevent users to log in! - any way to 'bypass' that, since users being able to log in to DB is very important?C) What do you think of my plan?

       

      Any input / concern / comment is welcome.

       

      Thanks in advance,

      Danijel

        • 1. Re: Managing AUD$ without DBMS_AUDIT_MGMT
          Mark D Powell
          Mark D Powell Sep 9, 2019 1:27 PM (in response to 3346177)

          Danijel, the only real issue I see with your plan is that the INSERT/SELECT statement is point in time and between the start of that statement and its completion new audit rows may be written which the TRUNCATE operation would then lose.  I think it would be better to add a WHERE clause condition that sets a time period for the data to be archived (copied to partitioned table) then after INSERT/SELECT you would need to DELETE from SYS.AUD$ using the same criteria.  After which if you want to reclaim space you can run a ALTER TABLE SHRINK operation or ALTER TABLE MOVE operation to release unnecessary extent allocations.

          - -

          I suspect you are going to want to repeat this operation on a regular basis in which case you need to know the time period of the data.

          - -

          IMHO -- Mark D Powell --

          • 2. Re: Managing AUD$ without DBMS_AUDIT_MGMT
            EdStevens
            EdStevens Sep 9, 2019 2:07 PM (in response to 3346177)

            3346177 wrote:

             

            Hello community,

             

            I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on DB version 12c (12.2.0.1.0).

            With time (in 3 years) my AUD$ table grew to 500GB in size, especially because of it's 2 CLOB columns (SQLBIND and SQLTEXT).

            I want to shrink storage consumed by AUD$ to minimum, without losing any audit data.

             

            I came up with the following plan:

             

            1. CREATE TABLESPACE TS_AUDIT_HISTORY

            2. CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY

            Huh?  Users are not "in" tablespaces.  There's no such concept as CREATE USER ... IN TABLESPACE.

             

            <SNIP>

             

            A) How 'smart' is to truncate SYSTEM's table AUD$, can it have any other impact besides the fact that user's won't be able to log in?
            B) When I will be TRUNCATEing AUD$, table will be locked, which will prevent users to log in! - any way to 'bypass' that, since users being able to log in to DB is very important?C) What do you think of my plan?

            What makes you think that a TRUNCATE prevents users from logging in? Even if the table is locked during the duration of the truncate, a truncate is about as fast an operation as can be done.  All it does is reset the high water mark.  It does NOT go through the table and clear the blocks of data.

            • 4. Re: Managing AUD$ without DBMS_AUDIT_MGMT
              3346177
              3346177 Sep 12, 2019 8:45 AM (in response to 3346177)

              I successfully created a new tablespace, new user (with default new tablespace), new table with partitions and compression on it as well as on the LOB's and inserted all the data from SYS.AUD$ to the new table -I had to use parallel hint here and it still took me 6 hours. Afterwards I truncated AUD$. Original AUD$ was 500GB in size and now the new one (with all the records of course) is only 40GB in size. You were right guys, Truncate SYS.AUD$ finished in like 1 or 2 seconds.

               

              So now I saved like 460GB.... but not really. Coz now these 460GB can be used only in AUD$'s tablespace (SYSAUX) and not in other tablespaces :/   I really hoped that TRUNCATE TABLE AUD$ DROP STORAGE; actually releases the allocated storage, but it does not. I can see that SYSAUX is 460GB less in size now, but these 460GB didn't free in ASM storage.

               

               

              The only 100% way to reallocate the storage to be available as Usable storage in ASM as far as i know is:

              1. Drop a table (which is of course not an option for AUD$)

              2. export / import table (which is not an option since Oracle does not allow that for AUD$ table, already tried to export it with expdp but it didn't work).

               

              @Mark D Powell: you suggest ALTER TABLE SHRINK here ? How can I use it and can it be used on AUD$ table since this is a SYSTEM's table?

              • 5. Re: Managing AUD$ without DBMS_AUDIT_MGMT
                Mark D Powell
                Mark D Powell Sep 13, 2019 7:43 PM (in response to 3346177)

                user3346177, sorry you cannot use shrink, use move instead.. Example ran on 11.2.0.4 with AUD$ in SYSTEM tablespace

                - -

                Connected.
                SYS@DUT1> alter table aud$ shrink space;
                alter table aud$ shrink space
                *
                ERROR at line 1:
                ORA-10635: Invalid segment or tablespace type


                SYS@DUT1> alter table aud$ move;

                Table altered.

                 

                - -

                HTH -- Mark D Powell --

                • 7. Re: Managing AUD$ without DBMS_AUDIT_MGMT
                  AndrewSayer
                  AndrewSayer Sep 20, 2019 11:29 AM (in response to 3346177)

                  3346177 wrote:


                  ...

                  The only 100% way to reallocate the storage to be available as Usable storage in ASM as far as i know is:

                  1. Drop a table (which is of course not an option for AUD$)

                  2. export / import table (which is not an option since Oracle does not allow that for AUD$ table, already tried to export it with expdp but it didn't work).

                   

                  @Mark D Powell: you suggest ALTER TABLE SHRINK here ? How can I use it and can it be used on AUD$ table since this is a SYSTEM's table?

                  Neither of those releases space from a tablespace back to the OS.

                  In order to release space from a tablespace you must shrink the files, this is not always possible as you might have extents allocated in the data file above where you want to shrink it to (depending on the segment there might be support for you to shuffle segments around to help).