trace file 一直增加嗎 ?? 可能是遇到 bug 了 !!

環境

Linux 7.2
Oracle 12.1.0.2 

異常  

trace file 持續大量成長

細節

沒有安排行程的一天
先是遇到Ａ客戶spatial index 有誤
再來是B客戶的 cluster 1 node 1 自動重啟後, 向 cluster 2 scan 註冊
還沒處理完, C客戶因為重建 index 也發生了 ORA-4031
最後, 高雄同事又問 trace file 一直增加
遠端連線看了一下, 果然如此

# ls -l 
-rw-r-----. 1 oracle asmadmin  1810987 May 24 15:29 CATOSP1_m000_4177.trc
-rw-r-----. 1 oracle asmadmin   650296 May 24 15:29 CATOSP1_m000_4177.trm
-rw-r-----. 1 oracle asmadmin  1810987 May 24 15:28 CATOSP1_m000_3859.trc
-rw-r-----. 1 oracle asmadmin   650263 May 24 15:28 CATOSP1_m000_3859.trm
-rw-r-----. 1 oracle asmadmin  1809963 May 24 15:27 CATOSP1_m000_2615.trc
-rw-r-----. 1 oracle asmadmin   650220 May 24 15:27 CATOSP1_m000_2615.trm
-rw-r-----. 1 oracle asmadmin  3620892 May 24 15:26 CATOSP1_m000_2380.trc
-rw-r-----. 1 oracle asmadmin  1300501 May 24 15:26 CATOSP1_m000_2380.trm
-rw-r-----. 1 oracle asmadmin  1808910 May 24 15:25 CATOSP1_m000_2144.trc
-rw-r-----. 1 oracle asmadmin   650201 May 24 15:25 CATOSP1_m000_2144.trm
-rw-r-----. 1 oracle asmadmin  1809935 May 24 15:24 CATOSP1_m000_1349.trc
-rw-r-----. 1 oracle asmadmin   650210 May 24 15:24 CATOSP1_m000_1349.trm
-rw-r-----. 1 oracle asmadmin  1808911 May 24 15:23 CATOSP1_m000_1063.trc
-rw-r-----. 1 oracle asmadmin   650195 May 24 15:23 CATOSP1_m000_1063.trm

 Trace file /u01/app/oracle/diag/rdbms/catosp/CATOSP1/trace/CATOSP1_m000_12151.trc
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      SCTBDBOKHH01
Release:        3.10.0-327.22.2.el7.x86_64
Version:        #1 SMP Thu Jun 9 10:09:10 EDT 2016
Machine:        x86_64
Instance name: CATOSP1
Redo thread mounted by this instance: 1
Oracle process number: 149
Unix process pid: 12151, image: oracle@SCTBDBOKHH01 (M000)
*** 2018-05-24 15:33:59.857
*** SESSION ID:(488.34984) 2018-05-24 15:33:59.857
*** CLIENT ID:() 2018-05-24 15:33:59.857
*** SERVICE NAME:(SYS$BACKGROUND) 2018-05-24 15:33:59.857
*** MODULE NAME:(MMON_SLAVE) 2018-05-24 15:33:59.857
*** CLIENT DRIVER:() 2018-05-24 15:33:59.857
*** ACTION NAME:(KDILM background CLeaNup) 2018-05-24 15:33:59.857

 ----- Cursor Obsoletion Dump sql_id=a6ygk0r9s5xuj -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=0x63e9162c8 phd=0x63e9162c8
----- Dump Cursor sql_id=a6ygk0r9s5xuj xsc=0x7ffba1f6a798 cur=0x7ffba6f42108 -----

 LibraryHandle:  Address=0x63e9162c8 Hash=d382f751 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=SELECT A.JOB_NAME, ( CASE A.STATE WHEN 'SCHEDULED' THEN :B11 WHEN 'DISABLED' THEN :B10 WHEN 'RUNNING' THEN :B9 ELSE
:B8 END ) FROM DBA_SCHEDULER_JOBS A, ILM_RESULTS$ B, USER$ C, ILM_EXECUTION$ D WHERE A.JOB_NAME = B.JOBNAME AND (B.EXECUTION_ID = :B7 OR
 :B7 =:B6 ) AND B.EXECUTION_ID = D.EXECUTION_ID AND (B.JOBTYPE = :B5 OR :B5 = :B3 ) AND (B.JOBTYPE1 = :B4 OR :B4 = :B3 ) AND C.USER# = D
.OWNER AND (:B1 = :B2 OR D.FLAG = :B1 )
    FullHashValue=a50bce95c6943bcea379f205d382f751 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=354857
