暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / 开启同步工具后Oracle数据库审计目录暴增

开启同步工具后Oracle数据库审计目录暴增

原创 Xiaofei Huangfu 2025-07-16
278

本文与我之前写过的同步工具导致Oracle ASM实例审计日志暴增场景类似。
适用范围
Oracle Database 11g+

问题概述
Oracle RAC 节点2数据库软件基础目录/u01使用率超过99%，并且在持续增加，应用连接数据库异常。

问题原因
节点2 数据库审计日志目录审计日志产生大量审计日志。根本原因是配置同步工具DRS后调整了数据库归档备份和删除策略。
【知识点】什么是DRS？

数据复制服务（Data Replication Service，简称DRS）是一种易用、稳定、高效、用于数据库实时迁移、数据库实时同步的云服务。
实时同步是指在不同的系统之间，将数据通过同步技术从一个数据源拷贝到其他数据库，并保持一致，实现关键业务的数据实时流动。
实时同步不同于迁移，迁移是以整体数据库搬迁为目的，而实时同步是维持不同业务之间的数据持续性流动。

解决方案
1、应急处理方式。备份并清理数据库审计日志。
2、/u01基础软件目录扩容。有100g扩容至300G。
3、调整数据库归档备份和删除策略。
分析过程：
1、检查目录使用率。
这套环境数据库服务器是AIX 7.1

# df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hdoracle     100.00      0.54   99%    10749     8% /u01
/dev/hd2           6.00      3.55   41%    51985     6% /usr
/dev/hd9var        4.00      0.85   79%     6616     4% /var
/dev/hd3          10.00      9.91    1%      886     1% /tmp
/dev/hd1           2.00      1.98    1%      142     1% /home
/dev/hdarch       200        80     60%     9025     2% /arch
/dev/lidump      2.00      2.00    1%        4     1% 
#

/u01目录是Oracle数据库软件基础目录，数据库软件和审计日志存储在该目录，该目录使用率已超过99%，并且持续增长。/arch是共享目录，用来存储数据库归档日志当前使用时60%。
2、检查审计日志

more /u01/app/oracle/admin/xfdb/adump/xfdb2_ora_17105260_20250704112601877496143795.aud

Audit file /u01/app/oracle/admin/xfdb/adump/xfdb2_ora_17105260_20250704112601877496143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1
System name:	AIX
Node name:	host02
Release:	1
Version:	7
Machine:	host02
Instance name: xfdb2
Redo thread mounted by this instance: 2
Oracle process number: 450
Unix process pid: 105260, image: oracle@host02 (TNS V1-V3)

Fri Jul  4 11:26:01 2025 +08:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '318'
ACTION :[168] 'select decode(status, 'OPEN', 1, 0), decode(archiver, 'FAILED', 1, 0), decode(database_status, 'SUSPENDED', 1, 0) into :status, :archstuck, :dbsuspended from v$instance'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '226'
ACTION :[77] 'select decode(open_mode, 'READ WRITE', 1, 0) into :read_write from v$database'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '221'
ACTION :[72] 'select value into :vcomp_txt from  v$parameter where name = 'compatible''
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '615'
ACTION :[465] 'declare dot1st number; dot2nd number; dot3rd number; comptxt varchar2(255) := :vcomp_txt; begin comptxt := comptxt || '.0.0'; dot1st := instr(comptxt, '.', 1, 1); dot2nd := instr(comptxt, '.', 1, 2); dot3rd := instr(comptxt, '.', 1, 3); comptxt :=  lpad(substr(comptxt, 1, dot1st - 1), 2, '0') || lpad(substr(comptxt, dot1st + 1, dot2nd - dot1st - 1), 2, '0')  || lpad(substr(comptxt, dot2nd + 1, dot3rd - dot2nd - 1), 2, '0');:vcomp_ub4 := to_number(comptxt); end;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '693'
ACTION :[543] 'begin :vsn_min := dbms_backup_restore.protocol_version_number_min; :rel_min := dbms_backup_restore.protocol_release_number_min; :upd_min := dbms_backup_restore.protocol_update_number_min; :cmp_min := 0; :vsn_max := dbms_backup_restore.protocol_version_number_max; :rel_max := dbms_backup_restore.protocol_release_number_max; :upd_max := dbms_backup_restore.protocol_update_number_max; :cmp_max := 0; :pkg_vsn := to_char(:vsn_max, 'fm00')||'.'|| to_char(:rel_max, 'fm00')||'.'|| to_char(:upd_max, 'fm00')||'.'|| to_char(:cmp_max, 'fm00'); end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '425'
ACTION :[275] 'begin :cmp_min := dbms_backup_restore.protocol_component_number_min; :cmp_max := dbms_backup_restore.protocol_component_number_max; :pkg_vsn := to_char(:vsn_max, 'fm00')||'.'|| to_char(:rel_max, 'fm00')||'.'|| to_char(:upd_max, 'fm00')||'.'|| to_char(:cmp_max, 'fm00'); end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '386'
ACTION :[236] 'declare vsn  varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn,1,2) || substr(vsn,4,2) || substr(vsn,7,2) || nvl(substr(vsn,10,2),'00')); end if; end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '386'
ACTION :[236] 'declare vsn  varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn,1,2) || substr(vsn,4,2) || substr(vsn,7,2) || nvl(substr(vsn,10,2),'00')); end if; end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '386'
ACTION :[236] 'declare vsn  varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn,1,2) || substr(vsn,4,2) || substr(vsn,7,2) || nvl(substr(vsn,10,2),'00')); end if; end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '386'
ACTION :[236] 'declare vsn  varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn,1,2) || substr(vsn,4,2) || substr(vsn,7,2) || nvl(substr(vsn,10,2),'00')); end if; end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'

