禁用Oracle 11gR2 DataGuard默认审计功能
NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
audit_trail string DB大家好！最近公司服务器system表空间越来越大，segments logical reads，segments physical reads 越来越高，awr报告里体现的对象是AUD$,很明显可以定位是开启审计功能导致。下面我来给大家介绍如何停止DataGuard架构的审计功能：
1.检查应用连接停止状态
SQL> select USERNAME,STATUS from v$session where username is not null;
USERNAME STATUS
------------------------------ --------------
SYS ACTIVE
2.停止主库传输进程
alter system set LOG_ARCHIVE_DEST_STATE_2=defer scope=both;
3.停止备库实时应用
alter database recover managed standby database cancel;
4.查看当前审计状态
SQL> show parameter audit_trail;
NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
audit_trail string DB
5.修改主库审计参数
alter system set audit_trail=none scope=spfile;
6.查询主库审计表数据量
SQL> select count(*) from SYS.AUD$;
COUNT(*)
----------------
21545536
7.停止主库数据库
shutdown immediate;
8.启动主库数据库
startup;
9.清理审计表数据
truncate table SYS.AUD$;
10.检查审计参数设置是否正确
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------- ----------- ------------------------------
audit_trail string NONE
11.查看备库审计参数
SQL> show parameter audit_trail;
NAME TYPE VALUE
-------------------------------- ----------- ------------------------------
audit_trail string OS