5569 OwnerIdn=0
  Statistics:  InvalidationCount=488 ExecutionCount=75882 LoadCount=1630 ActiveLocks=1 TotalLockCount=245191 TotalPinCount=2
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=18583 HandleInUse=18583 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0x63e916378(0, 4712, 0, 0) Mutex=0x63e916410(0, 2915346, 0, 0)
  Flags=RON/PIN/TIM/OBS/PN0/DBN/[10412841] Flags2=[0000]
  WaitersLists:
    Lock=0x63e916358[0x63e916358,0x63e916358]
    Pin=0x63e916338[0x63e916338,0x63e916338]
    LoadLock=0x63e9163b0[0x63e9163b0,0x63e9163b0]
  Timestamp:  Current=02-11-2018 02:00:05
  HandleReference:  Address=0x63e916630 Handle=(nil) Flags=[00]
  LibraryObject:  Address=0x2af5174a8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='1024'
      Child:  id='0' Table=0x2af518328 Reference=0x2af517db8 Handle=0x63c6df540
      Child:  id='1' Table=0x2af518328 Reference=0x2af518220 Handle=0x63df10510
      Child:  id='2' Table=0x2af518328 Reference=0x2af92c8c8 Handle=0x2ad360350
      Child:  id='3' Table=0x2af518328 Reference=0x2af92cbd0 Handle=0x63c5fd440
      Child:  id='4' Table=0x2af518328 Reference=0x2af92cfd8 Handle=0x2ddbd0a50
      Child:  id='5' Table=0x2af518328 Reference=0x2af92d310 Handle=0x2ddd50a18
      Child:  id='6' Table=0x2af518328 Reference=0x2ad1571d0 Handle=0x63ececcb8
      Child:  id='7' Table=0x2af518328 Reference=0x2ad157508 Handle=0x63eec3ee0

Workaround 

使用關鍵字 Cursor Obsoletion Dump sql_id 至 metalink 上搜尋, 找到 Doc ID 1955319.1 Huge Trace Files Created Containing "----- Cursor Obsoletion Dump sql_id=%s -----"  
依文件上的 solution , 執行
alter system set "_kks_obsolete_dump_threshold" = 0;
過幾分鐘, 再查看 trace file , 就沒再異常的增長
[oracle@SCTBDBOKHH01 trace]$ date
Thu May 24 15:43:58 CST 2018

 [oracle@SCTBDBOKHH01 trace]$ ls -lt CATOSP1_m000*|more
-rw-r-----. 1 oracle asmadmin 1810960 May 24 15:39 CATOSP1_m000_16496.trc
-rw-r-----. 1 oracle asmadmin  650212 May 24 15:39 CATOSP1_m000_16496.trm
-rw-r-----. 1 oracle asmadmin 1808913 May 24 15:38 CATOSP1_m000_16047.trc
-rw-r-----. 1 oracle asmadmin  650203 May 24 15:38 CATOSP1_m000_16047.trm
-rw-r-----. 1 oracle asmadmin 1809935 May 24 15:37 CATOSP1_m000_15774.trc
-rw-r-----. 1 oracle asmadmin  650341 May 24 15:37 CATOSP1_m000_15774.trm
-rw-r-----. 1 oracle asmadmin 1809937 May 24 15:36 CATOSP1_m000_13202.trc
-rw-r-----. 1 oracle asmadmin  650299 May 24 15:36 CATOSP1_m000_13202.trm
-rw-r-----. 1 oracle asmadmin 1808912 May 24 15:35 CATOSP1_m000_12430.trc
-rw-r-----. 1 oracle asmadmin  650176 May 24 15:35 CATOSP1_m000_12430.trm

留言

張貼留言

這個網誌中的熱門文章

12c RAC, OS log 出現 WARNING: couldn't allocate FBT table for module oracleacfs

環境 Solaris 11 SPARC (LDOM) Oracle 12.2.0.1 EE RAC  異常   OS log 出現  WARNING: couldn't allocate FBT table for module oracleacfs 細節 root@HISRAC1:~# dmesg |grep acfs Apr 27 01:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 02:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 03:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 04:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 05:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 06:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 07:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracl
閱讀完整內容

11g client 連上 12c server, 出現 ora-28040

圖片
環境 Solaris 11 Oracle 12.2.0.1 EE 異常   開發人員使用 11g tools 連至 12c DB 時出現 ora-28040 細節 User 轉寄以下圖片 Workaround  這個很好查, 馬上就找到  12c: ORA-28040 After Upgrade: No Matching Authentication Protocol (Doc ID 1957995.1)  在 sqlnet.ora 加入以下字串 SQLNET.ALLOWED_LOGON_VERSION_SERVER=10 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 有幾點要注意 1. 將數值設成低於 11, 也不代表一定支援那個版本的 client  2. 在RAC環境下, 有時必需重開 cluster service 3. 在 12cR1 預設值是 11, 在 12cR2 預設值是 12 4. 在 12c 已廢棄   SQLNET.ALLOWED_LOGON_VERSION
閱讀完整內容

新建的 12.2.0.1 資料庫 alert 出現 ORA-12012

新建的 12.2.0.1 資料庫, 在 alert log 裡持續的出現 ORA-12012 , 如下所示 2018-06-04T01:30:45.124533+08:00 Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_24469.trc: ORA-12012 : error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14438" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 47207 ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201 ORA-06512: at "SYS.DBMS_STATS", line 47197 2018-06-04T01:40:45.589708+08:00 Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_2328.trc: ORA-12012 : error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14440" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 47207 ORA-06512: at "SYS.DBMS_STATS_A
閱讀完整內容