在11.2.0.4环境中频繁出现ora-8103的问题、dbsnmp执行dbms_job权限不够报错

ORA-06508 could not find program unit being called: "DBSNMP.BSLN_INTERNAL

Errors in file /oracle/app/db/diag/rdbms/mis/MIS1/trace/MIS1_j001_30212440.trc:
ORA-12012: error on auto execute of job 12696
ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DBSNMP.BSLN_INTERNAL"
ORA-06512: at line 1

MOS中的解释为，为了安全起见，之前授权给public的execute on dbms_job权限被收回，导致dbsnmp执行dbms_job权限不够报错。解决措施

grant execute on sys.dbms_job to dbsnmp;

注： 此job为每周日执行。

Ora-06508: Pl/Sql: Could Not Find Program Unit Being Called: “DBSNMP.BSLN_INTERNAL” (Doc ID 1323597.1)

Symptoms
Following errors are found in database alert.log

Errors in file .trc:
ORA-12012: error on auto execute of job 11689
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Trying to compile DBSNMP.BSLN_INTERNAL package, it fails.

Cause
For security reasons, the privileges on DBMS_JOB package to public has been removed.

Verification can be done with:

select * from dba_tab_privs where table_name='DBMS_JOB';

GRANTEE      OWNER        TABLE_NAME   GRANTOR      PRIVILEGE    GRA HIE
------------ ------------ ------------ ------------ ------------ --- ---
OWBSYS       SYS          DBMS_JOB     SYS          EXECUTE      NO  NO
SYSMAN       SYS          DBMS_JOB     SYS          EXECUTE      NO  NO
EXFSYS       SYS          DBMS_JOB     SYS          EXECUTE      NO  NO
PUBLIC       SYS          DBMS_JOB     SYS          EXECUTE      YES NO

Solution
Explicit permissions on DBMS_JOB must be granted to DBSNMP user.
In case the execute privilege has been removed from PUBLIC check the following note for more details:
Note 247093.1 Be Cautious When Revoking Privileges Granted to PUBLIC

GRANT EXECUTE ON sys.dbms_job to DBSNMP;

附：还有一个报错信息很像的

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073

MOS中解释的原因不同，如果你的数据库是通过模板创建或者使用克隆，表DBSNMP.BSLN_BASELINES中的已存在数据会与新插入的数据冲突。可以通过重新创建DBSNMP这个用户。

ORA-12012: error on auto execute of job “SYS”.“BSLN_MAINTAIN_STATS_JOB” (Doc ID 1413756.1)

Symptoms
BSLN_MAINTAIN_STATS_JOB fails with the following error:
ORA-12012: error on auto execute of job “SYS”.“BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1Cause
The same is addressed in
Bug 13637859 - "BSLN_MAINTAIN_STATS_JOB FAILS WITH ORA-06502
which is marked as a duplicate of
Bug 10110625 - DBSNMP.BSLN_INTERNAL RECEIVES ORA-06502

As in a specific customer’s situation, the issue can occur when a Database is created with a template from the old database or created as a clone from another database. The existing records in table “DBSNMP.BSLN_BASELINES” conflict with new baseline information inserted in the cloned database, thereby containing inconsistent information.

Solution

For 11.2.0.2, please apply generic Patch 10110625.

As a workaround, the DBSNMP user can be dropped and re-created using the standard scripts.
Ensure you have Backed up your database.

-- Login as sys user.
SQL> sqlplus / as sysdba
-- From the sqlplus execute the following:
-- Drop the DBSNMP user by executing catnsnmp.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql
-- Create the DBSNMP user by executing catsnmp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql

2、Oracle 11.2.0.4 frequently ORA-8103 on Local non-prefixed index

最近遇到了2个客户出现在11.2.0.4环境中频繁出现ora-8103的问题，基本上都是索引对象object mismatch, 重建后过段时间会再现， 该类问题使用rman validate logical 还无法发现，算是当前oracle软件的一个未知bug.

db alert log

Tue Aug 02 07:12:29 2022
Archived Log entry 701841 added for thread 1 sequence 333804 ID 0x6d3dadc5 dest 1:
Tue Aug 02 07:15:00 2022
Errors in file /oracle/app/oracle/diag/rdbms/anbobl/anbobl1/trace/anbobl1_ora_31343.trc:
ORA-08103: object no longer exists
Tue Aug 02 07:15:00 2022
Errors in file /oracle/app/oracle/diag/rdbms/anbobl/anbobl1/trace/anbobl1_ora_31599.trc:
ORA-08103: object no longer exists
Tue Aug 02 07:15:01 2022

trace file

*** SESSION ID:(8467.28134) 2022-08-02 10:45:01.797
OBJD MISMATCH typ=6, seg.obj=9603279, diskobj=9603277, dsflg=100000, dsobj=9603279, tid=9603279, cls=1

*** 2022-08-02 10:45:01.798
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-08103: object no longer exists
----- Current SQL Statement for this session (sql_id=4np3fkhwcwv1z) -----
update u1.xxx  set APPLYOID='88872xxxx'  where SUBSID='31722xxxxx6' and DAYID='20220802'

