ORA-01555原理分析

SQL> create undo tablespace undotbs1 datafile '/opt/app/oracle/RHYS/undotbs1.dbf' size 30M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs1;

System altered.
SQL> select a.usn,a.status,b.name from v$rollstat a,v$rollname b where a.usn=b.usn;

       USN STATUS          NAME
---------- --------------- ------------------------------
         0 ONLINE          SYSTEM
         1 ONLINE          _SYSSMU1_3143656763$
         2 ONLINE          _SYSSMU2_2613008803$
         3 ONLINE          _SYSSMU3_4015766757$
         4 ONLINE          _SYSSMU4_2233917592$
         5 ONLINE          _SYSSMU5_1374253473$
         6 ONLINE          _SYSSMU6_1614295565$
         7 ONLINE          _SYSSMU7_874818192$
         8 ONLINE          _SYSSMU8_2318326044$
         9 ONLINE          _SYSSMU9_1447715703$
        10 ONLINE          _SYSSMU10_2612552350$

11 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
_undo_autotune                       boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> alter system set event="10203 trace name context forever" scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             260048968 bytes
Database Buffers          138412032 bytes
Redo Buffers                4345856 bytes
Database mounted.
Database opened.
SQL>
SQL> select *  from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM

SQL> create rollback segment one tablespace undotbs1;

Rollback segment created.

SQL> select * from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM

SQL> alter rollback segment one online;

Rollback segment altered.

SQL> select * from v$rollname;

       USN NAME
---------- ------------------------------
         0 SYSTEM
        21 ONE

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
_undo_autotune                       boolean     FALSE
undo_management                      string      MANUAL
undo_retention                       integer     3600
undo_tablespace                      string      UNDOTBS1
SQL>
SQL> set linesize 200
SQL> col value for a60
SQL> col name for a20
SQL> select * from t1;

        ID        SAL JOB
---------- ---------- ----------
         2          3 b
         2          1 a
         3            D

SQL> update t1 set id=1 where sal=3;

1 row updated.

SQL> select usn,xacts from v$rollstat where xacts>0;

       USN      XACTS
---------- ----------
        21          1

SQL> select xidusn,ubafil,ubablk from v$transaction;     

    XIDUSN     UBAFIL     UBABLK
---------- ---------- ----------
        21          3        290

SQL> alter system dump datafile 3 block 290;

System altered.

SQL> set linesize 200
SQL> select * from v$diag_info;

   INST_ID NAME                 VALUE
---------- -------------------- ------------------------------------------------------------
         1 Diag Enabled         TRUE
         1 ADR Base             /opt/app/oracle
         1 ADR Home             /opt/app/oracle/diag/rdbms/rhys/RHYS
         1 Diag Trace           /opt/app/oracle/diag/rdbms/rhys/RHYS/trace
         1 Diag Alert           /opt/app/oracle/diag/rdbms/rhys/RHYS/alert
         1 Diag Incident        /opt/app/oracle/diag/rdbms/rhys/RHYS/incident
         1 Diag Cdump           /opt/app/oracle/diag/rdbms/rhys/RHYS/cdump
         1 Health Monitor       /opt/app/oracle/diag/rdbms/rhys/RHYS/hm
         1 Default Trace File   /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3316.trc
         1 Active Problem Count 1
         1 Active Incident Coun 1

   INST_ID NAME                 VALUE
---------- -------------------- ------------------------------------------------------------
           t

11 rows selected.

SQL> !vi /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3316.trc

查看undo 前镜像：
* Rec #0x16  slt: 0x3c  objn: 73888(0x000120a0)  objd: 73888  tblspc: 5(0x00000005)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c00122.016e.13 ctl max scn: 0x0000.00acf0a4 prv tx scn: 0x0000.00acf0a6
txn start scn: scn: 0x0000.00acf251 logon user: 0
 prev brb: 12583237 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000d.016.000000d8 uba: 0x020000ed.02b6.08
                      flg: C---    lkc:  0     scn: 0x0000.00aa0d31
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01400087  hdba: 0x01400082
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
col  0: [ 2]  c1 03

SQL> select utl_raw.cast_to_number(replace('c1 03',' ')) a from dual;

         A
----------
         2

