ORA-00600 錯誤解決一例

ORA-00600 錯誤解決一例

資料庫中執行某個查詢會拋出ORA-00600的錯誤異常

ORA-00600: internal error code, arguments: [kcbzibmlt_lobds_1], [], [], [], [], [], [], [], [], [], [], []

查看相關的trace檔案

DDE: Problem Key 'ORA 600 [kcbzibmlt_lobds_1]' was flood controlled (0x4) (incident: 309092)
 ORA-00600: 內部錯誤碼, 參數: [kcbzibmlt_lobds_1], [], [], [], [], [], [], [], [], [], [], []
 ds flg=0x00100001, flg2=0x00000800
 BH (0x7ef79138) file#: 13 rdba: 0x0343ee36 (13/257590) class: 1 ba: 0x7e1ce000
  set: 23 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
  dbwrid: 0 obj: 191590 objn: 191590 tsn: 10 afn: 13 hint: f
  hash: [0x1981bc910,0x1981bc910] lru: [0xb7fc5940,0x83fa47a0]
  ckptq: [NULL] fileq: [NULL] objq: [0xb7fc5968,0x189122f88] objaq: [0xb7fc5978,0x189122f78]
  use: [0x199a1a4d8,0x199a1a4d8] wait: [NULL]
  st: READING md: EXCL tch: 0
  flags: only_sequential_access
  cr pin refcnt: 0 sh pin refcnt: 0
  Using State Objects
    ----------------------------------------
    SO: 0x199a1a458, type: 38, owner: 0x18910ee98, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
      proc=0x19fb51f28, name=buffer handle, file=kcb2.h LINE:2629, pg=0
    (buffer) (CR) PR: 0x19fb51f28 FLG: 0x0
    class bit: 0x0
      cr[0]:
      sh[0]:
    kcbbfbp: [BH: 0x7ef79138, LINK: 0x199a1a4d8]
    type: normal pin
    multiblock read ptr: 0x19ede49a0, count: 7
    kcbbfmbr[0]: [BH: 0x70f7cc98, LINK: 0x19ede49b0]
    type: normal pin
    kcbbfmbr[1]: [BH: 0x82fdce68, LINK: 0x19ede4a00]
    type: normal pin
    kcbbfmbr[2]: [BH: 0xa6fa7d58, LINK: 0x19ede4a50]
    type: normal pin
    kcbbfmbr[3]: [BH: 0xcbf800d8, LINK: 0x19ede4aa0]
    type: normal pin
    kcbbfmbr[4]: [BH: 0x7dfdf0d8, LINK: 0x19ede4af0]
    type: normal pin
    kcbbfmbr[5]: [BH: 0x8cfde168, LINK: 0x19ede4b40]
    type: normal pin
    kcbbfmbr[6]: [BH: 0xa7fdaac8, LINK: 0x19ede4b90]

貌似是library cache的pin有問題，flush buffer_cache和shared_pool後無果
 後來網友badly 去官方幫忙查出如下bug

下載相應的補丁包，打上補丁後問題修複，記錄補丁過程如下：

