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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29812751/viewspace-1717143/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29812751/viewspace-1717143/