Manage Undo tablespace (datafile + v$rollstat + 01555)

 

This time I will introduce some about tablespace UNDO:

 There are three types tablespace :
   Permanent
   temporary
   undo
   
Step1:

 If you want to see the tablespace type, you can use below command:
 SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
UNDOTBS1                       UNDO
SYSAUX                         PERMANENT
TEMP                           TEMPORARY
USERS                          PERMANENT

Step2:
  If you want to create the tablespace, please see below database dictionary first:
 
  SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                  TABLESPA
---------------------------------------------------------- --------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\USERS01.DBF         USERS
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSAUX01.DBF        SYSAUX
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\UNDOTBS01.DBF       UNDOTBS1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF        SYSTEM

Step3:
  create the undo tablespace:
  CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\UNDOTBS01.DBF'
  SIZE 10M AUTOEXTEND ON NEXT 5M;
 
  WE CAN not put the data on undo tablespace:
 
  example:
    create table t1(ID int) tablespace undotbs1
    error:
    ora-30022: can not create segement on undo tablespace
   
Step4:

   Let us look at the segemnt about database:
  
    Oracle have its special key : Undo space, it can put unupdated data in undo space.
    other database dose not have undo function.
    
----------
  We will do a test to check the relationship between tablespace and data:
 
  SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\test.dbf' size 5m;

表空间已创建。

SQL> create table t1 (ID int) tablespace test;

表已创建。
 
SQL> select * from t1;

        ID
----------
      1981
      2012

SQL> update t1 set id=1983 where id=1981;

已更新 1 行。

SQL> select * from t1;

        ID
----------
      1983
      2012

Now I will open another session to check the t1:
SQL> select * from t1;

        ID
----------
      1981
      2012

We found that the data had not updated, because the second data check from the undo tablespace:

------------------------------
Next step we will see the data in which datafile:

Step1:

SQL> select dbms_rowid.rowid_relative_fno(rowid) fon, dbms_rowid.rowid_block_number(rowid) bno from t1;

       FON        BNO
---------- ----------
         5         13
         5         13

Step2:
 next step we will dump the datafile number: 5, block NO: 13
 The text file location: $ORACLE_BASE/admin/udump
 
SQL> alter system dump datafile 5 block 13;

系统已更改。

let us look at below user process trace file details:

------
Dump file d:\oracle\product\10.2.0\admin\sheng\udump\sheng_ora_1140.trc
Sun Feb 12 15:15:47 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1093M/1983M, Ph+PgF:2085M/3270M, VA:1272M/2047M
Instance name: sheng

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 1140, image: ORACLE.EXE (SHAD)

*** 2012-02-12 15:15:47.921
*** SERVICE NAME:(SYS$USERS) 2012-02-12 15:15:47.906
*** SESSION ID:(154.124) 2012-02-12 15:15:47.906
Start dump data blocks tsn: 6 file#: 5 minblk 13 maxblk 13
buffer tsn: 6 rdba: 0x0140000d (5/13)
scn: 0x0000.000d266d seq: 0x01 flg: 0x00 tail: 0x266d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04D02200 to 0x04D04200
4D02200 0000A206 0140000D 000D266D 00010000  [......@.m&......]
4D02210 00000000 00000001 0000C900 000D266D  [............m&..]
4D02220 00000000 00320002 01400009 002A0004  [......2...@...*.]
4D02230 0000018F 00800A5F 0032011C 00000001  [...._.....2.....]
4D02240 00000000 00180007 0000017E 00800473  [........~...s...]
4D02250 001C0149 00008000 000D1F8F 00000000  [I...............]
4D02260 00000000 00020100 0016FFFF 1F701F8A  [..............p.]
4D02270 00001F70 1F910002 00001F8A 00000000  [p...............]
4D02280 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
4D041E0 00000000 00000000 00000000 002C0000  [..............,.]
4D041F0 15C20301 01012C0D 5414C203 266D0601  [.....,.....T..m&]
Block header dump:  0x0140000d
 Object id on Block? Y
 seg/obj: 0xc900  csc: 0x00.d266d  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400009 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.02a.0000018f  0x00800a5f.011c.32  ----    1  fsc 0x0000.00000000
0x02   0x0007.018.0000017e  0x00800473.0149.1c  C---    0  scn 0x0000.000d1f8f
 
data_block_dump,data header at 0x4d02264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x04d02264
bdba: 0x0140000d
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f8a
avsp=0x1f70
tosp=0x1f70
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f91
0x14:pri[1] offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f91
tl: 7 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 3]  c2 14 54
tab 0, row 1, @0x1f8a
tl: 7 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 3]  c2 15 0d
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 13 maxblk 13

---
  Let us clarify:
  lb: lock block
 
  "tl: 7 fb: --H-FL-- lb: 0x1  cc: 1"
  "0x1" : not "0x0" means : this row has been locked.
 
  uba: undo block address
  (the database suggest user to check the file from uba)

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.02a.0000018f  0x00800a5f.011c.32  ----    1  fsc 0x0000.00000000
0x02   0x0007.018.0000017e  0x00800473.0149.1c  C---    0  scn 0x0000.000d1f8f

  If you want to check the data,  you can go "0x00800a5f" to see the first data:
  (Flag  Lck is :"----  1" : this data is locked)
 
---------
  You know the datafile contains : file NO and block NO
 
  Let us to do:
  Oracle packet: 10 decimal
  block address is 16 hex:
  let us do a transfer from hex to decimal :
 
SQL> select to_number('00800a5f','xxxxxxxx') from dual;

TO_NUMBER('00800A5F','XXXXXXXX')
--------------------------------
                         8391263

SQL> select dbms_utility.data_block_address_file(8391263) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8391263)
---------------------------------------------
                                            2

SQL> select dbms_utility.data_block_address_block(8391263) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(8391263)
----------------------------------------------
                                          2655

----
if you want to see the data, you can go: datafile: 2 ,block: 2655 check:

SQL> alter system dump datafile 2 block 2655;

系统已更改。

ok,let check the dump file again:

let us check the datafile: 2 :
ok, we find the datafile is undotable:

SQL> select file_name,file_id from dba_data_files;

FILE_NAME                                                     FILE_ID
---------------------------------------------------------- ----------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\USERS01.DBF                  4
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSAUX01.DBF                 3
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\UNDOTBS01.DBF                2
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF                 1
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\TEST.DBF                     5

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

------

let us check how many undo segment:
select * from dba_rollback_segs

------
Next we will face one case:
  if you have not commit one session, at this time, you have to change one undotablespace to another,
  how about the undo tablespace do:
 
  we will invole another database dictionary: v$rollstat
 
  SQL> select xacts,status,usn from v$rollstat;

     XACTS STATUS                 USN
---------- --------------- ----------
         0 ONLINE                   0
         0 ONLINE                   1
         0 ONLINE                   2
         0 ONLINE                   3
         1 ONLINE                   4
         0 ONLINE                   5
         0 ONLINE                   6
         0 ONLINE                   7
         0 ONLINE                   8
         0 ONLINE                   9
         0 ONLINE                  10

已选择11行。

Xacts: means: active session, No 4 session,
we will check another database dictionary: V$transation

Oracle involed on parameter to control the session number in one roll block:

SQL> show parameter roll

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
rollback_segments                    string
transactions_per_rollback_segment    integer     5

---

Check oracle is there is undo session waiting : v$waitstat

---
If there is more than 10 (USN : 0 is used by sys by private) session:
smon process will create added undo segment to process the session:

select * from v$rollstat, you will see more than 10 counts.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

-------------------
Step5:

The following , I will give detailed about undo_retention parameter:

if the undo size is low, ora-01555 will occur.
because the undo datafile had be overwrite.

Reason:
  1: The undo tablespace autoextend : off
 2: The undo_retention time is short (the default is 900 seconds)
 3: The undo size is low.
 4: The sql search time is too long.