Home » RDBMS Server » Server Administration » Truncate SYS.AUD$ Using dbms_audit_mgmt (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685072] Sun, 17 October 2021 19:21 Go to next message
mushy_1983
Messages: 5
Registered: October 2021
Junior Member
Dear All,

I need to delete data older than 12 months SYS.AUD$ using DBMS_AUDIT_MGMT. I am not familiar much with DBMS_AUDIT_MGMT any help how can I delete data older than 12 months.

declare
retention_days number;
begin
retention_days := 100;
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-5);
SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_DB_STD,use_last_arch_timestamp => TRUE);
end;

[Error] Execution (1: 1): ORA-46258: Cleanup not initialized for the audit trail
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1085
ORA-06512: at line 5
Thanks
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685076 is a reply to message #685072] Mon, 18 October 2021 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 100 characters when you format.

ORA-46258: Cleanup not initialized for the audit trail
 *Cause: DBMS_AUDIT_MGMT.INIT_CLEANUP was not called for the erroring audit trail.
 *Action: Invoke the procedure, DBMS_AUDIT_MGMT.INIT_CLEANUP.
You must first call the INIT_CLEANUP procedure.

[Updated on: Mon, 18 October 2021 00:42]

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685077 is a reply to message #685072] Mon, 18 October 2021 01:09 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
It is a good thing that your code didn't work Smile
Because your statement here Quote:
I need to delete data older than 12 months
is not met by your code here
last_archive_time => SYSTIMESTAMP-5);
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685079 is a reply to message #685077] Mon, 18 October 2021 05:35 Go to previous messageGo to next message
mushy_1983
Messages: 5
Registered: October 2021
Junior Member
I was just testing that I can not change can you tell me the solution how I can make it work.
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685080 is a reply to message #685076] Mon, 18 October 2021 05:37 Go to previous messageGo to next message
mushy_1983
Messages: 5
Registered: October 2021
Junior Member
I called up the init procedure but it takes too much time and still when I see its not on.

BEGIN
IF NOT SYS.DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
(SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
SYS.dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
SYS.DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => SYS.dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 12);
else
dbms_output.put_line('Cleanup for STD was already initialized');
end if;
end;
--moderator edit: added [code] tags

[Updated on: Mon, 18 October 2021 06:15] by Moderator

Report message to a moderator

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685081 is a reply to message #685080] Mon, 18 October 2021 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you see but us we see nothing.
Please follow the guide you have been pointed to.

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685082 is a reply to message #685080] Mon, 18 October 2021 06:16 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I called up the init procedure but it takes too much time
How much time is "too much"? IF you have zillions of audit records and your AUD$ table is in your SYSTEM tablespace, it will take a long time as it moves the table to SYSAUX. You should probably move the audit trail before running the INIT_CLEANUP.

ps - I've added [code] tags to your previous post, please do so yourself in future.
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685083 is a reply to message #685082] Mon, 18 October 2021 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Mon, 18 October 2021 13:16
...
ps - I've added [code] tags to your previous post, please do so yourself in future.
... and indent the code.

Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685241 is a reply to message #685083] Fri, 19 November 2021 08:05 Go to previous messageGo to next message
mushy_1983
Messages: 5
Registered: October 2021
Junior Member
Hi Everyone,

I have moved the table out of system tablespace and I can see that DBMS_AUDIT_MGMT.INIT_CLEANUP is initialized but still init_cleanup is not working. I am able to execute procedure successfully but nothing has been deleted.

Any suggestions I have the all the rights on SYS.AUD$ table.

Thanks
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685242 is a reply to message #685241] Fri, 19 November 2021 11:51 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
Come on, man! Haven't you read the docs? After initializing the clean up, you actually have to do it: dbms_audit_mgmt.set_last_archive_timestamp and dbms_audit_mgmt.clean_audit_trail
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685248 is a reply to message #685242] Tue, 23 November 2021 12:33 Go to previous messageGo to next message
mushy_1983
Messages: 5
Registered: October 2021
Junior Member
Hi thanks for the correction yes it is working but I am not sure how its working. For testing I have set the timestamp for sysdate-1 so ideally it should delete the data only for one day but it keeps on running and running and deleting whole data. Can you please check and confirm the way how it works or any correction required at my side.

"exec DBMS_AUDIT_MGMT.set_last_archive_timestamp(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,last_archive_time =>SYSDATE-1);

EXEC DBMS_AUDIT_MGMT.clean_audit_trail(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,use_last_arch_timestamp => TRUE);

SELECT count(*) FROM SYS.AUD$
where NTIMESTAMP#>SYSDATE-1"
Re: Truncate SYS.AUD$ Using dbms_audit_mgmt [message #685252 is a reply to message #685248] Wed, 24 November 2021 03:47 Go to previous message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I have set the timestamp for sysdate-1 so ideally it should delete the data only for one day
You want to remove data for one day? Why?? There is no way to do that. Your setting will remove everything more than one day old.

[Updated on: Wed, 24 November 2021 03:47]

Report message to a moderator

Previous Topic: Parse statistic values don't seem to add up
Next Topic: ORA-30928: Connect by filtering phase runs out of temp tablespace
Goto Forum:
  


Current Time: Mon Dec 06 09:51:34 CST 2021