oracle数据库审计功能
audit_trail参数设置启动或是关闭数据库的审计功能
有none
os
db
dbextended
xml
xmlextended
note
当参数为dbextended或是xmlextended的时候会在aud$表中额外记录sql bind 和sql text clobtype的字段
If the database was started in readonly 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 readonly access
SMON: enabling cache recovery
Database Characterset is WEMSWIN
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 bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers 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表空间会存在 空间不足情况怎么自动删除随后很多国外的专家分别给出了自己的建议（如PattersonJoelSteve GardinerIggy FernandezChristopher等等）看如下邮件信息
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的方法
）Archive and purge aud$ table
>创建新的表空间创建新的归档表 创建过程创建调度计划或是crontab执行检查结果
我实验结果如下
eg
[root@oracleone ~]# su oracle
[oracle@oracleone ~]$ sqlplus / as sysdba
SQL*Plus: Release Production on Fri Aug ::
Copyright (c) Oracle All rights reserved
Connected to an idle instance
SQL> startup
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
Database opened
SQL> select sysdate from dual;
SYSDATE
AUG
SQL> col name for a
SQL> select file#name from v$datafile;
FILE# NAME
/opt/app/oracle/RHYS/systemdbf
/opt/app/oracle/RHYS/sysauxdbf
/opt/app/oracle/RHYS/undotbsdbf
/opt/app/oracle/RHYS/usersdbf
/opt/app/oracle/RHYS/testdbf
SQL> create tablespace arch_tbs datafile /opt/app/oracle/RHYS/arch_tbsdbf size M;
Tablespace created
SQL> set pagesize
SQL> select to_char(dbms_metadataget_ddl(TABLEAUD$)) FROM DUAL;
TO_CHAR(DBMS_METADATAGET_DDL(TABLEAUD$))
CREATE TABLE SYSAUD$
( SESSIONID NUMBER NOT NULL ENABLE
ENTRYID NUMBER NOT NULL ENABLE
STATEMENT NUMBER NOT NULL ENABLE
TIMESTAMP# DATE
USERID VARCHAR()
USERHOST VARCHAR()
TERMINAL VARCHAR()
ACTION# NUMBER NOT NULL ENABLE
RETURNCODE NUMBER NOT NULL ENABLE
OBJ$CREATOR VARCHAR()
OBJ$NAME VARCHAR()
AUTH$PRIVILEGES VARCHAR()
AUTH$GRANTEE VARCHAR()
NEW$OWNER VARCHAR()
NEW$NAME VARCHAR()
SES$ACTIONS VARCHAR()
SES$TID NUMBER
LOGOFF$LREAD NUMBER
LOGOFF$PREAD NUMBER
LOGOFF$LWRITE NUMBER
LOGOFF$DEAD NUMBER
LOGOFF$TIME DATE
COMMENT$TEXT VARCHAR()
CLIENTID VARCHAR()
SPARE VARCHAR()
SPARE NUMBER
OBJ$LABEL RAW()
SES$LABEL RAW()
PRIV$USED NUMBER
SESSIONCPU NUMBER
NTIMESTAMP# TIMESTAMP ()
PROXY$SID NUMBER
USER$GUID VARCHAR()
INSTANCE# NUMBER
PROCESS# VARCHAR()
XID RAW()
AUDITID VARCHAR()
SCN NUMBER
DBID NUMBER
SQLBIND CLOB
SQLTEXT CLOB
OBJ$EDITION VARCHAR()
) PCTFREE PCTUSED INITRANS MAXTRANS NOCOMPRESS LOGGING
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS 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 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT))
LOB (SQLTEXT) STORE AS BASICFILE (
TABLESPACE SYSTEM ENABLE STORAGE IN ROW CHUNK RETENTION
NOCACHE LOGGING
STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS
PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT))
SQL>
SQL> CREATE TABLE sysAUD$_ARCH
( SESSIONID NUMBER NOT NULL ENABLE
ENTRYID NUMBER NOT NULL ENABLE
STATEMENT NUMBER NOT NULL ENABLE
TIMESTAMP# DATE
USERID VARCHAR()
USERHOST VARCHAR()
TERMINAL VARCHAR()
ACTION# NUMBER NOT NULL ENABLE
RETURNCODE NUMBER NOT NULL ENABLE
OBJ$CREATOR VARCHAR()
OBJ$NAME VARCHAR()
AUTH$PRIVILEGES VARCHAR()
AUTH$GRANTEE VARCHAR()
NEW$OWNER VARCHAR()
NEW$NAME VARCHAR()
SES$ACTIONS VARCHAR()
SES$TID NUMBER
LOGOFF$LREAD NUMBER
LOGOFF$PREAD NUMBER
LOGOFF$LWRITE NUMBER
LOGOFF$DEAD NUMBER
LOGOFF$TIME DATE
COMMENT$TEXT VARCHAR()
CLIENTID VARCHAR()
SPARE VARCHAR()
SPARE NUMBER
OBJ$LABEL RAW()
SES$LABEL RAW()
PRIV$USED NUMBER
SESSIONCPU NUMBER
NTIMESTAMP# TIMESTAMP ()
PROXY$SID NUMBER
USER$GUID VARCHAR()
INSTANCE# NUMBER
PROCESS# VARCHAR()
XID RAW()
AUDITID VARCHAR()
SCN NUMBER
DBID NUMBER
SQLBIND CLOB
SQLTEXT CLOB
OBJ$EDITION VARCHAR()
)
tablespace arch_tbs
nologging;
Table created
