Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Audits are not Purged

Mustafa, 2022-07-302022-07-30

Hello,

This week I realized that some of my databases’ audit data are not purged and audit tables get bigger. I already have a scheduler job to purge my audit records but somehow job has been completed successfully but audit records are not deleted. This is 19.15 version, NON-CDB databases.

I am using unified audit in all of my databases and purge audit records based on last archive timestamp. here is a sample code to purge unified audit records:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Declare
  L_Days Number := 31;
Begin
  Dbms_Audit_Mgmt.Set_Last_Archive_Timestamp(Dbms_Audit_Mgmt.Audit_Trail_Unified, Trunc(Systimestamp)-L_Days);
 
  Dbms_Audit_Mgmt.Clean_Audit_Trail(Audit_Trail_Type       =>  Dbms_Audit_Mgmt.Audit_Trail_Unified,
                                    Use_Last_Arch_Timestamp=>  True);
  Commit;
  Dbms_Audit_Mgmt.Set_Last_Archive_Timestamp(Dbms_Audit_Mgmt.AUDIT_TRAIL_OS, Trunc(Systimestamp)-L_Days);
 
  Dbms_Audit_Mgmt.Clean_Audit_Trail(Audit_Trail_Type       =>  Dbms_Audit_Mgmt.AUDIT_TRAIL_OS,
                                    Use_Last_Arch_Timestamp=>  True);
  Commit;
End;
/

This code could belong to Tim Hall (www.oracle-base.com)  not sure. This basically sets a date to delete audit records which are before that date. So, to understand the problem I started to digging but didn’t find much (before starting a trace, I found it out).

I checked what the saved last time stamp is:

Oracle PL/SQL
1
2
3
4
5
6
7
8
select * from  DBA_AUDIT_MGMT_LAST_ARCH_TS;
 
AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                   DATABASE_ID CONTAINER_GUID                  
-------------------- ------------ --------------------------------- ----------- ---------------------------------
OS AUDIT TRAIL                 1 28/06/2022 00.00.00          2158381708 B6AB832DC96B2686E0534406C40AC0E8
UNIFIED AUDIT TRAIL 0 28/06/2022 00.00.00          2158381708 B6AB832DC96B2686E0534406C40AC0E8
OS AUDIT TRAIL 1 27/10/2021 00.00.00          2128036467 B6AB832DC96B2686E0534406C40AC0E8
UNIFIED AUDIT TRAIL 0 27/10/2021 00.00.00          2128036467 B6AB832DC96B2686E0534406C40AC0E8

that is interesting because this is NON-CDB database but it has 2 different database_id for same AUDIT_TRAIL type. One of them is actual id (2158381708) of current database but other one is unknown (at first). Also, for the rows with 2158381708 database id, last_archive_ts is correct but the other one was pretty old. So, some of my databases has this not purged audit problems and all of them has double rows in this data dictionary view. So, I thought there might be a bug and maybe code gets an too many rows error. Simply I deleted foreign database id rows. base table of DBA_AUDIT_MGMT_LAST_ARCH_TS is SYS.DAM_LAST_ARCH_TS$. I deleted rows:

Oracle PL/SQL
1
2
3
-- this code is for NON-CDB database only! CDB database has more than one database and database id.
delete DAM_LAST_ARCH_TS$ where database_id != (select dbid from v$database);
commit;

then I re-run audit purge code and it worked!

Of course, I didn’t stop at here. Why there were different rows on this table? while checking database id’s I realized that all databases I had this problem are duplicated databases. I duplicated PROD to create a TEST or ACP for example and every time I duplicate a database I always change database id with “nid” tool.

I learned that after nid, those table remain to store old rows. I deleted old database id rows from all databases and problem solved. purge operation is a success. this is something new that I will add to my duplicate database steps. if you have duplicated database and didn’t check audit rows, you should.

thanks for reading. wish you all healthy, happy, peaceful days.

19c Administration Useful Scripts audit nor purgedaudit records are not purgedaudit rows are not purgedpurge auditpurge problemunified audit

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • June 2025
  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

Archives

  • June 2025
  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes