DBA Hub

📋Steps in this guide1/13

Auditing Enhancements (Audit Policies and Unified Audit Trail) in Oracle Database 12c Release 1 (12.1)

The introduction of audit policies and the unified audit trail simplifies the configuration of database auditing in Oracle 12c.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Creating Audit Policies

Just like standard auditing that came before it, unified auditing can be used to create extremely complex auditing rules. The documentation for managing audit policies is very good, so rather than trying to duplicate that, I will just show some simple examples to give a flavour of the functionality. It is better to create an audit policy that contains all necessary auditing for a session, rather using several small policies. Using multiple policies results in greater login overhead, greater UGA consumption and less efficient internal audit check functionality. An audit policy is made up of several distinct clauses, some of which are optional. Examples of the usages are given in the sections below, but here is a quick summary of them. - : Used to specify a list of system privileges to be audited. - : Defines the actions that need to be audited. These can be , like , or object-specific, like . They can also be that target specific features like data pump or SQL*Loader. - : Specifies a list of roles. All system privileges granted via those roles are audited. - : Allows you to define an to determine when the auditing should take place. The condition can be evaluated for each , or , depending on the level of granularity the condition requires. - : Determines if an audit policy is specific to an individual PDB ( ) or common to all PDBs ( ). This might sound a little confusing, but if you've ever used database auditing in previous releases, it will quickly look quite familiar. The main thing to remember is rather than issuing the commands directly, you create an audit policy containing the relevant pieces, then enable and disable it using the commands. Some of the following examples require these three test users. In some cases the contents of the unified audit trail has been purged between tests to keep the output simple and specific to the functionality being tested.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE AUDIT POLICY policy_name
    { {privilege_audit_clause [action_audit_clause ] [role_audit_clause ]}
        | { action_audit_clause  [role_audit_clause ] } 
        | { role_audit_clause }
     }        
    [WHEN audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}] 
    [CONTAINER = {CURRENT | ALL}];

conn sys@pdb1 as sysdba

create user test identified by test quota unlimited on users;
grant create session, create table, create sequence to test;

create user test2 identified by test2 quota unlimited on users;
grant create session to test2;

create user test3 identified by test3 quota unlimited on users;
grant create session to test3;
2

Privilege Auditing

As the name suggests, privilege auditing allows you to audit the use of system privileges. The view identifies the system privileges that can be audited. If we want to audit the creation of tables and sequences by the user, we might do something like the following. Display the configuration of the policy. Connect to the user and create some objects. Check the audit trail. If you are in delayed-write mode , you may need to flush the audit trail before you can see the audit records. Disable the policy and drop it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
select name
from   system_privilege_map
order by name;

NAME
----------------------------------------
ADMINISTER ANY SQL TUNING SET
ADMINISTER DATABASE TRIGGER
.
.
.
UPDATE ANY TABLE
USE ANY SQL TRANSLATION PROFILE

237 rows selected.

SQL>

conn sys@pdb1 as sysdba

create audit policy test_audit_policy
  privileges create table, create sequence
  when    'sys_context(''userenv'', ''session_user'') = ''TEST'''
  evaluate per session
  container = current;

audit policy test_audit_policy;

set linesize 200
column audit_option format a15
column condition_eval_opt format a10
column audit_condition format a50

select audit_option,
       condition_eval_opt,
       audit_condition
from   audit_unified_policies
where  policy_name = 'TEST_AUDIT_POLICY';

AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- ---------- --------------------------------------------------
CREATE SEQUENCE SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST'
CREATE TABLE    SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST'

SQL>

conn test/test@pdb1

create table tab1 (id number);
create sequence tab1_seq;

conn sys@pdb1 as sysdba

-- You might need to flush the audit information before it is visible.
-- EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

column event_timestamp format a30
column dbusername format a10
column action_name format a20
column object_schema format a10
column object_name format a20

select event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
from   unified_audit_trail
where  dbusername = 'TEST'
order by event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
27-JUN-2015 10:25:37.359596    TEST       CREATE TABLE         TEST       TAB1
27-JUN-2015 10:25:37.363989    TEST       CREATE SEQUENCE      TEST       TAB1_SEQ

SQL>

noaudit policy test_audit_policy;

drop audit policy test_audit_policy;
3

Action Auditing

Actions against all objects, specific objects or even those actions performed by specific utilities can be audited quite easily in Oracle 12c. Create some schema objects to audit and grant access to them to the second user. Create and enable an audit policy that audits some actions against those objects, when performed by the user. Display the configuration of the policy. Perform some actions that won't match the policy condition. Perform some actions that will match the policy condition. Check the audit trail. If you are in delayed-write mode , you may need to flush the audit trail before you can see the audit records. Amend the audit policy and check the changes have been reflected in the configuration. Disable and drop the audit policy, then check the changes have been reflected in the configuration. In the above examples the audit policies were object specific. We could have omitted the part of the action to make it apply to all objects. If we wanted to audit all DML and queries issued by the user, we might create a policy like the following.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
conn test/test@pdb1

drop table tab1 purge;
drop sequence tab1_seq;
drop table tab2 purge;
drop sequence tab2_seq;

create table tab1 (
  id number,
  constraint tab1_pk primary key (id)
);

create sequence tab1_seq;

create table tab2 (
  id number,
  constraint tab2_pk primary key (id)
);

create sequence tab2_seq;

grant select, insert, update, delete on tab1 to test2;
grant select on tab1_seq to test2;
grant select, insert, update, delete on tab2 to test2;
grant select on tab2_seq to test2;

conn sys@pdb1 as sysdba

create audit policy test_audit_policy
  actions delete on test.tab1,
          insert on test.tab1,
          update on test.tab1,
          select on test.tab1_seq,
          all on test.tab2,
          select on test.tab2_seq
  when    'sys_context(''userenv'', ''session_user'') = ''TEST2'''
  evaluate per session
  container = current;

audit policy test_audit_policy;

set linesize 200
column object_schema format a15
column object_name format a15
column object_type format a12
column audit_option format a15
column condition_eval_opt format a10
column audit_condition format a50

select object_schema,
       object_name,
       object_type,
       audit_option,
       condition_eval_opt,
       audit_condition
from   audit_unified_policies
where  policy_name = 'TEST_AUDIT_POLICY';


OBJECT_SCHEMA   OBJECT_NAME     OBJECT_TYPE  AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- --------------- ------------ --------------- ---------- --------------------------------------------------
TEST            TAB1            TABLE        DELETE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        INSERT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        UPDATE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1_SEQ        SEQUENCE     SELECT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB2            TABLE        ALL             SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB2_SEQ        SEQUENCE     SELECT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'

SQL>

conn test/test@pdb1

insert into tab1 (id) values (tab1_seq.nextval);
insert into tab2 (id) values (tab2_seq.nextval);
commit;

conn test2/test2@pdb1

update test.tab1 set id = test.tab1_seq.nextval;
update test.tab2 set id = test.tab2_seq.nextval;
delete from test.tab1;
delete from test.tab2;
commit;

conn sys@pdb1 as sysdba

-- You might need to flush the audit information before it is visible.
-- EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

column event_timestamp format a30
column dbusername format a10
column action_name format a20
column object_schema format a10
column object_name format a20

select event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
from   unified_audit_trail
where  dbusername like 'TEST%'
order by event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
27-JUN-2015 10:41:40.070965    TEST2      SELECT               TEST       TAB1_SEQ
27-JUN-2015 10:41:40.071033    TEST2      UPDATE               TEST       TAB1
27-JUN-2015 10:41:40.073326    TEST2      SELECT               TEST       TAB2_SEQ
27-JUN-2015 10:41:40.073347    TEST2      UPDATE               TEST       TAB2
27-JUN-2015 10:41:40.074657    TEST2      DELETE               TEST       TAB1
27-JUN-2015 10:41:40.076132    TEST2      DELETE               TEST       TAB2

SQL>

alter audit policy test_audit_policy
  drop actions all on test.tab2,
               select on test.tab2_seq;

set linesize 200
column object_schema format a15
column object_name format a15
column object_type format a12
column audit_option format a15
column condition_eval_opt format a10
column audit_condition format a50

select object_schema,
       object_name,
       object_type,
       audit_option,
       condition_eval_opt,
       audit_condition
from   audit_unified_policies
where  policy_name = 'TEST_AUDIT_POLICY';

OBJECT_SCHEMA   OBJECT_NAME     OBJECT_TYPE  AUDIT_OPTION    CONDITION_ AUDIT_CONDITION
--------------- --------------- ------------ --------------- ---------- --------------------------------------------------
TEST            TAB1            TABLE        DELETE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        INSERT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1            TABLE        UPDATE          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'
TEST            TAB1_SEQ        SEQUENCE     SELECT          SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST2'

SQL>

noaudit policy test_audit_policy;

drop audit policy test_audit_policy;

set linesize 200
column object_schema format a15
column object_name format a15
column object_type format a12
column audit_option format a15
column condition_eval_opt format a10
column audit_condition format a50

select object_schema,
       object_name,
       object_type,
       audit_option,
       condition_eval_opt,
       audit_condition
from   audit_unified_policies
where  policy_name = 'TEST_AUDIT_POLICY';

no rows selected

SQL>

create audit policy test_audit_policy
  actions delete, insert, update, select
  when    'sys_context(''userenv'', ''session_user'') = ''TEST2'''
  evaluate per session
  container = current;

--drop audit policy test_audit_policy;
4

Component Action Auditing

Rather than auditing actions on specific objects, you can instead audit actions relevant to specific functionality or utilities, such as Oracle Label Security (OLS), Real Application Security, Database Vault, Data Pump or SQL*Loader. There are two examples of auditing linked below. - Auditing Data Pump Operations - Auditing SQL*Loader Direct Path Loads
5

Role Auditing

It is possible to audit all system privileges granted via a built-in or custom role using an audit policy. Create a new role, then grant privileges on the TEST objects via the new role. Create an audit policy based on all the system privileges associated with the role. Enable the auditing policy and check the configuration. Perform some actions that will match the policy condition. Check the audit trail. If you are in delayed-write mode , you may need to flush the audit trail before you can see the audit records. Disable and drop the policy.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
conn sys@pdb1 as sysdba

create role create_table_role;
grant create table to create_table_role;

grant create_table_role to test3;

create audit policy create_table_role_policy
  roles create_table_role
  when    'sys_context(''userenv'', ''session_user'') = ''TEST3'''
  evaluate per session
  container = current;

audit policy create_table_role_policy;

set linesize 200
column audit_option format a20
column condition_eval_opt format a10
column audit_condition format a50

select audit_option,
       audit_option_type,
       condition_eval_opt,
       audit_condition
from   audit_unified_policies
where  policy_name = 'CREATE_TABLE_ROLE_POLICY';

AUDIT_OPTION         AUDIT_OPTION_TYPE  CONDITION_ AUDIT_CONDITION
-------------------- ------------------ ---------- --------------------------------------------------
CREATE_TABLE_ROLE    ROLE PRIVILEGE     SESSION    SYS_CONTEXT('USERENV', 'SESSION_USER') = 'TEST3'

SQL>

conn test3/test3@pdb1

create table tab1 (id number);

conn sys@pdb1 as sysdba

-- You might need to flush the audit information before it is visible.
-- EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

column event_timestamp format a30
column dbusername format a10
column action_name format a20
column object_schema format a10
column object_name format a20

select event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
from   unified_audit_trail
where  dbusername = 'TEST3'
order by event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME
------------------------------ ---------- -------------------- ---------- --------------------
27-JUN-2015 10:48:40.744492    TEST3      CREATE TABLE         TEST3      TAB1

SQL>

noaudit policy create_table_role_policy;

drop audit policy create_table_role_policy;
6

Unified Audit Trail Administration

Administration of the unified audit trail can seem a little complicated at first, but there are a few things to keep in mind. - Unified auditing works by default, so you don't need to do anything to get started. - The default settings are OK. You will probably only have to focus on your specific audit policies. - Setting up an archival and purging process will need some thought, but you will probably only do this once in the lifetime of your database, so don't get scared off by this aspect of auditing. With that said, the remaining parts of this article will explain how to perform some of the basic administration tasks. For brevity, I will avoid repetition of functionality explained in previous articles, but links will be provided.
7

Available By Default

The unified audit trail and audit policy functionality is available by default in all editions, but it can run in two modes. By default it runs in mixed mode, which means you can combine traditional auditing with unified auditing. The following query shows that "pure" unified auditing is not enabled, which means we are running in mixed mode. All the functionality of unified auditing is still available, but you may still need to consider the parameter, as it still controls how the traditional auditing is performed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
select value from v$option where parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL>
8

Enable/Disable Pure Unified Auditing

Running in mixed mode is perfectly acceptable, but you can choose to switch to pure unified auditing, so the traditional auditing functionality is no longer available. This is done by relinking the Oracle binaries as follows. After restarting, the banner will include the "Unified Auditing" option and we can see the unified auditing option is enabled. Switching back to mixed mode involves a relink again. After restarting, the banner no longer includes the "Unified Auditing" option and we can see the unified auditing option is disabled. For Windows, the documentation suggests it is just a case of turning the services off and moving the relevant DLL. I've not tried this, so I can't guarantee it works.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
sqlplus / as sysdba <<EOF
shutdown immediate;
exit
EOF

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

sqlplus / as sysdba <<EOF
startup;
exit
EOF

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 1 11:35:52 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select value from v$option where parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL>

sqlplus / as sysdba <<EOF
shutdown immediate;
exit
EOF

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle

sqlplus / as sysdba <<EOF
startup;
exit
EOF

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 1 11:35:52 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select value from v$option where parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

SQL>

Rem Enable
%ORACLE_HOME%/bin/orauniaud12.dll.dbl file to %ORACLE_HOME%/bin/orauniaud12.dll

Rem Disable
%ORACLE_HOME%/bin/orauniaud12.dll file to %ORACLE_HOME%/bin/orauniaud12.dll.dbl
9

Audit Trail Security

Maintenance of the audit trail and audit policies is limited to those users granted the role. The role can be granted to users who need to view the audit information, but not manage the audit trail or audit policies. Under unified auditing, users are no longer able to create auditing policies against their own objects. For backwards compatibility, this is still possible for traditional auditing. This is possibly a reason to move away from mixed-mode auditing.
10

Write Mode

For performance reasons unified auditing uses a queued-write mechanism, so audited actions are written to the SGA and periodically pushed out to disk. This can result in loss of audit records during an instance crash, since the records may not be pushed out to disk before the instance fails. If this represents a problem, the write mode can be altered to immediate-write, so the audit actions are pushed directly to disk, but this will result in auditing having a greater overhead. The following code shows how to switch between these two modes. In a multitenant environment, these setting are container-specific. The size of the queue in the SGA is controlled by the initialization parameter, which can be set to values between 1-30MB, with the default being 1MB. Each RAC instance can have a different value. As a consequence of running in queued-write mode, in some cases, especially while testing or before maintenance, it may be necessary to flush audit records to the audit trail. This can be done using the procedure. It accepts the and parameters, which allow the following variations.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- Switch to immediate-write.
begin
  dbms_audit_mgmt.set_audit_trail_property(
    audit_trail_type           => dbms_audit_mgmt.audit_trail_unified,
    audit_trail_property       => dbms_audit_mgmt.audit_trail_write_mode, 
    audit_trail_property_value => dbms_audit_mgmt.audit_trail_immediate_write
  );
end;
/

-- Switched to queued-write.
begin
  dbms_audit_mgmt.set_audit_trail_property(
    audit_trail_type           => dbms_audit_mgmt.audit_trail_unified,
    audit_trail_property       => dbms_audit_mgmt.audit_trail_write_mode, 
    audit_trail_property_value => dbms_audit_mgmt.audit_trail_queued_write
  );
end;
/

-- Flush records to audit trail for the current container in the current instance.
exec dbms_audit_mgmt.flush_unified_audit_trail;

exec dbms_audit_mgmt.flush_unified_audit_trail(flush_type => dbms_audit_mgmt.flush_current_instance);

exec dbms_audit_mgmt.flush_unified_audit_trail(container => dbms_audit_mgmt.container_current);

begin
  dbms_audit_mgmt.flush_unified_audit_trail(
    flush_type => dbms_audit_mgmt.flush_current_instance,
    container  => dbms_audit_mgmt.container_current);
end;
/

