Print Friendly, PDF & Email

 
 
12c AUDITING
 
Since the Oracle Database 12c Release 12.1.0.1.0 the Oracle invented a new approach of auditing – The Unified Auditing
The Unified Auditing can operate in one of 2 available auditing modes – the Mixed Mode Auditing and the Pure Unified Auditing Mode.

By default, every newly created database uses the Mixed Mode Auditing. The Mixed Mode Auditing is enabled through the predefined ORA_SECURECONFIG Audit Policy.

SET LINES 300
SET PAGES 999
COL USER_NAME FOR A10 
COL POLICY_NAME FOR A20 
COL ENABLED_OPT FOR A11 
COL SUCCESS FOR A7
COL FAILURE FOR A7

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;
USER_NAME  POLICY_NAME          ENABLED_OPT SUCCESS FAILURE
---------- -------------------- ----------- ------- -------
ALL USERS  ORA_SECURECONFIG     BY          YES     YES

 
The default ORA_SECURECONFIG Audit Policy audits the following events

SET PAGES 999
SET LINES 300
COL POLICY_NAME FOR A20
COL AUDIT_OPTION FOR A35
COL AUDIT_OPTION_TYPE FOR A25 
SELECT POLICY_NAME,
       AUDIT_OPTION,
       AUDIT_OPTION_TYPE
FROM
       AUDIT_UNIFIED_POLICIES
WHERE
       POLICY_NAME='ORA_SECURECONFIG'
ORDER BY
       POLICY_NAME,
       AUDIT_OPTION;
POLICY_NAME          AUDIT_OPTION                        AUDIT_OPTION_TYPE
-------------------- ----------------------------------- -------------------------
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT           SYSTEM PRIVILEGE
ORA_SECURECONFIG     ALTER ANY PROCEDURE                 SYSTEM PRIVILEGE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE   SYSTEM PRIVILEGE
ORA_SECURECONFIG     ALTER ANY TABLE                     SYSTEM PRIVILEGE
ORA_SECURECONFIG     ALTER DATABASE                      SYSTEM PRIVILEGE
ORA_SECURECONFIG     ALTER DATABASE LINK                 STANDARD ACTION
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE            STANDARD ACTION
ORA_SECURECONFIG     ALTER PROFILE                       STANDARD ACTION
ORA_SECURECONFIG     ALTER ROLE                          STANDARD ACTION
ORA_SECURECONFIG     ALTER SYSTEM                        SYSTEM PRIVILEGE
ORA_SECURECONFIG     ALTER USER                          STANDARD ACTION
ORA_SECURECONFIG     AUDIT SYSTEM                        SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE ANY JOB                      SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE ANY LIBRARY                  SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE ANY PROCEDURE                SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE  SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE ANY TABLE                    SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE DATABASE LINK                STANDARD ACTION
ORA_SECURECONFIG     CREATE DIRECTORY                    STANDARD ACTION
ORA_SECURECONFIG     CREATE EXTERNAL JOB                 SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE           STANDARD ACTION
ORA_SECURECONFIG     CREATE PROFILE                      STANDARD ACTION
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM               SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE ROLE                         STANDARD ACTION
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE      SYSTEM PRIVILEGE
ORA_SECURECONFIG     CREATE USER                         SYSTEM PRIVILEGE
ORA_SECURECONFIG     DROP ANY PROCEDURE                  SYSTEM PRIVILEGE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE    SYSTEM PRIVILEGE
ORA_SECURECONFIG     DROP ANY TABLE                      SYSTEM PRIVILEGE
ORA_SECURECONFIG     DROP DATABASE LINK                  STANDARD ACTION
ORA_SECURECONFIG     DROP DIRECTORY                      STANDARD ACTION
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE             STANDARD ACTION
ORA_SECURECONFIG     DROP PROFILE                        STANDARD ACTION
ORA_SECURECONFIG     DROP PUBLIC SYNONYM                 SYSTEM PRIVILEGE
ORA_SECURECONFIG     DROP ROLE                           STANDARD ACTION
ORA_SECURECONFIG     DROP USER                           SYSTEM PRIVILEGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY                SYSTEM PRIVILEGE
ORA_SECURECONFIG     EXEMPT REDACTION POLICY             SYSTEM PRIVILEGE
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE          SYSTEM PRIVILEGE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE                 SYSTEM PRIVILEGE
ORA_SECURECONFIG     GRANT ANY ROLE                      SYSTEM PRIVILEGE
ORA_SECURECONFIG     LOGMINING                           SYSTEM PRIVILEGE
ORA_SECURECONFIG     LOGOFF                              STANDARD ACTION
ORA_SECURECONFIG     LOGON                               STANDARD ACTION
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN                SYSTEM PRIVILEGE
ORA_SECURECONFIG     SET ROLE                            STANDARD ACTION
ORA_SECURECONFIG     TRANSLATE ANY SQL                   SYSTEM PRIVILEGE

