[20211029]udump磁盘空间暴涨.txt

--//生产系统,udump磁盘空间暴涨.记录一下诊断与解决过程.

1.环境：
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

2.分析:

# du -sm udump/ ; sleep 10 ; du -sm udump/
388697  udump/
388701  udump/
--//10秒增加388701-388697 = 4M.
--//10g下udump目录下文件格式是<oracle_sid>_ora_<spid>.trc. 这样以前的也dump也保存在这个文件里面,不能看前面的部分。
--//打开其中一个发现如下内容:
...
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1597052889224064
WAIT #0: nam='SQL*Net message from client' ela= 3086 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1597052889331166
=====================
PARSING IN CURSOR #1 len=82 dep=1 uid=0 oct=3 lid=0 tim=1597052889331451 hv=3873422482 ad='a47dba68'
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
END OF STMT
PARSE #1:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1597052889331449
BINDS #1:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fdcf3c4d4c0  bln=22  avl=02  flg=05
  value=62
EXEC #1:c=1000,e=172,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1597052889331687
FETCH #1:c=0,e=66,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=1597052889331771
--//感觉是打开10046跟踪。

SQL> oradebug setmypid
Statement processed.

SQL> oradebug eventdump system
10046 trace name CONTEXT level 12, forever

SQL> oradebug eventdump session
10046 trace name CONTEXT level 12, forever

--//可以再次确定某人在系统级别打开10046跟踪。
--//检查alert文件可以确定时间发生在9/29 15:39:08.
# grep -C5 -i "alter system " alert_xxxx.log | grep -C5 10046
kupprdp: master process DM00 started with pid=426, OS id=3378
         to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_02', 'SYS', 'KUPC$C_1_20210929153149', 'KUPC$S_1_20210929153149', 0);
kupprdp: worker process DW01 started with worker id=1, pid=429, OS id=3380
         to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_02', 'SYS');
Wed Sep 29 15:39:08 2021
OS Pid: 3655 executed alter system set events '10046 trace name context forever, level 12'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Wed Sep 29 15:43:54 2021
WARNING: inbound connection timed out (ORA-3136)
Wed Sep 29 15:43:54 2021
WARNING: inbound connection timed out (ORA-3136)
Wed Sep 29 15:43:54 2021

SQL> alter system set events '10046 trace name context off';
System altered.

--//重新登录检查:
SQL> oradebug setmypid
Statement processed.

SQL> oradebug eventdump session
Statement processed.

SQL> oradebug eventdump system
Statement processed.

--//删除垃圾文件.
# find . -mtime +0  -name "*.trc"  -print | xargs -P10 rm

select * from DBA_AUDIT_TRAIL;
--//10g缺省没有打开审计，无法任何记录。

--//实际上这样还是不行，大量的文件句柄没有释放。空间还是无法回收。
# lsof | grep delete | sort -k7 -nr | head
oracle    30130  oracle    2w      REG                8,3  9503424512   32664042 /u01/app/oracle/admin/xxxx/bdump/xxxx_lns1_30130.trc (deleted)
oracle    28877  oracle    5w      REG                8,3  3274162176   32715899 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_28877.trc (deleted)
oracle    24910  oracle    5w      REG                8,3   522918304   32710401 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_24910.trc (deleted)
oracle    29359  oracle    5w      REG                8,3   394892188   32688562 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_29359.trc (deleted)
oracle    24271  oracle    5w      REG                8,3   254185472   32715859 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_24271.trc (deleted)
oracle    29083  oracle    5w      REG                8,3   241707825   32650091 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_29083.trc (deleted)
oracle    25070  oracle    5w      REG                8,3   237270275   32701512 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_25070.trc (deleted)
oracle    24874  oracle    5w      REG                8,3   188691034   32642091 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_24874.trc (deleted)
oracle    10944  oracle    5w      REG                8,3   188186624   32661146 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_10944.trc (deleted)
oracle    28732  oracle    5w      REG                8,3   186159881   32657692 /u01/app/oracle/admin/xxxx/udump/xxxx_ora_28732.trc (deleted)

--//利用中午的空隙,kill 掉全部用户进程.
# lsof | grep delete | grep xxxx_ora_ | sort -k7 -nr | grep -v lkinstxxxx | awk '{print $2}'| paste -sd, | xargs ps -fp
# lsof | grep delete | grep xxxx_ora_ | sort -k7 -nr | grep -v lkinstxxxx | awk '{print $2}'| paste -sd, | xargs kill -9
or
# lsof | grep delete | grep xxxx_ora_ | sort -k7 -nr | grep -v lkinstxxxx | awk '{print $2}'| xargs -IQ kill -9 Q

--//xxxx_lns1_30130 进程是传输日志的进程.
SYS@xxxx> select * from V$BGPROCESS where name like 'LNS%' and Paddr<>'00';
PADDR              PSERIAL# NAME                 DESCRIPTION                         ERROR
---------------- ---------- -------------------- ------------------------------ ----------
00000000A5BB9F18         89 LNS1                 Network Server 1               0

SYS@xxxx> alter system set log_archive_dest_state_2=defer;
System altered.

SYS@xxxx> alter system archive log current;
System altered.

$ ps -ef | grep ln[s]
--//这样对应的lns进程消失了.

SYS@xxxx> SYS@xxxx> alter system set log_archive_dest_state_2=enable ;
System altered.

SYS@xxxx> alter system archive log current;
System altered.

$ ps -ef | grep ln[s]
oracle   11882     1  0 15:59 ?        00:00:00 ora_lns1_xxxx
--//再次启动,这样磁盘空间就可以回收了.
--//整个过程并不复杂,但是浪费了一个上午时间.