-- Flush records to audit trail for all containers in the current instance.
-- Must be run from root container.
exec dbms_audit_mgmt.flush_unified_audit_trail(container => dbms_audit_mgmt.container_all);

begin
  dbms_audit_mgmt.flush_unified_audit_trail(
    flush_type => dbms_audit_mgmt.flush_current_instance,
    container  => dbms_audit_mgmt.container_all);
end;
/

-- Flush records to audit trail for the all RAC instances. Current Container.
exec dbms_audit_mgmt.flush_unified_audit_trail(flush_type => dbms_audit_mgmt.flush_all_instances);

begin
  dbms_audit_mgmt.flush_unified_audit_trail(
    flush_type => dbms_audit_mgmt.flush_all_instances,
    container  => dbms_audit_mgmt.container_current);
end;
/

-- Flush records to audit trail for the all RAC instances. All Containers.
-- Must be run from root container.
begin
  dbms_audit_mgmt.flush_unified_audit_trail(
    flush_type => dbms_audit_mgmt.flush_all_instances,
    container  => dbms_audit_mgmt.container_all);
end;
/
11

Purging the Unified Audit Trail

Oracle 11g Release 2 introduced the package to manage the audit trail. Oracle 12c uses the same functionality to archive and purge the unified audit trail, described here , with the addition of constants to identify the unified audit trail. For mixed mode auditing environments, remember to manage both the conventional audit trails as well as the unified audit trail. The following example shows how to set the last archived time for the unified audit trail and manually purge audit records older than that time. Remember to archive records before purging them if you need to store them long term. You can automate purging by creating a purge job, as described here . The unified audit trail is held in an interval partitioned table called . By default the partition interval is 1 month. When purging, if a whole partition can be removed the partition is dropped. If not, a delete is performed on the partition. In the example below we switch the partition interval to 1 day.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- Set the last archive timestamp.
begin
  dbms_audit_mgmt.set_last_archive_timestamp(
    audit_trail_type     => dbms_audit_mgmt.audit_trail_unified,
    last_archive_time    => systimestamp-5,
    --rac_instance_number  =>  1,
    container            => dbms_audit_mgmt.container_current
  );
end;
/

-- Check the new setting.
column audit_trail format a20
column last_archive_ts format a40

select audit_trail,
       last_archive_ts
from   dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          LAST_ARCHIVE_TS
-------------------- ----------------------------------------
UNIFIED AUDIT TRAIL  10-JUN-15 13.48.14.000000 +00:00

SQL>

-- Manually purge the audit trail to the last archive timestamp.
select count(*) from unified_audit_trail;

  COUNT(*)
----------
        49

SQL>

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        => dbms_audit_mgmt.audit_trail_unified,
   use_last_arch_timestamp => true);
end;
/

select count(*) from unified_audit_trail;

  COUNT(*)
----------
         4

SQL>

begin
 dbms_audit_mgmt.alter_partition_interval(
   interval_number       => 1,
   interval_frequency    => 'DAY');
end;
/


set linesize 150
column owner format a10
column table_name format a20
column interval format a25

select owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
from   dba_part_tables
where  owner = 'AUDSYS';

OWNER      TABLE_NAME           INTERVAL                  PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
---------- -------------------- ------------------------- --------- --------------- ------------------------------
AUDSYS     CLI_SWP$fcf94b1c$1$1                           RANGE                   1 SYSAUX
AUDSYS     AUD$UNIFIED          NUMTODSINTERVAL(1, 'DAY') RANGE             1048575 SYSAUX

SQL>
12

Performance Improvements in 12.2

One of the main criticisms of the unified audit trail in 12.1 was the performance. In 12.2 the unified audit trail now resides in a conventional table called . When you upgrade a database you can choose to migrate the existing audit information to this table using the in the , which should give better performance.
13

Export/Import the Unified Audit Trail in 18c

From Oracle 18c onward it is possible to export and import the unified audit trail. This functionality is described here . For more information see: - Introduction to Auditing - Configuring Audit Policies - Administering the Audit Trail - CREATE AUDIT POLICY - DBMS_AUDIT_MGMT - Audit Data Pump Operations - Audit Data Pump Commands - Audit SQL*Loader Direct Path Loads - Auditing : All Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!