Oracle 開啟10046跟蹤引起 $ORACLE_BASE 目錄空間暴漲

$ORACLE_BASE  --&gt /oradata2目錄幾天內 使用了40G多 ..  

[oracle@kfdb trace]$ echo $ORACLE_BASE

/oradata2/app/oracle

[oracle@kfdb trace]$

1）只針對 $ORACLE_BASE(等同於/oradata2/app/oracle/)目錄 大小...較大 進行分析即可；

2）找到對應目錄 再一步步根據命令（du -sh *）進行深度分析,,找到目錄大小較大的將很可能為問題根源；

3）根據情況找到最終問題... 提供解決方案；

[oracle@kfdb ~ ]$ du -sh /oradata2/app/oracle/*          --- 檢視/oradata2/app/oracle/目錄下物件大小情況  Linux 命令

18M     admin

1.2M    cfgtoollogs

4.0K    checkpoints

53.3G    diag                         --- alert/trace檔案位置...怎麼會這面大？

9.5M    fast_recovery_area

143G    oradata                     --- Oracle 資料檔案非自動擴充套件;

2.4G    product

[oracle@kfdb oracle]$ 

[oracle@kfdb trace]$pwd

/oradata2/app/oracle/diag/rdbms/sinodb/sinodb/trace

[oracle@kfdb trace]$ du -sh

45.0G    .

[oracle@kfdb trace]$ 

① 任意檢視一個檔案內容     [oracle@kfdb trace]$ head -300 sinodb_ora_32530.trc Trace file /oradata2/app/oracle/diag/rdbms/sinodb/sinodb/trace/sinodb_ora_32530.trc Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oradata2/app/oracle/product/11.2.0.3/db_1 System name:     Linux Node name:     kfdb Release:     2.6.9-89.0.25.ELhugemem Version:     #1 SMP Thu May 6 12:41:24 EDT 2010 Machine:     i686 Instance name: sinodb Redo thread mounted by this instance: 1 Oracle process number: 73 Unix process pid: 32530, image: oracle@kfdb *** 2014-09-05 10:08:56.962 WAIT #4142061156: nam='SQL*Net message from client' ela= 2120864 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1409882936962785 ===================== PARSING IN CURSOR #4142060468 len=1500 dep=0 uid=76 oct=3 lid=76 tim=1409882936963051 hv=676666410 ad='dee3244c' sqlid='3c9ucx4n5a71a' select count(1) as count from(SELECT T.CON_ACCE_ST_ID AS ID,T.CON_ACCE_ID AS CONACCEID,T.GLOBAL_FLOW AS GLOBALFLOW,T.ACCU_PRO AS ACCUPRO,T.PATCH_ACCU_PROP as PATCHACCUPROP ,T.IS_TRAN_FILE ISTRANFILE,T.IS_DOINS_BENEFITS AS ISDOINSBENEFITS,A.ACCE_NAME AS ACCENAME,R.AREA_NAME AS AREANAME, T.CITY_ID  AS CITYID, EA.UNIT_REG_NO AS UNITREGNO,EA.UNIT_NO AS UNITNO,EA.ACCU_SUPP_NAME AS ACCUSUPPNAME,EA. UNIT_NAME_PRINT AS UNITNAMEPRINT,EA.PAY_WAY AS PAYWAYA,EI.REG_NO AS REGNO,EI. PAY_WAY AS PAYWAYE,EI. REG_NO_NAME AS REGNONAME, A. INS_PAY_FEES_WAY AS INSPAYWAY,A. ACCU_PAY_FEES_WAY AS ACCUPAYWAY, T.APP_STATE AS APPSTATE, T.PKG_TYPE AS PKGTYPE,   T.SINGLE_APP_STATE AS SINGLEAPPSTATE, (SELECT D.EDIT_VALUE FROM CS_CON_ACCE_ST_DATE D WHERE D.ST_DATE_ID = (SELECT MAX(DA.ST_DATE_ID) AS STID FROM CS_CON_ACCE_ST_DATE DA WHERE DA.CON_ACCE_ST_ID = T.CON_ACCE_ST_ID AND DA.DATE_TYPE = 2  AND DA.IS_VALID = 1))AS INSPAYTYPEVALUE, (SELECT D.EDIT_VALUE FROM CS_CON_ACCE_ST_DATE D WHERE D.ST_DATE_ID = (SELECT MAX(DA.ST_DATE_ID) AS STID FROM CS_CON_ACCE_ST_DATE DA WHERE DA.CON_ACCE_ST_ID = T.CON_ACCE_ST_ID AND DA.DATE_TYPE = 1  AND DA.IS_VALID = 1))AS ACCUPAYTYPEVALUE  ,T.IS_EFFECTIVE AS ISEFFECTIVE  FROM CS_CON_ACCE_ST T   LEFT JOIN CS_CON_ACCE A ON T.CON_ACCE_ID=A.CON_ACCE_ID  LEFT JOIN BASE_PROD_AREA R ON T.CITY_ID=R.AREA_ID   LEFT JOIN ES_ACCU_CUST_INFO EA ON EA.CUST_ACCU_ID=T.CUST_ACCU_ID  LEFT JOIN ES_INS_CUST_INFO EI ON EI.CUST_INS_ID=T.CUST_INS_ID  WHERE T.CON_ACCE_ID=:1   AND T.IS_EFFECTIVE = :2 )                  --&gt 程式用的SQL END OF STMT PARSE #4142060468:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3873176278,tim=1409882936963046 BINDS #4142060468: Bind#0   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=873 siz=48 off=0   kxsbbbfp=f6e2cf6c  bln=22  avl=03  flg=05   value=7220 Bind#1   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24   kxsbbbfp=f6e2cf84  bln=22  avl=02  flg=01   value=1 EXEC #4142060468:c=0,e=236,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3873176278,tim=1409882936963393 WAIT #4142060468: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1409882936963456 WAIT #4142060468: nam='SQL*Net message from client' ela= 685 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1409882936964192 WAIT #4142060468: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1409882936964324 FETCH #4142060468:c=0,e=120,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3873176278,tim=1409882936964366 STAT #4142060468 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3 pr=0 pw=0 time=93 us)' STAT #4142060468 id=2 cnt=1 pid=1 pos=1 obj=428440 op='TABLE ACCESS BY INDEX ROWID CS_CON_ACCE_ST (cr=3 pr=0 pw=0 time=69 us cost=2 size=22 card=2)' STAT #4142060468 id=3 cnt=1 pid=2 pos=1 obj=434151 op='INDEX RANGE SCAN IDX_ST_CON_ACCE_ID (cr=2 pr=0 pw=0 time=25 us cost=1 size=0 card=2)' WAIT #4142060468: nam='SQL*Net message from client' ela= 1713 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1409882936966212 CLOSE #4142060468:c=0,e=10,dep=0,type=1,tim=1409882936966256 ===================== PARSING IN CURSOR #4142059780 len=1552 dep=0 uid=76 oct=3 lid=76 tim=1409882936966426 hv=822780441 ad='dcc09a60' sqlid='ay8xgu4shp8ht' select * from (select page.*,rownum rn from (SELECT T.CON_ACCE_ST_ID AS ID,T.CON_ACCE_ID AS CONACCEID,T.GLOBAL_FLOW AS GLOBALFLOW,T.ACCU_PRO AS ACCUPRO,T.PATCH_ACCU_PROP as PATCHACCUPROP ,T.IS_TRAN_FILE ISTRANFILE,T.IS_DOINS_BENEFITS AS ISDOINSBENEFITS,A.ACCE_NAME AS ACCENAME,R.AREA_NAME AS AREANAME, T.CITY_ID  AS CITYID, EA.UNIT_REG_NO AS UNITREGNO,EA.UNIT_NO AS UNITNO,EA.ACCU_SUPP_NAME AS ACCUSUPPNAME,EA. UNIT_NAME_PRINT AS UNITNAMEPRINT,EA.PAY_WAY AS PAYWAYA,EI.REG_NO AS REGNO,EI. PAY_WAY AS PAYWAYE,EI. REG_NO_NAME AS REGNONAME, A. INS_PAY_FEES_WAY AS INSPAYWAY,A. ACCU_PAY_FEES_WAY AS ACCUPAYWAY, T.APP_STATE AS APPSTATE, T.PKG_TYPE AS PKGTYPE,   T.SINGLE_APP_STATE AS SINGLEAPPSTATE, (SELECT D.EDIT_VALUE FROM CS_CON_ACCE_ST_DATE D WHERE D.ST_DATE_ID = (SELECT MAX(DA.ST_DATE_ID) AS STID FROM CS_CON_ACCE_ST_DATE DA WHERE DA.CON_ACCE_ST_ID = T.CON_ACCE_ST_ID AND DA.DATE_TYPE = 2  AND DA.IS_VALID = 1))AS INSPAYTYPEVALUE, (SELECT D.EDIT_VALUE FROM CS_CON_ACCE_ST_DATE D WHERE D.ST_DATE_ID = (SELECT MAX(DA.ST_DATE_ID) AS STID FROM CS_CON_ACCE_ST_DATE DA WHERE DA.CON_ACCE_ST_ID = T.CON_ACCE_ST_ID AND DA.DATE_TYPE = 1  AND DA.IS_VALID = 1))AS ACCUPAYTYPEVALUE  ,T.IS_EFFECTIVE AS ISEFFECTIVE  FROM CS_CON_ACCE_ST T   LEFT JOIN CS_CON_ACCE A ON T.CON_ACCE_ID=A.CON_ACCE_ID  LEFT JOIN BASE_PROD_AREA R ON T.CITY_ID=R.AREA_ID   LEFT JOIN ES_ACCU_CUST_INFO EA ON EA.CUST_ACCU_ID=T.CUST_ACCU_ID  LEFT JOIN ES_INS_CUST_INFO EI ON EI.CUST_INS_ID=T.CUST_INS_ID  WHERE T.CON_ACCE_ID=:1   AND T.IS_EFFECTIVE = :2 ) page where rownum<=:3 ) where rn>=:4  --&gt 程式用的SQL END OF STMT PARSE #4142059780:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3160914622,tim=1409882936966421 BINDS #4142059780: Bind#0   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=873 siz=96 off=0   kxsbbbfp=f6e2cc8c  bln=22  avl=03  flg=05   value=7220 Bind#1   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=24   kxsbbbfp=f6e2cca4  bln=22  avl=02  flg=01   value=1 Bind#2   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=48   kxsbbbfp=f6e2ccbc  bln=22  avl=02  flg=01   value=10 Bind#3   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=72   kxsbbbfp=f6e2ccd4  bln=22  avl=02  flg=01   value=1 EXEC #4142059780:c=1000,e=426,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3160914622,tim=1409882936966953 WAIT #4142059780: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1409882936967027 .....等... [oracle@kfdb trace]$

分析：
  大量的跟蹤檔案生成.. 可以推算出很大可能是開啟系統級別事件跟蹤...

-- 檢視資料庫是否開啟了跟蹤事件
SQL> oradebug setmypid                                                       --跟蹤當前會話
已處理的語句
SQL> oradebug eventdump system                                         --檢視系統跟蹤事件..
10046 trace name CONTEXT level 12, forever                          --發現有開啟系統級別 跟蹤
SQL> 

.... 8月29 開啟了system 10046 沒關閉 導致trace目錄下大量檔案產生.....