RE: Large sys.aud$ - External Table Solution?
- From: "Newman, Christopher" <cjnewman@xxxxxxxxxxxxx>
- To: <mwf@xxxxxxxx>, <Tom.Terrian.ctr@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Dec 2008 11:22:06 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Terrian, Thomas J Mr CTR DLA J6DIB
Sent: Monday, December 08, 2008 7:33 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: Large sys.aud$ - External Table Solution? - Newman, Christopher
