禁用11gR2 DataGuard默认审计功能
NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
audit_trail string DB
--------------------------------- ----------- ------------------------------
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
------------------------------ --------------
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
--------------------------------- ----------- ------------------------------
audit_trail string DB
5.修改主库审计参数
alter system set audit_trail=none scope=spfile;
6.查询主库审计表数据量
SQL> select count(*) from SYS.AUD$;
COUNT(*)
----------------
21545536
----------------
21545536
7.停止主库数据库
shutdown immediate;
8.启动主库数据库
startup;
9.清理审计表数据
truncate table SYS.AUD$;
10.检查审计参数设置是否正确
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------- ----------- ------------------------------
audit_trail string NONE
------------------------------- ----------- ------------------------------
audit_trail string NONE
11.查看备库审计参数
SQL> show parameter audit_trail;
NAME TYPE VALUE
-------------------------------- ----------- ------------------------------
audit_trail string OS
-------------------------------- ----------- ------------------------------
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
-------------------------------- ----------- ------------------------------
audit_trail string NONE
16.查看审计表数据，应该和主库一致
SQL> select count(*) from SYS.AUD$;
COUNT(*)
----------
21545536
----------
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#;
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#;
select SEQUENCE#,first_time,next_time,APPLIED,DELETED,THREAD# from v$archived_log order by SEQUENCE#,THREAD#;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24558279/viewspace-753846/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
