[20181123]模擬ora-01555.txt

lfree發表於2018-11-23

[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/,如需轉載,請註明出處,否則將追究法律責任。