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

[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 --//再次启动,这样磁盘空间就可以回收了. --//整个过程并不复杂,但是浪费了一个上午时间.