oracle 审计表满,由于开启审计AUD$表导致system表空间满了~

于是进行详细的查询：

查看system表空间里磁盘占用较大的segment

SELECT *

FROM (SELECT BYTES, segment_name, segment_type, owner

FROM dba_segments

WHERE tablespace_name = 'SYSTEM'

ORDER BY BYTES DESC)

WHERE ROWNUM < 10;

select A.SEGMENT_NAME,SUM(A.BYTES)/1024/1024/1024 GB,A.SEGMENT_TYPE

from dba_extents a,v$datafile b,v$tablespace C

where b.TS#=C.TS# AND C.NAME='SYSTEM' AND A.FILE_ID=B.FILE#

GROUP BY A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY GB DESC;

select DECODE( PARTITION_NAME, NULL, segment_name, segment_name || ':' || PARTITION_NAME) segment_name ,

segment_type, nvl(initial_extent, 0) initial_extent, nvl(next_extent, 0) next_extent,

nvl(extents, 0) extents, nvl(bytes, 0) bytes, Owner, nvl(max_extents, 0)max_extents

from   dba_segments

where  tablespace_name = 'SYSTEM'

order by bytes desc;

可看到sysytem表空间中，AUD$表达到了161个G，占system的98%以上。

审计结果存储在system表空间中的SYS.AUD$表中，通过视图dba_audit_trail查看

show parameter audit;

NAME                 TYPE    VALUE

-------------------- ------- ------------------------------

audit_file_dest      string  /usr/oracle/admin/mpj/adump

audit_sys_operations boolean FALSE

audit_syslog_level   string

audit_trail          string  DB

audit_trail       说明数据库开启的是DB级别的审计。

发现数据库启用了DB级别的审计功能，相关审计级别如下：

None：是默认值，不做审计；

DB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；

DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；

OS：将audit trail 记录在操作系统文件中，文件名由audit_file_dest参数指定；

XML：10g里新增的。

select * from dba_stmt_audit_opts;

发现都为by_access。

查看触发oracle审计的策略：

1)

By session / By Access

by session对每个session中发生的重复操作只记录一次

by access对每个session中发生的每次操作都记录，而不管是否重复。

2)Whenever successful/ Whenever not successful

Whenever successful表示操作成功以后才记录下来。

Whenever not successful表示操作失败后才记录下来。

关闭审计命令：alter system set audit_trail=none scope=spfile;  该参数为静态参数，需要重启数据库

由于移动不让关闭审计

解决方法：

一、

导出AUD$表：

exp  system/oracle   dumpfile=20150629.dmp directory=arch_tables  logfile=mpj_20150629.log TABLES=AUD$

truncate表：truncate table AUD$；

resize system 表空间：

法一、

col file_id for 999

col file_name for a50;

col size_m for 9999;

col free_m for 9999;

col use_m for 9999;

select a.*,size_m-free_m use_m,'alter database datafile '||file_id||' resize '||ceil(size_m-free_m)||'m;' resize_file

from (select a.file_id, a.file_name,a.bytes/1024/1024 size_m, b.bytes/1024/1024 free_m from dba_data_files a, dba_free_space b

where a.file_id=b.file_id and b.tablespace_name=upper(trim('&ts_name')) group by a.file_id,a.file_name,a.bytes, b.bytes) a

order by file_id,ceil(size_m-free_m) desc;

法二、

select a.file#,

a.name,

a.bytes / 1024 / 1024 CurrentMB,

ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,

