标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,609)
- DB2 (22)
- MySQL (71)
- Oracle (1,473)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (535)
- Oracle安装升级 (85)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 11.2.0.4最新psu-202404
- ORA-600 2131故障处理
- 收集统计信息报ORA-00600 16515问题解决
- rm -rf误删Oracle数据库恢复
- 分布式存储故障导致数据库无法启动故障处理
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
月归档:八月 2012
ORACLE用户重命名
从oracle 11.2.0.2开始提供了用户重命名的新特性,在以前的版本中,如果想对用户重命名,一般来说是先创建一个新的用户并授权,然后将原用户下的所有对象导入,然后删除旧的用户!
数据库版本信息
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
创建测试环境
SQL> create user xifenfei identified by xifenfei; User created. SQL> grant connect,resource to xifenfei; Grant succeeded. SQL> conn xifenfei/xifenfei Connected. SQL> create table t_xifenfei as select * from user_users; Table created. SQL> create index ind_t_xifenfei on t_xifenfei(user_id); Index created. SQL> conn / as sysdba Connected. SQL> select object_type,object_name from dba_objects where owner='XIFENFEI'; OBJECT_TYPE OBJECT_NAME ------------------- --------------------------------------------------------- TABLE T_XIFENFEI INDEX IND_T_XIFENFEI
尝试修改用户名
SQL> alter user xifenfei rename to xff identified by xifenfei; alter user xifenfei rename to xff identified by xifenfei * ERROR at line 1: ORA-00922: missing or invalid option --默认值是false SQL> col name for a32 SQL> col value for a24 SQL> col description for a70 SQL> set linesize 150 SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description 2 from x$ksppi a,x$ksppcv b 3 where a.inst_id = USERENV ('Instance') and b.inst_id = USERENV ('Instance') 4 5 and a.indx = b.indx 6 and upper(a.ksppinm) LIKE upper('%¶m%') 7 order by name 8 / Enter value for param: _enable_rename_user old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_enable_rename_user%') NAME VALUE DESCRIPTION -------------------------------- ------------------------ ------------------------------------------------ _enable_rename_user FALSE enable RENAME-clause using ALTER USER statement SQL> startup force restrict 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. --_enable_rename_user=false,在restrict模式也不能修改用户名 SQL> ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei; ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei * ERROR at line 1: ORA-00922: missing or invalid option
设置隐含参数
SQL> alter system set "_enable_rename_user"=true scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup restrict 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> ALTER user xifenfei RENAME TO xff IDENTIFIED BY xifenfei; User altered.
测试结果
SQL> startup force 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 object_type,object_name from dba_objects where owner='XIFENFEI'; no rows selected SQL> select object_type,object_name from dba_objects where owner='XFF'; OBJECT_TYPE OBJECT_NAME ------------------- ---------------------------------------------------- TABLE T_XIFENFEI INDEX IND_T_XIFENFEI SQL> conn xff/xifenfei Connected. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 1
相关文档和上面的测试,得出结论:数据库版本在11.2.0.2及其以上版本,_enable_rename_user设置为true,数据库启动到restrict模式可以修改用户名
bbed 使用实现 drop index 操作
这里个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操作过程.
bbed 删除 cluster table 记录
对应cluster table使用bbed删除其中的部分记录,一直没有被攻克的难关,今天突发灵感,解决了cluster table 通过bbed删除记录后验证不能通过的难题.主要修改操作:oracle在index cluster中删除一条记录实际上只是把这条记录的行头由0x6c修改为0x7c,并且把这条记录所对应的聚簇键所在行的行头中记录的comc减1;修改验证信息
模拟cluster table 环境
SQL> create cluster clu_xff(id number(4)); Cluster created. SQL> create table t_xifenfei 2 (id number(4) 3 ,name varchar2(25) 4 )CLUSTER clu_xff (id); Table created. SQL> create index ind_clu_xff ON CLUSTER clu_xff; Index created. SQL> insert into t_xifenfei values(1,'xifenfei'); 1 row created. SQL> insert into t_xifenfei values(2,'www.xifenfei.com'); 1 row created. SQL> insert into t_xifenfei values(3,'XIFENFEI'); 1 row created. SQL> insert into t_xifenfei values(4,'WWW.XIFENFEI.COM'); 1 row created. SQL> insert into t_xifenfei values(2,'WWW.xifenfei.COM'); 1 row created. SQL> insert into t_xifenfei values(3,'XFF_CHF'); 1 row created. SQL> COMMIT; Commit complete. SQL> select t.*, 2 dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location 3 from t_xifenfei t; ID NAME LOCATION ---------- ------------------------- ---------- 2 www.xifenfei.com 4_171 2 WWW.xifenfei.COM 4_171 3 XIFENFEI 4_172 3 XFF_CHF 4_172 <----需要删除记录 4 WWW.XIFENFEI.COM 4_174 1 xifenfei 4_175 6 rows selected. SQL> alter system checkpoint; System altered. SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
bbed删除记录操作
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 09:33:58 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set file 4 block 172 FILE# 4 BLOCK# 172 BBED> map File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 172 Dba:0x010000ac ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @100 struct kdbt[2], 8 bytes @114 sb2 kdbr[3] @122 ub1 freespace[8013] @128 ub1 rowdata[47] @8141 ub4 tailchk @8188 --查看该块的相关数据值 BBED> p kdbr sb2 kdbr[0] @122 8066 sb2 kdbr[1] @124 8053 sb2 kdbr[2] @126 8041 BBED> p *kdbr[0] rowdata[25] ----------- ub1 rowdata[25] @8166 0xac BBED> x /rn rowdata[25] @8166 ----------- flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@8167: 0x00 cols@8168: 1 kref@8169: 2 mref@8171: 2 hrid@8173:0x010000ac.0 nrid@8179:0x010000ac.0 col 0[2] @8185: 3 BBED> p *kdbr[1] rowdata[12] ----------- ub1 rowdata[12] @8153 0x6c BBED> x /rc rowdata[12] @8153 ----------- flag@8153: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@8154: 0x00 cols@8155: 1 col 0[8] @8157: XIFENFEI BBED> p *kdbr[2] rowdata[0] ---------- ub1 rowdata[0] @8141 0x6c BBED> x /rc rowdata[0] @8141 ---------- flag@8141: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@8142: 0x02 cols@8143: 1 col 0[7] @8145: XFF_CHF <----需要删除记录 BBED> m /x 7c offset 8141 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 172 Offsets: 8141 to 8191 Dba:0x010000ac ------------------------------------------------------------------------ 7c020100 07584646 5f434846 6c000100 08584946 454e4645 49ac0001 02000200 010000ac 00000100 00ac0000 02c10402 066c1d <32 bytes per line> BBED> m /x 01 offset 8171 File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 172 Offsets: 8171 to 8191 Dba:0x010000ac ------------------------------------------------------------------------ 01000100 00ac0000 010000ac 000002c1 0402066c 1d <32 bytes per line> BBED> p *kdbr[0] rowdata[25] ----------- ub1 rowdata[25] @8166 0xac BBED> x /rn rowdata[25] @8166 ----------- flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@8167: 0x00 cols@8168: 1 kref@8169: 2 mref@8171: 1 hrid@8173:0x010000ac.0 nrid@8179:0x010000ac.0 col 0[2] @8185: 3 BBED> sum apply Check value for File 4, Block 172: current = 0x8f87, required = 0x8f87 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/users01.dbf BLOCK = 172 Block Checking: DBA = 16777388, Block Type = KTB-managed data block data header at 0xb53ed264 kdbchk: the amount of space used is not equal to block size <----数据块使用空间错误 used=67 fsc=0 avsp=8013 dtl=8088 Block 172 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 @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 2 sb2 kdbhnrow @102 3 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 28 sb2 kdbhfseo @108 8041 sb2 kdbhavsp @110 8013 sb2 kdbhtosp @112 8013 BBED> m /x 551f offset 110 File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 172 Offsets: 110 to 126 Dba:0x010000ac ------------------------------------------------------------------------ 551f4d1f 00000100 01000200 821f751f <32 bytes per line> BBED> p kdbh struct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 2 sb2 kdbhnrow @102 3 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 28 sb2 kdbhfseo @108 8041 sb2 kdbhavsp @110 8021 sb2 kdbhtosp @112 8013 BBED> sum apply Check value for File 4, Block 172: current = 0x8f9f, required = 0x8f9f BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/users01.dbf BLOCK = 172 Block Checking: DBA = 16777388, Block Type = KTB-managed data block data header at 0xb53ed264 kdbchk: avsp(8021) > tosp(8013) <----avsp 不能大于tosp Block 172 failed with check code 6128 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 551f offset 112 File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 172 Offsets: 112 to 128 Dba:0x010000ac ------------------------------------------------------------------------ 551f0000 01000100 0200821f 751f691f <32 bytes per line> BBED> sum apply Check value for File 4, Block 172: current = 0x8f87, required = 0x8f87 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/users01.dbf BLOCK = 172 Block Checking: DBA = 16777388, Block Type = KTB-managed data block data header at 0xb53ed264 kdbchk: space available on commit is incorrect tosp=8021 fsc=0 stb=4 avsp=8021 <----tosp值不合适 Block 172 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> set count 64 COUNT 64 BBED> m /x 591f offset 112 File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 172 Offsets: 112 to 175 Dba:0x010000ac ------------------------------------------------------------------------ 591f0000 01000100 0200821f 751f691f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 4, Block 172: current = 0x8f8b, required = 0x8f8b --修改块工作完成 BBED> verify DBVERIFY - Verification starting FILE = /u01/oracle/oradata/ora11g/users01.dbf BLOCK = 172 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
启动库验证
SQL> startup ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 260049780 bytes Database Buffers 46137344 bytes Redo Buffers 6328320 bytes Database mounted. Database opened. SQL> conn chf/xifenfei Connected. SQL> select * from t_xifenfei; ID NAME ---------- ------------------------- 2 www.xifenfei.com 2 WWW.xifenfei.COM 3 XIFENFEI 4 WWW.XIFENFEI.COM 1 xifenfei --XFF_CHF记录被删除 SQL> insert into t_xifenfei values(3,'惜分飞'); 1 row created. SQL> delete from t_xifenfei where name='XIFENFEI'; 1 row deleted. SQL> commit; Commit complete. SQL> select * from t_xifenfei; ID NAME ---------- ------------------------- 2 www.xifenfei.com 2 WWW.xifenfei.COM 3 惜分飞 4 WWW.XIFENFEI.COM 1 xifenfei --证明XFF_CHF所在数据块其他dml操作正常,证明修改正确