47 rows selected.

 
All Unified Auditing records reside in the AUDSYS schema and are stored in the SYSAUX tablespace.

SET PAGES 999
SET LINES 300
COL OWNER FOR A10 
COL SEGMENT_NAME FOR A30 
COL SEGMENT_TYPE FOR A15 
COL MB FOR 9999999
COL STORED_IN FOR A10

SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(BYTES/1024/1024) MB,
       TABLESPACE_NAME STORED_IN
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME='SYSAUX' and owner='AUDSYS';
OWNER      SEGMENT_NAME                   SEGMENT_TYPE          MB STORED_IN
---------- ------------------------------ --------------- -------- ----------
AUDSYS     CLI_SWP$8d34b5cd$1$1           TABLE               7800 SYSAUX
AUDSYS     CLI_SCN$8d34b5cd$1$1           INDEX                137 SYSAUX
AUDSYS     SYS_IL0000091833C00014$$       LOBINDEX               0 SYSAUX
AUDSYS     SYS_LOB0000091833C00014$$      LOBSEGMENT          7221 SYSAUX
AUDSYS     CLI_TIME$8d34b5cd$1$1          INDEX                146 SYSAUX
AUDSYS     CLI_LOB$8d34b5cd$1$1           INDEX                129 SYSAUX

6 rows selected.

 
All Unified Auditing records are available through the SYS.UNIFIED_AUDIT_TRAIL view.
For instance, to find all sessions logged in the database as the sysdba

SET LINES 300
SET PAGES 999
COL DBUSERNAME FOR A10
COL USERHOST FOR A30 
COL EVENT_TIMESTAMP FOR A30
COL ACTION_NAME FOR A10 
COL SYSTEM_PRIVILEGE_USED FOR A10
SELECT DBUSERNAME,
       USERHOST,
       EVENT_TIMESTAMP,
       ACTION_NAME,
       SYSTEM_PRIVILEGE_USED,
CLIENT_PROGRAM_NAME
FROM
       SYS.UNIFIED_AUDIT_TRAIL
WHERE
       SYSTEM_PRIVILEGE_USED='SYSDBA' AND
       ACTION_NAME='LOGON';
DBUSERNAME USERHOST            EVENT_TIMESTAMP                ACTION_NAM SYSTEM_PRI CLIENT_PROGRAM_NAME
---------- ------------------- ------------------------------ ---------- ---------- ------------------------------------------------
SYS        srv-oem13c-r3.local 10-SEP-20 04.00.02.570678 PM   LOGON      SYSDBA     OMS
SYS        srv-meridb2.local   10-SEP-20 04.00.04.979589 PM   LOGON      SYSDBA     rman@srv-meridb2.local (TNS V1-V3)
SYS        srv-meridb2.local   10-SEP-20 04.00.05.430693 PM   LOGON      SYSDBA     rman@srv-meridb2.local (TNS V1-V3)
SYS        srv-meridb2.local   10-SEP-20 04.00.05.004066 PM   LOGON      SYSDBA     rman@srv-meridb2.local (TNS V1-V3)

...

SYS        srv-meridb2.local   10-SEP-20 05.00.07.102258 PM   LOGON      SYSDBA     rman@srv-meridb2.local (TNS V1-V3)
SYS        srv-meridb2.local   10-SEP-20 05.00.06.595121 PM   LOGON      SYSDBA     perl@srv-meridb2.local (TNS V1-V3)
SYS        srv-meridb2.local   10-SEP-20 05.00.06.684649 PM   LOGON      SYSDBA     rman@srv-meridb2.local (TNS V1-V3)
SYS        srv-meridb2.local   10-SEP-20 05.00.11.985163 PM   LOGON      SYSDBA     rman@srv-meridb2.local (TNS V1-V3)
SYS        srv-meridb2.local   10-SEP-20 05.23.07.049371 PM   LOGON      SYSDBA     sqlplus@srv-meridb2.local (TNS V1-V3)