[Oracle@17-oraba-dg2 Oracle_Opatch]$ unzip p6880880_112000_Linux-x86-64.zip
 Archive:  p6880880_112000_Linux-x86-64.zip
    creating: OPatch/
    creating: OPatch/oplan/
  inflating: OPatch/oplan/README.html 
  inflating: OPatch/oplan/README.txt 
    creating: OPatch/oplan/jlib/
  inflating: OPatch/oplan/jlib/oplan.jar 
  inflating: OPatch/oplan/jlib/oracle.oplan.classpath.jar 
  inflating: OPatch/oplan/jlib/automation.jar 
  inflating: OPatch/oplan/jlib/OsysModel.jar 
  inflating: OPatch/oplan/jlib/EMrepoDrivers.jar 
    creating: OPatch/oplan/jlib/apache-commons/
  inflating: OPatch/oplan/jlib/apache-commons/commons-cli-1.0.jar 
    creating: OPatch/oplan/jlib/jaxb/
  inflating: OPatch/oplan/jlib/jaxb/activation.jar 
  inflating: OPatch/oplan/jlib/jaxb/jaxb-api.jar 
  inflating: OPatch/oplan/jlib/jaxb/jaxb-impl.jar 
  inflating: OPatch/oplan/jlib/jaxb/jsr173_1.0_api.jar 
  inflating: OPatch/oplan/jlib/osysmodel-utils.jar 
  inflating: OPatch/oplan/jlib/CRSProductDriver.jar 
  inflating: OPatch/oplan/oplan     
    creating: OPatch/docs/
  inflating: OPatch/docs/FAQ       
  inflating: OPatch/docs/Users_Guide.txt 
  inflating: OPatch/docs/Prereq_Users_Guide.txt 
    creating: OPatch/jlib/
  inflating: OPatch/jlib/opatch.jar 
  inflating: OPatch/jlib/opatchsdk.jar 
  inflating: OPatch/jlib/oracle.opatch.classpath.jar 
  inflating: OPatch/jlib/oracle.opatch.classpath.unix.jar 
  inflating: OPatch/jlib/oracle.opatch.classpath.windows.jar 
    creating: OPatch/opatchprereqs/
    creating: OPatch/opatchprereqs/opatch/
  inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml 
  inflating: OPatch/opatchprereqs/opatch/rulemap.xml 
  inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml 
    creating: OPatch/opatchprereqs/oui/
  inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml 
  inflating: OPatch/opatchprereqs/prerequisite.properties 
  inflating: OPatch/opatch         
  inflating: OPatch/opatch.bat     
  inflating: OPatch/opatch.pl       
  inflating: OPatch/opatch.ini     
  inflating: OPatch/opatchdiag     
  inflating: OPatch/opatchdiag.bat 
  inflating: OPatch/emdpatch.pl     
  inflating: OPatch/README.txt     

 [oracle@17-oraba-dg2 Oracle_Opatch]$ unzip p10260808_112020_Linux-x86-64.zip
 Archive:  p10260808_112020_Linux-x86-64.zip
    creating: 10260808/
    creating: 10260808/files/
    creating: 10260808/files/lib/
    creating: 10260808/files/lib/libserver11.a/
  inflating: 10260808/files/lib/libserver11.a/kcbz.o 
    creating: 10260808/etc/
    creating: 10260808/etc/config/
  inflating: 10260808/etc/config/inventory.xml 
  inflating: 10260808/etc/config/actions.xml 
  inflating: 10260808/etc/config/deploy.xml 
    creating: 10260808/etc/xml/
  inflating: 10260808/etc/xml/GenericActions.xml 
  inflating: 10260808/etc/xml/ShiphomeDirectoryStructure.xml 
  inflating: 10260808/README.txt   
    creating: 10260808/online/
    creating: 10260808/online/files/
    creating: 10260808/online/files/hpatch/
  inflating: 10260808/online/files/hpatch/bug10260808.pch 
    creating: 10260808/online/etc/
    creating: 10260808/online/etc/config/
  inflating: 10260808/online/etc/config/inventory.xml 
  inflating: 10260808/online/etc/config/actions.xml 
  inflating: 10260808/online/etc/config/deploy.xml 
    creating: 10260808/online/etc/xml/

 [oracle@17-oraba-dg2 Oracle_Opatch]$ mv OPatch $ORACLE_HOME/
 [oracle@17-oraba-dg2 Oracle_Opatch]$ cd 10260808/
 [oracle@17-oraba-dg2 10260808]$ $ORACLE_HOME/OPatch/opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.0
 Copyright (c) 2012, Oracle Corporation.  All rights reserved.
 Oracle Home      : /u01/app/oracle/product/11.2.0/dbhome_1
 Central Inventory : /u01/app/oraInventory
    from          : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version      : 11.2.0.2.0
 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-21_13-13-53PM_1.log

 Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-21_13-13-53PM.txt
 --------------------------------------------------------------------------------
 Installed Top-level Products (1):

 Oracle Database 11g                                                  11.2.0.2.0
 There are 1 products installed in this Oracle Home.
 There are no Interim patches installed in this Oracle Home.
 --------------------------------------------------------------------------------
 OPatch succeeded.

 [oracle@17-oraba-dg2 10260808]$ $ORACLE_HOME/OPatch/opatch apply online -connectstring yoursid:system:yoursystempassword:
 Oracle Interim Patch Installer version 11.2.0.3.0
 Copyright (c) 2012, Oracle Corporation.  All rights reserved.

 Oracle Home      : /u01/app/oracle/product/11.2.0/dbhome_1
 Central Inventory : /u01/app/oraInventory
    from          : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
 OPatch version    : 11.2.0.3.0
 OUI version      : 11.2.0.2.0
 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/10260808_Oct_21_2015_13_15_36/apply2015-10-21_13-15-36PM_1.log
 The patch should be applied/rolled back in '-all_nodes' mode only.
 Converting the RAC mode to '-all_nodes' mode.
 Applying interim patch '10260808' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
 Verifying environment and performing prerequisite checks...
 All checks passed.
 Provide your email address to be informed of security issues, install and
 initiate Oracle Configuration Manager. Easier for you if you use your My
 Oracle Support Email address/User Name.
 Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

 You have not provided an email address for notification of security issues.
 Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y
 Backing up files...

 Patching component oracle.rdbms, 11.2.0.2.0...
 Installing and enabling the online patch 'bug10260808.pch', on database 'fogdg2'.
 Verifying the update...
 Patch 10260808 successfully applied
 Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/10260808_Oct_21_2015_13_15_36/apply2015-10-21_13-15-36PM_1.log
 OPatch succeeded.

[oracle@fograc-dg 10260808]$ /opt/oracle/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home      : /opt/oracle/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /opt/oracle/app/oraInventory
  from          : /opt/oracle/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version      : 11.2.0.2.0
Log file location : /opt/oracle/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-21_13-33-30PM_1.log
Lsinventory Output file location : /opt/oracle/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-21_13-33-30PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch (online) 10260808: applied on Wed Oct 21 13:08:21 CST 2015
Unique Patch ID:  13555810
  Created on 15 Mar 2011, 16:40:38 hrs PST8PDT
  Bugs fixed:
    10260808
--------------------------------------------------------------------------------
OPatch succeeded.