oracle数据库审计功能

audit_trail：参数设置启动或是关闭数据库的审计功能：

有none，

db，extended

xml

xml,extended

note：

当参数为db，extended或是xml，extended的时候会在aud$表中额外记录sql bind 和sql text clob-type的字段。

If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

然后看一下数据库日志：

alter database open read only
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
SMON: enabling cache recovery
Database Characterset is WE8MSWIN1252
Opening with Resource Manager plan: DEFAULT_MAINTENANCE_PLAN

可以看到当数据库audit_tail参数设置为db，但是我依然使用open read only打开的话，那么会自动转到os，当正常启动后，那么会自动转到db状态。

可以查看audit_file_dest参数，进行查看os文件所在位置：

eg：
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/RHYS/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string OS
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2213816 bytes
Variable Size 251660360 bytes
Database Buffers 146800640 bytes
Redo Buffers 4345856 bytes
Database mounted.
Database opened.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/RHYS/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
SQL>

这里，我突然想起了一个老外（Nagaraj S)的问题，那就是如果审计内容非常大的话，那么system表空间会存在空间不足情况，怎么自动删除。随后，很多国外的专家分别给出了自己的建议（如，Patterson,Joel,Steve Gardiner,Iggy Fernandez,Christopher等等）看如下邮件信息：

Hello Gurus,

I have a task to purge aud$ table and it need to done in automated way

on every month. Please help on sharing the purge script to schedule in db

scheduler

-Naga

好了，然后我们看看老外有什么好的办法。首先看看Ulfet的方法：

1）Archive and purge aud$ table

>创建新的表空间，创建新的归档表，创建过程，创建调度计划或是crontab，执行检查结果。

我实验结果如下：

eg：
[root@oracle-one ~]# su - oracle
[oracle@oracle-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 16 02:43:02 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2213816 bytes
Variable Size 251660360 bytes
Database Buffers 146800640 bytes
Redo Buffers 4345856 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;
SYSDATE
---------
16-AUG-13
SQL> col name for a60
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------
1 /opt/app/oracle/RHYS/system01.dbf
2 /opt/app/oracle/RHYS/sysaux01.dbf
3 /opt/app/oracle/RHYS/undotbs01.dbf
4 /opt/app/oracle/RHYS/users01.dbf
5 /opt/app/oracle/RHYS/test.dbf
SQL> create tablespace arch_tbs datafile '/opt/app/oracle/RHYS/arch_tbs01.dbf' size 500M;
Tablespace created.
SQL> set pagesize 2000 
SQL> select to_char(dbms_metadata.get_ddl('TABLE','AUD$')) FROM DUAL;
TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','AUD$'))
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."AUD$"
( "SESSIONID" NUMBER NOT NULL ENABLE,
"ENTRYID" NUMBER NOT NULL ENABLE,
"STATEMENT" NUMBER NOT NULL ENABLE,
"TIMESTAMP#" DATE,
"USERID" VARCHAR2(30),
"USERHOST" VARCHAR2(128),
"TERMINAL" VARCHAR2(255),
"ACTION#" NUMBER NOT NULL ENABLE,
"RETURNCODE" NUMBER NOT NULL ENABLE,
"OBJ$CREATOR" VARCHAR2(30),
"OBJ$NAME" VARCHAR2(128),
"AUTH$PRIVILEGES" VARCHAR2(16),
"AUTH$GRANTEE" VARCHAR2(30),
"NEW$OWNER" VARCHAR2(30),
"NEW$NAME" VARCHAR2(128),
"SES$ACTIONS" VARCHAR2(19),
"SES$TID" NUMBER,
"LOGOFF$LREAD" NUMBER,
"LOGOFF$PREAD" NUMBER,
"LOGOFF$LWRITE" NUMBER,
"LOGOFF$DEAD" NUMBER,
"LOGOFF$TIME" DATE,
"COMMENT$TEXT" VARCHAR2(4000),
"CLIENTID" VARCHAR2(64),
"SPARE1" VARCHAR2(255),
"SPARE2" NUMBER,
"OBJ$LABEL" RAW(255),
"SES$LABEL" RAW(255),
"PRIV$USED" NUMBER,
"SESSIONCPU" NUMBER,
"NTIMESTAMP#" TIMESTAMP (6),
"PROXY$SID" NUMBER,
"USER$GUID" VARCHAR2(32),
"INSTANCE#" NUMBER,
"PROCESS#" VARCHAR2(16),
"XID" RAW(8),
"AUDITID" VARCHAR2(64),
"SCN" NUMBER,
"DBID" NUMBER,
"SQLBIND" CLOB,
"SQLTEXT" CLOB,
"OBJ$EDITION" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
LOB ("SQLBIND") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT))
LOB ("SQLTEXT") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT))

SQL> 
SQL> CREATE TABLE sys.AUD$_ARCH
2 ( SESSIONID NUMBER NOT NULL ENABLE,
3 ENTRYID NUMBER NOT NULL ENABLE,
4 STATEMENT NUMBER NOT NULL ENABLE,
5 TIMESTAMP# DATE,
6 USERID VARCHAR2(30),
7 USERHOST VARCHAR2(128),
8 TERMINAL VARCHAR2(255),
9 ACTION# NUMBER NOT NULL ENABLE,
10 RETURNCODE NUMBER NOT NULL ENABLE,
11 OBJ$CREATOR VARCHAR2(30),
12 OBJ$NAME VARCHAR2(128),
13 AUTH$PRIVILEGES VARCHAR2(16),
14 AUTH$GRANTEE VARCHAR2(30),
15 NEW$OWNER VARCHAR2(30),
16 NEW$NAME VARCHAR2(128),
SES$ACTIONS VARCHAR2(19),
17 18 SES$TID NUMBER,
19 LOGOFF$LREAD NUMBER,
20 LOGOFF$PREAD NUMBER,
21 LOGOFF$LWRITE NUMBER,
22 LOGOFF$DEAD NUMBER,
23 LOGOFF$TIME DATE,
24 COMMENT$TEXT VARCHAR2(4000),
25 CLIENTID VARCHAR2(64),
26 SPARE1 VARCHAR2(255),
27 SPARE2 NUMBER,
28 OBJ$LABEL RAW(255),
29 SES$LABEL RAW(255),
30 PRIV$USED NUMBER,
31 SESSIONCPU NUMBER,
32 NTIMESTAMP# TIMESTAMP (6),
33 PROXY$SID NUMBER,
34 USER$GUID VARCHAR2(32),
35 INSTANCE# NUMBER,
36 PROCESS# VARCHAR2(16),
37 XID RAW(8),
38 AUDITID VARCHAR2(64),
39 SCN NUMBER,
40 DBID NUMBER,
41 SQLBIND CLOB,
42 SQLTEXT CLOB,
43 OBJ$EDITION VARCHAR2(30)
44 ) 
45 tablespace arch_tbs
46 nologging;
Table created.