Audits are not Purged – Keep Learning Keep Living

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:

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;
/

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:

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

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:

-- 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;

-- 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.

Leave a Reply Cancel reply