How to move and query audit log data?

2010-11 update:
11gR2 offers sys.dbms_audit_mgmt package, so manually moving audit log is more or less unnecessary.
Ref: http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60security-176069.html

2010-03 update:
I consider it best practice to set AUDIT_TRAIL to TRUE on all databases from the very beginning. 
As long as you don't do auditing (i.e. type the command "audit <something>"), there's no 
performance overhead. The reason for this suggestion is that because the parameter can only be 
changed after a database bounce, leaving it as TRUE all the time offers the convenience when you 
need auditing, even just for a short period of time, as in case of troubleshooting. When you're 
done, type "noaudit <something>".



Goal: Create a separate tablespace to archive sys.aud$ data into. This is not the same as redirecting 
the location where Oracle writes audit data to, although that would have a nice side effect that 
a new tablespace is ASSM by default so there's no worry about freelist contention (SYSTEM tablespace 
is MSSM and you're not allowed to increase aud$'s freelists).


sqlplus / as sysdba

create tablespace audit_data ...
create user audit_owner ... default tablespace audit_data quota unlimited on audit_data;

grant create session, select_catalog_role to audit_owner;
grant select on aud$ to audit_owner;

create table audit_owner.audit_data compress as select * from sys.aud$ where 1=2;
--With license for partitioning, create it as a table range-partitioned on ntimestamp#
create table audit_owner.audit_data
partition by range (ntimestamp#) (
partition p201309 values less than (to_date('201310','yyyymm')),
partition p201310 values less than (to_date('201311','yyyymm')),
partition p201311 values less than (to_date('201312','yyyymm')),
partition p201312 values less than (to_date('201401','yyyymm'))
) compress
as select * from sys.aud$ where 1=2;
--Later, when more partitions are needed, e.g.:
--alter table audit_owner.audit_data add partition p201401 values less than (to_date('201402','yyyymm'));

