联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
bbed打开丢失部分system数据文件库
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在某种情况下，数据库system表空间可能有多个数据文件，而意外的丢失了其中某个(不能为第一个),然后通过bbed来模拟一个数据文件来open库
system增加数据文件
SQL> alter tablespace system add datafile '/u01/oracle/oradata/ora11g/system02.dbf' size 10m; Tablespace altered. --创建表，为了使得数据库发生类此生产环境的部分操作，使得system表空间可能发生改变 SQL> create table t_xifenfei tablespace system 2 as 3 select * from dba_tables; Table created.
删除system中某个文件(system02.dbf)
[oracle@xifenfei ora11g]$ mv system02.dbf system02.dbf_bak
尝试启动数据库
SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 251661172 bytes Database Buffers 54525952 bytes Redo Buffers 6328320 bytes Database mounted. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'
错误思路offline system数据文件
SQL> alter database datafile 8 offline; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01147: SYSTEM tablespace file 8 is offline ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf' SQL> alter database datafile 8 online; alter database datafile 8 online * ERROR at line 1: ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'
使用system表空间其他数据文件来模拟丢失数据文件
[oracle@xifenfei ora11g]$ cp system01.dbf system02.dbf
通过dul获取file$相关信息
FILE# RELFILE# CRSCNWRP CRSCNBAS
bbed修改下面参数值
--rdba ub4 rdba_kcbh @4 0x02000001 --绝对文件号 ub2 kccfhfno @52 0x0008 --scn ub4 kscnbas @100 0xc01a3581 ub2 kscnwrp @104 0x0b2c --相对文件号 ub4 kcvfhrfn @368 0x00000008 --文件大小(不修改,为了重建欺骗数据库重建控制文件) kccfhfsz --文件创建时间(重建控制文件来实现控制文件和数据文件头一致) kcvfhcrt
重建控制文件
1.因为复制来自同一个表空间下面的数据文件，数据文件大小和原数据文件一样, 所以不要修改kccfhfsz大小，不然会出现
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01200: actual file size of 90880 is smaller than correct size of 10485760 blocks ORA-01110: data file 8: '/u01/oracle/oradata/ora11g/system02.dbf'
2. 数据文件创建时间是通过kcvfhcrt参数值来控制的，而这个值是通过1988年01月01日00时00分00秒开始计时，按照每月31天计算的累计值，按照这个规则可以推断出来kcvfhcrt.因为数据库在启动的时候会验证控制文件中这个值和数据文件头的该值是否一致，所以如果你不修改kcvfhcrt,可以选择重建控制文件来完成.
再次open数据库
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf' SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.
操作到这里,库已经可以正常的被open,如果通过这种方面屏蔽掉的异常的system数据文件中数据字典的部分表信息时,可能数据库依然不能被正常逻辑导出(例如dba_segments,dba_extents的基表等),需要进一步特殊处理，如果不能自行解决相关问题,需要恢复支持,请联系我们
Phone:17813235971 Q Q:107644445
E-Mail:dba@xifenfei.com
密码保护：dul抽取异常asm文件
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在win中运行bbed程序
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
学习ORACLE三剑客:互联网,ORACLE资料,交流.今天在朋友的帮助下,了结了一个心结:在WIN平台中完美的使用bbed(8i/9i部分版本/10g 11g不支持),今天在朋友的帮助下解决了该问题
9i中bbed相关程序
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\XIFENFEI>cd E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win
C:\Users\XIFENFEI>e:
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>dir
驱动器 E 中的卷没有标签。
卷的序列号是 000C-3B41
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win 的目录
2012/09/24 19:45 <DIR> .
2012/09/24 19:45 <DIR> ..
2006/07/29 13:33 147,728 bbed.exe
2006/06/28 11:32 20,752 heteroxa9.dll
2004/10/26 16:35 49,152 kpp95rdr.dll
2004/10/26 16:35 45,056 kpp97rdr.dll
2012/09/24 19:49 250 log.bbd
2006/07/29 02:08 229,648 njssl9.dll
2006/06/28 11:33 61,712 ocijdbc9.dll
2002/04/26 16:22 733,184 oip9.dll
2006/07/29 14:02 1,589,520 oraclient9.dll
2006/07/29 15:25 602,384 oracommon9.dll
2006/06/28 09:54 651,536 ORACORE9.DLL
2006/06/27 08:44 1,736,976 oractxx9.dll
2006/07/29 14:02 2,691,344 orageneric9.dll
2006/07/29 13:56 24,576 oraimr9.dll
2006/07/29 15:19 3,014,656 orajox9.dll
2006/06/28 03:09 155,920 oraldapclnt9.dll
2006/06/28 03:09 24,848 oraldapjclnt9.dll
2006/07/29 02:18 741,648 oran9.dll
2006/07/29 02:10 41,232 oranad9.dll
2006/07/29 02:06 33,040 oranbeq9.dll
2006/07/29 02:08 24,848 ORANCDS9.DLL
2006/07/29 02:07 98,576 orancrypt9.dll
2006/07/29 02:07 33,040 ORANGSS9.DLL
2006/07/29 02:07 24,848 oranhost9.dll
2006/07/29 02:06 33,040 oranipc9.dll
2006/07/29 02:08 176,400 oranjni9.dll
2006/07/29 02:07 213,264 ORANK59.DLL
2006/07/29 02:06 180,496 oranl9.dll
2006/07/29 02:07 74,000 oranldap9.dll
2006/07/29 02:08 28,944 oranldapj9.dll
2006/06/28 06:32 499,984 ORANLS9.DLL
2006/07/29 02:06 33,040 orannmp9.dll
2006/07/29 02:07 53,520 orannts9.dll
2006/07/29 02:07 28,944 orannzentr9.dll
2006/07/29 02:08 28,944 ORANNZMCS9.DLL
2006/07/29 02:07 586,000 orannzsbb9.dll
2006/07/29 02:07 24,848 oranoname9.dll
2006/07/29 02:08 57,616 oranoncj9.dll
2006/07/29 02:07 45,328 ORANRAD9.DLL
2006/07/29 02:06 225,552 oranro9.dll
2006/07/29 02:08 41,232 oransgr9.dll
2006/07/29 02:06 49,424 orantcp9.dll
2006/07/29 02:07 53,520 ORANTCPS9.DLL
2006/07/29 02:07 28,944 orantns9.dll
2006/07/29 13:58 57,784 oraobjop9.dll
2006/07/29 14:58 311,568 oraocci9.dll
2006/07/29 14:02 24,848 oraodm9.dll
2006/06/26 19:19 2,314,240 oraolapapi9.dll
2006/07/29 13:58 57,784 oraolapop9.dll
2006/07/18 08:53 377,104 oraordim9.dll
2006/07/29 12:39 61,712 ORAPLC9.DLL
2006/07/29 12:40 909,584 ORAPLP9.DLL
2006/07/29 12:39 3,281,168 ORAPLS9.DLL
2006/07/29 13:58 57,784 ORAPRTOP9.DLL
2003/01/22 18:59 20,480 orarac9.dll
2002/04/26 17:18 28,944 ORASLAX9.DLL
2006/06/28 06:33 69,904 ORASNLS9.DLL
2006/06/28 16:04 483,600 ORASQL9.DLL
2002/04/29 14:04 246,032 oratrace9.dll
2002/04/29 14:04 86,288 oratracepls9.dll
2006/06/28 06:32 69,904 ORAUNLS9.DLL
2006/07/29 14:02 24,848 oravsn9.dll
2005/03/29 17:17 24,576 orawsec9.dll
2006/07/29 14:02 24,848 orawtc9.dll
2001/04/16 19:19 191,248 orawwg9.dll
2006/06/27 01:50 520,464 ORAXML9.DLL
2006/06/27 01:50 41,232 ORAXMLG9.DLL
2006/06/27 01:50 172,304 ORAXSD9.DLL
2010/06/29 05:38 181,560 vspp97.dll
2010/06/29 05:38 83,256 vsqp9.dll
2010/06/29 05:38 177,464 vsw97.dll
72 个文件 25,234,522 字节
2 个目录 58,747,236,352 可用字节
执行bbed程序报错
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed Message 112 not found; No message file for product=RDBMS, facility=BBED BBED-00113: file not found
分析原因:因为在11g中ORACLE并没有在?/RDBMS/MESG/中提供bbedus.msb程序,导致bbed运行时候无法显示相关提示信息,从而出现类此该错误
解决方法:在11g环境中需要从10g库中拷贝bbedus.msb文件放置到?/RDBMS/MESG/中即可
运行bbed程序
E:\study\Oracle\数据库非常规恢复\bbed\win-bbed\bbed_win>bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Sep 24 19:45:40 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info all;
File# Name Size(blks)
----- ---- ----------
BBED> show all;
FILE# 0
BLOCK# 1
OFFSET 0
DBA 0x00000000 (0 0,1)
FILENAME
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 2048
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
密码保护：dul实现对数据文件内容更新
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
bbed_wrap脚本获取数据块内容
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
bbed的功能很强大,可以通过bbed_wrap来获得数据块记录,相当用途:抢救坏块中的数据
环境准备
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 12 18:29:50 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei
2 as
3 select object_id,object_name from dba_objects where rownum<20;
Table created.
SQL> select file_id,block_id,block_id+blocks-1
2 from dba_extents
3 where segment_name ='T_XIFENFEI' AND owner='CHF';
FILE_ID BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
4 680 687
SQL> alter system checkpoint;
System altered.
--查询记录
SQL> col object_name for a20
SQL> select object_id,object_name,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from chf.t_xifenfei;
OBJECT_ID OBJECT_NAME REL_FNO BLOCKNO ROWNO
---------- -------------------- ---------- ---------- ----------
20 ICOL$ 4 683 0
46 I_USER1 4 683 1
28 CON$ 4 683 2
15 UNDO$ 4 683 3
29 C_COBJ# 4 683 4
3 I_OBJ# 4 683 5
25 PROXY_ROLE_DATA$ 4 683 6
41 I_IND1 4 683 7
54 I_CDEF2 4 683 8
40 I_OBJ5 4 683 9
26 I_PROXY_ROLE_DATA$_1 4 683 10
17 FILE$ 4 683 11
13 UET$ 4 683 12
9 I_FILE#_BLOCK# 4 683 13
43 I_FILE1 4 683 14
51 I_CON1 4 683 15
38 I_OBJ3 4 683 16
7 I_TS# 4 683 17
56 I_CDEF4 4 683 18
19 rows selected.
bbed参数配置
[oracle@xifenfei ~]$ more bbed_file
1 /u01/oracle/oradata/ora11g/system01.dbf
2 /u01/oracle/oradata/ora11g/sysaux01.dbf
3 /u01/oracle/oradata/ora11g/undotbs01.dbf
4 /u01/oracle/oradata/ora11g/users01.dbf
5 /u01/oracle/oradata/ora11g/dbfs01.dbf
[oracle@xifenfei ~]$ more bbed.par
blocksize=8192
listfile=/home/oracle/bbed_file
mode=browse
SILENT=yes
PASSWORD=blockedit
bbed_wrap脚本执行
[oracle@xifenfei ~]$ ./bbed_wrap.sh 4 683 "/rn2cntn" There are 19 rows in block 683 on file 4 " 20 "," ICOL$" " 46 "," I_USER1" " 28 "," CON$" " 15 "," UNDO$" " 29 "," C_COBJ#" " 3 "," I_OBJ#" " 25 "," PROXY_ROLE_DATA$" " 41 "," I_IND1" " 54 "," I_CDEF2" " 40 "," I_OBJ5" " 26 "," I_PROXY_ROLE_DATA$_1" " 17 "," FILE$" " 13 "," UET$" " 9 "," I_FILE#_BLOCK#" " 43 "," I_FILE1" " 51 "," I_CON1" " 38 "," I_OBJ3" " 7 "," I_TS#" " 56 "," I_CDEF4" --和我们查询的结果完全一致
密码保护：dul实现expdp dump文件转换sqlldr格式
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：密码保护：dul实现expdp dump文件转换sqlldr格式
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
bbed 删除普通表记录
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：bbed 删除普通表记录
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友和我说我的bbed系列缺少一篇普通表使用bbed删除记录的文章,月底现场保证回来没睡意,完善这篇文章,也算是对bbed系列的一个终结.
创建模拟记录
SQL> create table t_xifenfei tablespace users
2 as
3 select * from dba_tables where rownum<10;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select table_name,owner,rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei;
TABLE_NAME OWNER ROWID REL_FNO BLOCKNO ROWNO
------------------------------ ------------------------------ ------------------ ---------- ---------- ----------
CON$ SYS AAAM9UAAEAAACA0AAA 4 8244 0
UNDO$ SYS AAAM9UAAEAAACA0AAB 4 8244 1
CDEF$ SYS AAAM9UAAEAAACA0AAC 4 8244 2
CCOL$ SYS AAAM9UAAEAAACA0AAD 4 8244 3
PROXY_ROLE_DATA$ SYS AAAM9UAAEAAACA0AAE 4 8244 4
FILE$ SYS AAAM9UAAEAAACA0AAF 4 8244 5
FET$ SYS AAAM9UAAEAAACA0AAG 4 8244 6
TS$ SYS AAAM9UAAEAAACA0AAH 4 8244 7
PROXY_DATA$ SYS AAAM9UAAEAAACA0AAI 4 8244 8
9 rows selected.
SQL> select dump('FILE$',16) from dual;
DUMP('FILE$',16)
----------------------------
Typ=96 Len=5: 46,49,4c,45,24
SQL> select dump('SYS',16) FROM DUAL;
DUMP('SYS',16)
----------------------
Typ=96 Len=3: 53,59,53
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
这里创建一张测试表,有9条记录,计划使用bbed删除file$的记录
bbed删除表记录
[oracle@xifenfei ~]$ bbed listfile=bbedfile
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat Sep 1 10:28:57 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/oradata/orcl/system01.dbf 0
3 /u01/oradata/orcl/sysaux01.dbf 0
4 /u01/oradata/orcl/users01.dbf 0
5 /u01/oradata/orcl/GS_ORADB_001.dbf 0
6 /u01/oradata/orcl/GS_ORADB_IDX_001.dbf 0
7 /u01/oradata/orcl/undo01.dbf 0
BBED> set file 4 block 8244
FILE# 4
BLOCK# 8244
BBED> map
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Dba:0x01002034
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[9] @142
ub1 freespace[6137] @160
ub1 rowdata[1891] @6297
ub4 tailchk @8188
BBED> set count 32
COUNT 32
--查找对应值,估算起位置
BBED> find /x 494c4524
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6929 to 6960 Dba:0x01002034
------------------------------------------------------------------------
494c4524 06535953 54454dff ff055641 4c494402 c10b02c1 2902c102 03c20338
<32 bytes per line>
BBED> p *kdbr[7]
rowdata[209]
------------
ub1 rowdata[209] @6506 0x2c
--6506肯定不是在这个位置
BBED> p *kdbr[5]
rowdata[623]
------------
ub1 rowdata[623] @6920 0x2c
--6920包含了6929,可以确定在该位置
--查看对应值
BBED> x /rccc
rowdata[623] @6920
------------
flag@6920: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6921: 0x00
cols@6922: 49
col 0[3] @6923: SYS
col 1[5] @6927: FILE$
col 2[6] @6933: SYSTEM
col 3[0] @6940: *NULL*
col 4[0] @6941: *NULL*
col 5[5] @6942: VALID
col 6[2] @6948: ..
col 7[2] @6951: .)
col 8[2] @6954: ..
col 9[3] @6957: ..8
col 10[4] @6961: ..8%
col 11[0] @6966: *NULL*
col 12[2] @6967: ..
col 13[6] @6970: ..01%.
col 14[0] @6977: *NULL*
col 15[2] @6978: ..
col 16[2] @6981: ..
col 17[3] @6984: YES
col 18[1] @6988: N
col 19[2] @6990: ..
col 20[2] @6993: ..
col 21[1] @6996: .
col 22[1] @6998: .
col 23[1] @7000: .
col 24[2] @7002: .(
col 25[1] @7005: .
col 26[1] @7007: .
col 27[10] @7009: 1
col 28[10] @7020: 1
col 29[5] @7031: N
col 30[7] @7037: ENABLED
col 31[2] @7045: ..
col 32[7] @7048: xp....!
col 33[2] @7056: NO
col 34[0] @7059: *NULL*
col 35[1] @7060: N
col 36[1] @7062: N
col 37[2] @7064: NO
col 38[7] @7067: DEFAULT
col 39[8] @7075: DISABLED
col 40[3] @7084: YES
col 41[2] @7088: NO
col 42[0] @7091: *NULL*
col 43[8] @7092: DISABLED
col 44[3] @7101: YES
col 45[0] @7105: *NULL*
col 46[8] @7106: DISABLED
col 47[8] @7115: DISABLED
col 48[2] @7124: NO
BBED> d
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6920 to 6951 Dba:0x01002034
------------------------------------------------------------------------
2c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02
<32 bytes per line>
BBED> set mode edit
MODE Edit
--修改为delete状态
BBED> m /x 3c
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6920 to 6951 Dba:0x01002034
------------------------------------------------------------------------
3c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274
--验证不通过,因为空闲空间不正确(删除了数据还是以前的值当然不正确)
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x7f0a75d0327c
kdbchk: the amount of space used is not equal to block size
used=1722 fsc=0 avsp=6137 dtl=8064
Block 8244 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 9
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 36
sb2 kdbhfseo @132 6173
b2 kdbhavsp @134 6137
b2 kdbhtosp @136 6137
BBED> m /x c618 offset 134
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 134 to 165 Dba:0x01002034
------------------------------------------------------------------------
c618f917 00000900 b01ee11d 0a1d311c 5b1b8c1a be19ee18 1d180000 00000000
<32 bytes per line>
BBED> m /x c618 offset 136
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 136 to 167 Dba:0x01002034
------------------------------------------------------------------------
c6180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x13ef07c
kdbchk: space available on commit is incorrect
tosp=6342 fsc=0 stb=2 avsp=6342
Block 8244 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
BBED> m /x c8 offset 136
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 136 to 167 Dba:0x01002034
------------------------------------------------------------------------
c8180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000
<32 bytes per line>
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 9
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 36
sb2 kdbhfseo @132 6173
b2 kdbhavsp @134 6342
b2 kdbhtosp @136 6344
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa27a, required = 0xa27a
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
启动数据库测试
SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 2019320 bytes Variable Size 75497480 bytes Database Buffers 88080384 bytes Redo Buffers 2174976 bytes Database mounted. Database opened. SQL> set lines 150 SQL> select table_name,owner,rowid, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from t_xifenfei; TABLE_NAME OWNER ROWID REL_FNO BLOCKNO ROWNO ------------------------------ ------------------------------ ------------------ ---------- ---------- ---------- CON$ SYS AAAM9UAAEAAACA0AAA 4 8244 0 UNDO$ SYS AAAM9UAAEAAACA0AAB 4 8244 1 CDEF$ SYS AAAM9UAAEAAACA0AAC 4 8244 2 CCOL$ SYS AAAM9UAAEAAACA0AAD 4 8244 3 PROXY_ROLE_DATA$ SYS AAAM9UAAEAAACA0AAE 4 8244 4 FET$ SYS AAAM9UAAEAAACA0AAG 4 8244 6 TS$ SYS AAAM9UAAEAAACA0AAH 4 8244 7 PROXY_DATA$ SYS AAAM9UAAEAAACA0AAI 4 8244 8 8 rows selected.
可以看到file$这条记录已经被删除,证明bbed操作普通表删除成功
相关文章:
1.bbed 删除 cluster table 记录
2.bbed 找回被删除数据
3.利用bbed找回ORACLE更新前值
密码保护：dul实现exp dump文件转换sqlldr格式
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：密码保护：dul实现exp dump文件转换sqlldr格式
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
bbed 使用实现 drop index 操作
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
这里个bbed的测试是为了实现通过bbed来实现删除index,该方法有两个用途:
1.数据库因为index出了问题不能启动,使用该方法可以屏蔽index,来实现数据库正常启动
2.bootstrap$中的某个index异常
准备环境
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei
2 as
3 select * from dba_objects;
Table created.
SQL> create index ind_t_xifenfei on t_xifenfei(object_id);
Index created.
SQL> SET LINES 150
SQL> col owner for a5
SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ------------------------------ ------------------------------ --------
CHF IND_T_XIFENFEI T_XIFENFEI VALID
SQL> select object_id from dba_objects where object_name='IND_T_XIFENFEI';
OBJECT_ID
----------
75558
SQL> select obj#,dataobj#,ts#,file#,block#,bo#,FLAGS from sys.ind$ where obj#=75558;
OBJ# DATAOBJ# TS# FILE# BLOCK# BO# FLAGS
---------- ---------- ---------- ---------- ---------- ---------- ----------
75558 75558 4 4 298 75557 2
SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from sys.ind$ where obj#=75558;
ROWID REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ----------
AAAAACAABAAAT50AAA 1 81524 0
SQL> alter system checkpoint;
System altered.
SQL> select dump(75558,'16') from dual;
DUMP(75558,'16')
-----------------------
Typ=2 Len=4: c3,8,38,3b
SQL> select dump(4,'16') from dual;
DUMP(4,'16')
-----------------
Typ=2 Len=2: c1,5
SQL> select dump(298,'16') from dual;
DUMP(298,'16')
--------------------
Typ=2 Len=3: c2,3,63
SQL> select dump(75557,'16') from dual;
DUMP(75557,'16')
-----------------------
Typ=2 Len=4: c3,8,38,3a
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
删除ind$中记录
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 17:09:55 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info all
File# Name Size(blks)
----- ---- ----------
1 /u01/oracle/oradata/ora11g/system01.dbf 0
2 /u01/oracle/oradata/ora11g/sysaux01.dbf 0
3 /u01/oracle/oradata/ora11g/undotbs01.dbf 0
4 /u01/oracle/oradata/ora11g/users01.dbf 0
BBED> set file 1 block 81524
FILE# 1
BLOCK# 81524
BBED> map
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Dba:0x00413e74
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[6], 24 bytes @106
sb2 kdbr[33] @130
ub1 freespace[5420] @196
ub1 rowdata[2572] @5616
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @130 8074
sb2 kdbr[1] @132 7987
sb2 kdbr[2] @134 7896
sb2 kdbr[3] @136 7618
sb2 kdbr[4] @138 7523
sb2 kdbr[5] @140 6700
sb2 kdbr[6] @142 6573
sb2 kdbr[7] @144 5524
sb2 kdbr[8] @146 5633
sb2 kdbr[9] @148 -1
sb2 kdbr[10] @150 7771
sb2 kdbr[11] @152 7703
sb2 kdbr[12] @154 7642
sb2 kdbr[13] @156 7546
sb2 kdbr[14] @158 7459
sb2 kdbr[15] @160 7397
sb2 kdbr[16] @162 7330
sb2 kdbr[17] @164 7267
sb2 kdbr[18] @166 6516
sb2 kdbr[19] @168 6450
sb2 kdbr[20] @170 6384
sb2 kdbr[21] @172 6327
sb2 kdbr[22] @174 6265
sb2 kdbr[23] @176 6202
sb2 kdbr[24] @178 6147
sb2 kdbr[25] @180 6086
sb2 kdbr[26] @182 6025
sb2 kdbr[27] @184 5967
sb2 kdbr[28] @186 5906
sb2 kdbr[29] @188 5845
sb2 kdbr[30] @190 5784
sb2 kdbr[31] @192 5727
sb2 kdbr[32] @194 5663
--这里使用直接查看的方法,来找出来ind$中相关记录,实际中方法很多find/第三方工具都可以
BBED> p *kdbr[0]
rowdata[2550]
-------------
ub1 rowdata[2550] @8166 0xac
BBED> x /rn
rowdata[2550] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 1
mref@8171: 1
hrid@8173:0x00400095.1
nrid@8179:0x00400095.1
col 0[2] @8185: 80
BBED> p *kdbr[1]
rowdata[2463]
-------------
ub1 rowdata[2463] @8079 0xac
BBED> x /rn
rowdata[2463] @8079
-------------
flag@8079: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8080: 0x00
cols@8081: 1
kref@8082: 1
mref@8084: 1
hrid@8086:0x004000a1.1
nrid@8092:0x004000a1.1
col 0[3] @8098: 330
BBED> p *kdbr[2]
rowdata[2372]
-------------
ub1 rowdata[2372] @7988 0xac
BBED> x /rn
rowdata[2372] @7988
-------------
flag@7988: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7989: 0x00
cols@7990: 1
kref@7991: 1
mref@7993: 1
hrid@7995:0x004000a7.6
nrid@8001:0x004000a7.6
col 0[3] @8007: 471
BBED> p *kdbr[3]
rowdata[2094]
-------------
ub1 rowdata[2094] @7710 0xac
BBED> x /rn
rowdata[2094] @7710
-------------
flag@7710: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7711: 0x00
cols@7712: 1
kref@7713: 1
mref@7715: 1
hrid@7717:0x0040eb9a.6
nrid@7723:0x0040eb9a.6
col 0[4] @7729: 59484
BBED> p *kdbr[4]
rowdata[1999]
-------------
ub1 rowdata[1999] @7615 0xac
BBED> x /rn
rowdata[1999] @7615
-------------
flag@7615: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7616: 0x00
cols@7617: 1
kref@7618: 4
mref@7620: 4
hrid@7622:0x00403371.6
nrid@7628:0x00403371.6
col 0[3] @7634: 8871
BBED> p *kdbr[5]
rowdata[1176]
-------------
ub1 rowdata[1176] @6792 0xac
BBED> x /rn
rowdata[1176] @6792
-------------
flag@6792: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@6793: 0x00
cols@6794: 1
kref@6795: 18
mref@6797: 18
hrid@6799:0x00413e74.5
nrid@6805:0x00413e74.5
col 0[4] @6811: 75557
BBED> p *kdbr[6]
rowdata[1049]
-------------
ub1 rowdata[1049] @6665 0x6c
BBED> x /rn
rowdata[1049] @6665
-------------
flag@6665: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@6666: 0x00
cols@6667: 36
ckix@6668: 5
col 0[4] @6669: 75557
col 1[2] @6674: 4
col 2[2] @6677: 4
col 3[3] @6680: 170
col 4[0] @6684: *NULL*
col 5[0] @6685: *NULL*
col 6[2] @6686: 15
col 7[0] @6689: *NULL*
col 8[2] @6690: 10
col 9[2] @6693: 40
col 10[2] @6696: 1
col 11[3] @6699: 255
col 12[6] @6703: 1073741825
col 13[38] @6710: 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d
0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d
0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d
0x2d 0x2d
col 14[0] @6749: *NULL*
col 15[0] @6750: *NULL*
col 16[0] @6751: *NULL*
col 17[0] @6752: *NULL*
col 18[0] @6753: *NULL*
col 19[0] @6754: *NULL*
col 20[0] @6755: *NULL*
col 21[0] @6756: *NULL*
col 22[0] @6757: *NULL*
col 23[0] @6758: *NULL*
col 24[0] @6759: *NULL*
col 25[0] @6760: *NULL*
col 26[2] @6761: 15
col 27[2] @6764: 15
col 28[6] @6767: 536870912
col 29[1] @6774: 0
col 30[3] @6776: 736
col 31[0] @6780: *NULL*
col 32[0] @6781: *NULL*
col 33[0] @6782: *NULL*
col 34[0] @6783: *NULL*
col 35[7] @6784: #########################################
BBED> p *kdbr[7]
rowdata[0]
----------
ub1 rowdata[0] @5616 0x6c
BBED> x /rn
rowdata[0] @5616
----------
flag@5616: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@5617: 0x02
cols@5618: 33
ckix@5619: 5
col 0[4] @5620: 75558
col 1[4] @5625: 75558
col 2[2] @5630: 4
col 3[2] @5633: 4
col 4[3] @5636: 298
col 5[1] @5640: 0
col 6[2] @5642: 1
col 7[2] @5645: 10
col 8[2] @5648: 2
col 9[3] @5651: 255
col 10[0] @5655: *NULL*
col 11[2] @5656: 1
col 12[2] @5659: 2
col 13[1] @5662: 0
col 14[2] @5664: 1
col 15[3] @5667: 165
col 16[4] @5671: 74491
col 17[2] @5676: 1
col 18[2] @5679: 1
col 19[3] @5682: 1720
col 20[7] @5686: #########################################
col 21[4] @5694: 74491
col 22[4] @5699: 74491
col 23[2] @5704: 1
col 24[0] @5707: *NULL*
col 25[0] @5708: *NULL*
col 26[0] @5709: *NULL*
col 27[2] @5710: 1
col 28[0] @5713: *NULL*
col 29[0] @5714: *NULL*
col 30[0] @5715: *NULL*
col 31[0] @5716: *NULL*
col 32[7] @5717: #########################################
BBED> set count 64
COUNT 64
BBED> d
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 5616 to 5679 Dba:0x00413e74
------------------------------------------------------------------------
6c022105 04c30838 3b04c308 383b02c1 0502c105 03c20363 018002c1 0202c10b
02c10303 c20338ff 02c10202 c1030180 02c10203 c2024204 c3082d5c 02c10202
<32 bytes per line>
BBED> m /x 7c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 5616 to 5679 Dba:0x00413e74
------------------------------------------------------------------------
7c022105 04c30838 3b04c308 383b02c1 0502c105 03c20363 018002c1 0202c10b
02c10303 c20338ff 02c10202 c1030180 02c10203 c2024204 c3082d5c 02c10202
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 81524:
current = 0x88be, required = 0x88be
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524
Block Checking: DBA = 4275828, Block Type = KTB-managed data block
data header at 0xb53c625c
kdbchk: key comref count wrong
keyslot=5
Block 81524 failed with check code 6121
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> p *kdbr[5]
rowdata[1176]
-------------
ub1 rowdata[1176] @6792 0xac
BBED> x /rn
rowdata[1176] @6792
-------------
flag@6792: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@6793: 0x00
cols@6794: 1
kref@6795: 18
mref@6797: 18
hrid@6799:0x00413e74.5
nrid@6805:0x00413e74.5
col 0[4] @6811: 75557
BBED> d offset 6797
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 6797 to 6860 Dba:0x00413e74
------------------------------------------------------------------------
12000041 3e740005 00413e74 000504c3 08383a7c 02140502 c10602c1 0602c102
01800d52 4f4c4c42 41434b5f 4f4e4c59 02c16102 c1020180 ffff0180 ffff02c1
<32 bytes per line>
BBED> m /x 11
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 6797 to 6860 Dba:0x00413e74
------------------------------------------------------------------------
11000041 3e740005 00413e74 000504c3 08383a7c 02140502 c10602c1 0602c102
01800d52 4f4c4c42 41434b5f 4f4e4c59 02c16102 c1020180 ffff0180 ffff02c1
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 81524:
current = 0x8bbe, required = 0x8bbe
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524
Block Checking: DBA = 4275828, Block Type = KTB-managed data block
data header at 0xb53c625c
kdbchk: the amount of space used is not equal to block size
used=1835 fsc=0 avsp=6156 dtl=8096
Block 81524 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 6
sb2 kdbhnrow @94 33
sb2 kdbhfrre @96 9
sb2 kdbhfsbo @98 104
sb2 kdbhfseo @100 5524
sb2 kdbhavsp @102 6156
sb2 kdbhtosp @104 6156
BBED> d offset 102
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 102 to 165 Dba:0x00413e74
------------------------------------------------------------------------
0c180c18 00000600 06000100 07000000 07000100 08000200 0a001700 8a1f331f
d81ec21d 631d2c1a ad199415 0116ffff 5b1e171e da1d7a1d 231de51c a21c631c
<32 bytes per line>
BBED> m /x 7518
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 102 to 165 Dba:0x00413e74
------------------------------------------------------------------------
75180c18 00000600 06000100 07000000 07000100 08000200 0a001700 8a1f331f
d81ec21d 631d2c1a ad199415 0116ffff 5b1e171e da1d7a1d 231de51c a21c631c
<32 bytes per line>
BBED> m /x 7518 offset 104
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 104 to 167 Dba:0x00413e74
------------------------------------------------------------------------
75180000 06000600 01000700 00000700 01000800 02000a00 17008a1f 331fd81e
c21d631d 2c1aad19 94150116 ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419
<32 bytes per line>
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
sb1 kdbhntab @93 6
sb2 kdbhnrow @94 33
sb2 kdbhfrre @96 9
sb2 kdbhfsbo @98 104
sb2 kdbhfseo @100 5524
sb2 kdbhavsp @102 6261
sb2 kdbhtosp @104 6261
BBED> sum apply
Check value for File 1, Block 81524:
current = 0x8bbe, required = 0x8bbe
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524
Block Checking: DBA = 4275828, Block Type = KTB-managed data block
data header at 0xb53c625c
kdbchk: space available on commit is incorrect
tosp=6261 fsc=0 stb=4 avsp=6261
Block 81524 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> m /x 7918 offset 104
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 81524 Offsets: 104 to 167 Dba:0x00413e74
------------------------------------------------------------------------
79180000 06000600 01000700 00000700 01000800 02000a00 17008a1f 331fd81e
c21d631d 2c1aad19 94150116 ffff5b1e 171eda1d 7a1d231d e51ca21c 631c7419
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 81524:
current = 0x8bb2, required = 0x8bb2
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 81524
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
启动数据库测试ind$是否修改成功
SQL> startup
ORACLE instance started.
Total System Global Area 230162432 bytes
Fixed Size 1344088 bytes
Variable Size 88083880 bytes
Database Buffers 134217728 bytes
Redo Buffers 6516736 bytes
Database mounted.
Database opened.
SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI';
select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI'
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [],
[], [], [], [], []
SQL> set autot trace exp
SQL> set lines 150
SQL> select /*+ full(t) */obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;
Execution Plan
----------------------------------------------------------
Plan hash value: 3378156415
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 206 (0)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| IND$ | 1 | 41 | 206 (0)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJ#"=75558)
SQL> select obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;
Execution Plan
----------------------------------------------------------
Plan hash value: 3312860272
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 41 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJ#"=75558)
SQL> select count(*) from ind$ where obj#=75558;
Execution Plan
----------------------------------------------------------
Plan hash value: 4150977594
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX UNIQUE SCAN| I_IND1 | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJ#"=75558)
SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558;
Execution Plan
----------------------------------------------------------
Plan hash value: 809192456
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 206 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| IND$ | 1 | 5 | 206 (0)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJ#"=75558)
SQL> set autot off
SQL> select /*+ full(t) */obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;
no rows selected
SQL> select obj#,dataobj#,ts#,file#,block#,bo#,FLAGS,rowid from sys.ind$ t where obj#=75558;
OBJ# DATAOBJ# TS# FILE# BLOCK# BO# FLAGS ROWID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------
75558 75558 4 4 298 75557 2 AAAAACAABAAAT50AAA
SQL> select count(*) from ind$ where obj#=75558;
COUNT(*)
----------
1
SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558;
COUNT(*)
----------
0
SQL> COL COLUMN_NAME FOR A15
SQL> SELECT INDEX_OWNER,COLUMN_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='IND$' AND TABLE_OWNER='SYS';
INDEX_OWNER COLUMN_NAME INDEX_NAME
------------------------------ --------------- ------------------------------
SYS OBJ# I_IND1
通过上面的查询我们可以知道ind$本身有一个关于obj#列的index,当我们查询使用该index的时候出现上面的ora-600[kdsgrp1]错误.而因为ind$相关index是bootstarp$中对象,不能直接或者upgrade,甚至event 38003都不能drop或者rebuid
分析I_IND1 索引信息
SQL> select obj#,dataobj# from obj$ where name='I_IND1';
OBJ# DATAOBJ#
---------- ----------
41 41
SQL> alter session set events 'immediate trace name treedump level 41';
Session altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
---------------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17321.trc
--通过这个dump出来的rdba信息,结合我们dump出来其他数据块信息可以找到叶子节点的值用来匹配我们需要delete值在I_IND1中位置
----- begin tree dump
branch: 0x400179 4194681 (0: nrow: 10, level: 1)
leaf: 0x40017a 4194682 (-1: nrow: 575 rrow: 575)
leaf: 0x40017b 4194683 (0: nrow: 569 rrow: 567)
leaf: 0x40017c 4194684 (1: nrow: 540 rrow: 540)
leaf: 0x40017d 4194685 (2: nrow: 533 rrow: 533)
leaf: 0x40017e 4194686 (3: nrow: 362 rrow: 361)
leaf: 0x40017f 4194687 (4: nrow: 533 rrow: 533)
leaf: 0x411d98 4267416 (5: nrow: 533 rrow: 532)
leaf: 0x411d99 4267417 (6: nrow: 533 rrow: 533)
leaf: 0x411d9a 4267418 (7: nrow: 533 rrow: 533)
leaf: 0x411d9b 4267419 (8: nrow: 386 rrow: 386)
----- end tree dump
SQL> set serveroutput on
SQL> declare
2 p_dba VARCHAR2 (255) :='0x00411d9b';
3 l_str VARCHAR2 (255) DEFAULT NULL;
4 BEGIN
5 l_str :=
6 'datafile# is:'
7 || DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
8 || chr(10)||'datablock is:'
9 || DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
10 dbms_output.put_line(l_str);
11 END;
12 /
datafile# is:1
datablock is:73115
PL/SQL procedure successfully completed.
SQL> alter system dump datafile 1 block 73115;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17583.trc
--找到对应块在叶子节点中的块的信息
Block header dump: 0x00411d9b
Object id on Block? Y
seg/obj: 0x29 csc: 0x00.c92c9 itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x411d9c ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.002.000001c6 0x00c0483f.004a.01 CB-- 0 scn 0x0000.000a66a1
0x02 0x0006.009.000002b3 0x00c02389.0075.2e --U- 1 fsc 0x0000.000c92cb
row#385[2538] flag: ------, lock: 2, len=13, data:(6): 00 41 3e 74 00 00
col 0; len 4; (4): c3 08 38 3b
--对于ASSM：76+（itc-1)*24
--对于MSSM：68+（itc-1)*24
SQL> select 2538+68+(2-1)*24 from dual;
2538+68+(2-1)*24
----------------
2630
bbed修改I_IND1中记录
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 17:36:59 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 73115
BLOCK# 73115
BBED> set offset 2630
OFFSET 2630
BBED> x /rn
rowdata[4] @2630
----------
flag@2630: 0x00 (NONE)
lock@2631: 0x02
keydata[6]: 0x00 0x41 0x3e 0x74 0x00 0x00
data key:
col 0[4] @2639: 75558
BBED> set count 64
COUNT 64
BBED> d
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Offsets: 2630 to 2693 Dba:0x00411d9b
------------------------------------------------------------------------
00020041 3e740000 04c30838 3b000000 40264a00 0104c308 38380100 0040264a
000004c3 08383701 00004026 4a000004 c3083832 01000040 264a0001 04c30838
<32 bytes per line>
BBED> m /x 01
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Offsets: 2630 to 2693 Dba:0x00411d9b
------------------------------------------------------------------------
01020041 3e740000 04c30838 3b000000 40264a00 0104c308 38380100 0040264a
000004c3 08383701 00004026 4a000004 c3083832 01000040 264a0001 04c30838
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 73115:
current = 0xe027, required = 0xe027
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 73115
Block Checking: DBA = 4267419, Block Type = KTB-managed data block
**** actual free space credit for itl 2 = 15 != # in trans. hdr = 0 <----修改_ktbitfsc信息
**** actual rows marked deleted = 1 != kdxlende = 0 <----修改kdxlende信息
---- end index block validation
Block 73115 failed with check code 6401
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing (Index): 1
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> map
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Dba:0x00411d9b
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdxle, 32 bytes @92
sb2 kd_off[386] @124
ub1 freespace[1730] @896
ub1 rowdata[5494] @2626
ub4 tailchk @8188
BBED> p kdxle
struct kdxle, 32 bytes @92
struct kdxlexco, 16 bytes @92
ub1 kdxcolev @92 0x00
ub1 kdxcolok @93 0x00
ub1 kdxcoopc @94 0x80
ub1 kdxconco @95 0x01
ub4 kdxcosdc @96 0x00000001
sb2 kdxconro @100 386
sb2 kdxcofbo @102 808
sb2 kdxcofeo @104 2538
sb2 kdxcoavs @106 2210
sb2 kdxlespl @108 0
sb2 kdxlende @110 0 <----需要修改
ub4 kdxlenxt @112 0x00000000
ub4 kdxleprv @116 0x00411d9a
ub1 kdxledsz @120 0x06
ub1 kdxleflg @121 0x00 (NONE)
BBED> d offset 110
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Offsets: 110 to 173 Dba:0x00411d9b
------------------------------------------------------------------------
00000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f
f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e
<32 bytes per line>
BBED> m /x 01
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Offsets: 110 to 173 Dba:0x00411d9b
------------------------------------------------------------------------
01000000 00009a1d 41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f
f81eeb1e de1ed11e c41eb71e aa1e9d1e 901e831e 761e691e 5c1e4f1e 421e351e
<32 bytes per line>
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x02 (KDDBTINDEX)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00000029
ub4 ktbbhod1 @24 0x00000029
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x000c92c9
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 2
ub1 ktbbhflg @38 0x03 (KTBFONFL)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x00411d9c
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0001
ub2 kxidslt @46 0x0002
ub4 kxidsqn @48 0x000001c6
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c0483f
ub2 kubaseq @56 0x004a
ub1 kubarec @58 0x01
ub2 ktbitflg @60 0xc000 (KTBFIBI, KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x000a66a1
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0006
ub2 kxidslt @70 0x0009
ub4 kxidsqn @72 0x000002b3
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c02389
ub2 kubaseq @80 0x0075
ub1 kubarec @82 0x2e
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0 <----需要修改
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x000c92cb
BBED> d offset 86
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Offsets: 86 to 149 Dba:0x00411d9b
------------------------------------------------------------------------
0000cb92 0c000000 80010100 00008201 2803ea09 a2080000 01000000 00009a1d
41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e
<32 bytes per line>
BBED> m /x 0f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 73115 Offsets: 86 to 149 Dba:0x00411d9b
------------------------------------------------------------------------
0f00cb92 0c000000 80010100 00008201 2803ea09 a2080000 01000000 00009a1d
41000600 0e00601f 0000531f 461f391f 2c1f1f1f 121f051f f81eeb1e de1ed11e
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 73115:
current = 0xe029, required = 0xe029
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 73115
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
启动数据库测试
SQL> startup
ORACLE instance started.
Total System Global Area 230162432 bytes
Fixed Size 1344088 bytes
Variable Size 88083880 bytes
Database Buffers 134217728 bytes
Redo Buffers 6516736 bytes
Database mounted.
Database opened.
SQL> select count(*) from ind$ where obj#=75558;
COUNT(*)
----------
0
SQL> select /*+ full(t) */ count(*) from sys.ind$ t where obj#=75558;
COUNT(*)
----------
0
SQL> select OWNER,INDEX_NAME,TABLE_NAME,status from dba_indexes where index_name='IND_T_XIFENFEI';
no rows selected
扫尾和测试工作
SQL> delete from obj$ where obj# =75558; 1 row deleted. SQL> delete from icol$ where obj#=75558; 1 row deleted. SQL> delete from seg$ where ts#=4 and file#=4 and block#=298; 1 row deleted. SQL> commit; Commit complete. --重新创建/删除一个同名的index成功 SQL> create index chf.ind_t_xifenfei on chf.t_xifenfei(object_id); Index created. SQL> drop index chf.ind_t_xifenfei; Index dropped.
通过以上对于ind$和I_IND1操作大体上完成对于ind_t_xifenfei索引的手工删除,比较完美的实现了bbed drop index操作过程.