14 Replies Latest reply on Nov 28, 2018 10:31 AM by RobK

    Cleanup AUD$ before moving from SYSTEM tablespace

    RobK

      Hello!

       

      This question is related Oracle 11.2 and 12.x versions where we are supposed to use DBMS_AUDIT_MGMT package.

      Question is related to Standard Audit Trail with conventional auditing (AUD$ and not unified auditing)

       

      So imagine your AUD$ table is in SYSTEM tablespace. It is 200GB with data 10 years old.

       

      This is the current situation and setting

      -- CURRENT tablespace of AUD$

      select  tablespace_name as current_aud$_ts

      from dba_segments

      where (owner = 'SYS' and segment_name = 'AUD$');

      CURRENT_AUD$_TS

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

      SYSTEM

       

      -- CURRENT destination

      column current_aud$_destination_ts format a30

      select parameter_value as current_aud$_destination_ts

      from DBA_AUDIT_MGMT_CONFIG_PARAMS

      where parameter_name = 'DB AUDIT TABLESPACE'

        and audit_trail = 'STANDARD AUDIT TRAIL';

      CURRENT_AUD$_DESTINATION_TS

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

      SYSAUX

       

      What is my goal?

      I would like to cleanup data older than 1 year. I would like to delete old records.

      After the cleanup I would possibly move data to SYSAUX.

      The point is that it makes no sense to move such huge amount of data and then delete 90% of it. I would like to move only the needed 10% to SYSAUX.

      I would NOT like to move the whole data to SYSAUX.

       

      What is the problem?

      INIT_CLEANUP moves the data to the default SYSAUX tablespace (or somewhere else if specified with SET_AUDIT_TRAIL_LOCATION parameter)

      https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65423

       

      SET_AUDIT_TRAIL_LOCATON procedure also moves the data and does not only set a parameter. (Why does it not only set the location???)

      https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65427

       

      My idea was to SET_AUDIT_TRAIL_LOCATION to SYSTEM tablespace like this

      begin

      DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

         DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

         'SYSTEM') ;

      end;

      /

       

      But it had no effect. No errors. No nothing.... It is still SYSAUX tablespace.

      column current_aud$_destination_ts format a30

      select parameter_value as current_aud$_destination_ts

      from DBA_AUDIT_MGMT_CONFIG_PARAMS

      where parameter_name = 'DB AUDIT TABLESPACE'

        and audit_trail = 'STANDARD AUDIT TRAIL';

      CURRENT_AUD$_DESTINATION_TS

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

      SYSAUX

       

      Question

      The question is how one can avoid moving the huge amount of data and start the cleanup while still using SYSTEM tablespace.

      Shall I just delete from AUD$ without the DBMS_AUDIT_MGMT package? Is plain delete still supported?

       

      Thanks,

      RobK

        • 1. Re: Cleanup AUD$ before moving from SYSTEM tablespace
          RobK

          One more note:

          How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ (Doc ID 73408.1)

          says we can delete from aud$, but the note is only valid up to version 11.2

           

          So I don't know if delete is supported for 12.x as well or if you can solve my problem with DBMS_AUDIT_MGMT.

           

          RobK

          • 2. Re: Cleanup AUD$ before moving from SYSTEM tablespace
            top.gun

            11g didn;t have the mixed mode and unified auditing.

            So you can still purge from AUD$ as you did before.

             

            In 12c there is mixed mode and unified auditing, so not all auditing is inside AUD$.

            So to cater for this you use the DBMS_AUDIT_MGMT package.

            https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/administering-the-audit-trail.html#GUID-9F298B8A-6…

            • 3. Re: Cleanup AUD$ before moving from SYSTEM tablespace
              Mark D Powell

              RobK, you can still run a manual DELETE statement against SYS.AUD$ if using conventional auditing.

              - -

              HTH -- Mark D Powell --

              1 person found this helpful
              • 4. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                RobK

                Thanks!

                  Can you back up your statement with a reference to documentation or to a metalink note?

                **  Which is valid for 12c ***

                 

                I would like to do only supported stuff at our customer.

                All metalink notes I found

                  - either did not talk about manual delete

                  - or was not valid for 12c

                RobK

                • 5. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                  EdStevens

                  RobK wrote:

                   

                  Hello!

                   

                  This question is related Oracle 11.2 and 12.x versions where we are supposed to use DBMS_AUDIT_MGMT package.

                  Question is related to Standard Audit Trail with conventional auditing (AUD$ and not unified auditing)

                   

                  So imagine your AUD$ table is in SYSTEM tablespace. It is 200GB with data 10 years old.

                   

                   

                  <snip>

                   

                  What is my goal?

                  I would like to cleanup data older than 1 year. I would like to delete old records.

                  After the cleanup I would possibly move data to SYSAUX.

                  The point is that it makes no sense to move such huge amount of data and then delete 90% of it. I would like to move only the needed 10% to SYSAUX.

                  I would NOT like to move the whole data to SYSAUX.

                   

                  It may make "no sense to move such huge amount of data and then delete 90% of it." but it also makes no spending more time solving this non-problem than you will save when you actually perform this one-time operation.  Which you have already done just in the time you've already spent in arriving at the point of posting this thread.  I'm sorry, but in the oracle world, 200gb of data is not "huge".  Just relocate the entire table and get on with life.

                  1 person found this helpful
                  • 6. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                    RobK

                    Dear Ed!

                    Thanks for the your post. I think it is a good point that you made.

                     

                    Let me just give you few more points, some of which were not mentioned in the original post.

                     

                    • If I do delete and move (as I wish to do) then my target tablespace will only be 20GB
                    • If I do move and delete (as you suggested) then I wasted 180GB in the target tablespace, which will likely not be reused ever unless I do extra work to reclaim the space. But that would be extra work, extra time, extra complexity.
                    • I am currently developing a module that is to be used at our customer in hundreds of databases. Wasting e.g 20GB in each database in average means wasting 2TB of storage. This is clearly not an acceptable solution.

                     

                    So in short I wish to reclaim the space easily as well.

                     

                    What do you think now?

                    Thanks in advance,

                    RobK

                    • 7. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                      WadhahDaouehi

                      Hi,

                       

                      May you can delete old audit such as 80% or 90% of audited data and then move the audit table to a dedicated tablespace.

                      Check my article,  Move audit Trail table to created-user Tablespace with Oracle database 11g & 12c

                      https://wadhahdaouehi.tn/2017/10/move-audit-trail-table-to-created-user-tablespace-with-oracle-database-11g-12c/

                       

                       

                      Best regards

                      • 8. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                        Mark D Powell

                        RobK, I really do not have time to dig around for a reference especially since you did not list the articles you have referenced so I can check them out.

                        - -

                        But per the manual the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure can be used to manually clean the audit trail.  Did you try it?  If so, exactly what does the code you ran look like?

                        - -

                        https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/administering-the-audit-trail.html#GUID-B9D2B078-2…

                        - -

                        If you did try the procedure make sure you do not have the issue raised in the following note

                        Customer Recommended12c DBMS_AUDIT_MGMT not purging the audit records due to multiple DBIDs (Doc ID 2160057.1)

                        - -

                        HTH -- Mark D Powell --

                        • 9. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                          RobK

                          Thanks Mark,

                           

                          1) here are the metalink notes I checked:

                           

                          Useful ones

                          *************

                          How To Move The DB Audit Trails To A New Tablespace Using DBMS_AUDIT_MGMT? (Doc ID 1328239.1)

                          Oracle Database - Enterprise Edition - Version 11.2.0.2 and later

                           

                          Known Issues When Using: DBMS_AUDIT_MGMT (Doc ID 804624.1)

                           

                          Less useful ones

                          ********************

                          How to Truncate, Delete, or Purge Rows from the Audit Trail Table AUD$ (Doc ID 73408.1) xxx

                          Version 8.1.7.4 to 11.2.0.4 [Release 8.1.7 to 11.2]

                           

                          Moving or Reorganizing SYS Owned Objects to different tablespace or within SYSTEM tablespace (Doc ID 1073588.1)

                           

                          How to Reorganize SYS.AUD$ Table (Doc ID 166301.1)

                          Oracle Database - Enterprise Edition - Version 9.2.0.8 to 10.2.0.5.0 [Release 9.2 to 10.2]

                           

                          2) DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL

                          Yes it tried it. The problem is that before CLEAN_AUDIT_TRAIL you need to INIT_CLEANUP which moves the data first. Don't ask me why.

                          So we need double the space we would need if was deleted records first.

                           

                          3,4) Thanks I am aware of those.

                           

                          Thanks,

                          RobK

                          • 10. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                            EdStevens

                            RobK wrote:

                             

                            Dear Ed!

                            Thanks for the your post. I think it is a good point that you made.

                             

                            Let me just give you few more points, some of which were not mentioned in the original post.

                             

                            • If I do delete and move (as I wish to do) then my target tablespace will only be 20GB
                            • If I do move and delete (as you suggested) then I wasted 180GB in the target tablespace, which will likely not be reused ever unless I do extra work to reclaim the space. But that would be extra work, extra time, extra complexity.
                            • I am currently developing a module that is to be used at our customer in hundreds of databases. Wasting e.g 20GB in each database in average means wasting 2TB of storage. This is clearly not an acceptable solution.

                             

                            So in short I wish to reclaim the space easily as well.

                             

                            What do you think now?

                            Thanks in advance,

                            RobK

                            Well, scripting to reclaim the space after the move is not that big of a deal.  But as others have pointed out, dbms.audit_mgmt allows you to delete records from the audit trail as a totally separate operation from moving the audit tables.  After reviewing your opening post in more detail, it appears you are too focused on the options related to relocating tables and not looking at the other options of dbms.audit_trail, though I might still be missing something.

                            • 11. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                              Mark D Powell

                              RobK, if you cannot get the data to purge using DBMS_AUDIT_MGMT then I would just run the DELETE SYS.AUD$ statement and call it a day.  You can practice on a test system to verify the delete (as sysdba) works, the move tablespace operation works, audit rows are still collected after performing this task.

                              - -

                              HTH -- Mark D Powell --

                              • 12. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                                RobK

                                Thanks Mark.

                                 

                                I agree with you. I also arrived to the same conclusion.

                                 

                                I am not convinced if it is supported, but I gave feedback on notes 1328239.1,  804624.1, 73408.1 asking basically if delete is supported in 12c and also Standard Edition.

                                I got mails from MOS today that these notes will be reviewed.

                                 

                                Hopefully in few days we will have official answer. (I really did not want to open a support ticket...)

                                Thanks for your time.

                                 

                                RobK

                                • 13. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                                  EdStevens

                                  RobK wrote:

                                   

                                  <snip>
                                  (I really did not want to open a support ticket...)

                                  I understand that.  I'd almost rather 'take a poke in the eye with a sharp stick' than open an SR.

                                  Sadly, it hasn't always been that way.  But they continue to throw up more and more roadblocks before you can actually get an SR in front of a tech. The worst part is navigating the pre-scripted questions and answers.  Very often I have to select an answer that is not correct just to be allowed to advance to finally opening a ticket, whereupon I immediately update it to explain that none of the available responses were correct for my situation, and "here's what's really happening."  Oh, and having to download, install, and execute the latest information gathering tool and upload the results.  I understand the value of such, but it seems every time I open an SR, they have a new tool that replaces the one they made me install the last time -- or at least a new version that renders the previous as "unacceptable".

                                  • 14. Re: Cleanup AUD$ before moving from SYSTEM tablespace
                                    RobK

                                    Haha

                                    I learned a new English expression (take a poke...)

                                    Cheers,

                                    RobK