24 rows selected.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
10-SEP-20 05.35.34.997748 PM +03:00

 
 
SYMPTOMS
 
if there are large number of Unified Auditing records in the underlying tables of the SYS.UNIFIED_AUDIT_TRAIL view then any query is to be issued against that view will be extremely slow producing high CPU and IO load on the database.

SQL> set timing on
SQL> select count(*) from unified_audit_trail;
^Cselect count(*) from unified_audit_trail
                     *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 05:28:40.57

 
For any Oracle Database 12c that is monitored through the Oracle Enterprise Manager Cloud Control 13c and The Oracle Enterprise Manager Cloud Control Agent 13c Release 3, two default metrics are using the SYS.UNIFIED_AUDIT_TRAIL view while they are executing. These metrics are the Failed Logins and the Failed Logins (Historical) metrics. The metrics are executed on a regular base in the monitored database – every 30 minutes for The Failed Logins and every 12 hours for the Failed Logins (Historical).

Bearing in mind the slowness of the queries against the view and regular executions of the metrics based on that view the extra heavy load will be caused in the database. See LINK for details.

 
 
SOLUTION
 
The Unified Auditing records must be purged regularly. To clean up the audit data follow these steps

(1) Get a number of rows before cleanup. If there are large number of Unified Auditing records the query must hang.

 -- Query the view

SQL> set timing on
SQL> select count(*) from unified_audit_trail;
^Cselect count(*) from unified_audit_trail
                     *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 05:28:40.57

-- or query one of view's table directly

SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM AUDSYS."CLI_SWP$8d34b5cd$1$1";

  COUNT(*)
----------
   5095793

Elapsed: 00:00:50.49

 
(2) Optional. If the Unified Auditing is not required anymore you can disable it by the next command

-- Get audit policies list before disabling 

SET LINES 300
SET PAGES 999
COL USER_NAME FOR A10 
COL POLICY_NAME FOR A20 
COL ENABLED_OPT FOR A11 
COL SUCCESS FOR A7
COL FAILURE FOR A7
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME  POLICY_NAME          ENABLED_OPT SUCCESS FAILURE
---------- -------------------- ----------- ------- -------
ALL USERS  ORA_SECURECONFIG     BY          YES     YES

-- Disable default audit policy
 
SQL> NOAUDIT POLICY ORA_SECURECONFIG;

Noaudit succeeded.

-- Confirm that it's disabled

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES;

no rows selected

NOTE: Before purging Unified Auditing records in a database of release of 12.1.0.1.0 it’d better temporarily disable the default ORA_SECURECONFIG policy. Otherwise, during the Unified Auditing records clean up no one will be able to login to the database. All new connections that will try to establish sessions to the database during the cleanup operation will hang until the cleanup operation ends.
 
(3) Get your session id (SID) before starting data clean up

SET TIMING ON
SELECT DISTINCT SID FROM V$MYSTAT;
       SID
----------
       409

 
(4) Purge the Unified Auditing data. You can either purge all data or just part of it.

-- To purge all Unified Auditing records

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => FALSE);
END;
/

-- To purge all Unified Auditing data but the last day (SYSDATE-1)

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; 

EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( -
     AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
     LAST_ARCHIVE_TIME => SYSDATE - 1);
	 
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
     AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -   
     USE_LAST_ARCH_TIMESTAMP => TRUE);

 
NOTE: Before purging Unified Auditing records in a database of release of 12.1.0.1.0 it’d better temporarily disable the default ORA_SECURECONFIG policy. Otherwise, during the Unified Auditing records clean up no one will be able to login to the database. All new connections that will try to establish sessions to the database during the cleanup operation will hang until the cleanup operation ends.
 
(5) Optional. Montior the clean up session. The SID from step 2 is required

SET LINES 300 
SET PAGES 999
COL SID FOR 9999
COL CHN FOR 9999
COL EVENT FOR A30
COL SQL_FULLTEXT FOR A130 WORD_WRAPPED

SELECT S.SID,
       S.SERIAL#,
       S.SQL_ID,
       S.SQL_CHILD_NUMBER CHN,
       S.EVENT,
       S.LAST_CALL_ET ELAPSED_SEC,
       F.SQL_FULLTEXT
