Oracle 版本: 12.2.0.1.181016
OS 版本: Solaris 10
問題描述:
將資料庫由 12.2.0.1.181016 patch 至 12.2.0.1.200714 ，在最後階段執行 datapatch 時失敗產生了 ORA-29913 錯誤 :
-bash-3.2$ ./datapatch -verbose SQL Patching tool version 12.2.0.1.0 Production on Thu Sep 17 18:59:01 2020 Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /oracle/app/cfgtoollogs/sqlpatch/sqlpatch_23713_2020_09_17_18_59_01 /sqlpatch_invocation.log
Connecting to database...OK Bootstrapping registry and package to current versions...done
DBD::Oracle::db selectrow_array failed: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [50181], [63053], [], [], [], [], [], [], [], [] (DBD ERROR: error possibly near <*> indicator at char 7 in 'SELECT <*> dbms_sqlpatch.verify_queryable_inventory FROM dual') [for Statement "SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual"] at /oracle/app/product/12.2.0/sqlpatch/sqlpatch.pm line 6027, <LOGFILE> line 107.
|
問題分析:
由錯誤訊息中可知 datapatch 過程中在執行 dbms_sqlpatch.verify_queryable_inventory 時發生了錯誤，此時以 sqlplus 進行測試也同樣發生了 ORA-29913 錯誤 :
SQL> SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual 2 ; SELECT dbms_sqlpatch.verify_queryable_inventory * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [50181], [63053], [], [], [], [], [], [], [], [] |
由 trace file 發現 LOB Segments 有 Corrupt Block 所以才造成錯誤 :
Corrupt Block Found TIME STAMP (GMT) = 09/17/2020 19:02:17 CONT = 0, TSN = 1, TSNAME = SYSAUX RFN = 3, BLK = 50181, RDBA = 12633093 OBJN = 10486, OBJD = 10486, OBJECT = SYS_LOB0000010485C00001$$, SUBOBJECT = SEGMENT OWNER = SYS, SEGMENT TYPE = Lob Segment Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_24528.trc (incident=1080037): ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [50181], [63053], [], [], [], [], [],[], [], [] Incident details in: /oracle/app/diag/rdbms/orcl/orcl/incident/incdir_1080037 /orcl_ora_24528_i1080037.trc |
查詢這個 LOB Segment ，發現它是屬於 OPATCH_XINV_TAB 這個 table :
SQL> select table_name,column_name,TABLESPACE_NAME from dba_lobs where owner='SYS' and segment_name='SYS_LOB0000010485C00001$$';
TABLE_NAME COLUMN_NAME TABLESPACE_NAME ----------------------------------------------------------------- OPATCH_XINV_TAB XML_INVENTORY SYSAUX |
經由分析可知是由於 OPATCH_XINV_TAB 有 corrupt block 造成 datapatch 執行失敗。
解決方法:
將此 LOB Segments 進行一次搬移，避開這個 corrupt block :
SQL> alter table sys.opatch_xinv_tab move lob(xml_inventory) store as (tablespace sysaux);
Table altered. |
Move 之後確認物件狀態為 Valid :
SQL> select owner,object_name,object_type,status from dba_objects where object_name in ('DBMS_QOPATCH','OPATCH_XML_INV');
OWNER OBJECT_NAME OBJECT_TYPE STATUS ----------- ------------------ ------------------- ------- SYS DBMS_QOPATCH PACKAGE VALID SYS DBMS_QOPATCH PACKAGE BODY VALID SYS OPATCH_XML_INV TABLE VALID |
重新測試 dbms_sqlpatch.verify_queryable_inventory 就沒有發生錯誤了 :
SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY -------------------------------------------------- OK |
此時再重新執行一次 datapatch 即可通過。
