Oracle dump undo description

I. undo preparation knowledge

In the previous article, undo segment is described as follows:

Current online Redo and Undo corrupt methods

1.1 When undo_management is set to MENUAL, the SYSTEM rollback segment is used to record undo records to the SYSTEM segment under the SYSTEM tablespace.

SQL> col segment_name format a10

SQL>Select segment_name, tablespace_name, bytes, next_extent from dba_segments where segment_type = 'rollback ';

SEGMENT_NATABLESPACE_NAME BYTES NEXT_EXTENT

-------------------------------------------------------------

SYSTEM 393216 1048576

Through the preceding statement, we found that the system segment used for rollback exists with the system tablespace. By default, there is only one segment and it is relatively small. Therefore, if the system segment is used to store undo records. It will definitely affect the performance of the database. Therefore, we recommend that you use Undo tablespace to manage undo records in Oracle.

1.2 When undo_management is set to AUTO, UNDOtablespace is used to manage rollback segments. At this time, we will have multiple undo segments which are stored in the UNDO tablespace. This improves the performance of the database.

SYS @ anqing2 (rac2)>Select segment_name, tablespace_name, header_file, header_block, bytes from dba_segments where segment_type = 'type2 UNDO '; 

SEGMENT_NAME TABLESPACE_NAME HEADER_FILEHEADER_BLOCK BYTES

---------------------------------------------------------------

_ SYSSMU1 $ UNDOTBS1 2 9 107806720

_ SYSSMU2 $ UNDOTBS1 2 25 111411200

_ SYSSMU3 $ UNDOTBS1 2 41 120586240

_ SYSSMU4 $ UNDOTBS1 2 57 100990976

_ SYSSMU5 $ UNDOTBS1 2 73 112721920

_ SYSSMU6 $ UNDOTBS1 2 89 117243904

_ SYSSMU7 $ UNDOTBS1 105 106233856

_ SYSSMU8 $ UNDOTBS1 121 155975680

_ SYSSMU9 $ UNDOTBS1 137 184287232

_ SYSSMU10 $ UNDOTBS1 153 149356544

_ SYSSMU11 $ UNDOTBS2 5 9 131072

 

SEGMENT_NAME TABLESPACE_NAME HEADER_FILEHEADER_BLOCK BYTES

---------------------------------------------------------------

_ SYSSMU12 $ UNDOTBS2 5 25 25 131072

_ SYSSMU13 $ UNDOTBS2 5 41 131072

_ SYSSMU14 $ UNDOTBS2 5 57 131072

_ SYSSMU15 $ UNDOTBS2 5 73 131072

_ SYSSMU16 $ UNDOTBS2 5 89 131072

_ SYSSMU17 $ UNDOTBS2 5 105 131072

_ SYSSMU18 $ UNDOTBS2 5 121 131072

_ SYSSMU19 $ UNDOTBS2 5 137 131072

_ SYSSMU20 $ UNDOTBS2 5 153 131072

20 rows selected.

Through the preceding SQL query results, we can see that each node has 10 undo segments to store undo records.

The preceding figure shows the result in the dba_segment table. You can also view the information through the v $ rollstat and v $ rollname views. The two views display information of all rollback segments. Including the system and undo segments.

SQL> col name format a15

SQL>Select s. usn, n. name, s. extents, s. hwmsize, s. status from v $ rollstat s, v $ rollname n wheres. usn = n. usn;

 

USNNAME EXTENTS HWMSIZESTATUS

------------------------------------------------------------

0 SYSTEM 6 385024 ONLINE

1_SYSSMU1 $3 7659520 ONLINE

2_SYSSMU2 $3 9691136 ONLINE

3_SYSSMU3 $4 7462912 ONLINE

4_SYSSMU4 $3 76668928 ONLINE

5_SYSSMU5 $4 8511488 ONLINE

6_SYSSMU6 $3 7462912 ONLINE

7_SYSSMU7 $3 33480704 ONLINE

8_SYSSMU8 $3 8577024 ONLINE

9_SYSSMU9 $3 7462912 ONLINE

10_SYSSMU10 $3 13754368 ONLINE

11 rows selected.

1.3 view the undo segment currently used by the transaction

You can use the v $ transaction View to check the undo segment information currently used by the transaction. After undo segment is determined, you can perform related dump operations. For a description of the v $ transaction View, see the official documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_3114.htm#REFRN30291

The description is as follows:

ADDR	RAW (4 | 8)	Address of the transaction state object
XIDUSN	NUMBER	Undo segment number
XIDSLOT	NUMBER	Slot number
XIDSQN	NUMBER	Sequence number
UBAFIL	NUMBER	Undo block address (UBA) filenum
UBABLK	NUMBER	UBA block number
UBASQN	NUMBER	UBA sequence number
UBAREC	NUMBER	UBA record number
STATUS	VARCHAR2 (16)	Status

 

-- View the current SID Information

SYS @ anqing2 (rac2)> select sid fromv $ mystat where rownum = 1;

SID

----------

147

SYS @ anqing2 (rac2)> update ta set name = 'Dave 'where name = 'ora _ rowscn ';

474 rows updated.

 

-- View the rollback segment number that is being used in v $ transaction.

SYS @ anqing2 (rac2)> selectxidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from v $ transaction;

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC

------------------------------------------------------------

11 23, 1010, 12, 5, 9

-- Xidusn: undo segment number

-- Xidslot: slot number

-- Xidsqn: sequence number

-- Ubafil: undo block address (uba) filenum

-- Ubablk: uba block number

-- Ubarec: UBA record number

The UBA format is DBA. seq #. rec #,This part of UBA is described in my article about itl: Introduction e ITL (InterestedTransaction List)

-- Use the xidusn and v $ rollname to determine the undo segment name in use.

SYS @ anqing2 (rac2)> select usn, name from v $ rollname where usn = 11;

USN NAME

----------------------------------------

11_syssmu11 $

 

-- After commit, the corresponding transaction information is gone.

SYS @ anqing2 (rac2)> commit;

Commit complete.

SYS @ anqing2 (rac2)> selectxidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from v $ transaction;

No rows selected

SYS @ anqing2 (rac2)>

1 2 Next Page