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 "), 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 ". 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 #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 <