call stack
--------------
kgeselv ksesecl0 kcbz_check_objd_typ kcbzib kcbgtcr  ktrget3  ktrget2  kdsgrp qetlbr

Note:
From the above we can tell the following:

data segment object_id = 9603279
On disk DATA_OBJECT_ID (diskobj) = 9603277
In memory buffer DATA_OBJECT_ID (dsobj) = 9603279

查看SQL的执行计划

explian plan for 
update u1.xxx   from  u1.xxx  tset APPLYOID='88872xxxx' where SUBSID='31722xxxxx6' and DAYID='20220802';
Plan hash value: 3749668329

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     1 |    32 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                          |     1 |    32 |     6   (0)| 00:00:01 |    31 |    31 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TC_Sxxxxxxxxxxxxxxxx     |     1 |    32 |     6   (0)| 00:00:01 |    31 |    31 |
|*  3 |    INDEX RANGE SCAN                | IDX_xxxxxxxxxxxxxxxxxxxT |     2 |       |     3   (0)| 00:00:01 |    31 |    31 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DAYID"=20220802)
   3 - access("SUBSID"='31722xxxx6')

note:
可以确认是使用了index range scan, Local索引分区的#31，回表分区表#31。 该分区是20220802.

该表是个基于DAYID创建的每天一个分区， 只在subsid列创建了local 索引（未带分区列）. data_object_id 9603277和9603279分别对应的是表段 20220801和20220802 分区。 从dba_objects中查看对象OBJ#=OBJD#,CREATED=LAST_DDL_TIME,判断相关对象没有做过truncate或交换分区。

判断表中是否有该记录？

select  /*+full(t)*/  * from  u1.xxx  t  where SUBSID='31722xxxxx6' and DAYID='20220802'

使用full sql hint查询table segment中确实无该记录返回。

确认索引KEY记录的位置

select  distinct dbms_rowid.rowid_object(rowid) dsobj,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) blk#,
dbms_rowid.rowid_row_number(rowid) row#
from  u1.xxx  tset APPLYOID='88872xxxx' where SUBSID='31722xxxxx6' and   distinct dbms_rowid.rowid_object(rowid)=9603279;

Note:
可以从记录中找到指向9603279# 的rowid 的数据块地址。

确认数据块指向的对象

select owner, segment_name, partition_name, tablespace_name 
from dba_extents
where relative_fno = &v_dba_rfile
and &v_dba_block between block_id and block_id + blocks - 1
/

从上面的文件号和块号 确认了数据段指向了9603277#， 到这里可以确认的索引 31#分区(20220802) 通过key返回的ROWID指向了表段并不是31#表分区(DATA_OBJECT_ID 9603279#)，而是30#分区表20220801（DATA_OBJECT_ID =9603277#）。 不符合local partition。

确认表分区20220801中是否有上面索引中记录

可以dump 上面rowid 返回的文件号， 块号
-- or 
查询20220801分区，指定partition 关键字 ，确认使用分区索引

--  dump block
sys@ANBOBL>alter system dump datafile xxx block xxx;


tab 0, row 5, @0x9f3
tl: 65 fb: --H-FL-- lb: 0x0  cc: 7
col  0: [13]  33 31 37 32 32 37 39 38 37 37 33 38 36 


sys@ANBOBL> select UTL_RAW.CAST_TO_VARCHAR2(replace('33 31 37 32 32 37 39 38 37 37 33 38 36',' ','')) from dual;
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('33313732323739383737333836','',''))
-------------------------------------------------------------------------------
3172279877386

note:
确认表段上数据确实存在。

确认索引分区20220801中是否有上面索引中记录

select  distinct dbms_rowid.rowid_object(rowid) dsobj,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) blk#,
dbms_rowid.rowid_row_number(rowid) row# from  u1.xxx partition(p20220801)  t where SUBSID='31722xxxxx6'

Note:
20220801 索引分区中，无分区表20220801中 KEY对应的记录。

之前这个问题已排除过_part_access_version_by_number=false， 延迟段创建、交换分区特性。

到这里总结一下，分区表20220801中的一个KEY值 在对应的local 索引分区20220801中缺失，错误的记录到了索引分区20220802中。

查询alert log中历史错误对象

grep -B 1 "ORA-08103" alert*.log|egrep '^Errors in file'|awk '{print $4}'|sed 's/://'|while read LINE ; do echo $LINE; grep "ORA-08103" -A 2 $LINE; done;

Note:
可以确认发生在该相同的表上2个索引， 索引同时都是不带分区列的非前缀本地索引。每次临时解决都可以重建相邻的2个问题索引分区；

该问题应该属于ORACLE 11.2.0.4的未知bug. 彻底解决可以修改创建 Local prefixed index

Related Posts:

ora-600 [ktbdchk1: bad dscn] and ora-8103 corrupted block

https://www.anbob.com/archives/2695.html

Troubleshooting ora-01499 & ora-08103 block corrupted

https://www.anbob.com/archives/2765.html

Oracle 12c Alert log show ” ADVISORY: Please collect redo for investigation of ORA-8103″ frequently

https://www.anbob.com/archives/5783.html