[20181123]模拟ora-01555.txt

[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