Auditing on Physical Standby Database with XML

Turning on auditing on a physical standby database may be preferred when you want to audit the queries against the read only standby database. Obviously it's not possible to keep audit records on standby database by setting audit_trail to DB or DB_EXTENDED. Because the database is read-only and the aud$ table is just being synchronized with the primary aud$. So the DB or DB_EXTENDED setting on a physical standby database doesn't cause any auditing. When we open an 11g physical standby database in Read Only mode, the AUDIT_TRAIL parameter changes to OS automatically. What you'll see on the alert log will be:

AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access

Setting the audit_trail to OS works for a standby database but there's no option as OS_EXTENDED, so if you need sql_text and sql_bind info, this will not be the solution for you. The only solution that records sql_text and sql_bind will be XML_EXTENDED. This setting will produce xml files under audit_file_dest.

The problem in this point will be querying the audit records. Here comes the view V$XML_AUDIT_TRAIL which reads the XML files on the fly into memory when queried. Database used to maintain a txt file (adx_.txt) which keeps track of the XML audit files for this reason. However, starting with 11.2.0.2 the list of the xml audit files is dynamically obtained when V$XML_AUDIT_TRAIL is queried.

In the MOS note 879305.1 (ORA-00600[kzaxgfl:lowmem] when ADX file has dangling entries) this change was stated as:

Starting with release 11.2.0.2, the usage of the ADX file has been removed. The ADX global, generic file is no longer maintained in the adump destination. Consequently, starting with this release, the manual maintenance of the ADX file is no longer needed.

More precisely: From 11.2.0.2, the xml index file is dynamically created before creating the xml file list.

This file is specific to the process as opposed to generic index file present is older versions and will have the PID suffix with the index filename.

Once the operation is done, this file is deleted.

This file should never be manually managed - removing or changing the process specific file manually can result in the error investigated in this note or in ORA 600 [KZAXGFL:IDX]

Note 1392161.1 Queries on XML Audit Trail Views Crashes With: ORA 600 [KZAXGFL:IDX]

Obviously it was not a brilliant idea to keep xml file list in a text file which cannot track the changes when you delete xml files from filesystem in time, thus cause ORA-600 errors. Hereby this behaviour is changed with 11.2.0.2.

Anyway, to get to the main point, we can query V$XML_AUDIT_TRAIL on the standby to find out the audit records. In my case, i prepared a shell script running on the primary which connects to primary database, queries the standby V$XML_AUDIT_TRAIL via db link and insert into a table on primary, then deletes the xml files on the standby server. With this way it's possible to filter the audit data on the xml files when inserting into database. It's also possible to create need-based indexes which will speed up your audit queries or partition the table for administrative purposes.