[20170104]ORA-08103错误的模拟和诊断.txt

[20170104]ORA-08103错误的模拟和诊断.txt

参考连接http://www.oratea.com/2016/12/10/ora-08103%e9%94%99%e8%af%af%e7%9a%84%e6%a8%a1%e6%8b%9f%e5%92%8c%e8%af%8a%e6%96%ad/,重复测试:

$ oerr ora 8103
08103, 00000, "object no longer exists"
// *Cause:  The object has been deleted by another user since the operation
//          began, or a prior incomplete recovery restored the database to
//          a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
//          recovery.

在 Master Note for Handling Oracle Database Corruption Issues (文档 ID 1088018.1)中对ORA-8103错误的描述如下：

The object has been deleted by another user since the operation began.
If the error is reproducible, following may be the reasons:-
a.) The header block has an invalid block type.
b.) The data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header.

See dba_objects.data_object_id and compare it to the decimal value stored in the block (field seg/obj).

文档解释为：ORA-8103错误如果只发生1次，那表明该会话操作前，对象被其它会话删除了。
如果ORA-8103错误重复发生，可能有两个原因：

a) 段头是坏块
b) 数据块上存储的data_object_id和段头上存储的data_object_id不一致。

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t1 tablespace users as select * from all_objects;
Table created.

SCOTT@book> select object_id,data_object_id from dba_objects where object_name = 'T1' and owner = user;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
     89097          89097

--89097 = 0x15c09

SCOTT@book> select segment_type, HEADER_FILE, HEADER_BLOCK from dba_segments where owner = user and segment_name = 'T1';
SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK
------------------ ----------- ------------
TABLE                        4          522

SCOTT@book> select FILE_ID, block_id, blocks from dba_extents where owner = user and segment_name = 'T1';
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4        520          8
         4        528          8
         4        536          8
         4        544          8
         4        552          8
         4        560          8
         4        568          8
         4        576          8
         4        584          8
         4        592          8
         4        600          8
         4        608          8
         4        616          8
         4        624          8
         4        632          8
         4        640          8
         4        768        128
         4        896        128
         4       1024        128
         4       1152        128
         4       1280        128
         4       1408        128
         4       1536        128
         4       1664        128
         4       1792        128

25 rows selected.

2.模拟段头损坏:
SCOTT@book> alter system checkpoint ;
System altered.

BBED> set dba 4,522
        DBA             0x0100020a (16777738 4,522)

BBED> dump /v count 32
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 522                               Offsets:    0 to   31                            Dba:0x0100020a
-----------------------------------------------------------------------------------------------------------
23a20000 0a020001 cf113400 00000104 35f20000 00000000 00000000 00000000 l #.........4.....5...............
<32 bytes per line>

BBED> m /x 24a2 offset 0
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 522                                                  Offsets:    0 to   31  Dba:0x0100020a
---------------------------------------------------------------------------------------------------
24a20000 0a020001 cf113400 00000104 35f20000 00000000 00000000 00000000
<64 bytes per line>

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 522)
ORA-01110: data file 4: '/mnt/ramdisk/book/users01.dbf'

--//如果段头为坏块，则读取直接报坏块。

3.修改段头上的data_object_id:
--//还原
BBED> m /x 23a2 offset 0
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 522                                                  Offsets:    0 to   31  Dba:0x0100020a
---------------------------------------------------------------------------------------------------
23a20000 0a020001 cf113400 00000104 32f20000 00000000 00000000 00000000
<64 bytes per line>

BBED> sum apply
Check value for File 4, Block 522:
current = 0xf235, required = 0xf235

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from t1;
  COUNT(*)
----------
     84752

SCOTT@book> @ &r/10to16 89097
10 to 16 HEX   REVERSE16
-------------- ------------------
0000000015c09 0x095c0100

SCOTT@book> @ &r/bbvi 4 522
BVI_COMMAND
----------------------------------------------------
bvi -b 4276224 -s 8192 /mnt/ramdisk/book/users01.dbf

--//检索095c0100,09换成0a.
BBED> set dba 4,522
        DBA             0x0100020a (16777738 4,522)

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 4, Block 522:
current = 0xf236, required = 0xf236

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-08103: object no longer exists

--总结：段头上的data_object_id出现问题，导致ORA-08103错误发生

4.模拟数据块data_object_id与段头不一致:
--还原略.

select owner, object_name,
       dbms_rowid.rowid_relative_fno(rowid) as fno,
       dbms_rowid.rowid_block_number(rowid) as bno,
       dbms_rowid.rowid_row_number(rowid) as rno,
       to_char(dbms_utility.make_data_block_address(dbms_rowid.rowid_relative_fno(rowid),
               dbms_rowid.rowid_block_number(rowid)), 'xxxxxxxxxx') as dba
  7   from t1 where object_id = 7840;
OWNER  OBJECT_NAME                 FNO        BNO        RNO DBA
------ -------------------- ---------- ---------- ---------- -----------
SYS    V_$DIAG_EM_TARGET_IN          4        625         46     1000271
       FO

BBED> set dba 4,625
        DBA             0x01000271 (16777841 4,625)

BBED> p /d ktbbh.ktbbhsid
union ktbbhsid, 4 bytes                     @24
   ub4 ktbbhsg1                             @24       89097 
   ub4 ktbbhod1                             @24       89097

--查询相关文档,ktbbhsg1=>对象段号,ktbbhod1==>对象号.感觉不对,这个类型union.

BBED> assign ktbbh.ktbbhsid.ktbbhsg1=89098
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 ktbbhsg1                                @24       0x00015c0a

BBED> p /d ktbbh.ktbbhsid
union ktbbhsid, 4 bytes                     @24
   ub4 ktbbhsg1                             @24       89098
   ub4 ktbbhod1                             @24       89098

--噢,一起修改,也验证了前面的判断.

BBED> sum apply
Check value for File 4, Block 625:
current = 0xee92, required = 0xee92

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-08103: object no longer exists

SCOTT@book> select count(*) from t1 where rownum<=10 ;
  COUNT(*)
----------
        10

--//问题来了如何判断.那个块存在问题.

$ dbv file=/mnt/ramdisk/book/users01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 4 09:41:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 16000
Total Pages Processed (Data) : 11459
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 3585
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 690
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 266
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3412431 (0.3412431)

RMAN> validate datafile 4;
Starting validate at 2017-01-04 09:42:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=56 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              266          16027           3412431
  File Name: /mnt/ramdisk/book/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              11459
  Index      0              3585
  Other      0              690
Finished validate at 2017-01-04 09:42:21

--这样检查根本看不出来那块存在问题.另外backup check logical database;也不用.

SCOTT@book> alter session set events '8103 trace name errorstack level 3';
Session altered.

--//感觉监测8103就足够了.

SCOTT@book> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-08103: object no longer exists

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=5bc0v4my7dvr5) -----
select count(*) from t1
...

kcbzibmlt: dump suspect buffer, err=8103
buffer tsn: 4 rdba: 0x01000271 (4/625)
scn: 0x0000.003411ae seq: 0x02 flg: 0x04 tail: 0x11ae0602
frmt: 0x02 chkval: 0xee92 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000062E16000 to 0x0000000062E18000
...

Block header dump:  0x01000271
Object id on Block? Y
seg/obj: 0x15c0a  csc: 0x00.34118b  itc: 3  flg: E  typ: 1 - DATA
--obj不对.15c0a = 89098
     brn: 1  bdba: 0x1000268 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0034118b
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000271
data_block_dump,data header at 0x62e1607c
===============
tsiz: 0x1f80
hsiz: 0xa6
pbl: 0x62e1607c
     76543210
flag=--------
ntab=1
nrow=74
frre=-1
fsbo=0xa6
fseo=0x408
avsp=0x362
tosp=0x362
...
Dump of buffer cache at level 8 for tsn=4 rdba=16777841
BH (0x62fea448) file#: 4 rdba: 0x01000271 (4/625) class: 1 ba: 0x62e16000
  set: 69 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 2,19
  dbwrid: 2 obj: 89097 objn: 89097 tsn: 4 afn: 4 hint: f
  hash: [0x73fef7e0,0x849e1bd0] lru: [0x62fea670,0x842000a8]
  ckptq: [NULL] fileq: [NULL] objq: [0x633e7ab8,0x7d249880] objaq: [0x633e7ac8,0x7d249870]
  use: [0x8448b790,0x8448b790] wait: [NULL]
  st: READING md: EXCL tch: 0
  flags: only_sequential_access
  Using State Objects
    ----------------------------------------
    SO: 0x8448b710, type: 38, owner: 0x855656e8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
     proc=0x8591f618, name=buffer handle, file=kcb2.h LINE:2761, pg=0
    (buffer) (CR) PR: 0x8591f618 FLG: 0x0
    class bit: 0x0
     cr[0]:
     sh[0]:
    kcbbfbp: [BH: 0x62fea448, LINK: 0x8448b790]
    type: normal pin
    multiblock read ptr: 0x7da5dc80, count: 127
    where: kdswh11: kdst_fetch, why: 0