FROM
       V$SESSION S,
       V$SQL F
WHERE
       S.SQL_ID=F.SQL_ID AND
       S.SQL_CHILD_NUMBER=F.CHILD_NUMBER AND
       S.SID='&SID';
Enter value for sid: 409
old  13:        S.SID='&SID'
new  13:        S.SID='409'

  SID    SERIAL# SQL_ID          CHN EVENT                          ELAPSED_SEC SQL_FULLTEXT
----- ---------- ------------- ----- ------------------------------ ----------- ---------------------------------------------------------
  305        229 bzy6v0pnm0736     0 db file sequential read                 81 delete from "CLI_SWP$8d34b5cd$1$1"  where max_scn < :1

 
(6) Optional. Schedule the cleanup job. In this example, Unified Auditing records are kept for the last 1 day.

-- Create cleanup job 

EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( -
     AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
     LAST_ARCHIVE_TIME => SYSDATE - 1);
     --LAST_ARCHIVE_TIME => SYSDATE);

EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (-
     AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
     AUDIT_TRAIL_PURGE_INTERVAL => 1, -
     AUDIT_TRAIL_PURGE_NAME => 'CLEANUP_UNIFIED_AUDIT_RECORDS', -
     USE_LAST_ARCH_TIMESTAMP => TRUE);
     --USE_LAST_ARCH_TIMESTAMP => FALSE);
audit_trail_purge_interval
The interval, in hours, at which the clean up procedure is called. 
A lower value means that the cleanup is performed more often.

last_archive_time
The TIMESTAMP value based on which the audit records or files should be deleted. 
This indicates the last time when the audit records or files were archived.

 
To view the cleanup job executions.

COL JOB_NAME FOR A30
COL STATUS FOR A12
COL ACTUAL_START_DATE FOR A45

SELECT JOB_NAME, 
       STATUS, 
       ACTUAL_START_DATE
FROM
       DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE
      JOB_NAME='CLEANUP_UNIFIED_AUDIT_RECORDS'
ORDER BY ACTUAL_START_DATE;
JOB_NAME                       STATUS       ACTUAL_START_DATE
------------------------------ ------------ ---------------------------------------------
CLEANUP_UNIFIED_AUDIT_RECORDS  SUCCEEDED    10-SEP-20 09.19.23.193045 PM EUROPE/MOSCOW

 
To drop the cleanup job

-- SQL> EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB(AUDIT_TRAIL_PURGE_NAME => 'CLEANUP_UNIFIED_AUDIT_RECORDS');

PL/SQL procedure successfully completed.

 
(7) Make sure records were cleaned up/reduced for the specified period.

SET TIMING ON
SELECT COUNT(*) FROM SYS.UNIFIED_AUDIT_TRAIL;
  COUNT(*)
----------
      7451

Elapsed: 00:03:29.45

 
(8) Optional. If any Audit Policies were disabled before clean up then enable it again (only if you wish to use the Unified Auditing feature).

-- Get audit policies list before enabling

SET LINES 300
SET PAGES 999
COL USER_NAME FOR A10 
COL POLICY_NAME FOR A20 
COL ENABLED_OPT FOR A11 
COL SUCCESS FOR A7
COL FAILURE FOR A7
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

no rows selected

-- Enable default audit policy
 
SQL> AUDIT POLICY ORA_SECURECONFIG;

Audit succeeded.

-- Confirm that it's enabled

SQL> SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

USER_NAME  POLICY_NAME          ENABLED_OPT SUCCESS FAILURE
---------- -------------------- ----------- ------- -------
ALL USERS  ORA_SECURECONFIG     BY          YES     YES

 
 
REFERENCES
 
Auditing with Unified Auditing
Master Note For Database Unified Auditing (Doc ID 2351084.1)
How To Enable The New Unified Auditing In 12c ? (Doc ID 1567006.1)
Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database (Doc ID 2063340.1)
How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)
How to schedule UNIFIED_AUDIT_TRAIL purge job (Doc ID 2231430.1)
Unified Auditing: Unable To Clean Audit Trail Based On Last Archive Timestamp (Doc ID 1956200.1)
SELECT * FROM UNIFIED_AUDIT_TRAIL Blocks Other Sessions (Doc ID 2196171.1)

 
 

Version  : 17:39 11.09.2020
Database : 12.1.0.1
OEM      : 13.3.0.0.0