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

 

12.修改备库审计参数

alter system set audit_trail=none scope=spfile;

 

13.停止备库数据库

shutdown immediate;

 

14.启动备库数据库

startup;

 

15.检查备库审计参数设置是否正确

SQL> show parameter audit_trail;

NAME                              TYPE        VALUE
-------------------------------- ----------- ------------------------------
audit_trail                          string      NONE

 

16.查看审计表数据，应该和主库一致

SQL> select count(*) from SYS.AUD$;

  COUNT(*)
----------
   21545536

 

17.启用备库实时应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect FROM SESSION;

 

18.开启主库传输进程

alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both;

 

19.主库手动切换日志

alter system switch logfile;

 

20.检查主库归档传输状态

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select SEQUENCE#,first_time,next_time,APPLIED,DELETED,THREAD# from v$archived_log order by SEQUENCE#,THREAD#;

 

21.检查备库归档应用状态

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select SEQUENCE#,first_time,next_time,APPLIED,DELETED,THREAD# from v$archived_log order by SEQUENCE#,THREAD#;