BH (0x73fef728) file#: 4 rdba: 0x01000271 (4/625) class: 1 ba: 0x73e9e000
  set: 64 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 4,19
  dbwrid: 0 obj: 89075 objn: 89075 tsn: 4 afn: 4 hint: f
  hash: [0x849e1bd0,0x62fea500] lru: [0x73fef950,0x73fef6e0]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL fpin: 'kdswh02: kdsgrp' tch: 0 lfb: 33
  flags:

--//在数据缓存是对的.

5.换一种方法测试(利用10231事件):
$ oerr ora 10231
10231, 00000, "skip corrupted blocks on _table_scans_"
// *Cause:
// *Action: such blocks are skipped in table scans, and listed in trace files

--alter session set db_file_multiblock_read_count=1 ;
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
ALTER SESSION SET events '10046 trace name context forever, level 12';

--退出重新登录.补充我感觉不需要设置设置db_file_multiblock_read_count=1 ; 估计10046事件也不需要,看上面的信息.

ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
ALTER SESSION SET events '10046 trace name context forever, level 12';

SCOTT@book> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-08103: object no longer exists

--有点意外,10231事件并没有跳过这个有问题的块,直接报错.想想也正常,这个块通过了dbv以及rman检查测试.

select count(*) from t1
END OF STMT
PARSE #139737249221224:c=67990,e=68161,p=24,cr=440,cu=0,mis=1,r=0,dep=0,og=1,plh=3724264953,tim=1483494987390074
EXEC #139737249221224:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3724264953,tim=1483494987390185
WAIT #139737249221224: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1483494987390254
WAIT #139737249221224: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=4 filetype=2 obj#=89097 tim=1483494987390566
WAIT #139737249221224: nam='direct path read' ela= 145 file number=4 first dba=523 block cnt=13 obj#=89097 tim=1483494987390762
WAIT #139737249221224: nam='direct path read' ela= 134 file number=4 first dba=537 block cnt=15 obj#=89097 tim=1483494987390977
WAIT #139737249221224: nam='direct path read' ela= 66 file number=4 first dba=553 block cnt=15 obj#=89097 tim=1483494987391204
WAIT #139737249221224: nam='direct path read' ela= 43 file number=4 first dba=569 block cnt=15 obj#=89097 tim=1483494987391402
WAIT #139737249221224: nam='direct path read' ela= 43 file number=4 first dba=585 block cnt=15 obj#=89097 tim=1483494987391606
WAIT #139737249221224: nam='direct path read' ela= 44 file number=4 first dba=601 block cnt=15 obj#=89097 tim=1483494987391818
WAIT #139737249221224: nam='direct path read' ela= 43 file number=4 first dba=617 block cnt=15 obj#=89097 tim=1483494987392017
WAIT #139737249221224: nam='direct path read' ela= 43 file number=4 first dba=633 block cnt=15 obj#=89097 tim=1483494987392208
WAIT #139737249221224: nam='db file sequential read' ela= 9 file#=4 block#=625 blocks=1 obj#=89097 tim=1483494987392416
kcbzibmlt: dump suspect buffer, err=8103
buffer tsn: 4 rdba: 0x01000271 (4/625)
scn: 0x0000.003411ae seq: 0x02 flg: 0x04 tail: 0x11ae0602
frmt: 0x02 chkval: 0xee92 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000061B84000 to 0x0000000061B86000

--我后面测试仅仅10046也能发现问题. 单独使用10231也可以,不在另行测试.

6.如何跳过,这种情况在真正的生产环境很难出现:
--应该损坏的块对于的表结构跟原来的块对于的表结构一样.要通过扫描,先破坏这个块.设置10231事件.

BBED> set dba 4,625
        DBA             0x01000271 (16777841 4,625)

BBED> assign kcbh.type_kcbh=0x07
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 type_kcbh                               @0        0x07

BBED> sum apply
Check value for File 4, Block 625:
current = 0xee93, required = 0xee93

SCOTT@book> ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
Session altered.

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from t1;
COUNT(*)
----------
84678

--检查跟踪可以发现坏块.dba=4,625.

7.补充学习bbed修复:
BBED> set dba 4,625
        DBA             0x01000271 (16777841 4,625)

BBED> assign kcbh.type_kcbh=0x06
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 type_kcbh                               @0        0x06

BBED> assign ktbbh.ktbbhsid.ktbbhsg1=89097
ub4 ktbbhsg1                                @24       0x00015c09

BBED> p /d ktbbh.ktbbhsid
union ktbbhsid, 4 bytes                     @24
   ub4 ktbbhsg1                             @24       89097
   ub4 ktbbhod1                             @24       89097

BBED> sum apply
Check value for File 4, Block 625:
current = 0xee91, required = 0xee91

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from t1;
  COUNT(*)
----------
     84752

--对比前面比原来的数据多.

时间： 2024-11-15 09:19:56

