8 Replies Latest reply: Mar 16, 2011 5:34 PM by 706614 RSS

    the format of Audit log file

    665482
      We have a perl script to extract data from Audit log files(Oracle Database 10g Release 10.2.0.1.0) which have format as bellow.

      Audit file /u03/oracle/admin/NIKKOU/adump/ora_5037.aud
      Oracle Database 10g Release 10.2.0.1.0 - Production
      ORACLE_HOME = /u01/app/oracle/product/10.2.0
      System name:     Linux
      Node name:     TOYDBSV01
      Release:     2.6.9-34.ELsmp
      Version:     #1 SMP Fri Feb 24 16:54:53 EST 2006
      Machine:     i686
      Instance name: NIKKOU
      Redo thread mounted by this instance: 1
      Oracle process number: 22
      Unix process pid: 5037, image: oracleNIKKOU@TOYDBSV01

      Sun Jul 27 03:06:34 2008
      ACTION : 'CONNECT'
      DATABASE USER: 'sys'
      PRIVILEGE : SYSDBA
      CLIENT USER: oracle
      CLIENT TERMINAL:
      STATUS: 0

      After we update the db from Release 10.2.0.1.0 to Release 10.2.0.4.0, the format of Audit log file had been changed to something likes below.

      Audit file /u03/oracle/admin/NIKKOU/adump/ora_1897.aud
      Oracle Database 10g Release 10.2.0.4.0 - Production
      ORACLE_HOME = /u01/app/oracle/product/10.2.0
      System name:     Linux
      Node name:     TOYDBSV01
      Release:     2.6.9-34.ELsmp
      Version:     #1 SMP Fri Feb 24 16:54:53 EST 2006
      Machine:     i686
      Instance name: NIKKOU
      Redo thread mounted by this instance: 1
      Oracle process number: 21
      Unix process pid: 1897, image: oracle@TOYDBSV01

      Tue Oct 14 10:30:29 2008
      LENGTH : '135'
      ACTION :[7] 'CONNECT'
      DATABASE USER:[3] 'SYS'
      PRIVILEGE :[6] 'SYSDBA'
      CLIENT USER:[0] ''
      CLIENT TERMINAL:[7] 'unknown'
      STATUS:[1] '0'


      Because we have to rewrite the perl script, could anyone tell us where we can find the manual to describe the format of the Audit log file.
        • 1. Re: the format of Audit log file
          damorgan
          Why do you "need" to rewrite the Perl script?

          It seems to me, given the capabilities of the product to which this forum is dedicated, your Perl script is a reinvention of the wheel. I'd suggest putting it away and gain capabilities far beyond those you can code yourself.

          PS: Re-engineering Oracle is a violation of your license agreement. That, likely, includes the audit records you are asking about.
          • 2. Re: the format of Audit log file
            665482
            Thank you for your answer.

            I will try to explain the question more detailed.

            One of our custormers want to store the messages in the audit log file into a table (something as below
            create table audit_log (
            Timestamp varchar(26),
            ACTION varchar(1000),
            DB_USER varchar(26),
            ....... )
            )

            Maybe someone will think that setting audit_trail=DB is a better solution. But our customer prefered this one.
            So we wrote the perl script to extact the information from audit log file and write them into this table.

            We can roughly guess the format of the audit log file(of Oracle Database 10g Release 10.2.0.4.0) . But we hope to have something to confirm our guess.

            By the way , we have no will to re-engineering the oracle . It is far beyond our ability.
            • 3. Re: the format of Audit log file
              damorgan
              Due to the Oracle license issue I stated previously I doubt anyone will help you beyond advising you to stop your reverse engineering work unless you confirm with Oracle it is not a license violation.

              Your customer wanting to store records in a table is hardly a reason to put yourselves at risk or to write a single line of Perl.

              Oracle is more than capable of putting audit records into a table. You can then simply use a SQL statement, or a Materialized View, to extract what you wish.
              • 4. Re: the format of Audit log file
                user12603
                Trying to understand the construction of an Oracle audit trail file is certainly NOT attempting to "reverse-engineer Oracle". That is quite obviously an extreme exaggeration. If it were the case, then one could also say that reading the published documentation on (for example) DBA_AUDIT_TRAIL is also "reverse-engineering Oracle".

                To answer the original question: "There is no published documentation from Oracle Corporation on the format of an Oracle audit trail file." You have to figure it out yourself - and it changes without notice (as it did between 10.2.0.3 and 10.2.0.4).
                • 5. Re: the format of Audit log file
                  damorgan
                  Figuring it out could well be interpreted a reverse engineering. I would suggest getting advice from an attorney before making such statements. I am not speaking for Oracle in any manner. But I have observed situations where "figuring it out" resulted in being served with court papers.
                  • 6. Re: the format of Audit log file
                    Tammybednar-Oracle
                    Oracle publishes views of the audit trail data. You can find a list of the views for the 11.1 database here:
                    http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/auditing.htm#BCGIICFE

                    The audit trail does not really change between patchsets as that would constitute underlying structure changes and right now, the developers are not allowed to change the underlying structure of tables in patchsets. But, we can change what may be displayed in a column from patchset to patchset. For example, we are getting ready to update the comment$text field to display more information like dblinks and program names.

                    I personally don't like overloading the comment$text field like that, but sometimes when you need the information, that is the only choice except to wait for the next major release :)

                    As for the output of the audit log files, those can change between patchsets because of bugs that were found and some changes to support Audit Vault. My apologies out there for anyone that is reading the audit files written to the OS directly, I would recommend using the views.

                    Hope that helps. Tammy
                    • 7. Re: the format of Audit log file
                      736904
                      Hey !!

                      The Number in [] tell the byte-length of parameter values.
                      For example "ACTION :[7] 'CONNECT'" means values for ACTION tag is of 7 char long.

                      The change was done to make this audit data more compatible with Audit Vault (An Awesome product to manage audit data of multiple Databases)
                      • 8. Re: the format of Audit log file
                        706614
                        Hi user10434794:

                        To answer your question specifically, the format of the audit files an Oracle database writes is not documented in public documentation. As user tbednar said earlier, Oracle does reserve the right to change this format without notice, and has done so a few times recently. Ignoring for a moment the legality of doing so, "figuring out" the format is something that's a bad idea simply because you can never fully figure it out. There will always be corner cases and changes over time. Therefore, it is best to stick to documented interfaces, such as the DBA_AUDIT_TRAIL view.