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.


11 Responses so far.

  1. Dhiya says:

    I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.


    Best SEO Training Institute in Chennai

  2. Meghala says:

    Nice to see your blog post.. I really enjoyed by reading your blog post. Thanks a lot for sharing this with us.. Keep on sharing like this informative post.

    Seo Company in Chennai


  3. This blog having the details of Processes running. The way of runing is explained
    clearly. The content quality is really great. The full document is entirely
    amazing. Thank you very much for this blog.
    Web Designing Training in Chennai

  4. kalai says:

    Great tips, and awesome way to get exert tips from everyone – you are the master of content.

    Best Dot Net Training Institutes in Chennai

  5. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    Salesforce Training in Chennai

  6. Jeffy says:

    Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..!!
    seo company in india
    Digital Marketing Company in india

  7. Aasha says:

    This blog having the details of Processes running. The way of runing is explained clearly. The content quality is really great. The full document is entirely amazing. Thank you very much for this blog.

  8. Finding the time and actual effort to create a superb article like this is great thing. I’ll learn many new stuff right here! Good luck for the next post buddy..
    Isoft Innovation
    Isoft Innovations Facebook

  9. sathya says:

    Here i had read the content you had posted. It is much interesting so please keep update like this. Infact it will be useful for the beginners to develop their knowledge along with . I am expecting much more posts from you

    MSBI Training in Chennai

    Informatica Training in Chennai

  10. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Personal Installment Loans
    Payday Cash Advance loan
    Title Car loan
    Cash Advance Loan


  11. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic.


    Pawn Shop

    Pawn Loans

    Pawn Shops

    Pawn Loan

    Pawn Shop near me

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -