Handling unified auditing spillover files on the standby-site

Switching to Oracle Unified Auditing may produce lots of data when e.g. auditing activities of the SYS-user. I.e. according the documentation you can do the following to audit similarly as in traditional auditing with audit_sys_operations=TRUE:

SQL> CREATE AUDIT POLICY TOPLEVEL_ACTIONS ACTIONS ALL ONLY TOPLEVEL;
SQL> AUDIT POLICY TOPLEVEL_ACTIONS BY SYS;

REMARK1: You may check the Blog on traditional SYS-auditing here
REMARK2: Tests done in this Blog were done with Oracle 19.9. Auditing toplevel operations were not possible before 19c.

With unified auditing all data is written in the database – except when there’s no possibility to write to the database. If the database is e.g. not open then spillover files are written to the OS. By default files with extension bin are written to $ORACLE_BASE/audit/$ORACLE_SID.

REMARK: Container databases have a sub-directory per pluggable DB there, but all examples in this Blog are for a non-container-DB.

E.g.

oracle@boda1:/u01/app/oracle/audit/TISMED/ [TISMED] ls -ltr | tail -10
-rw-------. 1 oracle oinstall    6656 Jan  8 15:30 ora_audit_031.bin
-rw-------. 1 oracle oinstall   98304 Jan  8 15:35 ora_audit_0251.bin
-rw-------. 1 oracle oinstall    1536 Jan  8 15:57 ora_audit_0253.bin
-rw-------. 1 oracle oinstall 3140096 Jan  8 15:57 ora_audit_019.bin
-rw-------. 1 oracle oinstall    2048 Jan  8 15:57 ora_audit_0256.bin
-rw-------. 1 oracle oinstall    3584 Jan  8 15:58 ora_audit_0252.bin
-rw-------. 1 oracle oinstall    4096 Jan  8 15:58 ora_audit_0238.bin
-rw-------. 1 oracle oinstall    2048 Jan  8 15:58 ora_audit_024.bin
-rw-------. 1 oracle oinstall  314368 Jan  8 15:58 ora_audit_030.bin
-rw-------. 1 oracle oinstall    2048 Jan  8 15:58 ora_audit_029.bin
oracle@boda1:/u01/app/oracle/audit/TISMED/ [TISMED] 

You get an idea what’s in the files when doing a strings on them:

oracle@boda1:/u01/app/oracle/audit/TISMED/ [TISMED] strings ora_audit_030.bin | head -20
ANG Spillover Audit File
ORAAUDNG
oracle
boda2.localdomain
pts/0
(TYPE=(DATABASE));(CLIENT ADDRESS=((ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.6)(PORT=18890))));	
PUBLIC
[email protected] (TNS V1-V3)
`)',
26801
SYSOPR
PUBLIC:
ORAAUDNG
oracle
boda2.localdomain
pts/0
(TYPE=(DATABASE));(CLIENT ADDRESS=((ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.6)(PORT=18892))));	
PUBLIC
[email protected] (TNS V1-V3)
26813
oracle@boda1:/u01/app/oracle/audit/TISMED/ [TISMED] 

The data in the spillover-files are visible in the database when querying e.g. unified_audit_trail as the following simple example shows:

SQL> select count(*) from unified_audit_trail;

  COUNT(*)
----------
    237306

SQL> !mv /u01/app/oracle/audit/TISMED /u01/app/oracle/audit/TISMED_tmp

SQL> select count(*) from unified_audit_trail;

  COUNT(*)
----------
    233816

SQL> !mv /u01/app/oracle/audit/TISMED_tmp /u01/app/oracle/audit/TISMED

SQL> select count(*) from unified_audit_trail;

  COUNT(*)
----------
    237328

SQL> 

I.e. moving the spillover directory to a new name results in showing less data in UNIFIED_AUDIT_TRAIL. The view UNIFIED_AUDIT_TRAIL is a UNION ALL of the view v$unified_audit_trail and the table audsys.aud$unified (you may check $ORACLE_HOME/rdbms/admin/catuat.sql on what the metadata of UNIFIED_AUDIT_TRAIL is). The data of the spillover-files comes from the view v$unified_audit_trail:

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
      3501

SQL> select count(*) from audsys.aud$unified;

  COUNT(*)
----------
    233845

SQL> !mv /u01/app/oracle/audit/TISMED /u01/app/oracle/audit/TISMED_tmp

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
	 0

SQL> select count(*) from audsys.aud$unified;

  COUNT(*)
----------
    233849

SQL> !mv /u01/app/oracle/audit/TISMED_tmp /u01/app/oracle/audit/TISMED

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
      3501

SQL> 

Oracle provided the following possibility to load the data of the spillover-files into the database:

SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

The issue I want to talk about in this blog are spillover files on standby databases. Standby databases are usually running in MOUNT-mode and hence are not writable. I.e. all audit-data produced on the standby-DBs go to spillover-files. If you haven’t switched over to the standby database for a while and loaded the spillover-files to the database then there could be quite a lot of data in the spillover-directory. I saw systems with e.g. 10GB of data in the spillover-directory, especially when doing rman-backups on the standby-site.

That causes 3 issues:

1.) If you move your audit data on your primary database to history tables then those history-tables may not contain the full truth, because audit-records of spillover-files on the standby-DBs are not visible in the history tables.
2.) After a switchover a query on unified_audit_trail may be very slow, because reading spillover-files is slower than reading from the database.
3.) Loading the spillover files after a switchover to the new primary database may take a long time and causes the SYSAUX-tablespace to grow significantly.

So the question is: How to handle the spillover-files on the standby-DB?

One possibility is to just remove them regularly on the OS if your security rules can afford to lose auditing data of standby-DBs. If that’s not possible then you may move the spillover-files regularly to a backup-location, so that you can restore those if necessary. The third alternative I’ve tested was to copy the spillover-files from the standy DB to the primary DB and load them there. That worked for me, but 2 things have to be considered:

1. spillover-files are not unique over all DBs in data guard. I.e. don’t just copy files over to the primary. You have to move away the primnary spillover directory first and restore it when the data has been loaded.
2. The procedure is not documented and has to be confirmed by Oracle Support

E.g. here the process to copy and load spillover-files from standby to primary and load them:

1. Move away the primary spillover-folder

SQL> select count(*) from v$unified_audit_trail uview;

  COUNT(*)
----------
       239

SQL> !mv /u01/app/oracle/audit/${ORACLE_SID} /u01/app/oracle/audit/${ORACLE_SID}_tmp

SQL> select count(*) from v$unified_audit_trail uview;

  COUNT(*)
----------
	 0

2. Copy spillover-files from standby to primary

SQL> !scp -p -r standby:/u01/app/oracle/audit/${ORACLE_SID} /u01/app/oracle/audit
SQL> select count(*) from v$unified_audit_trail uview;

  COUNT(*)
----------
       101

SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

PL/SQL procedure successfully completed.

SQL> select count(*) from v$unified_audit_trail uview;

  COUNT(*)
----------
	24

–> Not all files were loaded. That’s normal.

3. Backup the current spillover-directory and restore the original spillover-directory

SQL> !mv /u01/app/oracle/audit/${ORACLE_SID} /u01/app/oracle/audit/${ORACLE_SID}_standby

SQL> !mv /u01/app/oracle/audit/${ORACLE_SID}_tmp /u01/app/oracle/audit/${ORACLE_SID}

SQL> select count(*) from v$unified_audit_trail uview;

  COUNT(*)
----------
       239

4. Backup the spillover-folder on the standby-site. I.e. do the following command on the standby-host

SQL> !mv /u01/app/oracle/audit/${ORACLE_SID} /u01/app/oracle/audit/${ORACLE_SID}_backup

5. Copy the remaining standby-files not loaded back to the standby host. I.e. do the following command on the standby-host

SQL> !scp -p -r primary:/u01/app/oracle/audit/${ORACLE_SID}_standby /u01/app/oracle/audit/${ORACLE_SID}
SQL> select count(*) from v$unified_audit_trail uview;

  COUNT(*)
----------
	24

REMARK: Above procedure is not 100% correct, because it doesn’t consider spillover-files produced while doing above steps.

Summary: Many people with standby-DBs and Unified Auditing active may not have realized that there are potential issues with spillover-files at the standby-site. Those spillover files on standby have to be considered. The easiest method is to just remove them regularly on the OS if that has been approved by the security team.