Fri Jul  4 11:26:02 2025 +08:00
LENGTH : '386'
ACTION :[236] 'declare vsn  varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn,1,2) || substr(vsn,4,2) || substr(vsn,7,2) || nvl(substr(vsn,10,2),'00')); end if; end; '
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '468337914'
...

从审计日志看，是Oracle用户以sysdba角色基于操作系统认证执行了查询 v$instance，v$database，v$ v$parameter等动态视图，同时调用了dbms_rcvman，dbms_backup_restore等rman备份相关的dbms包。通过查看审计日志产生的时间，是每5分钟产生4个（有规律）。
【说明】这是Oracle数据库正常的审计行为。
2、检查定时任务

# crontab -l
*/5 * * * * /home/oracle/scripts/delarch.sh

定时任务中每5分钟执行一次清理归档。这个与审计日志产生的频率一致。
3、检查清理归档脚本

$RMAN target / nocatalog log $RMAN_LOG_FILE append << EOF
crosscheck archivelog all;
RUN{
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' rate 100M;
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' rate 100M;
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE' rate 100M;
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE' rate 1200M;
SEND 'NB_ORA_POLICY=$ARCH_POLICY,NB_ORA_CLIENT=host02';
BACKUP
   FILESPERSET 5
   FORMAT 'al_%s_%p_%t'
   ARCHIVELOG ALL NOT BACKED UP;
   DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE -2';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}
EOF
...
RMAN_PROCESS=`ps -ef |grep rman | grep -v grep`
	if [ "${RMAN_PROCESS}" == "" ]; then
		if [ ${NUM} -gt 60 ]; then 
			echo $DATE": Clear all primary archive log use rman with ORACLE_SID ${1}" 
			CLEAR_PRIMARY_ARCH_USE_RMAN ${1} ALL
		else
			IS_NEED_RMAN=`find ${2} -maxdepth 1 -name '*.dbf' -type f -ctime +${KEEP_DAYS} | wc -l`
			if [ ${IS_NEED_RMAN} -gt 0 ]; then
				CLEAR_PRIMARY_ARCH_USE_RMAN ${1}
			else
				echo $DATE": No need to clean archive log use rman with ORACLE_SID ${1}" 
			fi
		fi
	else
		echo $DATE"
	fi
...

通过脚本逻辑可以看到，清理归档日志前，先通过NBU对归档日志进行备份，/arch超过60%触发清理归档日志任务。
业务高峰时产生的归档日志会超过归档目录/arch使用率的60%，定时任务每5分钟执行一下，会触发清理归档和NBU备份归档的任务，审计日志记录的时NBU备份归档日志的动作。
-thd end-

墨力计划oracle
最后修改时间：2025-07-17 09:52:40
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论