在一次数据库恢复的过程中,通过bbed修改事务状态,提交一个事务后,强制打开库遇到了ora-1555
ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-01555: snapshot too OLD: ROLLBACK segment NUMBER 286 WITH name "_SYSSMUxx_xxx$" too small |
这种错误一般出现undo表空间丢失或者存在问题。在使用隐藏参数屏蔽undo回滚断的时候,报错如下
ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-01555: snapshot too OLD: ROLLBACK segment NUMBER WITH name "" too small |
一般情况下可以通过推进scn号来解决,但是推进scn号多少呢? 最好发现是在bbed改块的时候忘记修改下事务的提交scn的wrap位,
导致事务的提交scn大于当前scn wrap很多.bbed fix后,启动数据库正常。
下面做个实验模拟下
环境准备
SQL> CREATE TABLE fsc_test (id NUMBER,name varchar2(20)); TABLE created. SQL> INSERT INTO fsc_test VALUES (1,'travel'); 1 ROW created. SQL> commit; Commit complete. SQL> ALTER system checkpoint 2 ; System altered. SQL> ALTER system FLUSH buffer_cache; System altered. SQL> / System altered. SQL> / System altered. SQL> SQL> / System altered. SQL> SQL> SQL> SELECT a.*,rowid FROM fsc_test a; ID NAME ROWID ---------- -------------------- ------------------ 1 travel AAAFsLAAEAAAIcbAAA SQL> @lookup_rowid AAAFsLAAEAAAIcbAAA +------------------------------------------------------------------------+ | Report : lookup_rowid.SQL | | Instance : orcl | | USER : TRAVEL | +------------------------------------------------------------------------+ ROWID: AAAFsLAAEAAAIcbAAA Object#: 23307 RelFile#: 4 Block#: 34587 ROW#: 0 PL/SQL PROCEDURE successfully completed. |
删除数据
SQL> DELETE FROM fsc_test; 1 ROW deleted. SQL> commit; Commit complete. SQL> ALTER system checkpoint; System altered. SQL> . SQL> / System altered. SQL> / System altered. SQL> ALTER system FLUSH buffer_cache; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> dump数据块 START dump DATA blocks tsn: 4 file#:4 minblk 34587 maxblk 34587 Block dump FROM cache: Dump OF buffer cache at level 4 FOR tsn=4 rdba=16811803 BH (0x62bf6108) file#: 4 rdba: 0x0100871b (4/34587) class: 1 ba: 0x62b2c000 SET: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 252,19 dbwrid: 0 obj: 23307 objn: 23307 tsn: 4 afn: 4 hint: f hash: [0x62bf7e38,0x61fe2bc8] lru: [0x623f4900,0x62bf7e70] lru-flags: on_auxiliary_list ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0 lfb: 33 flags: BH (0x62bf7d88) file#: 4 rdba: 0x0100871b (4/34587) class: 1 ba: 0x62b5c000 SET: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 3 pwc: 252,19 dbwrid: 0 obj: 23307 objn: 23307 tsn: 4 afn: 4 hint: f hash: [0x83cf1090,0x62bf61b8] lru: [0x62bf61f0,0x62bf2430] lru-flags: moved_to_tail on_auxiliary_list ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0 lfb: 33 flags: Block dump FROM disk: buffer tsn: 4 rdba: 0x0100871b (4/34587) scn: 0x0002.004c3854 seq: 0x01 flg: 0x06 tail: 0x38540601 frmt: 0x02 chkval: 0x7ded TYPE: 0x06=trans DATA Hex dump OF block: st=0, typ_found=1 Block header dump: 0x0100871b Object id ON Block? Y seg/obj: 0x5b0b csc: 0x02.4c3853 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1008718 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0025.01c.00000145 0x00c00148.0131.21 C--- 0 scn 0x0002.004c37f7 0x02 0x0021.004.000001d4 0x00c00142.0177.08 --U- 1 fsc 0x000b.004c3854 --事务槽2是我们delete的那条记录,块的提交scn为 0x02.4c3853 . bbed查看下 BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) UNION ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00005b0b ub4 ktbbhod1 @24 0x00005b0b struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x004c3853 ub2 kscnwrp @32 0x0002 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x32 (NONE) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x01008718 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0025 ub2 kxidslt @46 0x001c ub4 kxidsqn @48 0x00000145 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00148 ub2 kubaseq @56 0x0131 ub1 kubarec @58 0x21 ub2 ktbitflg @60 0x8000 (KTBFCOM) UNION _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 2 ub2 _ktbitwrp @62 0x0002 ub4 ktbitbas @64 0x004c37f7 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0021 ub2 kxidslt @70 0x0004 ub4 kxidsqn @72 0x000001d4 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c00142 ub2 kubaseq @80 0x0177 ub1 kubarec @82 0x08 ub2 ktbitflg @84 0x2001 (KTBFCOM) UNION _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 11 ub2 _ktbitwrp @86 0x000b ub4 ktbitbas @88 0x004c3854 |
_ktbitun该字段复用,如果事务已提交并完成块清理,该字段保存事务提交 SCN的SCN WRAP部分,
否则该字段保存空闲空间预支字节数(Free Space Credit)。
bbed修改
ub2 ktbitflg @84 0x2001 (KTBFCOM) --改成0x8000 UNION _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 11 ub2 _ktbitwrp @86 0x000b --改成0x0003 ub4 ktbitbas @88 0x004c385 |
不屏蔽回滚段的时候
SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed SIZE 2230952 bytes Variable SIZE 578815320 bytes DATABASE Buffers 20971520 bytes Redo Buffers 24309760 bytes DATABASE mounted. DATABASE opened. SQL> SQL> SELECT * FROM travel.fsc_test; ID NAME ---------- -------------------- --出现事物回滚 1 travel |
由于回滚段可用,oracle可以通过事务槽的uba来实现事物回滚
屏蔽undo回滚断后
SQL> startup pfile='/tmp/travel.ora' ORACLE instance started. Total System Global Area 626327552 bytes Fixed SIZE 2230952 bytes Variable SIZE 578815320 bytes DATABASE Buffers 20971520 bytes Redo Buffers 24309760 bytes DATABASE mounted. DATABASE opened. SQL> SELECT * FROM travel.fsc_test; SELECT * FROM travel.fsc_test * ERROR at line 1: ORA-01555: snapshot too OLD: ROLLBACK segment NUMBER WITH name "" too small |
下面修改下提交scn
0x02 0x0021.004.000001d4 0x00c00142.0177.08 C--- 0 scn 0x0003.004c3854 --改成0x0002.004c3855 |
SQL> startup pfile='/tmp/travel.ora' ORACLE instance started. Total System Global Area 626327552 bytes Fixed SIZE 2230952 bytes Variable SIZE 578815320 bytes DATABASE Buffers 20971520 bytes Redo Buffers 24309760 bytes DATABASE mounted. DATABASE opened. Block header dump: 0x0100871b Object id ON Block? Y seg/obj: 0x5b0b csc: 0x02.4c3853 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1008718 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0025.01c.00000145 0x00c00148.0131.21 C--- 0 scn 0x0002.004c37f7 0x02 0x0021.004.000001d4 0x00c00142.0177.08 C--- 0 scn 0x0002.004c3855 bdba: 0x0100871b data_block_dump,DATA header at 0x7fa48c384a64 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x7fa48c384a64 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f8b avsp=0x1f82 tosp=0x1f84 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f8b block_row_dump: tab 0, ROW 0, @0x1f8b tl: 2 fb: --HDFL-- lb: 0x0 end_of_block_dump END dump DATA blocks tsn: 4 file#: 4 minblk 34587 maxblk 34587 SQL> SELECT * FROM travel.fsc_test; no ROWS selected |
因为当前数据库的scn大于提交scn
把提交scn在改成比当前scn大的值
SQL> SELECT current_scn-2*POWER(2,32) FROM v$database; CURRENT_SCN-2*POWER(2,32) ------------------------- 5077199 0x02 0x0021.004.000001d4 0x00c00142.0177.08 C--- 0 scn 0x0002.004c3855 --改成0x0002.005c3855 SQL> SELECT * FROM travel.fsc_test; SELECT * FROM travel.fsc_test * ERROR at line 1: ORA-01555: snapshot too OLD: ROLLBACK segment NUMBER WITH name "" too small |
错误重现了。
这里就是想表达下通过bbed修改数据块的时候要严谨.虽然这是块清理的时候发现提交scn大于当前scn。如果不是相差太多的话可以推进scn来解决.但是这次是因为相差太多,会遇到scn天花板问题,才使用bbed 修改下。
