ITPub博客

首页 > 数据库 > Oracle > sysaux表空间中,审计信息表aud$过大清理实例

sysaux表空间中,审计信息表aud$过大清理实例

原创 Oracle 作者:宋祖强 时间:2015-08-02 11:56:46 0 删除 编辑
巡检发现系统system表空间达到26G,正常情况下其大小最多几个G。这里发现其只要原因是aud$信息过大导致。
oracle 11g 是默认开启审计DB,在数据库运行过程中,会将审计信息放入aud$中,system表空间就会随之不断扩大,也会带来system表空间的性能下降。
一般处理: truncate table  aud$ 

处理过程:
    1.查看aud$所占空间,及审计开启状况
    2.按要求备份部分审计信息aud$_bak,在其他用户作为中转备份,这里100为例
    3.truncate table aud$
    4.将刚才备份的信息再插回aud$表中,insert into aud$
    5.删除中间表aud$_bak
    6.查看
aud$表数据量及system表空间现在大小
    7.将新的审计信息迁移至新表空间,不再放在system中

1.查看aud$,发现表已经太大。
  1.   select    
  2.      b.tablespace_name "表空间",  
  3.     b.bytes/1024/1024 "大小M",  
  4.     (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",  
  5.      substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率"  
  6.  from dba_free_space a,dba_data_files b  
  7.   where a.file_id=b.file_id  and  b.tablespace_name='SYSTEM'  
  8.  group by b.tablespace_name,b.file_name,b.bytes 
  9.  order by b.tablespace_name;   
  1. SQL> SELECT *  FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB  
  2.             FROM DBA_SEGMENTS  
  3.             WHERE TABLESPACE_NAME = 'SYSTEM'  
  4.             GROUP BY SEGMENT_NAME  
  5.             ORDER BY 2 DESC)  
  6.     WHERE ROWNUM < 10; 


 
2.审计已经开启

