联系：手机/微信(+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操作过程.
bbed 恢复 GLOBAL_NAME 为空故障
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
看到dbsnake关于UPDATE GLOBAL_NAME为空之后的恢复写的不是很完整,自己通过试验和对bbed的研究,完善他的blog内容(泄露一点内部的东西)
模拟错误
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> select * from global_name ; GLOBAL_NAME -------------------------------------------------------------------- ORA11G SQL> update global_name set global_name=''; 1 row updated. SQL> COMMIT; Commit complete. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. 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. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Process ID: 28306 Session ID: 125 Serial number: 5
alert日志
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc (incident=20556): ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_20556/ora11g_ora_28306_i20556.trc Wed Aug 08 23:21:48 2012 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc: ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc: ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 28306): terminating the instance due to error 600 Instance terminated by USER, pid = 28306 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid (28306) as a result of ORA-1092 Wed Aug 08 23:21:48 2012 ORA-1092 : opitsk aborting process
找出global_name相关信息(另外库中)
SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','GLOBAL_NAME','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'
SQL> select * from props$ where value$='XIFENFEI';
NAME VALUE$
------------------------------ ----------------------------------------
COMMENT$
--------------------------------------------------------------------------
GLOBAL_DB_NAME XIFENFEI
Global database name
SQL> select dump('GLOBAL_DB_NAME','16') from dual;
DUMP('GLOBAL_DB_NAME','16')
--------------------------------------------------------
Typ=96 Len=14: 47,4c,4f,42,41,4c,5f,44,42,5f,4e,41,4d,45
--得出GLOBAL_DB_NAME存储字16进制字符串为:0e474c4f42414c5f44425f4e414d45
bbed操作
--通过第三方工具定位props$表中的GLOBAL_DB_NAME列在数据块0x00400321的31行上
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 00:26:12 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
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 block 801
BLOCK# 801
BBED> map
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Dba:0x00400321
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[37] @110
ub1 freespace[5771] @184
ub1 rowdata[2233] @5955
ub4 tailchk @8188
BBED> p *kdbr[31]
rowdata[0]
----------
ub1 rowdata[0] @5955 0x2c
BBED> x /rccc
rowdata[0] @5955
----------
flag@5955: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5956: 0x02
cols@5957: 3
col 0[14] @5958: GLOBAL_DB_NAME
col 1[0] @5973: *NULL*
col 2[20] @5974: Global database name
BBED> set count 64
COUNT 64
BBED> f /x 0e474c4f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5958 to 6021 Dba:0x00400321
------------------------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d45ff 14476c6f 62616c20 64617461 62617365
206e616d 652c0003 0e474c4f 42414c5f 44425f4e 414d4506 4f524131 31471447
<32 bytes per line>
BBED> f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5998 to 6061 Dba:0x00400321
------------------------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4506 4f524131 31471447 6c6f6261 6c206461
74616261 7365206e 616d652c 00030a44 4254494d 455a4f4e 45053030 3a30300c
<32 bytes per line>
BBED> f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 6460 to 6523 Dba:0x00400321
------------------------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4508 53454544 44415441 14476c6f 62616c20
64617461 62617365 206e616d 652c0003 114e4c53 5f524442 4d535f56 45525349
<32 bytes per line>
BBED> f
BBED-00212: search string not found
BBED> d /v offset 5958
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5958 to 6021 Dba:0x00400321
-------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d45ff l .GLOBAL_DB_NAME.
14476c6f 62616c20 64617461 62617365 l .Global database
206e616d 652c0003 0e474c4f 42414c5f l name,...GLOBAL_
44425f4e 414d4506 4f524131 31471447 l DB_NAME.ORA11G.G
<16 bytes per line>
BBED> d /v offset 5998
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5998 to 6061 Dba:0x00400321
-------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4506 l .GLOBAL_DB_NAME.
4f524131 31471447 6c6f6261 6c206461 l ORA11G.Global da
74616261 7365206e 616d652c 00030a44 l tabase name,...D
4254494d 455a4f4e 45053030 3a30300c l BTIMEZONE.00:00.
<16 bytes per line>
BBED> d /v offset 6460
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 6460 to 6523 Dba:0x00400321
-------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4508 l .GLOBAL_DB_NAME.
53454544 44415441 14476c6f 62616c20 l SEEDDATA.Global
64617461 62617365 206e616d 652c0003 l database name,..
114e4c53 5f524442 4d535f56 45525349 l .NLS_RDBMS_VERSI
<16 bytes per line>
--通过时上面的查找可以知道数据库对GLOBAL_DB_NAME有进行两次update操作
--GLOBAL_DB_NAME初始化值SEEDDATA,第一次更新为ORA11G,第二次更新为null
BBED> p kdbr[31]
sb2 kdbr[31] @172 5863
--这里可以发现我们看到offset 5995开始有值,但是row directory却指向了5863
BBED> d offset 5863 count 128
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5863 to 5990 Dba:0x00400321
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 2c02030e
474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520
<32 bytes per line>
--通过dump看到row directory指向的值和实际的数据相差(5995-5863),都是0
BBED> d /v offset 5900
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5900 to 6027 Dba:0x00400321
-------------------------------------------------------
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 0000002c 02030e47 4c4f4241 l .......,...GLOBA
4c5f4442 5f4e414d 45ff1447 6c6f6261 l L_DB_NAME..Globa
6c206461 74616261 7365206e 616d652c l l database name,
00030e47 4c4f4241 4c5f4442 5f4e414d l ...GLOBAL_DB_NAM
45064f52 41313147 14476c6f 62616c20 l E.ORA11G.Global
<16 bytes per line>
--需要指定的新值前面也存在同样的0,所以模仿的处理方法,让row directory同样向前偏移92
BBED> m /x 0f17 offset 172
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 172 to 299 Dba:0x00400321
------------------------------------------------------------------------
0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> p kdbr[31]
sb2 kdbr[31] @172 5903
BBED> p *kdbr[31]
rowdata[40]
-----------
ub1 rowdata[40] @5995 0x2c
BBED> x /rccc
rowdata[40] @5995
-----------
flag@5995: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5996: 0x00
cols@5997: 3
col 0[14] @5998: GLOBAL_DB_NAME
col 1[6] @6013: ORA11G
col 2[20] @6020: Global database name
修改lock信息
BBED> m /x 02 offset 5996
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5996 to 6123 Dba:0x00400321
------------------------------------------------------------------------
02030e47 4c4f4241 4c5f4442 5f4e414d 45064f52 41313147 14476c6f 62616c20
64617461 62617365 206e616d 652c0003 0a444254 494d455a 4f4e4505 30303a30
300c4442 2074696d 65207a6f 6e652c00 02174e4f 5f555345 5249445f 56455249
46494552 5f53414c 54203633 39364335 38414231 37414530 30374539 41373238
<32 bytes per line>
BBED> d offset 5955
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5955 to 6082 Dba:0x00400321
------------------------------------------------------------------------
2c02030e 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162
61736520 6e616d65 2c02030e 474c4f42 414c5f44 425f4e41 4d45064f 52413131
4714476c 6f62616c 20646174 61626173 65206e61 6d652c00 030a4442 54494d45
5a4f4e45 0530303a 30300c44 42207469 6d65207a 6f6e652c 0002174e 4f5f5553
<32 bytes per line>
BBED> m /x 2c00
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5955 to 6082 Dba:0x00400321
------------------------------------------------------------------------
2c00030e 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162
61736520 6e616d65 2c02030e 474c4f42 414c5f44 425f4e41 4d45064f 52413131
4714476c 6f62616c 20646174 61626173 65206e61 6d652c00 030a4442 54494d45
5a4f4e45 0530303a 30300c44 42207469 6d65207a 6f6e652c 0002174e 4f5f5553
<32 bytes per line>
--验证块
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe836, required = 0xe836
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801
Block Checking: DBA = 4195105, Block Type = KTB-managed data block
data header at 0xb677b25c
kdbchk: the amount of space used is not equal to block size
used=2056 fsc=6 avsp=6040 dtl=8096
Block 801 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
--修改_ktbitfsc信息，让其通过块验证
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0004
ub2 kxidslt @46 0x000e
ub4 kxidsqn @48 0x000001d4
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c00a93
ub2 kubaseq @56 0x0083
ub1 kubarec @58 0x33
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x000c78fe
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0016
ub4 kxidsqn @72 0x000001eb
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0015b
ub2 kubaseq @80 0x008f
ub1 kubarec @82 0x0d
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 6
ub2 _ktbitwrp @86 0x0006
ub4 ktbitbas @88 0x00000000
BBED> m /x 00 offset 86
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 86 to 213 Dba:0x00400321
------------------------------------------------------------------------
00000000 00000001 25000600 5c00e716 98179e17 00002500 701f571e 92189c1e
101efb1d ffff981d 551d0f1d d91cb71c 941c731c 4a1c1a1c ef1bc51b 9c1b701b
471b191b d61a931a 3c1a101a ee19bc19 86194e19 0d190f17 60181618 9a175e17
3d170000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe830, required = 0xe830
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801
Block Checking: DBA = 4195105, Block Type = KTB-managed data block
data header at 0xb677b25c
kdbchk: space available on commit is incorrect
tosp=6046 fsc=0 stb=0 avsp=6040
Block 801 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
--修改kdbhtosp信息
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
b1 kdbhntab @93 1
b2 kdbhnrow @94 37
sb2 kdbhfrre @96 6
sb2 kdbhfsbo @98 92
sb2 kdbhfseo @100 5863
b2 kdbhavsp @102 6040
b2 kdbhtosp @104 6046
BBED> d offset 102
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 102 to 229 Dba:0x00400321
------------------------------------------------------------------------
98179e17 00002500 701f571e 92189c1e 101efb1d ffff981d 551d0f1d d91cb71c
941c731c 4a1c1a1c ef1bc51b 9c1b701b 471b191b d61a931a 3c1a101a ee19bc19
86194e19 0d190f17 60181618 9a175e17 3d170000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> d offset 104
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 104 to 231 Dba:0x00400321
------------------------------------------------------------------------
9e170000 2500701f 571e9218 9c1e101e fb1dffff 981d551d 0f1dd91c b71c941c
731c4a1c 1a1cef1b c51b9c1b 701b471b 191bd61a 931a3c1a 101aee19 bc198619
4e190d19 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 98
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 104 to 231 Dba:0x00400321
------------------------------------------------------------------------
98170000 2500701f 571e9218 9c1e101e fb1dffff 981d551d 0f1dd91c b71c941c
731c4a1c 1a1cef1b c51b9c1b 701b471b 191bd61a 931a3c1a 101aee19 bc198619
4e190d19 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
--至此修改row directory指针完成
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe836, required = 0xe836
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801
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 313860096 bytes Fixed Size 1344652 bytes Variable Size 260049780 bytes Database Buffers 46137344 bytes Redo Buffers 6328320 bytes Database mounted. Database opened. SQL> select * from global_name ; GLOBAL_NAME -------------------------------------------------------- ORA11G SQL>
使用oradebug修改数据库scn
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
闲着无事看到几篇文章介绍了使用oradebug修改数据库scn的案例,这里也做了两个测试,发现该功能确实很巧妙,通过修改内存中的scn值,然后写入控制文件和数据文件,实现修改scn的方法,不过同样该方法的危害性极大,这里仅供测试使用,生产环境切不可乱使用,可能引起很严重后果
数据库版本信息
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> select '惜分飞' XIFENFEI FROM DUAL; XIFENF ------ 惜分飞
在open库中修改scn
SQL> oradebug setmypid
Statement processed.
--查看当前scn
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 0007A09F 00000019 00000000 00000000 00000000 00000000 20009034
SQL> select CHECKPOINT_CHANGE# a from v$datafile;
A
----------
499314
499314
499314
499314
SQL> select dbms_flashback.get_system_change_number a from dual;
A
----------
499877
SQL> select to_number('7A09F','xxxxxxxxx') from dual;
TO_NUMBER('7A09F','XXXXXXXXX')
------------------------------
499871
--修改内存中scn值(十进制)
SQL> oradebug poke 0x20009228 4 8
BEFORE: [20009228, 2000922C) = 00000000
AFTER: [20009228, 2000922C) = 00000008
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000008 0007A0D8 00000052 00000000 00000000 00000000 00000000 20009034
SQL> col a for 999999999999999
SQL> select dbms_flashback.get_system_change_number a from dual;
A
------------
34360238301
SQL> select to_number('8','xx')*4294967296+to_number('0007A0D8','xxxxxxxx') a from dual;
A
----------------
34360238296
--做一个checkpoint为了内存中的scn值写入控制文件和数据文件
SQL> alter system checkpoint;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 96471516 bytes
Database Buffers 213909504 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile;
A
----------------
34360238496
34360238496
34360238496
34360238496
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
A
----------------
34360238496
34360238496
34360238496
34360238496
在mount库中修改scn
SQL> startup mount
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 96471516 bytes
Database Buffers 213909504 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
--因为数据库是mount状态不能看到scn值
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 20009034
SQL> col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
A
----------------
34360240739
34360240739
34360240739
34360240739
--求出WRAP SCN值
SQL> select 34360240739/4294967296 from dual;
34360240739/4294967296
----------------------
8.00011697
--修改内存中scn值(十六进制)
SQL> oradebug poke 0x20009228 4 0x0000000a
BEFORE: [20009228, 2000922C) = 00000000
AFTER: [20009228, 2000922C) = 0000000A
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000000 00000000 00000000 00000000 00000000 00000000 20009034
SQL> alter database open;
Database altered.
SQL> select dbms_flashback.get_system_change_number a from dual
2 ;
A
----------------
42949673074
--注意:使用此种方法修改BASE SCN如果不指定,会从0开始计数
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000077 0000001C 00000000 00000000 00000000 00000000 20009034
SQL> select to_number('A','xx')*4294967296+to_number('00000077','xxxxxxxx') a from dual;
A
----------------
42949673079
SQL> alter system checkpoint;
System altered.
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
A
----------------
42949673095
42949673095
42949673095
42949673095
SQL> select CHECKPOINT_CHANGE# a from v$datafile;
A
----------------
42949673095
42949673095
42949673095
42949673095
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 96471516 bytes
Database Buffers 213909504 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
A
----------------
42949673231
42949673231
42949673231
42949673231
在oradebug推进scn的过程中，需要注意不同平台，不同位数的ORACLE数据库可能推进方式有一定的区别，操作前最好在系统平台位数上进行测试，否则有可能导致恢复后果更加麻烦
使用bbed解决ORA-00607/ORA-00600[4194]故障
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用bbed解决ORA-00607/ORA-00600[4194]故障
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
ORA-00607/ORA-00600[4194]错误
数据库启动因为出现ORA-00607/ORA-00600[4194],导致数据库不能正常open
Fri Nov 4 23:10:37 2011 SMON: enabling cache recovery Fri Nov 4 23:10:37 2011 ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the heartbeat ARCH ARC2 started with pid=18, OS id=21535 Fri Nov 4 23:10:38 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc: ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], [] Fri Nov 4 23:10:41 2011 Doing block recovery for file 1 block 18 Block recovery from logseq 2, block 48668 to scn 458453 Fri Nov 4 23:10:41 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log Block recovery stopped at EOT rba 2.48670.16 Block recovery completed at rba 2.48670.16, scn 0.458451 Doing block recovery for file 1 block 9 Block recovery from logseq 2, block 48668 to scn 458450 Fri Nov 4 23:10:41 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log Block recovery completed at rba 2.48670.16, scn 0.458451 Fri Nov 4 23:10:41 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], [] Error 604 happened during db open, shutting down database USER: terminating instance due to error 604 Instance terminated by USER, pid = 21529 ORA-1092 signalled during: ALTER DATABASE OPEN...
分析trace文件
*** SESSION ID:(159.3) 2011-11-04 23:10:37.648
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
*** ktuc_diag_dmp: dump of current change vector
ktudb redo: siz: 252 spc: 7200 flg: 0x0012 seq: 0x0037 rec: 0x06
xid: 0x0000.022.00000028
ktubl redo: slt: 34 rci: 0 opc: 11.1 objn: 15 objd: 15 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00400012.0037.1f
prev ctl max cmt scn: 0x0000.0006c75b prev tx cmt scn: 0x0000.0006c75d
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 4194318 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0000.020.00000029 uba: 0x00400013.0037.05
flg: C--- lkc: 0 scn: 0x0000.0006fecb
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0040006a hdba: 0x00400069
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 191
ncol: 17 nnew: 12 size: 0
col 1: [ 9] 5f 53 59 53 53 4d 55 31 24
col 2: [ 2] c1 02
col 3: [ 2] c1 03
col 4: [ 2] c1 0a
col 5: [ 4] c3 2e 55 0a
col 6: [ 1] 80
col 7: [ 3] c2 02 59
col 8: [ 3] c2 02 02
col 9: [ 1] 80
col 10: [ 2] c1 03
col 11: [ 2] c1 02
col 16: [ 2] c1 02
*** 2011-11-04 23:10:38.086
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Current SQL statement for this session:
update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,
scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+27 call ksedst1() 0 ? 1 ?
ksedmp()+557 call ksedst() 0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksfdmp()+19 call ksedmp() 3 ? BFFA8C28 ? AC152C0 ?
CBD2DA0 ? 3 ? BFFA9764 ?
kgeriv()+188 call 00000000 CBD2DA0 ? 3 ?
kseipre()+42 call kgeriv() CBD2DA0 ? B6A50020 ? 1062 ?
2 ? BFFA8C68 ? BFFA8C5C ?
ksesic2()+21 call kseipre() 1062 ? 2 ? BFFA8C68 ?
32B36940 ? BFFA8D38 ?
8C4A3A9 ?
kturdb()+1757 call ksesic2() 1062 ? 0 ? 23 ? 0 ? 0 ? 6 ?
0 ?
kco_issue_callback( call 00000000 B6A09FA4 ? B6A0A01E ? 11 ?
)+176 2D306014 ? B6A387C0 ?
kcoapl()+2440 call kco_issue_callback( B6A09FA0 ? 2D306000 ?
) B6A387C0 ?
kcbapl()+322 call kcoapl() B6A09FA0 ? 2D306000 ? 1 ? 0 ?
2000 ? 0 ? B6A387C0 ?
kcrfw_redo_gen()+94 call kcbapl() B6A09FA0 ? 2D3F6A1C ?
10 CBE3AE8 ? 0 ? B6A387C0 ?
kcbchg1_main()+8669 call kcrfw_redo_gen() 3 ? BFFA9358 ? BFFA9370 ?
CBE3AE8 ? 0 ? BFFA9390 ?
kcbchg1()+63 call kcbchg1_main() 0 ? 3 ? BFFA97B0 ? BFFA9798 ?
0 ? 0 ?
ktuchg()+3344 call kcbchg1() 0 ? 3 ? BFFA97B0 ? BFFA9798 ?
0 ? 0 ?
ktbchg2()+493 call ktuchg() 2 ? 2F9EEF8C ? 3 ? B6A0CA98 ?
B6A0CAA0 ? B6A09FA0 ?
B6A387C0 ? B6A0C7A0 ? 0 ? 0 ?
kddchg()+1661 call ktbchg2() 0 ? 2F9EEF8C ? B6A0CA98 ?
B6A0CAA0 ? B6A09FA0 ?
B6A387B8 ? B6A0C7A0 ? 0 ? 0 ?
kduovw()+7960 call kddchg() B6A3877C ? B6A0CA98 ?
B6A0CAA0 ? B6A09FA0 ?
B6A0C7A0 ? 0 ? 0 ? BFFA9C58 ?
kduurp()+2316 call kduovw() B6A3877C ? 0 ? 10 ?
B6A357A4 ? 0 ? B6A3877C ?
kdusru()+4339 call kduurp() B6A3877C ? 958412D ?
CBDC720 ? BFFA9FEC ? B8 ?
B6A40380 ?
kauupd()+366 call kdusru() B6A357A4 ? 2F9EEFF8 ?
B6A3877C ? 0 ?
updrow()+5889 call kauupd() B6A357A0 ? 2F9EEFF8 ?
B6A3877C ? 0 ? 2FA479FC ? E ?
F ? 2F9EF31C ? 12 ?
BFFB0544 ? BFFB04E4 ?
qerupRowProcedure() call updrow() 2F9E5B64 ? 7FFF ? DB4 ? 48 ?
+62 2F9EFBF4 ? BFFB08B4 ?
qerupFetch()+1187 call 00000000 2F9EF4B0 ? 7FFF ?
updaul()+3474 call 00000000 2F9EF4B0 ? 0 ? 2F9EF370 ?
7FFF ?
updThreePhaseExe()+ call updaul() 2F9E5B64 ? BFFB0D2C ? 0 ?
3470
updexe()+813 call updThreePhaseExe() 2F9E5B64 ? 0 ? B6A3877C ?
BFFB0E00 ? 2F9E5B64 ? 1 ?
BFFB0E00 ? 0 ?
opiexe()+17967 call updexe() 2F9E5B64 ? BFFB1074 ?
opiodr()+2347 call 00000000 4 ? 4 ? BFFB25A8 ?
rpidrus()+434 call opiodr() 4 ? 4 ? BFFB25A8 ? 2 ?
skgmstack()+210 call 00000000 BFFB2004 ? 97492FE ?
CBD2E9C ? BFFB1FE8 ?
BFFB24EC ? BFFB2004 ?
rpidru()+98 call skgmstack() BFFB1FE8 ? CBD2B60 ? F618 ?
9749546 ? BFFB2004 ?
rpiswu2()+1061 call 00000000 BFFB24EC ? BFFB25E8 ?
BFFB2500 ? 2 ? BFFB24B0 ?
5953 ?
rpidrv()+1915 call rpiswu2() 32F0A1D4 ? 0 ? BFFB24B0 ? 2 ?
BFFB2528 ? 0 ? BFFB24B0 ? 0 ?
9749800 ? 97498DC ?
BFFB24EC ? 8 ?
rpiexe()+65 call rpidrv() 2 ? 4 ? BFFB25A8 ? 8 ?
ktuscu()+697 call rpiexe() 2 ? 1C ? 2A ? 32FF3404 ? 0 ?
BFFB2710 ?
kqrcmt()+945 call 00000000 32AFA70C ? 3 ?
ktcrcm()+945 call kqrcmt() 31A2B84C ? 1 ? 0 ?
ktuswr()+1855 call ktcrcm() 31A2B84C ? 0 ? 0 ? 0 ? 0 ?
1 ? 0 ? 0 ?
ktusmous_online_und call ktuswr() 1 ? 0 ? 0 ? 0 ? 0 ? 0 ?
oseg()+951
ktusmout_online_ut( call ktusmous_online_und 1 ? A ? 0 ? 3 ?
)+737 oseg()
ktusmiut_init_ut()+ call ktusmout_online_ut( 1 ? 0 ? 0 ?
1084 )
ktuini()+688 call ktusmiut_init_ut() 0 ? BFFB4744 ? CBD2E9C ?
CBD2E9C ? CBD2DA0 ? 7 ?
adbdrv()+5699 call ktuini() 0 ? 0 ? 0 ? 0 ? 64000000 ?
3 ?
opiexe()+18301 call adbdrv() 59D4 ? 0 ? 9EE16E2F ? 494C4 ?
32B33CD0 ? 0 ?
opiosq0()+3918 call opiexe() 4 ? 0 ? BFFB8988 ?
kpooprx()+250 call opiosq0() 3 ? E ? BFFB8B90 ? A4 ?
kpoal8()+867 call kpooprx() BFFBAD68 ? BFFB990C ? 13 ?
1 ? 0 ? A4 ?
opiodr()+2347 call 00000000 5E ? 17 ? BFFBAD64 ?
ttcpip()+4227 call 00000000 5E ? 17 ? BFFBAD64 ? 0 ?
DABCA66 ? 93 ?
opitsk()+1991 call ttcpip() CBDA5A0 ? 5E ? BFFBAD64 ? 0 ?
BFFBA244 ? BFFBAE88 ?
opiino()+1387 call opitsk() 0 ? 0 ?
opiodr()+2347 call 00000000 3C ? 4 ? BFFBB950 ?
opidrv()+915 call opiodr() 3C ? 4 ? BFFBB950 ? 0 ?
sou2o()+113 call opidrv() 3C ? 4 ? BFFBB950 ?
opimai_real()+212 call sou2o() BFFBB934 ? 3C ? 4 ?
BFFBB950 ?
main()+111 call opimai_real() 2 ? BFFBB980 ?
__libc_start_main() call 00000000 2 ? BFFBBA44 ? BFFBBA50 ?
+220 47D9A828 ? 0 ? 1 ?
--------------------- Binary Stack Dump ---------------------
数据库在open的时候,需要去修改undo$对象的状态,从2该为3(offline->online)这个时候需要使用到系统回滚段,但是在使用系统回滚段的时候,使用uba=0x00400012的时候发生异常,导致数据库不能正常open,从而出现了ORA-00600[4194]的错误.而出现这个故障的原因,很可能是由于file 1 block 18块的异常导致.我们需要做的,就是让数据库启动的时候不使用file 1 block 18的block,而让数据库去另外的分配一个undo块.
bbed清除rollback分配块信息
[oracle@xifenfei ~]$ bbed listfile=list mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Sat Nov 5 01:11:49 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 1 block 9
FILE# 1
BLOCK# 9
BBED> map
File: /u01/oracle/oradata/XFF/system01.dbf (1)
Block: 9 Dba:0x00400009
------------------------------------------------------------
Unlimited Undo Segment Header
struct kcbh, 20 bytes @0
struct ktech, 72 bytes @20
struct ktemh, 16 bytes @92
struct ktetb[6], 48 bytes @108
struct ktuxc, 104 bytes @4148
struct ktuxe[255], 10200 bytes @4252
ub4 tailchk @8188
BBED> p ktuxc
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
ub4 kscnbas @4148 0x0006c75b
ub2 kscnwrp @4152 0x0000
struct ktuxcuba, 8 bytes @4156
ub4 kubadba @4156 0x00400012
ub2 kubaseq @4160 0x0037
ub1 kubarec @4162 0x1f
sb2 ktuxcflg @4164 1 (KTUXCFSK)
ub2 ktuxcseq @4166 0x0037
sb2 ktuxcnfb @4168 1
ub4 ktuxcinc @4172 0x00000000
sb2 ktuxcchd @4176 34
sb2 ktuxcctl @4178 32
ub2 ktuxcmgc @4180 0x8002
ub4 ktuxcopt @4188 0x7ffffffe
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x00400012
ub2 kubaseq @4196 0x0037
ub1 kubarec @4198 0x05
sb2 ktufbext @4200 1
sb2 ktufbspc @4202 7200
struct ktuxcfbp[1], 12 bytes @4204
struct ktufbuba, 8 bytes @4204
ub4 kubadba @4204 0x00000000
ub2 kubaseq @4208 0x0035
ub1 kubarec @4210 0x2a
sb2 ktufbext @4212 5
sb2 ktufbspc @4214 3446
struct ktuxcfbp[2], 12 bytes @4216
struct ktufbuba, 8 bytes @4216
ub4 kubadba @4216 0x00000000
ub2 kubaseq @4220 0x0035
ub1 kubarec @4222 0x37
sb2 ktufbext @4224 5
sb2 ktufbspc @4226 1336
struct ktuxcfbp[3], 12 bytes @4228
struct ktufbuba, 8 bytes @4228
ub4 kubadba @4228 0x00000000
ub2 kubaseq @4232 0x0000
ub1 kubarec @4234 0x00
sb2 ktufbext @4236 0
sb2 ktufbspc @4238 0
struct ktuxcfbp[4], 12 bytes @4240
struct ktufbuba, 8 bytes @4240
ub4 kubadba @4240 0x00000000
ub2 kubaseq @4244 0x0000
ub1 kubarec @4246 0x00
sb2 ktufbext @4248 0
sb2 ktufbspc @4250 0
BBED> set count 16
COUNT 16
########################################################
使用bbed修改相关参数
########################################################
启动数据库
SQL> startup ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1219160 bytes Variable Size 96470440 bytes Database Buffers 213909504 bytes Redo Buffers 7168000 bytes Database mounted. Database opened. SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
通过bbed模拟ORA-00607/ORA-00600 4194 故障
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：通过bbed模拟ORA-00607/ORA-00600 4194 故障
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在数据库恢复的案例中,遇到system rollback异常的故障算是中彩票了.处理起来比较麻烦,有些情况甚至是无法处理.这里通过试验模拟ORA-00607/ORA-00600[4194].类此的错误在一次银联的数据库恢复中也遇到过,不过当时由于功底不深,理解出现部分误差.
通过bbed模拟ORA-00607/ORA-00600[4194]错误
[oracle@xifenfei ~]$ bbed listfile=list mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Fri Nov 4 22:59:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/oracle/oradata/XFF/system01.dbf 0
2 /u01/oracle/oradata/XFF/undotbs01.dbf 0
3 /u01/oracle/oradata/XFF/sysaux01.dbf 0
4 /u01/oracle/oradata/XFF/users01.dbf 0
5 /u01/oracle/oradata/XFF/datfttuser.dbf 0
BBED> set block 9
BLOCK# 9
BBED> map
File: /u01/oracle/oradata/XFF/system01.dbf (1)
Block: 9 Dba:0x00400009
------------------------------------------------------------
Unlimited Undo Segment Header
struct kcbh, 20 bytes @0
struct ktech, 72 bytes @20
struct ktemh, 16 bytes @92
struct ktetb[6], 48 bytes @108
struct ktuxc, 104 bytes @4148
struct ktuxe[255], 10200 bytes @4252
ub4 tailchk @8188
BBED> p ktuxc
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
ub4 kscnbas @4148 0x0006c75b
ub2 kscnwrp @4152 0x0000
struct ktuxcuba, 8 bytes @4156
ub4 kubadba @4156 0x00400012
ub2 kubaseq @4160 0x0037
ub1 kubarec @4162 0x1f
sb2 ktuxcflg @4164 1 (KTUXCFSK)
ub2 ktuxcseq @4166 0x0037
sb2 ktuxcnfb @4168 1 <==free undo block num
ub4 ktuxcinc @4172 0x00000000
sb2 ktuxcchd @4176 34
sb2 ktuxcctl @4178 32
ub2 ktuxcmgc @4180 0x8002
ub4 ktuxcopt @4188 0x7ffffffe
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x00400013 <==uba (模拟试验修改为其他uba地址)
ub2 kubaseq @4196 0x0037 <==uba sequence
ub1 kubarec @4198 0x05
sb2 ktufbext @4200 1
sb2 ktufbspc @4202 7200
struct ktuxcfbp[1], 12 bytes @4204
struct ktufbuba, 8 bytes @4204
ub4 kubadba @4204 0x00000000
ub2 kubaseq @4208 0x0035
ub1 kubarec @4210 0x2a
sb2 ktufbext @4212 5
sb2 ktufbspc @4214 3446
struct ktuxcfbp[2], 12 bytes @4216
struct ktufbuba, 8 bytes @4216
ub4 kubadba @4216 0x00000000
ub2 kubaseq @4220 0x0035
ub1 kubarec @4222 0x37
sb2 ktufbext @4224 5
sb2 ktufbspc @4226 1336
struct ktuxcfbp[3], 12 bytes @4228
struct ktufbuba, 8 bytes @4228
ub4 kubadba @4228 0x00000000
ub2 kubaseq @4232 0x0000
ub1 kubarec @4234 0x00
sb2 ktufbext @4236 0
sb2 ktufbspc @4238 0
struct ktuxcfbp[4], 12 bytes @4240
struct ktufbuba, 8 bytes @4240
ub4 kubadba @4240 0x00000000
ub2 kubaseq @4244 0x0000
ub1 kubarec @4246 0x00
sb2 ktufbext @4248 0
sb2 ktufbspc @4250 0
BBED> set dba 0x00400013
DBA 0x00400013 (4194323 1,19)
BBED> p ktubh
struct ktubh, 26 bytes @20
struct ktubhxid, 8 bytes @20
ub2 kxidusn @20 0x0000
ub2 kxidslt @22 0x0020
ub4 kxidsqn @24 0x00000029
ub2 ktubhseq @28 0x0037 <==uba seq
ub1 ktubhcnt @30 0x05
ub1 ktubhirb @31 0x05
ub1 ktubhicl @32 0x00
ub1 ktubhflg @33 0x00
ub2 ktubhidx[0] @34 0x1fe8
ub2 ktubhidx[1] @36 0x1f2c
ub2 ktubhidx[2] @38 0x1e70
ub2 ktubhidx[3] @40 0x1db4
ub2 ktubhidx[4] @42 0x1cf8
ub2 ktubhidx[5] @44 0x1c3c
BBED> set dba 0x00400012
DBA 0x00400012 (4194322 1,18)
BBED> p ktubh
struct ktubh, 86 bytes @20
struct ktubhxid, 8 bytes @20
ub2 kxidusn @20 0x0000
ub2 kxidslt @22 0x0020
ub4 kxidsqn @24 0x00000029
ub2 ktubhseq @28 0x0037
ub1 ktubhcnt @30 0x23
ub1 ktubhirb @31 0x23
ub1 ktubhicl @32 0x00
ub1 ktubhflg @33 0x00
ub2 ktubhidx[0] @34 0x1fe8
…………
ub2 ktubhidx[35] @104 0x00b4
BBED> set block 9
BLOCK# 9
BBED> set count 16
COUNT 16
BBED> m /x 12004000 offset 4192
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/XFF/system01.dbf (1)
Block: 9 Offsets: 4192 to 4207 Dba:0x00400009
------------------------------------------------------------------------
12004000 37000500 0100201c 00000000
<32 bytes per line>
BBED> p ktuxc
struct ktuxc, 104 bytes @4148
struct ktuxcscn, 8 bytes @4148
ub4 kscnbas @4148 0x0006c75b
ub2 kscnwrp @4152 0x0000
struct ktuxcuba, 8 bytes @4156
ub4 kubadba @4156 0x00400012
ub2 kubaseq @4160 0x0037
ub1 kubarec @4162 0x1f
sb2 ktuxcflg @4164 1 (KTUXCFSK)
ub2 ktuxcseq @4166 0x0037
sb2 ktuxcnfb @4168 1
ub4 ktuxcinc @4172 0x00000000
sb2 ktuxcchd @4176 34
sb2 ktuxcctl @4178 32
ub2 ktuxcmgc @4180 0x8002
ub4 ktuxcopt @4188 0x7ffffffe
struct ktuxcfbp[0], 12 bytes @4192
struct ktufbuba, 8 bytes @4192
ub4 kubadba @4192 0x00400012 <==uba已经被修改
ub2 kubaseq @4196 0x0037
ub1 kubarec @4198 0x05
sb2 ktufbext @4200 1
sb2 ktufbspc @4202 7200
struct ktuxcfbp[1], 12 bytes @4204
struct ktufbuba, 8 bytes @4204
ub4 kubadba @4204 0x00000000
ub2 kubaseq @4208 0x0035
ub1 kubarec @4210 0x2a
sb2 ktufbext @4212 5
sb2 ktufbspc @4214 3446
struct ktuxcfbp[2], 12 bytes @4216
struct ktufbuba, 8 bytes @4216
ub4 kubadba @4216 0x00000000
ub2 kubaseq @4220 0x0035
ub1 kubarec @4222 0x37
sb2 ktufbext @4224 5
sb2 ktufbspc @4226 1336
struct ktuxcfbp[3], 12 bytes @4228
struct ktufbuba, 8 bytes @4228
ub4 kubadba @4228 0x00000000
ub2 kubaseq @4232 0x0000
ub1 kubarec @4234 0x00
sb2 ktufbext @4236 0
sb2 ktufbspc @4238 0
struct ktuxcfbp[4], 12 bytes @4240
struct ktufbuba, 8 bytes @4240
ub4 kubadba @4240 0x00000000
ub2 kubaseq @4244 0x0000
ub1 kubarec @4246 0x00
sb2 ktufbext @4248 0
sb2 ktufbspc @4250 0
BBED> sum apply
Check value for File 1, Block 9:
current = 0xe686, required = 0xe686
启动数据库
SQL> startup ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1219160 bytes Variable Size 96470440 bytes Database Buffers 213909504 bytes Redo Buffers 7168000 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced
alert日志
Fri Nov 4 23:10:37 2011 SMON: enabling cache recovery Fri Nov 4 23:10:37 2011 ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the heartbeat ARCH ARC2 started with pid=18, OS id=21535 Fri Nov 4 23:10:38 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc: ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], [] Fri Nov 4 23:10:41 2011 Doing block recovery for file 1 block 18 Block recovery from logseq 2, block 48668 to scn 458453 Fri Nov 4 23:10:41 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log Block recovery stopped at EOT rba 2.48670.16 Block recovery completed at rba 2.48670.16, scn 0.458451 Doing block recovery for file 1 block 9 Block recovery from logseq 2, block 48668 to scn 458450 Fri Nov 4 23:10:41 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0 Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log Block recovery completed at rba 2.48670.16, scn 0.458451 Fri Nov 4 23:10:41 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], [] Error 604 happened during db open, shutting down database USER: terminating instance due to error 604 Instance terminated by USER, pid = 21529 ORA-1092 signalled during: ALTER DATABASE OPEN...
通过bbed解决ORA-00600[4000]案例
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库启动出现ORA-00600[4000]错误
Fri Nov 4 06:50:38 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7046.trc: ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], [] Fri Nov 4 06:50:40 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7046.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], [] Fri Nov 4 06:50:40 2011 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 7046 ORA-1092 signalled during: ALTER DATABASE OPEN...
查看trace文件
*** 2011-11-04 06:50:38.942
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4000], [5], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
Block header dump: 0x0040007a
Object id on Block? Y
seg/obj: 0x12 csc: 0x00.1020770d itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.029.0000029a 0x00802381.01f9.03 --U- 1 fsc 0x0000.1020770e
查询trace相关数据对应值
SQL> select DBMS_UTILITY.data_block_address_file (TO_NUMBER ('0040007a','xxxxxxxx')) file_no,
2 DBMS_UTILITY.data_block_address_block (TO_NUMBER ('0040007a','xxxxxxxx')) block_no
3 from dual;
FILE_NO BLOCK_NO
---------- ----------
1 122
SQL> select to_number('1020770e','xxxxxxxxxxx') itl_commit from dual;
ITL_COMMIT
----------
270563086
SQL> select to_number('1020770d','xxxxxxxxxxxx') csc from dual;
CSC
----------
270563085
通过这里的分析,我们可以得出file 1 block 122的内容未提交,可能是导致错误ORA-00600[4000],尝试这手工提交该事务
BBED> set file 1 block 122
FILE# 1
BLOCK# 122
BBED> map
File: /u01/oracle/oradata/XFF/system01.dbf (1)
Block: 122 Dba:0x0040007a
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 48 bytes @20
struct kdbh, 14 bytes @68
struct kdbt[1], 4 bytes @82
sb2 kdbr[108] @86
ub1 freespace[873] @302
ub1 rowdata[7013] @1175
ub4 tailchk @8188
BBED> p ktbbh
struct ktbbh, 48 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00000012
ub4 ktbbhod1 @24 0x00000012
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x1020770d
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 1
ub1 ktbbhflg @38 0x02 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x00000000
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0005
ub2 kxidslt @46 0x0029
ub4 kxidsqn @48 0x0000029a
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00802381
ub2 kubaseq @56 0x01f9
ub1 kubarec @58 0x03
ub2 ktbitflg @60 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x1020770e
BBED> set count 16
COUNT 16
BBED> m /x 0180 offset 60
File: /u01/oracle/oradata/XFF/system01.dbf (1)
Block: 122 Offsets: 60 to 75 Dba:0x0040007a
------------------------------------------------------------------------
01800000 0e772010 00016c00 ffffea00
<32 bytes per line>
BBED> p ktbbh
struct ktbbh, 48 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00000012
ub4 ktbbhod1 @24 0x00000012
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x1020770d
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 1
ub1 ktbbhflg @38 0x02 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x00000000
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0005
ub2 kxidslt @46 0x0029
ub4 kxidsqn @48 0x0000029a
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00802381
ub2 kubaseq @56 0x01f9
ub1 kubarec @58 0x03
ub2 ktbitflg @60 0x8001 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x1020770e
BBED> sum apply
Check value for File 1, Block 122:
current = 0x6902, required = 0x6902
尝试重启库
SQL> startup ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1219160 bytes Variable Size 92276136 bytes Database Buffers 218103808 bytes Redo Buffers 7168000 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced
查看alert日志
Fri Nov 4 07:42:46 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7702.trc: ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [] Fri Nov 4 07:42:46 2011 Errors in file /u01/oracle/admin/XFF/udump/xff_ora_7702.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [] Fri Nov 4 07:42:46 2011 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 7702 ORA-1092 signalled during: ALTER DATABASE OPEN...
分析trace文件
*** 2011-11-04 07:42:46.273
Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
[ktbdchk] -- readers_dsz -- bad dscn
scn: 0x0000.1020770escn: 0x0000.0021fa09
*** 2011-11-04 07:42:46.530
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
Block header dump: 0x0040007a
Object id on Block? Y
seg/obj: 0x12 csc: 0x00.1020770d itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.029.0000029a 0x00802381.01f9.03 C--- 0 scn 0x0000.1020770e
根据这个错误提示ktbdchk–>bad dscn,猜测ktbdchk是header scn中的ktbdchk,查找1020770e发现是itl comomit scn,通过bbed查看
BBED> p kcvfhckp.kcvcpscn struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x0021fa09 ub2 kscnwrp @488 0x0000
通过这里可以知道ORA-00600[ktbdchk1: bad dscn]是因为itl comomit scn远大于datafile header scn,从而oracle认为datafile header scn是错误的,从而提示ktbdchk1: bad dscn.尝试bbed修改itl comomit scn
BBED> p ktbbh
struct ktbbh, 48 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00000012
ub4 ktbbhod1 @24 0x00000012
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x1020770d
ub2 kscnwrp @32 0x0000
sb2 ktbbhict @36 1
ub1 ktbbhflg @38 0x02 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x00000000
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0005
ub2 kxidslt @46 0x0029
ub4 kxidsqn @48 0x0000029a
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00802381
ub2 kubaseq @56 0x01f9
ub1 kubarec @58 0x03
ub2 ktbitflg @60 0x8001 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x1020770e
BBED> m /x 09fa2100 offset 64
File: /u01/oracle/oradata/XFF/system01.dbf (1)
Block: 122 Offsets: 64 to 79 Dba:0x0040007a
------------------------------------------------------------------------
09fa2100 00016c00 ffffea00 53046903
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 122:
current = 0xf404, required = 0xf404
启动数据库
SQL> conn / as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1219160 bytes Variable Size 92276136 bytes Database Buffers 218103808 bytes Redo Buffers 7168000 bytes Database mounted. Database opened.
dul 10支持oracle 11g r2
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
以前一直以为dul对应的版本只能恢复最高的数据库版本一致,今天测试发现dul 10可以恢复11g最新版的数据库.
模拟环境
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> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') "WWW.XIFENFEI.COM" FROM DUAL;
WWW.XIFENFEI.COM
-------------------
2012-08-04 12:49:29
SQL> create table t_xifenfei
2 as
3 select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
COUNT(*)
----------
74491
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
dul 参数配置
[oracle@xifenfei dul]$ more init.dul
osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=0
osd_word_size = 32
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
control_file = control11.dul
db_block_size=8192
export_mode=false
compatible=11
BUFFER=10000000
LDR_ENCLOSE_CHAR=|
[oracle@xifenfei dul]$ more control11.dul
0 1 /u01/oracle/oradata/ora11g/system01.dbf
1 2 /u01/oracle/oradata/ora11g/sysaux01.dbf
2 3 /u01/oracle/oradata/ora11g/undotbs01.dbf
4 4 /u01/oracle/oradata/ora11g/users01.dbf
dul恢复11g数据库
[oracle@xifenfei dul]$ ./dul10
Data UnLoader: 10.2.0.5.13 - Internal Only - on Sat Aug 4 00:36:15 2012
with 64-bit io functions
Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved.
Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
Found db_id = 4185048347
Found db_name = ORA11G
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
60 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 11
OBJ$: segobjno 18, file 1 block 240
TAB$: segobjno 2, tabno 1, file 1 block 144
COL$: segobjno 2, tabno 5, file 1 block 144
USER$: segobjno 10, tabno 1, file 1 block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$ 74493 rows unloaded
. unloading table TAB$ 2858 rows unloaded
. unloading table COL$ 93503 rows unloaded
. unloading table USER$ 88 rows unloaded
Reading USER.dat 88 entries loaded
Reading OBJ.dat 74493 entries loaded and sorted 74493 entries
Reading TAB.dat 2858 entries loaded
Reading COL.dat 93503 entries loaded and sorted 93503 entries
Reading BOOTSTRAP.dat 60 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 11
OBJ$: segobjno 18, file 1 block 240
TAB$: segobjno 2, tabno 1, file 1 block 144
COL$: segobjno 2, tabno 5, file 1 block 144
USER$: segobjno 10, tabno 1, file 1 block 208
TABPART$: segobjno 568, file 1 block 3872
INDPART$: segobjno 573, file 1 block 3912
TABCOMPART$: segobjno 590, file 1 block 4056
INDCOMPART$: segobjno 595, file 1 block 4096
TABSUBPART$: segobjno 580, file 1 block 3976
INDSUBPART$: segobjno 585, file 1 block 4016
IND$: segobjno 2, tabno 3, file 1 block 144
ICOL$: segobjno 2, tabno 4, file 1 block 144
LOB$: segobjno 2, tabno 6, file 1 block 144
COLTYPE$: segobjno 2, tabno 7, file 1 block 144
TYPE$: segobjno 495, tabno 1, file 1 block 3344
COLLECTION$: segobjno 495, tabno 2, file 1 block 3344
ATTRIBUTE$: segobjno 495, tabno 3, file 1 block 3344
LOBFRAG$: segobjno 601, file 1 block 4144
LOBCOMPPART$: segobjno 604, file 1 block 4168
UNDO$: segobjno 15, file 1 block 224
TS$: segobjno 6, tabno 2, file 1 block 176
PROPS$: segobjno 98, file 1 block 800
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
74493 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
2858 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
93503 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
88 rows unloaded
. unloading table TABPART$ 90 rows unloaded
. unloading table INDPART$ 106 rows unloaded
. unloading table TABCOMPART$ 1 row unloaded
. unloading table INDCOMPART$ 0 rows unloaded
. unloading table TABSUBPART$ 32 rows unloaded
. unloading table INDSUBPART$ 0 rows unloaded
. unloading table IND$ 5092 rows unloaded
. unloading table ICOL$ 7518 rows unloaded
. unloading table LOB$ 1251 rows unloaded
. unloading table COLTYPE$ 2967 rows unloaded
. unloading table TYPE$ 2872 rows unloaded
. unloading table COLLECTION$ 985 rows unloaded
. unloading table ATTRIBUTE$ 11127 rows unloaded
. unloading table LOBFRAG$ 1 row unloaded
. unloading table LOBCOMPPART$ 0 rows unloaded
. unloading table UNDO$ 21 rows unloaded
. unloading table TS$ 6 rows unloaded
. unloading table PROPS$ 36 rows unloaded
Reading USER.dat 88 entries loaded
Reading OBJ.dat 74493 entries loaded and sorted 74493 entries
Reading TAB.dat 2858 entries loaded
Reading COL.dat 93503 entries loaded and sorted 93503 entries
Reading TABPART.dat 90 entries loaded and sorted 90 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 106 entries loaded and sorted 106 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 5092 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
1251 entries loaded
Reading ICOL.dat 7518 entries loaded
Reading COLTYPE.dat 2967 entries loaded
Reading TYPE.dat 2872 entries loaded
Reading ATTRIBUTE.dat 11127 entries loaded
Reading COLLECTION.dat 985 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
DUL> desc chf.t_xifenfei;
Table CHF.T_XIFENFEI
obj#= 75562, dataobj#= 75562, ts#= 4, file#= 4, block#=170
tab#= 0, segcols= 15, clucols= 0
Column information:
icol# 01 segcol# 01 OWNER len 30 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 02 segcol# 02 OBJECT_NAME len 128 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 03 segcol# 03 SUBOBJECT_NAME len 30 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 04 segcol# 04 OBJECT_ID len 22 type 2 NUMBER(0,-127)
icol# 05 segcol# 05 DATA_OBJECT_ID len 22 type 2 NUMBER(0,-127)
icol# 06 segcol# 06 OBJECT_TYPE len 19 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 07 segcol# 07 CREATED len 7 type 12 DATE
icol# 08 segcol# 08 LAST_DDL_TIME len 7 type 12 DATE
icol# 09 segcol# 09 TIMESTAMP len 19 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 10 segcol# 10 STATUS len 7 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 11 segcol# 11 TEMPORARY len 1 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 12 segcol# 12 GENERATED len 1 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 13 segcol# 13 SECONDARY len 1 type 1 VARCHAR2 cs 852(ZHS16GBK)
icol# 14 segcol# 14 NAMESPACE len 22 type 2 NUMBER(0,-127)
icol# 15 segcol# 15 EDITION_NAME len 30 type 1 VARCHAR2 cs 852(ZHS16GBK)
DUL> unload table chf.t_xifenfei;
. unloading table T_XIFENFEI 74491 rows unloaded
通过一些列的dul 10测试,发现dul 10功能确实较9强大了很多.支持asm,支持11g,支持字符串换行+lob类型
通过bbed修改回滚段状态解决ORA-00704故障
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
undo文件丢失
SQL> startup ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 218106740 bytes Database Buffers 88080384 bytes Redo Buffers 6328320 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' [root@xifenfei ~]# ls -l /u01/oracle/oradata/ora11g/undotbs01.dbf ls: /u01/oracle/oradata/ora11g/undotbs01.dbf: No such file or directory
尝试offline 数据文件
SQL> alter database datafile 3 offline; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 16365 Session ID: 125 Serial number: 5
通过这里的错误提示可以看到因为datafile 3丢失并且offline,而在数据库启动的时候需要去使用该数据文件中的回滚段去回滚事务,但是因为该数据文件被offline,使得回滚不能进行从而出现该错误.这里出现ORA-00704和ORA-00604的错误,根据感觉不能轻易的使用屏蔽回滚段的方法实现,但是还是做一尝试.使用其他方法找到回滚段.
屏蔽回滚段后重启库
SQL> startup mount pfile='/tmp/pfile' force ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 218106740 bytes Database Buffers 88080384 bytes Redo Buffers 6328320 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 16696 Session ID: 125 Serial number: 5
增加了合适的回滚段屏蔽,但是数据库还不能正常启动,而且依然报ORA-00704,决定对数据库启动过程做一个10046跟踪,来判断在哪一步出了问题
对数据库open过程做10046
SQL> conn / as sysdba Connected to an idle instance. SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 218106740 bytes Database Buffers 88080384 bytes Redo Buffers 6328320 bytes Database mounted. SQL> oradebug setmypid Statement processed. SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 Statement processed. SQL> oradebug TRACEFILE_NAME /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16869.trc SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' Process ID: 16869 Session ID: 125 Serial number: 5 --trace中发现 PARSE ERROR #3063083528:len=60 dep=1 uid=0 oct=3 lid=0 tim=1342472283605146 err=604 SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf' ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
通过这里可以看出来很可能是在SELECT PROPS$表的时候,需要使用到回滚段,这里有两种可能需要使用回滚段(1.有数据未提交[提交事务],2.数据块scn过大[提交事务/推进scn]).对于这个问题,我尝试着修改回滚段状态来解决这个问题
修改回滚段状态
[oracle@xifenfei ~]$ bbed listfile=listfile password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Jul 17 09:10:01 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 1 block 225
FILE# 1
BLOCK# 225
BBED> map
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 225 Dba:0x004000e1
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 48 bytes @20
struct kdbh, 14 bytes @68
struct kdbt[1], 4 bytes @82
sb2 kdbr[21] @86
ub1 freespace[5357] @128
ub1 rowdata[2703] @5485
ub4 tailchk @8188
BBED> p *kdbr[1]
rowdata[1393]
-------------
ub1 rowdata[1393] @6878 0x2c
BBED> x /rncnnnnn
rowdata[1393] @6878
-------------
flag@6878: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6879: 0x00
cols@6880: 17
col 0[2] @6881: 1
col 1[20] @6884: _SYSSMU1_3138885392$
col 2[2] @6905: 1
col 3[2] @6908: 3
col 4[3] @6911: 128
col 5[4] @6915: 822624
col 6[1] @6920: 0
col 7[3] @6922: 498
col 8[2] @6926: 94
col 9[1] @6929: 0
col 10[2] @6931: 3
col 11[2] @6934: 2
col 12[0] @6937: *NULL*
col 13[0] @6938: *NULL*
col 14[0] @6939: *NULL*
col 15[0] @6940: *NULL*
col 16[2] @6941: 2
BBED> m /x 02 offset 6933
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 225 Offsets: 6933 to 7444 Dba:0x004000e1
------------------------------------------------------------------------
0202c103 ffffffff 02c1032c 001102c1 03145f53 5953534d 55325f34 32323832
33383232 322402c1 0202c104 03c2022d 04c3531b 16018003 c2075303 c2031401
8002c102 02c103ff ffffff02 c1032c00 1102c104 145f5359 53534d55 335f3232
31303734 32363432 2402c102 02c10403 c2023d04 c3531b17 018003c2 074903c2
02290180 02c10202 c103ffff ffff02c1 032c0011 02c10514 5f535953 534d5534
5f313435 35333138 30303624 02c10202 c10403c2 024d04c3 4f4f5101 8003c205
4502c164 018002c1 0402c103 ffffffff 02c1032c 001102c1 06145f53 5953534d
55355f33 37383736 32323331 362402c1 0202c104 03c2025d 04c3531b 1c018003
c2071603 c2021701 8002c102 02c103ff ffffff02 c1032c00 1102c107 145f5359
53534d55 365f3234 36303234 38303639 2402c102 02c10403 c2030904 c3531b1d
018003c2 075a03c2 02130180 02c10202 c103ffff ffff02c1 032c0011 02c10814
5f535953 534d5537 5f313932 34383833 30333724 02c10202 c10403c2 031904c3
531b2201 8003c207 5a03c202 1d018002 c10202c1 03ffffff ff02c103 2c001102
c109135f 53595353 4d55385f 32373630 33383233 372402c1 0202c104 03c20329
04c34f2e 34018003 c2071703 c2024a01 8002c102 02c103ff ffffff02 c1032c00
1102c10a 145f5359 53534d55 395f3335 39333435 30363135 2402c102 02c10403
<32 bytes per line>
…………类似方法修改其他值
BBED> sum apply
Check value for File 1, Block 225:
current = 0x4d51, required = 0x4d51
启动数据库并解决异常undo
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 218106740 bytes
Database Buffers 88080384 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> select type# from seg$ where file#=3;
TYPE#
----------
10
10
10
10
10
10
10
10
10
10
10 rows selected.
SQL> update seg$ set type#=3 where file#=3;
10 rows updated.
SQL> commit;
Commit complete.
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/u01/oracle/oradata/ora11g/undotbs01.dbf' size 10m;
Tablespace created.
bbed修改undo$(回滚段)状态
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
指定undo$对应数据块(11g 1_225,10g 1_106)[具体时候需要查询]
BBED> set file 1 block 225
FILE# 1
BLOCK# 225
查询数据内容,主要关注kdbr
BBED> map File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 225 Dba:0x004000e1 ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 48 bytes @20 struct kdbh, 14 bytes @68 struct kdbt[1], 4 bytes @82 sb2 kdbr[31] @86 <===表示31条记录,从0开始 ub1 freespace[3644] @148 ub1 rowdata[4396] @3792 ub4 tailchk @8188
查看30号回滚段内容(列举其中一个)
BBED> p *kdbr[30] rowdata[0] ---------- ub1 rowdata[0] @3792 0x2c BBED> x /rncnnnnnnn rowdata[0] @3792 ---------- flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@3793: 0x01 cols@3794: 17 col 0[2] @3795: 30 col 1[21] @3798: _SYSSMU30_3379578723$ col 2[2] @3820: 1 col 3[2] @3823: 3 col 4[3] @3826: 432 col 5[1] @3830: 0 col 6[1] @3832: 0 col 7[1] @3834: 0 col 8[1] @3836: 0 col 9[1] @3838: 0 col 10[2] @3840: 5 <===修改前对应值undo$.status$ col 11[2] @3843: 6 col 12[0] @3846: *NULL* col 13[0] @3847: *NULL* col 14[0] @3848: *NULL* col 15[0] @3849: *NULL* col 16[2] @3850: 2 BBED> x /r rowdata[0] @3792 ---------- flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@3793: 0x01 cols@3794: 17 col 0[2] @3795: 0xc1 0x1f col 1[21] @3798: 0x5f 0x53 0x59 0x53 0x53 0x4d 0x55 0x33 0x30 0x5f 0x33 0x33 0x37 0x39 0x35 0x37 0x38 0x37 0x32 0x33 0x24 col 2[2] @3820: 0xc1 0x02 col 3[2] @3823: 0xc1 0x04 col 4[3] @3826: 0xc2 0x05 0x21 col 5[1] @3830: 0x80 col 6[1] @3832: 0x80 col 7[1] @3834: 0x80 col 8[1] @3836: 0x80 col 9[1] @3838: 0x80 col 10[2] @3840: 0xc1 0x06 <===修改前16进制值 col 11[2] @3843: 0xc1 0x07 col 12[0] @3846: *NULL* col 13[0] @3847: *NULL* col 14[0] @3848: *NULL* col 15[0] @3849: *NULL* col 16[2] @3850: 0xc1 0x03
修改回滚段状态
BBED> m /x 02 offset 3842 <===注意修改为02 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/oracle/oradata/ora11g/system01.dbf (1) Block: 225 Offsets: 3842 to 4353 Dba:0x004000e1 ------------------------------------------------------------------------ 0202c107 ffffffff 02c1032c 001102c1 <32 bytes per line> BBED> p *kdbr[30] rowdata[0] ---------- ub1 rowdata[0] @3792 0x2c BBED> x /r rowdata[0] @3792 ---------- flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@3793: 0x01 cols@3794: 17 col 0[2] @3795: 0xc1 0x1f col 1[21] @3798: 0x5f 0x53 0x59 0x53 0x53 0x4d 0x55 0x33 0x30 0x5f 0x33 0x33 0x37 0x39 0x35 0x37 0x38 0x37 0x32 0x33 0x24 col 2[2] @3820: 0xc1 0x02 col 3[2] @3823: 0xc1 0x04 col 4[3] @3826: 0xc2 0x05 0x21 col 5[1] @3830: 0x80 col 6[1] @3832: 0x80 col 7[1] @3834: 0x80 col 8[1] @3836: 0x80 col 9[1] @3838: 0x80 col 10[2] @3840: 0xc1 0x02 <===修改后16进制值 col 11[2] @3843: 0xc1 0x07 col 12[0] @3846: *NULL* col 13[0] @3847: *NULL* col 14[0] @3848: *NULL* col 15[0] @3849: *NULL* col 16[2] @3850: 0xc1 0x03 BBED> x /rncnnnnnnn rowdata[0] @3792 ---------- flag@3792: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@3793: 0x01 cols@3794: 17 col 0[2] @3795: 30 col 1[21] @3798: _SYSSMU30_3379578723$ col 2[2] @3820: 1 col 3[2] @3823: 3 col 4[3] @3826: 432 col 5[1] @3830: 0 col 6[1] @3832: 0 col 7[1] @3834: 0 col 8[1] @3836: 0 col 9[1] @3838: 0 col 10[2] @3840: 1 <===实际展示值undo$.status$ col 11[2] @3843: 6 col 12[0] @3846: *NULL* col 13[0] @3847: *NULL* col 14[0] @3848: *NULL* col 15[0] @3849: *NULL* col 16[2] @3850: 2
1.修改为02(表示的实际值比修改值大1[1:DELETE,2:OFFLINE, 3:ONLINE,4:UNDEFINED,5:NEEDS RECOVERY,6:PARTLY AVAILABLE,其他表示:UNDEFINED])
2.Offset需要在10列漂移量上+2(或者11列偏移量-1)
修改验证值
BBED> sum apply Check value for File 1, Block 225: current = 0x9708, required = 0x9708
补充说明
1)通过dbms_metadata.get_ddl分析DBA_ROLLBACK_SEGS可以知道seg$.status$表示含义
2, 'OFFLINE' 3, 'ONLINE', 4, 'UNDEFINED' 5, 'NEEDS RECOVERY', 6, 'PARTLY AVAILABLE' other, 'UNDEFINED'
2)该方法一般出现在使用隐含参数屏蔽回滚段无效之后使用,一般不到最后,不建议使用该方法,可能引起不可预知的后果
3)使用该方法启动数据库后,需要手工删除seg$中被bbed处理过的回滚段