[20181123]模拟ora-01555.txt
--//ora-01555一般情况是回滚找不到前映像(已经被覆盖),报这个错误。通过bbed修改块模拟看看。
$ oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select current_scn from v$database;
CURRENT_SCN
------------
13276934327
SCOTT@book> @ tx 13276934327 32
select 13276934327,trunc(13276934327/power(2,32)) scn_wrap,mod(13276934327,power(2,32)) scn_base from dual
13276934327 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
13276934327 3 392032439 3 175df0b7
--//SCN_WRAP=3.
SCOTT@book> create table t as select rownum id ,'test' name from dual connect by level<=2;
Table created.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ ------------ -----
AAAWEGAAEAAAAIjAAA 1 test
AAAWEGAAEAAAAIjAAB 2 test
SCOTT@book> @ rowid AAAWEGAAEAAAAIjAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90374 4 547 0 0x1000223 4,547 alter system dump datafile 4 block 547 ;
SCOTT@book> delete from t where id=1;
1 row deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
2.bbed修改块产生ora-01555错误。
BBED> set dba 4,547
DBA 0x01000223 (16777763 4,547)
BBED> x /rnc *kdbr[0]
rowdata[11] @8177
-----------
flag@8177: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8178: 0x02
cols@8179: 0
--//使用事务槽2.从0开始编号.
BBED> x /rnc *kdbr[1]
rowdata[0] @8166
----------
flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8167: 0x00
cols@8168: 2
col 0[2] @8169: 2
col 1[4] @8172: test
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0011
ub4 kxidsqn @72 0x00004bea
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0104d
ub2 kubaseq @80 0x0eb5
ub1 kubarec @82 0x24
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 9
ub2 _ktbitwrp @86 0x0009
ub4 ktbitbas @88 0x175df15e
--//ktbbh.ktbbhitl[1]._ktbitun._ktbitfsc表示dml操作回事的空间(不包括flag,lock),1+1+2+1+4 = 9.
--//当itl槽重用时,这数值加入kdbh.kdbhavsp.
--//修改ktbbh.ktbbhitl[1].ktbitbas=0x275df15e
BBED> assign ktbbh.ktbbhitl[1].ktbitbas=0x275df15e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub4 ktbitbas @88 0x275df15e
BBED> sum apply ;
Check value for File 4, Block 547:
current = 0x051d, required = 0x051d
--//注:按照道理itl事务槽的提交scn号不应该大于块号的scn号,因为它不包括wrap部分,oracle一般不会认为错误.
--//另外我设置_ktbitwrp=4.修改ktbitflg=0x8000,也不报错.
--//打开新会话可以发现可以通过回滚段查询数据.依旧可以查询到删除数据.
SCOTT@book> select * from t;
ID NAME
---------- --------------------
1 test
2 test
3.屏蔽回滚段看看.
SCOTT@book> select * from v$rollname where usn=10;
USN NAME
---------- ----------------------
10 _SYSSMU10_1197734989$
SYS@book> create pfile='/tmp/@.ora' from spfile;
File created.
--//修改/tmp/book.ora加入如下:
*._corrupted_rollback_segments='_SYSSMU10_1197734989$'
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup pfile='/tmp/book.ora';
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
SYS@book> set numw 12
SYS@book> select current_scn from v$database ;
CURRENT_SCN
------------
13276936029
SYS@book> @ tx 13276936029 32
select 13276936029,trunc(13276936029/power(2,32)) scn_wrap,mod(13276936029,power(2,32)) scn_base from dual
13276936029 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
13276936029 3 392034141 3 175df75d
SYS@book> select * from scott.t;
select * from scott.t
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
--//因为设置了*._corrupted_rollback_segments='_SYSSMU10_1197734989$'.看不到回滚段号.
4.顺便温习提升scn:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup mount pfile='/tmp/book.ora';
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
--//仅仅到mount状态,scn没有载入,看到是0.
SYS@book> oradebug poke 0x06001AE70 4 0x375df75d
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER: [06001AE70, 06001AE74) = 375DF75D
--//注意还没有完,必须修改scn_wrap部分.
SYS@book> oradebug poke 0x06001AE74 4 0x00000003
BEFORE: [06001AE74, 06001AE78) = 00000000
AFTER: [06001AE74, 06001AE78) = 00000003
SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 375DF75D 00000003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
--//注意千万不要一次修改完成,这样实际上错误的.我测试犯了严重错误!!
SYS@book> oradebug poke 0x06001AE70 8 0x375df75d00000003
BEFORE: [06001AE70, 06001AE78) = 375DF75D 00000003
AFTER: [06001AE70, 06001AE78) = 00000003 375DF75D
SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000003 375DF75D 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
--//实际上这样scn_wrap变成了0xF75D,scn_base=0x00000003.这样scn提升太快了,会出现如下错误ora-00600[2552]:
ARC1 started with pid=23, OS id=53718
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_lgwr_53688.trc (incident=2271885):
ORA-00600: internal error code, arguments: [2252], [5318], [4], [3787], [3340107776], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_2271885/book_lgwr_53688_i2271885.trc
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/app/oracle/diag/rdbms/book/book/trace/book_lgwr_53688.trc:
ORA-00600: internal error code, arguments: [2252], [5318], [4], [3787], [3340107776], [], [], [], [], [], [], []
LGWR (ospid: 53688): terminating the instance due to error 470
Fri Nov 23 09:33:13 2018
System state dump requested by (instance=1, osid=53688 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_diag_53674_20181123093313.trc
Dumping diagnostic data in directory=[cdmp_20181123093313], requested by (instance=1, osid=53688 (LGWR)), summary=[abnormal instance termination].
Instance terminated by LGWR, pid = 53688
----------------
--//还原:
SYS@book> oradebug poke 0x06001AE70 4 0x375df75d
BEFORE: [06001AE70, 06001AE74) = 00000003
AFTER: [06001AE70, 06001AE74) = 375DF75D
SYS@book> oradebug poke 0x06001AE74 4 0x00000003
BEFORE: [06001AE74, 06001AE78) = 375DF75D
AFTER: [06001AE74, 06001AE78) = 00000003
SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 375DF75D 00000003 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
--//这样scn号提升到 0x3375DF75D=13813806941.
SYS@book> alter database open ;
Database altered.
SYS@book> select * from scott.t;
ID NAME
---------- --------------------
2 test
--//OK,现在看不到id=1的记录.
SYS@book> select current_scn from v$database ;
CURRENT_SCN
------------
13813807246
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2221318/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2221318/