A crucial aspect of any database audit concept is the management and maintenance of audit trails. Depending on the defined audit policies and the database activity, an audit trail can grow relatively quickly. Oracle Multitenant environments increase the operational effort because the root container and each PDB uses their own audit trail. Ok, for a simple CDB with 2-3 PDB this effort is manageable, but what about CDB’s with 40 or more PDB’s?
Let’s look into the different possibilities.
Common Purge Job for all PDB’s
Oracle allow’s to initiate an audit trail clean up for all PDB’s using dbms_audit_mgmt.clean_audit_trail
procedure by specifying dbms_audit_mgmt.container_all
. The following example initiate a clean up in the root container for all PDB’s without considering the last archive timestamp.
ALTER SESSION SET CONTAINER=cdb$root; BEGIN dbms_audit_mgmt.clean_audit_trail( audit_trail_type => dbms_audit_mgmt.audit_trail_unified, container => dbms_audit_mgmt.container_all, use_last_arch_timestamp => false); END; /
The problem with this method are the closed PDB’s. First let’s check how much audit records we do have per PDB.
COLUMN con_id FORMAT 999999 COLUMN count FORMAT 99999 COLUMN name FORMAT a8 SELECT v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count FROM cdb_unified_audit_trail u FULL OUTER JOIN v$containers v ON u.con_id = v.con_id GROUP BY v.con_id, v.name, v.open_mode ORDER BY v.con_id; CON_ID NAME OPEN_MODE COUNT ====== ======== =========== ===== 1 CDB$ROOT READ WRITE 644 2 PDB$SEED READ ONLY 0 3 PDB1 MOUNTED 0 4 PDB2 READ WRITE 36 5 PDB3 MOUNTED 0 6 PDBSEC READ WRITE 27
Running the clean up on this container database will end with an ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB.
BEGIN dbms_audit_mgmt.clean_audit_trail( audit_trail_type => dbms_audit_mgmt.audit_trail_unified, container => dbms_audit_mgmt.container_all, use_last_arch_timestamp => false); END; / BEGIN * ERROR at line 1: ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204 ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137 ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409 ORA-06512: at line 2
The error ORA-46273 is confusing. When we check the audit records, we see that the entries have been effectively be deleted. Here it would be more understandable if Oracle simply issue a warning rather than an error.
COLUMN con_id FORMAT 999999 COLUMN count FORMAT 99999 COLUMN name FORMAT a8 SELECT v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count FROM cdb_unified_audit_trail u FULL OUTER JOIN v$containers v ON u.con_id = v.con_id GROUP BY v.con_id, v.name, v.open_mode ORDER BY v.con_id; CON_ID NAME OPEN_MODE COUNT ====== ======== =========== ===== 1 CDB$ROOT READ WRITE 5 2 PDB$SEED READ ONLY 0 3 PDB1 MOUNTED 0 4 PDB2 READ WRITE 1 5 PDB3 MOUNTED 0 6 PDBSEC READ WRITE 1
The same does apply when we run the clean up task as a job. The job will always fail if one PDB is in MOUNT stat. This is annoying when monitoring scheduler jobs.
SQL> BEGIN 2 DBMS_SCHEDULER.RUN_JOB(job_name => '"AUDSYS"."TVD_TEST"'); 3 END; 4 / BEGIN * ERROR at line 1: ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204 ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137 ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_ISCHED", line 242 ORA-06512: at "SYS.DBMS_SCHEDULER", line 566 ORA-06512: at line 2
There is an other issue related to dbms_audit_mgmt.clean_audit_trail
in Oracle 12.1 to 18c. The procedure does create a wrong scheduler job. The container_all is not set for the scheduler job, which results in the job running only in the root container. The issue is addressed in Oracle bug 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL. A bugfix is available for Oracle 12.1.0.2, 12.2.0.1 and 18.0.0.0. Alternatively you can workaround this issue by manually create a scheduler job to purge the audit trail rather than using dbms_audit_mgmt.clean_audit_trail
. The issue is permanently fixed in Oracle 19.0.0.0.
dbms_audit_mgmt or Scheduler Job
Considering the above problems, it seems better to directly create a regular scheduler job for deleting audit trails. All right, this does work as expected, but these kind of jobs will never be visible in cdb_audit_mgmt_cleanup_jobs view. Technically this is not a problem, but it is good practice when audit-related clean up jobs are visible where they should.
The following example creates a regular scheduler job.
BEGIN dbms_scheduler.create_job( job_name => 'TVD_AUDIT_PURGE_JOB', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN dbms_audit_mgmt.clean_audit_trail( audit_trail_type => dbms_audit_mgmt.audit_trail_unified, container => dbms_audit_mgmt.container_current, use_last_arch_timestamp => false); END;', start_date => trunc(sysdate)+(2/24), repeat_interval => 'FREQ=HOURLY;INTERVAL=12', enabled => true, comments => 'Create an audit purge job for unified audit'); END; /
Insights when creating audit purge job via dbms_scheduler:
- Job is not visible in cdb_audit_mgmt_cleanup_jobs
- Start date can be defined when the job is created
- Job can be created in any schema, which has the appropriate rights.
The following example does create an audit trail clean up job for unified audit trail using dbms_audit_mgmt
and adjusting the start time to 01:00 with dbms_scheduler
.
BEGIN -- Create regular purge job dbms_audit_mgmt.create_purge_job( audit_trail_type => dbms_audit_mgmt.audit_trail_unified, audit_trail_purge_interval => 12, audit_trail_purge_name=> 'tvd_unified_audit_purge_job', container => dbms_audit_mgmt.container_current, use_last_arch_timestamp => true); -- Adapt start date of dbms_audit_mgmt purge job dbms_scheduler.set_attribute ( name => 'tvd_unified_audit_purge_job', attribute => 'start_date', value => trunc(sysdate) + ( 1 / 24 ) ); END; /
Insights when creating audit purge job via dbms_audit_mgmt
:
- Job is visible in cdb_audit_mgmt_cleanup_jobs
- Start date can not be defined when the job is created with
dbms_audit_mgmt
. The start date has to be changed later usingdbms_scheduler.set_attribute
. This also applies to other adjustments to the job. - For Oracle 18c and 19c the job will be created in the AUDSYS schema. In Oracle 12c and earlier it will be created in the SYS schema.
Audit Window
In the above examples I did set use_last_arch_timestamp=>true. This means that only audit trails older than the archive timestamp will be deleted. The archive timestamp is usually set by the Oracle audit vault agent, when it reads the audit records. But what happens a third party tool like splunk collects the audit data? These tools will generally just read the data without setting a corresponding archive timestamp. Oracle will then never delete old audit records. Therefor it is crucial, that the archive timestamp is set by the tool as soon as the data as been collected. Additionally to this, it is good practice to define an audit window. This is nothing else than set up a job which regularly set an archive timestamp e.g. set the timestamp to sysdate-30
. This way all audit records older than 30 days will be removed. The database is thus always nicely tidied up. :-The following example creates such an archive timestamp job. Every 12 hours the archive timestamp is set to sysdate-30
.
BEGIN dbms_scheduler.create_job( job_name =>'tvd_unified_audit_timestamp_job', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN dbms_audit_mgmt.set_last_archive_timestamp( audit_trail_type => dbms_audit_mgmt.audit_trail_unified, container => dbms_audit_mgmt.container_current, last_archive_time => sysdate - 30); END;', start_date => trunc(sysdate)+(2/24), repeat_interval => 'FREQ=HOURLY;INTERVAL=12', enabled => true, comments => 'Create an regularly archive timestamp for unified audit'); END; /
Conclusion
The administration of audit records works well in Oracle Multitenant environments as well. dbms_audit_mgmt
helps with the automatic and manual management of audit trails. Nevertheless, there are a few issues and best practices to consider:
- Audit data should always be stored in a separate tablespace. However, depending on the Oracle version, there may be issues related to the relocation of the audi trail. For example, not all partitions are moved correctly, LOB and index partitions will continue to be created in the old tablespace. See also bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions or MOS note 2428624.1 Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions
- Adjusting the partition size or the interval when a new audit partition has to be created, has be done according to the audit quantity and database activity..
- The different jobs for the clean up as well as the setting of the archive timestamp should run in the same scheme. This increases the transparency. As of Oracle 18c <
dbms_audit_mgmt
creates the jobs under the user AUDSYS. Older versions usually use the SYS user
- In Oracle Multitenant environments a central clean up job is the easiest, but makes no sense, depending on the state of the PDB’s it always finishes with an error. Here it is recommended to define one job for each CDB / PDB. In this way, the frequency of the deletion can be individually adapted to the database activity and compliance requirements of the particular PDB.
- So far we just discussed unified audit. If the database does run in pure unified mode there is no need to define a housekeeping for the legacy audit trails (AUD$, FGA$ etc). Although it is a good practice to define a minimal housekeeping for the legacy audit trail.
- To define a time window in which audit records are keep in the database is usually a easy method to hold a certain level of audit information in the database. Third party tools like splunk do have enough time to collect the relevant information.
- For successful database audit concept it is crucial to define the different users and roles who can access or maintain audit information.
References
Some links related to this blog post:
- Oracle® Database PL/SQL Packages and Types Reference 19c DBMS_AUDIT_MGMT
- Master Note For Oracle Database Auditing [1299033.1]
- Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions [2428624.1]
- Bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions
- Bug 22859443 – CONTAINER_ALL IS IGNORED IN CREATE_PURGE_JOB
- Patch 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL (Patch)