Unified Audit Policy Scope and SYSAUX Growth
After upgrading to 19c, unified auditing is enabled and mixed-mode auditing is off. Compliance requires auditing all successful and unsuccessful logons, all DDL by the local application user HR_APP in PDB_HR, and all changes to the AUDIT_ADMIN role. A DBA in the root container runs:
CREATE AUDIT POLICY POL_SEC ACTIONS LOGON, CREATE TABLE, ALTER TABLE, DROP TABLE;
AUDIT POLICY POL_SEC;
No purge job is configured. Two weeks later, UNIFIED_AUDIT_TRAIL shows logons for SYS and SYSTEM but none for HR_APP. SYSAUX has grown from 20 GB to 70 GB, and users intermittently receive ORA-02002; the database sometimes stalls for a few seconds when the audit trail cannot write. The audit trail is still in SYSAUX. The DBA suggests setting AUDIT_TRAIL=DB to reduce SYSAUX growth.
1. Why are HR_APP's logons missing, and why is AUDIT_TRAIL=DB the wrong response?
Read the worked answer
The policy was created and enabled only in the root container. In a CDB, a unified audit policy enabled in root applies to the root unless it is enabled as a common policy with CONTAINER=ALL (or enabled separately in each PDB). HR_APP is a local user in PDB_HR, so its logons are not covered. Also, the single policy mixes logon auditing with DDL; that is not the cause of the missing rows, but it makes later analysis harder. AUDIT_TRAIL is a legacy parameter; with unified auditing enabled it is ignored and does not redirect or reduce the unified trail. Setting it to DB creates a false sense of control and may confuse the team. The correct fix is to enable the policy in all containers (or create a PDB-scoped policy) and verify with a test logon.
2. Rewrite the auditing configuration to meet the three requirements with clear scope.
Read the worked answer
Create separate policies: (1) CREATE AUDIT POLICY POL_LOGON ACTIONS LOGON; enabled CONTAINER=ALL; this captures successful and unsuccessful logons by default. (2) CREATE AUDIT POLICY POL_HR_DDL ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE; then AUDIT POLICY POL_HR_DDL BY HR_APP; in PDB_HR (a local user must be handled in its PDB). (3) CREATE AUDIT POLICY POL_ROLE_CHG ACTIONS CREATE ROLE, ALTER ROLE, DROP ROLE, GRANT, REVOKE; enabled in root with CONTAINER=ALL, because AUDIT_ADMIN is a common role. Use WHENEVER SUCCESSFUL and WHENEVER UNSUCCESSFUL explicitly if both are required; otherwise both are audited by default. Avoid auditing all DDL for all users, which would add noise and volume.
3. How would you stop SYSAUX growth and make the audit trail operationally safe?
Read the worked answer
Move the unified audit trail out of SYSAUX into a dedicated tablespace: DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS'); (create the tablespace first, and do it in a maintenance window). Then create a purge job: DBMS_AUDIT_MGMT.CREATE_AUDIT_TRAIL_PURGE_JOB with an interval, or a daily job calling CLEAN_AUDIT_TRAIL for unified audit. Set a last archive timestamp only if records are archived externally. Size AUDIT_TBS for peak audit volume; monitor unified trail growth and V$SYSAUX_OCCUPANTS. ORA-02002 means the audit write failed; after moving and purging, confirm no sessions are failing and check the alert log. Do not truncate AUDSYS tables directly; use the supported package.