SQL>
查看undoheader

  TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x80  0x0024  0x0060  0x0000.00acf3c9  0x00c0016c  0x0000.000.00000000  0x00000001   0x00000000
   0x39    9    0x80  0x0023  0x0031  0x0000.00acf37f  0x00c00163  0x0000.000.00000000  0x00000001   0x00000000
   0x3a    9    0x80  0x0023  0x003e  0x0000.00acf38c  0x00c0016a  0x0000.000.00000000  0x00000001   0x00000000
   0x3b    9    0x80  0x0023  0x0035  0x0000.00acf384  0x00c0016a  0x0000.000.00000000  0x00000001   0x00000000
   0x3c   10    0x80  0x0022  0x0000  0x0000.00acf251  0x00c00122  0x0000.000.00000000  0x00000001   0x00000000
   0x3d    9    0x80  0x0023  0x0046  0x0000.00acf39e  0x00c0016b  0x0000.000.00000000  0x00000002   0x00000000
查看数据块信息：

SQL> alter system dump datafile 5 block 135;

System altered.
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0015.03c.00000022  0x00c00122.016e.16  ----    1  fsc 0x0000.00000000
0x02   0x000b.016.000000df  0x02006448.04fb.07  C---    0  scn 0x0000.00ab118a
bdba: 0x01400087
data_block_dump,data header at 0x7ff873eada64
===============
tsiz: 0x1f98
hsiz: 0x1e
pbl: 0x7ff873eada64
     76543210
flag=--------
ntab=1
nrow=6
frre=0
fsbo=0x1e
fseo=0x1f39
avsp=0x1f5b
tosp=0x1f5b
0xe:pti[0]      nrow=6  offs=0
0x12:pri[0]     sfll=2
0x14:pri[1]     offs=0x1f82
0x16:pri[2]     sfll=3
0x18:pri[3]     sfll=-1
0x1a:pri[4]     offs=0x1f61
0x1c:pri[5]     offs=0x1f39
block_row_dump:
tab 0, row 1, @0x1f82
tl: 11 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 02
col  1: [ 2]  c1 04
col  2: [ 1]  62
tab 0, row 4, @0x1f61
tl: 11 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 03
col  1: [ 2]  c1 02
col  2: [ 1]  61
tab 0, row 5, @0x1f39
tl: 9 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 04
col  1: *NULL*
col  2: [ 1]  44
end_of_block_dump
End dump data blocks tsn: 5 file#: 5 minblk 135 maxblk 135

进行提交事务：
查看undo header
 TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0026  0x0060  0x0000.00acf596  0x00c00175  0x0000.000.00000000  0x00000001   0x0000000
   0x36    9    0x80  0x0026  0x0034  0x0000.00acf5fe  0x00c00179  0x0000.000.00000000  0x00000001   0x00000000
   0x37    9    0x80  0x0026  0x003e  0x0000.00acf61e  0x00c0017d  0x0000.000.00000000  0x00000002   0x00000000
   0x38    9    0x80  0x0026  0x0035  0x0000.00acf60c  0x00c0017b  0x0000.000.00000000  0x00000002   0x00000000
   0x39    9    0x80  0x0026  0x0033  0x0000.00acf604  0x00c0017a  0x0000.000.00000000  0x00000001   0x00000000
   0x3a    9    0x80  0x0026  0x0037  0x0000.00acf610  0x00c0017c  0x0000.000.00000000  0x00000001   0x00000000
   0x3b    9    0x80  0x0026  0x003a  0x0000.00acf60f  0x00c0017c  0x0000.000.00000000  0x00000002   0x00000000
   0x3c    9    0x80  0x0022  0xffff  0x0000.00acf666  0x00c00122  0x0000.000.00000000  0x00000001   0x00000000
查看undo前镜像：
*-----------------------------
* Rec #0x16  slt: 0x3c  objn: 73888(0x000120a0)  objd: 73888  tblspc: 5(0x00000005)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c00122.016e.13 ctl max scn: 0x0000.00acf0a4 prv tx scn: 0x0000.00acf0a6
txn start scn: scn: 0x0000.00acf251 logon user: 0
 prev brb: 12583237 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000d.016.000000d8 uba: 0x020000ed.02b6.08
                      flg: C---    lkc:  0     scn: 0x0000.00aa0d31
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01400087  hdba: 0x01400082
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 3 nnew: 1 size: 0
col  0: [ 2]  c1 03
查看数据块信息：

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0015.03c.00000022  0x00c00122.016e.16  ----    1  fsc 0x0000.00000000
0x02   0x000b.016.000000df  0x02006448.04fb.07  C---    0  scn 0x0000.00ab118a
bdba: 0x01400087
data_block_dump,data header at 0x7ff873eada64
===============