[20170104]ORA-08103错误的模拟和诊断.txt的相关文章

Oracle数据库ORA 54013错误的解决办法_oracle

ORA-54013: 不允许对虚拟列执行 INSERT 操作 这是Oracle 11 的新特性 -- 虚拟列. 在以前的Oracle 版本,当我们需要使用表达式或者一些计算公式时,我们会创建数据库视图,如果我们需要在这个视图上使用索引,我们会创建基于函数的索引.现在Oracle 11允许我们直接在表上使用虚拟列来存储表达式.虚拟列的值是不存储在磁盘的,它们是在查询时根据定义的表达式临时计算的.我们不能往虚拟列中插入数据,我们也不能隐式的添加数据到虚拟列,我们只能使用物理列来插入数据.然后可以查询

plsql连接oracle数据库报ora 12154错误解决方法_oracle

plsql连接oracle数据库报ora 12154错误 今天遇到一个问题,使用sqlplus能够连接到远程的数据库,但是使用plsql却连接不上,报错"ORA-12154: TNS: 无法解析指定的连接标识符" 解决方法如下: 1.先检查服务器端的监听服务是否打开,如果没有打开请启动其监听 客户端:tnsping <tns_name> 服务器Linux下: #>lsnrctl status 查看监听状态 #>lsnrctl start 启动监听 2.通过Sql

oracle 11g导出数据时报ORA 1455错误的处理方法_oracle

由于导出的该用户的表可能存在空数据表,那么可能就会出现此其异常. 首先: 查看: SQL>show parameter deferred_segment_creation; 如果为TRUE,则将该参数改为FALSE: 在sqlplus中,执行如下命令: SQL>alter system set deferred_segment_creation=false; 然后: 可以针对数据表.索引.物化视图等手工分配Extent SQL>Select 'alter table '||table_n

ORA 00054错误处理方法

1.通过以下查找出已被锁定的数据库表及相关的sid.serial#及spid select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号 from v$locked_object l , dba_objects o , v$session s , v$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr; 2.在数据库中灭掉相关s

MySQL5.5安装出现CMake错误找不到CMakelists.txt原因

今天虚拟机上测试安装 CentOS6.3 + PHP5.4.8 + MySQL5.5.28,结果捣鼓了半天 MySQL都没装上,老是CMake目录下找不到那个 lists 文件,郁闷的不行,最后发现问题所在,总结在下面(我是把软件包下载错了): 1. 检查参数是否配置错误  可以参考这里的官方说明 http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html#cmake-general-options  我的配置

[20170821]使用oradebug模拟CBC(10g).txt

[20170821]使用oradebug模拟Cache Buffers chains(10g).txt --//上午测试了在读读模式下,不会出现Cache Buffers chains等待时间,链接http://blog.itpub.net/267265/viewspace-2143880/ --//下午在10g下使用oradebug模拟看看是否会出现Cache Buffers chains. 1.环境: SCOTT@test> @ &r/ver1 PORT_STRING          

【BBED】BBED模拟并修复ORA-08102错误

[BBED]BBED模拟并修复ORA-08102错误   1.1  BLOG文档结构图     1.2  前言部分 1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 使用BBED修复ORA-08102错误(重点) ② BBED的使用 ③ 数据块格式的dump文件解释 ④ ORA-08102错误的trace文件解释 ⑤ 从rdba获取ROWID信息 ⑥ 其它实用技能   Tips: ① 本文在itpub(h

Oracle 错误总结及问题解决 ORA

参考地址 ORA-00001: 违反唯一约束条件 (.)错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常.ORA-00017: 请求会话以设置跟踪事件ORA-00018: 超出最大会话数ORA-00019: 超出最大会话许可数ORA-00020: 超出最大进程数 ()ORA-00021: 会话附属于其它某些进程:无法转换会话ORA-00022: 无效的会话 ID:访问被拒绝ORA-00023: 会话引用进程私用内存:无法分离会话ORA-00024: 单一进程模式下不允许从多个进程注册

诊断和纠正 Java 程序中反复出现的错误类型

程序|错误 欢迎光临诊断 Java 代码,一个隔周更新的新专栏,着重讨论和您日常编程工作有关的 Java 解决方案.本文为第一篇,介绍了错误模式的概念,一个非常有用的概念,它将提高您检测和修正代码中错误的能力.您会了解到一种最普遍的错误模式,这将为您开始识别和避免更高级的错误模式奠定基础.错误模式和它们为什么有用正如好的编程技能涉及很多设计模式(您可以在不同的程序上下文中组合和应用这些模式)的知识一样,好的调试技能也涉及对 错误模式的一定了解.错误模式就是已发出的错误和程序中潜在的错误之间的重复