3. 这里要求保留最近100天的数据量,可以采用exp方式和create table 方式备份,这里采用create table方式
 注意:这里临时放在read用户下,要确认此用户所在表空间的剩余空间足够。
   Create  table  read.aud$_bak  as  SELECT  *  from  aud$   where  round((sysdate-cast(NTIMESTAMP#  AS DATE)),0) < 100;
4. 比较aud$_bak和aud$前100天的数据量
  select count(*) from aud$ where   round((sysdate-cast(NTIMESTAMP#  AS DATE)),0) < 100;

  s
elect count*from  read.aud$_bak
5. t
runcate table aud$ ;
   select  count(*) from aud$ ;
6.
 Inset into aud$ select * from read.aud$_bak;
  commit;
7.
 查看表空间的使用情况
  
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024/1024  From dba_segments t  WHERE t.tablespace_name = 'SYSTEM'
   
GROUP BY t.owner,t.segment_name  ORDER BY SUM(bytes) desc
8. 
创建新的审计专用表空间audittbs 
   
create tablespace audittbs  datafile  '/jydata/jydb2data/jyoracledb2/audit01.dbf'
    size 10000m   
autoextend on   next 50m maxsize 20480m  extent management local ;
9.迁移表空间
       SQL>conn  /as sysdba

SQL>alter table aud$ move tablespace audittbs;

SQL>alter index i_aud$ rebuild online  tablespace  audittbs;

SQL> alter table audit$  move tablespace  audittbs;

SQL> alter index i_audit rebuild online  tablespace audittbs;

SQL> alter table audit_actions move  tablespace   audittbs;

SQL> alter index i_audit_actions rebuild  online  tablespace audittbs;
10.验证表aud$、audit$ 和audit_actions是否在新的审计表空间

表空间的移动有一个包就可以简单移动(11g)

  1. SQL>  BEGIN  
  2.     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(  
  3.     AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,   
  4.     AUDIT_TRAIL_LOCATION_VALUE => 'AUD_FILE');  
  5.     END;  
  6.     /  
        其中新的ADU_FILE为要移动的新表空间。
  1. SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME  
  2.      FROM DBA_TABLES  
  3.      WHERE TABLE_NAME = 'AUD$'  AND OWNER = 'SYS';  



============================================================================================================



二、以下为oracle审计相关介绍

2.1 审计
审计(Audit)用于监视用户所执行的数据库操作,审计记录可存在数据字典表(称为审计记录:存储在system表空间中的 SYS.AUD$表中,可通过视图dba_audit_trail查看)或操作系统审计记录中(默认位置为$ORACLE_BASE/admin/$ORACLE_SID/adump/).。默认情况下审计是没有开启的。
当数据库的审计是使能的,在语句执行阶段产生审计记录。审计记录包含有审计的操作、用户执行的操作、操作的日期和时间等信息。
不管你是否打开数据库的审计功能,以下这些操作系统会强制记录:用管理员权限连接Instance;启动数据库;关闭数据库。
2.1.1 Oracle审计功能
审计是对选定的用户动作的监控和记录,通常用于:
审查可疑的活动。例如:数据被非授权用户所删除,此时安全管理员可决定对该数据库的所有连接进行审计,以及对数据库的所有表的成功地或不成功地删除进行审计。
监视和收集关于指定数据库活动的数据。例如:DBA可收集哪些被修改、执行了多少次逻辑的I/O等统计数据。
2.1.2 ORACLE所允许的审计选择限于下列方面:
审计语句的成功执行、不成功执行,或者其两者。
对每一用户会话审计语句执行一次或者对语句每次执行审计一次。
对全部用户或指定用户的活动的审计。
2.1.3 审计相关的表安装
SQLPLUS> connect / AS SYSDBA
SQLPLUS> select * from sys.aud$; --没有记录返回
SQLPLUS> select * from dba_audit_trail; - 没有记录返回
如果做上述查询的时候发现表不存在,说明审计相关的表还没有安装,需要安装。


SQLPLUS> connect / as sysdba
SQLPLUS> @$ORACLE_HOME/rdbms/admin/cataudit.sql
审计表安装在SYSTEM表空间。所以要确保SYSTEM表空间又足够的空间存放审计信息。
安装后要重启数据库
2.1.4 将审计相关的表移动到其他表空间
由于AUD$表等审计相关的表存放在SYSTEM表空间,因此为了不影响系统的性能,保护SYSTEM表空间,最好把AUD$移动到其他的表空间上。可以使用下面的语句来进行移动:


sql>connect / as sysdba;
sql>alter table aud$ move tablespace;
sql>alter index i_aud1 rebuild online  tablespace ;
SQL> alter table audit$ move tablespace;
SQL> alter index i_audit rebuild online  tablespace  ;
SQL> alter table audit_actions move  tablespace ;
SQL> alter index i_audit_actions rebuild  online tablespace ;
2.1.5 truncate 或者 delete sys.aud$ 表
在delete 之前,可以先把aud$表exp备份一下,注意,不要直接exp,先创建一张临时表,然后将临时表exp。
sql>createtable audit_record tablespace users as select * from sys.aud$;
然后exp:
exptables=AUDIT_RECORD file=audit_record.dmp
最后delete 数据:
sql>delete from sys.aud$;
或者删除指定表的审计:
sql>delete from sys.aud$ whereobj$name='&table_nmae';
注意,delete 不会释放system表空间。 可以使用truncate table:
sql>truncate table sys.aud$
2.2 和审计相关的两个主要参数
2.2.1 Audit_sys_operations
SYS@dave2(db2)> show parameteraudit_file_dest
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/dave2/adump

2.2.2 Audit_trail
AUDIT_TRAIL enables or disables databaseauditing.
None:是默认值,不做审计;
DB:将audit trail 记录在数据库的审计相关表中,如aud$,审计的结果只有连接信息;
DB,Extended:这样审计结果里面除了连接信息还包含了当时执行的具体语句;
OS:将audit trail 记录在操作系统文件中,文件名由audit_file_dest参数指定;
XML:10g里新增的。
注:这两个参数是static参数,需要重新启动数据库才能生效。

2.3审计级别
当开启审计功能后,可在三个级别对数据库进行审计:Statement(语句)、Privilege(权限)、object(对象)。
2.3.1 Statement
语句审计,对某种类型的SQL语句审计,不指定结构或对象。比如audit table 会审计数据库中所有的create table,droptable,truncate table语句,alter session by cmy会审计cmy用户所有的数据库连接。
2.3.2 Privilege
权限审计,当用户使用了该权限则被审计,如执行grant selectany table to a,当执行了auditselect any table语句后,当用户a 访问了用户b的表时(如select * from b.t)会用到select any table权限,故会被审计。注意用户是自己表的所有者,所以用户访问自己的表不会被审计。
2.3.3 Object
对象审计,对一特殊模式对象上的指定语句的审计. 如审计on关键字指定对象的相关操作,如aduitalter,delete,drop,insert on cmy.t by scott; 这里会对cmy用户的t表进行审计,但同时使用了by子句,所以只会对scott用户发起的操作进行审计。
注意:Oracle没有提供对schema中所有对象的审计功能,只能一个一个对象审计,对于后面创建的对象,Oracle则提供on default子句来实现自动审计,比如执行audit drop on defaultby access;后,对于随后创建的对象的drop操作都会审计。但这个default会对之后创建的所有数据库对象有效,似乎没办法指定只对某个用户创建的对象有效,想比 trigger可以对schema的DDL进行“审计”,这个功能稍显不足
2.4审计的一些其他选项
2.4.1 by access / by session
by access 每一个被审计的操作都会生成一条audit trail。
by session 一个会话里面同类型的操作只会生成一条audit trail,默认为by session。
2.4.2 whenever [not] successful
whenever successful 操作成功(dba_audit_trail中returncode字段为0) 才审计,
whenever not successful 反之。省略该子句的话,不管操作成功与否都会审计。


2.5 和审计相关的视图
dba_audit_trail:保存所有的audit trail,实际上它只是一个基于aud$的视图。其它的视图dba_audit_session,dba_audit_object,dba_audit_statement都只是dba_audit_trail的一个子集
dba_stmt_audit_opts:可以用来查看statement审计级别的audit options,即数据库设置过哪些statement级别的审计。dba_obj_audit_opts,dba_priv_audit_opts视图功能与之类似
all_def_audit_opts:用来查看数据库用on default子句设置了哪些默认对象审计。


2.6取消审计
将对应审计语句的audit改为noaudit即可,
如audit sessionwhenever successful
对应的取消审计语句为noauditsession whenever successful;




2.5



 




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

请登录后发表评论 登录
全部评论

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    277130