(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,

'alter database datafile ''' || a.name || ''' resize ' ||

ceil(HWM * a.block_size) / 1024 / 1024 || 'M;' ResizeCmd

from v$datafile a,

(SELECT file_id, MAX(block_id + blocks - 1) HWM

FROM DBA_EXTENTS

GROUP BY file_id) b

where a.file# = b.file_id(+)

And (a.bytes - HWM * a.block_size) >0

order by 5

法三：同学给的

select a.file#,

a.name,

c.tablespace_name,

round(a.bytes / 1024 / 1024) CurrentMB,

ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,

(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,

'alter database datafile ' || a.FILE# || ' resize ' ||

round(ceil(HWM * a.block_size) / 1024 / 1024 +5) || 'M;' ResizeCmd

from v$datafile a,

(SELECT file_id, MAX(block_id + blocks - 1) HWM

FROM DBA_EXTENTS

GROUP BY file_id) b,

dba_data_files c

where a.file# = b.file_id(+)

And (a.bytes - HWM * a.block_size) >0

and a.FILE#=c.file_id

and c.tablespace_name not in ('SYSTEM','SYSAUX')

and c.tablespace_name not like '%UNDO%'

order by 6 desc；

二、

将审计结果表从system表空间里移动到别的表空间上

1.手动迁移需停库

1)

alter table sys.aud$ move tablespace users;

alter table sys.aud$ move lob(sqlbind) store as( tablespace USERS);

alter table sys.aud$ move lob(SQLTEXT) store as( tablespace USERS);

alter index sys.I_AUD1 rebuild tablespace users；

2)

alter table AUDIT$ move tablespace users;

alter table AUDIT_ACTIONS move tablespace users;

alter table AUD$ move tablespace users;

alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users);

alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users);

alter index I_AUDIT rebuild online tablespace users;

alter index I_AUDIT_ACTIONS rebuild online tablespace users;

--可能修改值(index和lob index)

SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';

COLUMN_NAME                              INDEX_NAME

---------------------------------------- ------------------------------

SQLBIND                                  SYS_IL0000000384C00040$$

SQLTEXT                                  SYS_IL0000000384C00041$$

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';

INDEX_NAME

------------------------------

SYS_IL0000000384C00040$$

SYS_IL0000000384C00041$$

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';

INDEX_NAME

------------------------------

I_AUDIT

SQL>  SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';

INDEX_NAME

------------------------------

I_AUDIT_ACTIONS

2.自动迁移不需要停库

迁移前：查看所属表空间

SQL> select segment_name,tablespace_name from dba_segments where

2  segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',

3  'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');

SEGMENT_NAME                                                                      TABLESPACE_NAME

--------------------------------------------------------------------------------- ------------------------------

AUDIT_ACTIONS                                                                     SYSTEM

AUDIT$                                                                            SYSTEM

AUD$                                                                              SYSTEM

SYS_IL0000000384C00041$$                                                          SYSTEM

SYS_IL0000000384C00040$$                                                          SYSTEM

I_AUDIT_ACTIONS                                                                   SYSTEM

I_AUDIT                                                                           SYSTEM

DBMS_AUDIT_MGMT实现迁移

开始迁移

conn / as sysdba

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

audit_trail_location_value => 'USERS');

END;

/

使用

sysdba

登录数据库，使用如下脚本，将

SYS.AUD$

与

SYS.FGA_LOG$

迁移至

SYSAUX

表空间

：

BEGIN

DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$

audit_trail_location_value => 'SYSAUX');

END;

/

BEGIN

DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$

audit_trail_location_value => 'SYSAUX');

END;

/

验证DBMS_AUDIT_MGMT效果

SQL> select segment_name,tablespace_name from dba_segments where

2   segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',

3   'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');

SEGMENT_NAME                                                                      TABLESPACE_NAME

--------------------------------------------------------------------------------- ------------------------------

AUDIT_ACTIONS                                                                     SYSTEM

AUDIT$                                                                            SYSTEM

AUD$                                                                              USERS

SYS_IL0000000384C00041$$                                                          USERS

SYS_IL0000000384C00040$$                                                          USERS

I_AUDIT_ACTIONS                                                                   SYSTEM

I_AUDIT                                                                           SYSTEM

迁移完整解决方法：

1.创建表空间

create tablespace mpj

datafile '/oracle/OMT/admin/oradata/mpj01.dbf'  size 2G autoextend on;

SELECT table_name, tablespace_name

FROM dba_tables

WHERE table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY table_name;

2.aud$表移动到新tablespace

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

audit_trail_location_value => 'MPJ');

END;

/

3.sys用户procedure：

create or replace procedure

sp_trunc_audit_log is

begin

execute immediate

'truncate table aud$';

end;

授权：

grant execute on sp_trunc_audit_log to system;

4.system用户procedure：

create or replace procedure

sp_job_trunc_audit_log is

begin

sys.sp_trunc_audit_log;

end;

5.自动调度job

BEGIN

DBMS_SCHEDULER.CREATE_JOB

(

job_name   => 'day_trunc_audit_log',

job_type   => 'STORED_PROCEDURE',

job_action => 'SP_JOB_TRUNC_AUDIT_LOG',

start_date => sysdate,

repeat_interval => 'FREQ=DAILY; BYHOUR=04; BYMINUTE=05;INTERVAL=1',

enabled         => true,

comments        => 'every day truncate table audit log'

);

END;

SYS@MPJ>col segment_name format a30

SYS@MPJ>select segment_name,tablespace_name from dba_segments where segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$','AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');

SEGMENT_NAME                   TABLESPACE_NAME

------------------------------ ------------------------------

I_AUDIT_ACTIONS                SYSTEM

I_AUDIT                        SYSTEM

AUDIT_ACTIONS                  SYSTEM

AUDIT$                         SYSTEM

AUD$                           MPJ

SYS_IL0000000384C00041$$       MPJ

SYS_IL0000000384C00040$$       MPJ