How to schedule UNIFIED_AUDIT_TRAIL purge job (Doc ID 2231430.1)

 In this Document

Goal
Solution
References


APPLIES TO:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.

GOAL

 The Goal is to schedule purge job for unified_audit_trial. Note the purge job is configured for the particular PDB. If the job should be applicable to all the PDB then please use CONTAINER => DBMS_AUDIT_MGMT.CONTAINER__ALL

In the preceeding example The job is scheduled to run hourly, Delete audit records of 1 day old

SOLUTION

 

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
115

 


set last archive timestamp to 1 day

###################################

SQL> begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => sysdate - 1,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
end;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.


Setup a purge job to run hourly
###############################

SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL =>1,
AUDIT_TRAIL_PURGE_NAME => 'Unified_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

 

 

Schedule automatic advancement of the archive timestamp

#######################################################

SQL> create or replace procedure set_archive_retention
(retention in number default 1) as
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => sysdate - retention);
end;
/
2 3 4 5 6 7 8
Procedure created.

 

SQL> BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'advance_archive_timestamp',
job_type => 'STORED_PROCEDURE',
job_action => 'SET_ARCHIVE_RETENTION',
number_of_arguments => 1,
start_date => SYSDATE,
repeat_interval => 'freq=daily' ,
2 3 4 5 6 7 8 9 enabled => false,
auto_drop => FALSE);
dbms_scheduler.set_job_argument_value
(job_name =>'advance_archive_timestamp',
argument_position =>1,
-- one week, you can customize this line:
argument_value => 1);
DBMS_SCHEDULER.ENABLE('advance_archive_timestamp');
End;
/ 10 11 12 13 14 15 16 17 18

PL/SQL procedure successfully completed.

 

SQL> BEGIN
DBMS_SCHEDULER.run_job (job_name => 'advance_archive_timestamp',
use_current_session => FALSE);
END;
/
2 3 4 5
PL/SQL procedure successfully completed.

 

 

########## Getting the Job details ##########

SQL> select * from dba_audit_mgmt_last_arch_ts
2 ;

AUDIT_TRAIL RAC_INSTANCE
-------------------- ------------
LAST_ARCHIVE_TS
---------------------------------------------------------------------------
DATABASE_ID CONTAINER_GUID
----------- ---------------------------------
UNIFIED AUDIT TRAIL 0
07-FEB-17 05.01.27.000000 AM +00:00
1461278713 464785603CCA4A50E0538EA7400A824C

 

SQL> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME
--------------------------------------------------------------------------------
JOB_STAT AUDIT_TRAIL
-------- ----------------------------
JOB_FREQUENCY
--------------------------------------------------------------------------------
USE JOB_CON
--- -------
UNIFIED_AUDIT_TRAIL_PJ
ENABLED UNIFIED AUDIT TRAIL
FREQ=HOURLY;INTERVAL=1
YES CURRENT

 

 

#####Make sure the audit record count in unified_audit_trail reduces ###

 

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
11

 

 

 

DBA_AUDIT_MGMT_CLEAN_EVENTS : Displays the history of purge events of the traditional (that is, non-unified) audit trails. Do don't be surprised if the table does not show any output for unified_audit_trail

 

SQL> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

no rows selected

Note:

#####

In 12.2 if purging of unified_audit_trail job is created in container(ROOT) it is possible that this job will not purge audit records from pdbs. This is due to bug 27527173. Please apply patch.

 

REFERENCES

NOTE:1203353.1 - How to find Oracle Database Documentation on OTN web site
https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG1027

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more