上周一个朋友数据库启动不了，开始问题还不是很复杂，最后经过一系列的恢复后，出现了ORA-00704错误。
而导致ORA-00704错误的根本原因是访问OBJ$的时候，ORACLE需要回滚段中的数据，而访问回滚段的时候出现了
ORA-01555错误。
Fri Mar 22 19:54:06 2013
SMON: enabling cache recovery
Fri Mar 22 19:54:07 2013
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.16240fbf):
Fri Mar 22 19:54:07 2013
select ctime, mtime, stime from obj$ where obj# = :1
Fri Mar 22 19:54:07 2013
Errors in file /u01/oracle/admin/db001/udump/db001_ora_2113.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 2113
ORA-1092 signalled during: ALTER DATABASE OPEN...
下面是10046事件内容：
PARSING IN CURSOR #5 len=52 dep=1 uid=0 oct=3 lid=0 tim=35856777108 hv=429618617 ad='9e8c99e0'
select ctime, mtime, stime from obj$ where obj# = :1
END OF STMT
PARSE #5:c=0,e=260,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=35856777106
BINDS #5:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=9fffffffbf3d7fc0 bln=22 avl=02 flg=05
value=28
EXEC #5:c=0,e=548,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=35856777735
WAIT #5: nam='db file sequential read' ela= 79 file#=1 block#=218 blocks=1 obj#=-1 tim=35856777933
WAIT #5: nam='db file sequential read' ela= 69 file#=1 block#=219 blocks=1 obj#=-1 tim=35856778059
WAIT #5: nam='db file sequential read' ela= 64 file#=1 block#=122 blocks=1 obj#=-1 tim=35856778172
WAIT #5: nam='row cache lock' ela= 80 cache id=3 mode=0 request=3 obj#=-1 tim=35856778296
=====================
PARSING IN CURSOR #6 len=142 dep=2 uid=0 oct=3 lid=0 tim=35856779096 hv=361892850 ad='9e8c9070'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #6:c=0,e=746,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,tim=35856779092
BINDS #6:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=9fffffffbf31fbd0 bln=22 avl=02 flg=05
value=11
EXEC #6:c=0,e=1180,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,tim=35856780477
WAIT #6: nam='db file sequential read' ela= 72 file#=1 block#=202 blocks=1 obj#=-1 tim=35856780631
WAIT #6: nam='db file sequential read' ela= 66 file#=1 block#=106 blocks=1 obj#=-1 tim=35856780773
FETCH #6:c=0,e=310,p=2,cr=2,cu=0,mis=0,r=1,dep=2,og=3,tim=35856780829
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=310 us)'
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=170 us)'
WAIT #5: nam='db file sequential read' ela= 72 file#=4 block#=2151 blocks=1 obj#=-1 tim=35856781170
FETCH #5:c=0,e=3617,p=6,cr=6,cu=0,mis=0,r=0,dep=1,og=4,tim=35856781376
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
EXEC #1:c=100000,e=3397744,p=299,cr=65,cu=0,mis=0,r=0,dep=0,og=1,tim=35857765711
ERROR #1:err=1092 tim=3671835
设置隐含参数_currpution_rollback_segments 参数及其10513事件都不行。
个人感觉这个可以通过修改obj$块上的事务提交状态来屏蔽对回滚段的访问来解决。
遗憾的是当时太忙及其服务器上的BBED编译不了，没捞着搞这个案例。
不过下面介绍这种方法的修改步骤：
SQL> desc x
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(20)
SQL> select * from x;
no rows selected
SQL> insert into x select object_id,object_name from all_objects where rownum<=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from x;
FILE# BLOCK#
---------- ----------
4 1820
4 1820
4 1820
4 1820
4 1820
4 1820
4 1820
4 1820
4 1820
4 1820
10 rows selected.
SQL> select * from x;
ID NAME
---------- --------------------
20 ICOL$
46 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#
3 I_OBJ#
25 PROXY_ROLE_DATA$
41 I_IND1
54 I_CDEF2
40 I_OBJ5
10 rows selected.
然后再打开一个SESSION 2，进行如下更新操作：
[oracle@db2server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 04:40:18 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn test/test
Connected.
SQL> update x set name='abc';
10 rows updated.
SQL>
由于没有提交，因此对于其他的SESSION 是看不到更新后的结果的。
然后再重新打开一个SESSION 3，进行如下操作：
SQL> alter system checkpoint; --检查点的目的是将更新后的结果刷新到磁盘上。
System altered.
SQL> alter system dump datafile 4 block 1820;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/huateng/huateng/trace/huateng_ora_12994.trc
从跟踪文件中我们可以找到如下信息：
Block header dump: 0x0100071c
Object id on Block? Y
seg/obj: 0x12e56 csc: 0x00.3318b8 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000718 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.007.0000048f 0x00c007fa.017f.06 C--- 0 scn 0x0000.0033190d
0x02 0x0005.019.0000051e 0x00c02099.013f.01 ---- 10 fsc 0x0027.00000000
上面显示我们LCK了10条记录，并且使用的2号ITL事务槽。
关于事务的状态如下：
TRANSACTION_COMMITED = 0×08;
TRANSACTION_UPBOUND = 0×02;
TRANSACTION_ACTIVE = 0×01;
我们仅仅需要将事务的状态改成TRANSACTION_COMMITED就可以。
此外DUMP文件中我们也可以看到相应的NAME列也被改为了abc了。
SQL> select dump('abc',16) from dual;
DUMP('ABC',16)
----------------------
Typ=96 Len=3: 61,62,63
block_row_dump:
tab 0, row 0, @0x1e0c
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 15
col 1: [ 3] 61 62 63
tab 0, row 1, @0x1e02
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 2f
col 1: [ 3] 61 62 63
tab 0, row 2, @0x1df8
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 1d
col 1: [ 3] 61 62 63
tab 0, row 3, @0x1dee
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 10
col 1: [ 3] 61 62 63
tab 0, row 4, @0x1de4
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 1e
col 1: [ 3] 61 62 63
tab 0, row 5, @0x1dda
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 04
col 1: [ 3] 61 62 63
tab 0, row 6, @0x1dd0
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 1a
col 1: [ 3] 61 62 63
tab 0, row 7, @0x1dc6
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 2a
col 1: [ 3] 61 62 63
tab 0, row 8, @0x1dbc
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 37
col 1: [ 3] 61 62 63
tab 0, row 9, @0x1db2
tl: 10 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 29
col 1: [ 3] 61 62 63
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1820 maxblk 1820
在BBED修改文件之前，先进行一次FLUSH BUFFER_CACHE的操作。
SQL> alter system flush buffer_cache;
System altered.
这个操作的目的是为了将内存中的BLOCK 1820刷出去，下次读取将会发现物理读取，从而可以读取到我们修改后的内容。
否则ORACLE不会进行物理读，也就是看不到我们采用BBED修改后的结果。
下面是BBED修改文件的部分：
[oracle@db2server huateng]$ bbed filename=/u01/app/oracle/oradata/huateng/users01.dbf password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 21 04:41:11 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 1820
BLOCK# 1820
BBED> p ktbbh.ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x0007
ub4 kxidsqn @48 0x0000048f
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c007fa
ub2 kubaseq @56 0x017f
ub1 kubarec @58 0x06
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x0033190d
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0005
ub2 kxidslt @70 0x0019
ub4 kxidsqn @72 0x0000051e
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c02099
ub2 kubaseq @80 0x013f
ub1 kubarec @82 0x01
ub2 ktbitflg @84 0x000a (NONE) <---这里需要修改为0x8000
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 39
ub2 _ktbitwrp @86 0x0027
ub4 ktbitbas @88 0x00000000
BBED> m /x 0080 offset 84 <--LINUX平台字节序是little的
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/huateng/users01.dbf (0)
Block: 1820 Offsets: 84 to 595 Dba:0x00000000
------------------------------------------------------------------------
00802700 00000000 00000000 00000000 00010a00 ffff2600 b21de71e 0e1f0000
0a000c1e 021ef81d ee1de41d da1dd01d c61dbc1d b21d4e10 7e10ae10 de100e11
3e116e11 9e11ce11 fe112e12 5e128e12 be12ee12 26135f13 9613cd13 01143614
67149814 c814ff14 33156915 a115d915 0d164316 7c16b416 ec162417 5517840e
9f0b270f 8417b517 f10ee517 00082b0e 1618fc0c 3908630d 300df90d d20c6d0c
6b0b080b a50ad80a 3a0bd50b bb0e4218 c70d3a0c 070c970d 5a0e710a 0d0ada09
400a9e0c a9094309 0d09da08 7b09a408 71086e18 9b18ce18 fc182c19 5e199519
bf19ef19 1b1a491a 751aa21a d41a081b 401b6f1b a81bd21b 001c331c 6c1c991c
c81cf51c 201d4f1d 7e1dab1d e41d1b1e 501e861e bf1ef61e 281fa819 dd19111a
3f1a751a 970caa1a d91a051b 3a1b6a1b 9c1bd51b 0c1c3b1c 6c1ca11c da1c0f1d
481d811d b51ded1d 251e5a1e 911ec11e f31e5f0c 2c1f831b 991baf1b c51bdb1b
f11b071c 1d000000 40cf1400 51018014 6a617661 782f6a6d 732f436f 6e6e6563
74696f6e 02c102ff ffff02c1 1e018004 c3062d59 00000040 cf140050 0180166a
61766178 2f6a6d73 2f44656c 69766572 794d6f64 6502c102 ffffff02 c11e0180
04c3062d 58000000 40cf1400 4f018015 6a617661 782f6a6d 732f4465 7374696e
6174696f 6e02c102 ffffff02 c11e0180 04c3062d 57000000 40cf1400 4e018016
<32 bytes per line>
BBED> p ktbbh.ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x0007
ub4 kxidsqn @48 0x0000048f
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c007fa
ub2 kubaseq @56 0x017f
ub1 kubarec @58 0x06
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x0033190d
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0005
ub2 kxidslt @70 0x0019
ub4 kxidsqn @72 0x0000051e
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c02099
ub2 kubaseq @80 0x013f
ub1 kubarec @82 0x01
ub2 ktbitflg @84 0x8000 (KTBFCOM) <--修改后的结果
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 39
ub2 _ktbitwrp @86 0x0027
ub4 ktbitbas @88 0x00000000
BBED> sum apply
Check value for File 0, Block 1820:
current = 0x95c2, required = 0x95c2
BBED> exit
修改之后，打开一个SESSION 查询表结果发现还是修改前的结果：
SQL> select * from x;
ID NAME
---------- --------------------
20 ICOL$
46 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#
3 I_OBJ#
25 PROXY_ROLE_DATA$
41 I_IND1
54 I_CDEF2
40 I_OBJ5
10 rows selected.
SQL> alter system dump datafile 4 block 1820;
System altered.
BLOCK DUMP后的结果如下：
Block header dump: 0x0100071c
Object id on Block? Y
seg/obj: 0x12e56 csc: 0x00.3318b8 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000718 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.007.0000048f 0x00c007fa.017f.06 C--- 0 scn 0x0000.0033190d
0x02 0x0005.019.0000051e 0x00c02099.013f.01 C--- 0 scn 0x0027.00000000
bdba: 0x0100071c
data_block_dump,data header at 0x9e4264
2 号 ITL事务槽的事务状态已经修改为COMMIT的了，最后发现需要将Scn/Fsc中的27修改为0才行。
[oracle@db2server huateng]$ bbed filename=/u01/app/oracle/oradata/huateng/users01.dbf password=blockedit mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 21 04:43:11 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 1820
BLOCK# 1820
BBED> p ktbbh.ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x0007
ub4 kxidsqn @48 0x0000048f
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c007fa
ub2 kubaseq @56 0x017f
ub1 kubarec @58 0x06
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x0033190d
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0005
ub2 kxidslt @70 0x0019
ub4 kxidsqn @72 0x0000051e
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c02099
ub2 kubaseq @80 0x013f
ub1 kubarec @82 0x01
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 39
ub2 _ktbitwrp @86 0x0027 <---这里修改为0
ub4 ktbitbas @88 0x00000000
BBED> m /x 0000 offset 86
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/huateng/users01.dbf (0)
Block: 1820 Offsets: 86 to 597 Dba:0x00000000
------------------------------------------------------------------------
00000000 00000000 00000000 00000001 0a00ffff 2600b21d e71e0e1f 00000a00
0c1e021e f81dee1d e41dda1d d01dc61d bc1db21d 4e107e10 ae10de10 0e113e11
6e119e11 ce11fe11 2e125e12 8e12be12 ee122613 5f139613 cd130114 36146714
9814c814 ff143315 6915a115 d9150d16 43167c16 b416ec16 24175517 840e9f0b
270f8417 b517f10e e5170008 2b0e1618 fc0c3908 630d300d f90dd20c 6d0c6b0b
080ba50a d80a3a0b d50bbb0e 4218c70d 3a0c070c 970d5a0e 710a0d0a da09400a
9e0ca909 43090d09 da087b09 a4087108 6e189b18 ce18fc18 2c195e19 9519bf19
ef191b1a 491a751a a21ad41a 081b401b 6f1ba81b d21b001c 331c6c1c 991cc81c
f51c201d 4f1d7e1d ab1de41d 1b1e501e 861ebf1e f61e281f a819dd19 111a3f1a
751a970c aa1ad91a 051b3a1b 6a1b9c1b d51b0c1c 3b1c6c1c a11cda1c 0f1d481d
811db51d ed1d251e 5a1e911e c11ef31e 5f0c2c1f 831b991b af1bc51b db1bf11b
071c1d00 000040cf 14005101 80146a61 7661782f 6a6d732f 436f6e6e 65637469
6f6e02c1 02ffffff 02c11e01 8004c306 2d590000 0040cf14 00500180 166a6176
61782f6a 6d732f44 656c6976 6572794d 6f646502 c102ffff ff02c11e 018004c3
062d5800 000040cf 14004f01 80156a61 7661782f 6a6d732f 44657374 696e6174
696f6e02 c102ffff ff02c11e 018004c3 062d5700 000040cf 14004e01 80166a61
<32 bytes per line>
BBED> p ktbbh.ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x0007
ub4 kxidsqn @48 0x0000048f
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c007fa
ub2 kubaseq @56 0x017f
ub1 kubarec @58 0x06
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x0033190d
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0005
ub2 kxidslt @70 0x0019
ub4 kxidsqn @72 0x0000051e
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c02099
ub2 kubaseq @80 0x013f
ub1 kubarec @82 0x01
ub2 ktbitflg @84 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000 <--------------修改后的结果
ub4 ktbitbas @88 0x00000000
BBED> sum apply
Check value for File 0, Block 1820:
current = 0x95e5, required = 0x95e5
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/huateng/users01.dbf
BLOCK = 1820
Block Checking: DBA = 16779036, Block Type = KTB-managed data block
data header at 0x198264
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=2 ktbbhitc=2
Block 1820 failed with check code 6101
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>
重新查询即可看到事务已经被修改为提交：
[oracle@db2server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 21 04:44:26 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from Test.x;
ID NAME
---------- --------------------
20 abc
46 abc
28 abc
15 abc
29 abc
3 abc
25 abc
41 abc
54 abc
40 abc
10 rows selected.
阅读(3805) | 评论(0) | 转发(0) |
