RE: Large sys.aud$ - External Table Solution?

From: Newman, Christopher <cjnewman_at_uillinois.edu>
Date: Mon, 8 Dec 2008 11:22:06 -0600
Message-ID: <565F609E6D736D439837F1A1A797F34101C6F0DA@ADMINMAIL1.ui.uillinois.edu>

Another technique is to an external table to load the data. We're currently exploring the feasibility of this option. Currently, we spool a query involving sys.aud$ data from the targets, scp'ing that file over to the 'master', and concatenate the output onto a master.log file which continues to grow, then load that table. The thing I haven't figured out yet is how to keep the master table truncated to only include ~ 30 days worth.

This is pretty basic; I think it could be improved on so that the master.ksh runs just once, after all the targets have uploaded their data, hence recreating the audit data nightly.

The advantage of this approach is that a single table contains all auditing information for all databases in the enterprise, and can easily be queried as such thanks to the table containing the additional fields of hostname and instance.

At any rate, here's how we're doing it, please feel free to use/comment/point out any improvements/gotchas/etc:

Cron the following on the target DBs, which at the end calls a script on the 'master'. I haven't seen many scripts included in oracle-l posts, but I wanted to see if this will help anyone thinking along similar lines and possible provoke some discussion.

#!/bin/ksh


## -------------------------------------------------

## Nightly script to select auditing records from database and ship to
the mothership, then on the mothership, append to master file and create the external table. This script will also truncate the sys.aud$ of the database it is run on, after the data has been extracted. This script will run against each DB on the box and create a spool file named with the host, instance, timestamp, etc.
## ---------------------------------------------------
for i in `ps -ef | grep smon | grep -v grep | cut -d"_" -f3 | sort` do
export ORACLE_SID=$i
export ORACLE_HOME=`/bin/grep $i /var/opt/oracle/oratab|cut -f2 -d":"` echo $i $ORACLE_HOME
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<!

set linesize 240;
set colsep ","
set pagesize 0 echo off head off feedback off trims on; col OS_USERNAME format a12;
col USERNAME format a15;
col userhost format a40;
col timestamp format a25;
col instance_name format a8;
col action format 99999;
col action_name format a10;
col timestamp format a20;
col returncode format 9999;
col sessionid format 9999999999;
col os_process format a5;
col audit_opt new_value AUDIT_OPT;
select instance_name||'.'||host_name||'.'||sysdate||'.txt' as AUDIT_OPT from V\$INSTANCE;
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

spool /u02/orawork/audit/\&AUDIT_OPT;

select distinct a.OS_USERNAME, a.USERNAME, a.USERHOST, a.TIMESTAMP, a.ACTION, a.ACTION_NAME, a.RETURNCODE, a.SESSIONID, v.INSTANCE_NAME, v.HOST_NAME
from dba_audit_trail a, v\$instance v
where action_name='LOGON'
and username not in
('SYS','SYSTEM','ORACLE','DBSNMP','BAD_LOGIN','OPS\$ORACLE') order by a.timestamp;
SPOOL OFF;
exit;
!
done
echo 'EXPORTS COMPLETED'
## Still need to ADD truncate table sys.aud$; after SPOOL OFF; but
before exit
rm `find /u02/orawork/audit -type f -size 0` echo 'FILES OF ZERO SIZE DELETED'
scp /u02/orawork/audit/*.txt oracle:/var/oracle/u02/orawork/Blah/audit/ echo 'FILES HAVE BEEN SCPED TO MOTHERSHIP' rm /u02/orawork/audit/*
echo 'TODAYS FILES HAVE BEEN DELETED FROM REMOTE DB' echo 'NOW EXECUTING INSTANTIATION TASKS ON ORACLE' ssh mothership '/u01/app/oracle/chris/AUDIT/audit_master.ksh' echo 'ALL DONE, QUERY ORACLE.MASTER_AUDIT FOR ENTERPRISE AUDITING RECORDS' The master contains:

#!/bin/ksh


## -------------------------------------------------

## Nightly script to instantiate oracle.master_audit table
## ---------------------------------------------------
export PATH=/usr/sbin:/bin:/usr/bin:/usr/ccs/bin:/usr/ucb:/usr/local/bin basePath=$PATH
export ORACLE_HOME=/u01/app/oracle/product/10.2.0 export ORACLE_SID=PULSE
cat /var/oracle/u02/orawork/PULSE/audit/*.txt >> /var/oracle/u02/orawork/PULSE/audit/master_audit.log

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<!

set linesize 240;
set colsep ","
set pagesize 0 trims on;
col OS_USERNAME format a12;
col USERNAME format a15;
col userhost format a40;
col instance_name format a8;
col action format 99999;
col action_name format a10;
col timestamp format a20;
col returncode format 9999;
col sessionid format 9999999999;
col os_process format a5;

drop table oracle.master_audit;
alter session set nls_date_format='DD-MON-YY hh24:mi:ss';

CREATE TABLE oracle.master_audit (
OS_USERNAME VARCHAR2(255),
USERNAME VARCHAR2(30),
USERHOST VARCHAR2(128),
ACCESSED DATE,
ACTION VARCHAR2(128),
ACTION_NAME VARCHAR2(100),
RETURNCODE VARCHAR2(100),
SESSIONID VARCHAR2(100),
INSTANCE_NAME VARCHAR2(16),
HOST_NAME VARCHAR2(64)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY AUDIT_TRAIL
ACCESS PARAMETERS
(
records delimited by newline
BADFILE 'badfile'
DISCARDFILE 'discardfile'
LOGFILE 'logfile'
FIELDS TERMINATED BY ","
LRTRIM
MISSING FIELD VALUES ARE NULL
(OS_USERNAME, USERNAME, USERHOST, ACCESSED CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YY hh24:mi:ss", ACTION, ACTION_NAME, RETURNCODE, SESSIONID, INSTANCE_NAME, HOST_NAME)
)
LOCATION ('master_audit.log')
)
REJECT LIMIT UNLIMITED;
exit;
!
echo 'INSTANTION OF MASTER AUDIT TABLE COMPLETE' rm /var/oracle/u02/orawork/Blah/audit/*.txt echo 'TODAYS FILES DELETED FROM ORACLE'

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Terrian, Thomas J Mr CTR DLA J6DIB Sent: Monday, December 08, 2008 7:33 AM
To: oracle-l_at_freelists.org
Subject: Large sys.aud$

All, we have a requirement to keep 1 year worth of audit records. I am kicking around the idea of hourly moving all of the sys.aud$ records from each production database to a central database. That way it would keep sys.aud$ small for each database.

However, this will have its own set of complications......I would have to build new dba_audit views on the central database (in order to have a database name field), what happens when the structure of sys.aud$ changes between database versions, etc.

Has anyone tackled something like this already?

Another option would be to forget about the repository database idea .....instead hold 1 months worth of data online for each database and storing the other 11 months offline somewhere (maybe use RMAN for this?).

Again, any ideas?........

Thanks,
Tom Terrian

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 08 2008 - 11:22:06 CST