HOW TO TURN OFF ORACLE 12C UNIFIED AUDITING AND CLEAN UP ALL UNIFIED AUDITING RECORDS
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)
Database : 12.1.0.1
OEM : 13.3.0.0.0