--The view below is optional, just to make queries easier. It's modifed from dba_audit_trail in 10gR2. 
--Note the EXTENDED_TIMESTAMP part.
create view audit_owner.audit_trail as
select spare1           OS_USERNAME,
       userid           USERNAME,
       userhost,
       terminal,
       cast (
           (from_tz(ntimestamp#,'00:00') at local) as date) TIMESTAMP,
       obj$creator      OWNER,
       obj$name         OBJ_NAME,
       aud.action#      ACTION,
       act.name         ACTION_NAME,
       new$owner        NEW_OWNER,
       new$name         NEW_NAME,
       decode(aud.action#,
              108 /* grant  sys_priv */, null,
              109 /* revoke sys_priv */, null,
              114 /* grant  role */, null,
              115 /* revoke role */, null,
              auth$privileges) OBJ_PRIVILEGE,
       decode(aud.action#,
              108 /* grant  sys_priv */, spm.name,
              109 /* revoke sys_priv */, spm.name,
              null)     SYS_PRIVILEGE,
       decode(aud.action#,
              108 /* grant  sys_priv */, substr(auth$privileges,1,1),
              109 /* revoke sys_priv */, substr(auth$privileges,1,1),
              114 /* grant  role */, substr(auth$privileges,1,1),
              115 /* revoke role */, substr(auth$privileges,1,1),
              null)     ADMIN_OPTION,
       auth$grantee     GRANTEE,
       decode(aud.action#,
              104 /* audit   */, aom.name,
              105 /* noaudit */, aom.name,
              null)     AUDIT_OPTION,
       ses$actions      SES_ACTIONS,
       logoff$time      LOGOFF_TIME,
       logoff$lread     LOGOFF_LREAD,
       logoff$pread     LOGOFF_PREAD,
       logoff$lwrite    LOGOFF_LWRITE,
       decode(aud.action#,
              104 /* audit   */, null,
              105 /* noaudit */, null,
              108 /* grant  sys_priv */, null,
              109 /* revoke sys_priv */, null,
              114 /* grant  role */, null,
              115 /* revoke role */, null,
              aud.logoff$dead)
                        LOGOFF_DLOCK,
       comment$text     COMMENT_TEXT,
       sessionid,
       entryid,
       statement        STATEMENTID,
       returncode,
       spx.name         PRIVILEGE,
       clientid         CLIENT_ID,
       auditid          ECONTEXT_ID,
       sessioncpu       SESSION_CPU,
       cast ((from_tz(ntimestamp#,'00:00') at local) as date) /* syntax error in orig dba_audit_trail */
                        EXTENDED_TIMESTAMP,
       proxy$sid        PROXY_SESSIONID,
       user$guid        GLOBAL_UID,
       instance#        INSTANCE_NUMBER,
       process#         OS_PROCESS,
       xid              TRANSACTIONID,
       scn,
       to_nchar(substr(sqlbind,1,2000))      SQL_BIND,
       to_nchar(substr(sqltext,1,2000))      SQL_TEXT
from audit_owner.audit_data aud, /* the only real change from dba_audit_trail; changed from sys.aud$ */
     system_privilege_map spm, system_privilege_map spx,
     STMT_AUDIT_OPTION_MAP aom, audit_actions act
where   aud.action#     = act.action    (+)
  and - aud.logoff$dead = spm.privilege (+)
  and   aud.logoff$dead = aom.option#   (+)
  and - aud.priv$used   = spx.privilege (+);

Now you can query dba_audit_trail and audit_owner.audit_trail using the same select list:

col os_username for a15
col userhost for a30
col terminal for a30
--Can add comment_text if client port is needed to find the match in listener log
select os_username, userhost, terminal, timestamp, action_name, returncode 
from dba_audit_trail 
where username = 'APPUSER' and os_username != 'oracle'
order by timestamp;
select os_username, userhost, terminal, timestamp, returncode, action_name, comment_text 
from audit_owner.audit_trail 
where username = 'APPUSER' and os_username != 'oracle'
order by timestamp;

Note: Unfortunately if audit_data is partitioned, audit_trail view won't prune partitions. See Appendix 2.

Example: Find who attempted 5 times to login to APPUSER and caused the account to be locked
         that has a profile limiting failed_login_attempts to 5:

SQL> select os_username, userhost, terminal, timestamp, returncode from audit_owner.audit_trail
  2  where username = 'APPUSER' and os_username = 'oracle' and action_name = 'LOGON' order by timestamp;

OS_USERNAME USERHOST                  TERMINAL TIMESTAMP         RETURNCODE
----------- ------------------------- -------- ----------------- ----------
john.d      clientmachine.example.com          20080330 10:46:53       1017 <-- "invalid username/password; logon denied"
john.d      clientmachine.example.com          20080330 10:46:53       1017
john.d      clientmachine.example.com          20080330 10:46:54       1017
john.d      clientmachine.example.com          20080330 10:46:54       1017
john.d      clientmachine.example.com          20080330 13:25:09       1017
john.d      clientmachine.example.com          20080330 13:25:09      28000 <-- "the account is locked"
john.d      clientmachine.example.com          20080330 13:25:10      28000
john.d      clientmachine.example.com          20080330 13:25:10      28000

SQL> select comment_text from audit_owner.audit_trail
  2  where username = 'APPUSER' and os_username = 'oracle' and timestamp = '20080530 13:25:09' and action_name = 'LOGON';

COMMENT_TEXT
----------------------------------------------------------------------------------------------------
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.111.111.32)(PORT=18981))
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.111.111.32)(PORT=18985))

Then go to the database listener log for more info about the clients using ports 18981 and 18985 at or 
slightly before that moment. You can also refer to Note:352389.1 (Finding the source of failed login 
attempts) for more interesting info.

Example: Sometimes comment_text is empty (e.g. connection was made by OS user oracle on the server). 
You can get the terminal name and look it up in `last' if UNIX/Linux:

SQL> select os_username, username, userhost, terminal, timestamp from dba_audit_trail where action_name in ('CREATE TABLE', 'DROP TABLE') and obj_name = 'USERS';

OS_USERNAME     USERNAME                       USERHOST                      TERMINAL   TIMESTAMP
--------------- ------------------------------ ----------------------------- ---------- -----------------
oracle          LARRYELLISON                   clientmachine.example.com     pts/2      20080722 10:58:09
oracle          LARRYELLISON                   clientmachine.example.com     pts/2      20080722 10:58:09

$ last | grep pts/2 | more
...
oracle   pts/2        clientmachine.ex Tue Jul 22 10:12 - 14:24  (04:12)

On the OS, you focus on the connection to the second terminal and use command such as `last' to look for 
the terminal connection time range, which should encompass the timestamp in dba_audit_trail. Then find 
the user on clientmachine.example.com at that time. The last step may need some clever thinking: 
* nbtstat -A <IP> #Check its NetBIOS info, very useful if messenger service is left running
* ask team members one by one (normally only DBAs have oracle access to the DB server)
* search on company Intranet for the workstation #Some companies' Intranet may even have account passwords!
* net view \\clientmachine.example.com #See if he has some network shares; IP address works too
* psloggedon \\clientmachine.example.com #Download psloggedon from Sysinternals or Microsoft.com




Appendix 1: Script to transfer audit records

#!/bin/bash
#transfer_audit_record.sh: Move data from sys.aud$ to audit_owner.audit_data

export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin

sqlplus -L / as sysdba <<EOF
begin
 insert into audit_owner.audit_data select * from aud\$ where ntimestamp# < to_date(to_char(sysdate,'yyyy-mm'),'yyyy-mm');
 delete from aud\$ where ntimestamp# < to_date(to_char(sysdate,'yyyy-mm'),'yyyy-mm');
 execute immediate 'noaudit ...';  --disable audit; add more lines as needed
 execute immediate 'alter table aud$ move';
 execute immediate 'audit ...';    --enable audit
 --the line below is only needed for 10g
 --execute immediate 'alter index i_aud1 rebuild online';
end;
/
exit
EOF

Appendix 2: Example of query historical audit records

--make sure partition pruning occurs (Pstart and Pstop in plan table show numbers not from 1 to the last partition); use audit_data table not audit_trail view
explain plan for
select spare1 os_username, userhost, from_tz(ntimestamp#,'00:00') at local timestamp, returncode from audit_owner.audit_data
where userid='APPUSER' and ntimestamp# between to_date('20090415','yyyymmdd') and to_date('20090416','yyyymmdd') order by 3;
@?/rdbms/admin/utlxpls
--Or: set autot trace explain, explain plan for ..., set autot off
--if verified, run the query

If a subset needs to be queried often, create a table temporarily:
--alter session set db_file_multiblock_read_count = 128;
create table audit090415 as
 select /*+ parallel (t 4) */ *
 from audit_owner.audit_data t
 where ntimestamp# between to_date('20090415','yyyymmdd') and to_date('20090416','yyyymmdd');
--optionally
create view audit090415_v as
[same text as for audit_owner.audit_trail except line below]
from audit090415 aud,
[same text as for audit_owner.audit_trail except line above]

Unless you query often, it's not worth it creating index(es) on audit_data.