For a more up-to-date post about auditing and security, read about Unified Auditing. On a read-only standby, when in Pure mode, the audit trail is written to disk (as it cannot write to a read-only database!) in a binary format which can be shipped across to the Primary occasionally and loaded there using a standard command. Just put the audit in the correct directory and load it automagically.
Original Article
If your company has a passing interest in database security, you are probably running some sort of session auditing at the very least [audit session] (although this can also be useful for troubleshooting connectivity issues). There’s a reasonable chance you’re also running some level of object auditing, or even DML access auditing if your employer so dictates:
Check access/change of objects in the DB 1 select audit_option, success, failure 2 from dba_stmt_audit_opts 3 union 4 select privilege, success, failure 5 from dba_priv_audit_opts 6* order by 2,1
AUDIT_OPTION SUCCESS FAILURE -------------------------------------------------- ---------------------------------------- ----------------- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS ALTER TABLE BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS AUDIT SYSTEM BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE LIBRARY BY ACCESS BY ACCESS CREATE PROCEDURE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE TABLE BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS DIRECTORY BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS PUBLIC DATABASE LINK BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS USER BY ACCESS BY ACCESS CREATE JOB BY SESSION BY SESSION
And you’re probably writing it into a database table [AUDIT_TRAIL=’DB’]
so how does that work if you open a Dataguard database read only? You are writing into sys.aud$ on the Primary, and that table is replicated to the Standby. So what happens?
From Oracle 11G, if you are running Dataguard and opening the standby up for read access, you may not notice the line in the ALERT log which reads:
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
So it starts writing down O/S audit trail files for all of your auditing options (well, the session connect and DML audit options – you can’t run DDL in a r/o DB). You might want to go and see just how many files it has written to [audit_file_dest], as you may be surprised at just how many are in there.
You may, one day, either run out of space or (more worryingly) have so many millions of files that it causes a performance problem when Oracle access the O/S directory. You might want to think about some sort of periodic clean-up job.
Neil Chandler's DB Blog 
Leave a comment