Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to understand undo tablespaces. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Related views:

1. V$sysstat (check the number of user submissions and rollbacks)

SQL > SELECT name,value from v$sysstat where name like 'user'

NAME VALUE

-

User commits 176

User rollbacks 0

User calls 328

User I/O wait time 3132

In the awr report in our database, the value of Rollback per transaction% is calculated from these two values:

Round (User rollbacks/ (user commit+user rollback), 4) * 100%

The closer the number of metrics is to 0, the less rollbacks to the database.

2. V$ROLLNAME: rollback segment name and ID information.

For rollback tablespaces, oracle initializes 10 rollback segments by default, and then automatically allocates or recycles them

3. V$ROLLSTAT: this view stores the current status and related statistics of each allocated rollback segment in all UNDO tablespaces, and does not display the rollback segment whose status is in OFFLINE.

You can also see the corresponding rollback segment information from the v$rollstat. XACTS is the transaction that represents the activity.

USN: rollback segment identification

RSSIZE: default size of rollback segment

XACTS: number of active transactions

WRITES: number of rollback segment writes (in bytes)

SHRINKS: number of contraction of rollback segment

EXTENDS: number of rollback segment extensions

WRAPS: number of rollback segment flipping (wrap)

GETS: get the number of rollback segment headers

WAITS: number of wait times for rollback segment headers

SQL > select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat

USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS

0 7620 385024 0 385024 0 1

21 267816 2088960 0 2088960 0 0

22 36122 253952 1 253952 0 0

4. DBA_ROLLBACK_SEGS: this dictionary displays the current status of all rollback segments and information related to storage space allocation, including undo tablespace usage.

Select segment_name,owner from dba_rollback_segs where tablespace_name='UNDOTBS3'

SEGMENT_NAME OWNER

_ SYSSMU21 $PUBLIC

_ SYSSMU22 $PUBLIC

5. V$UNDOSTAT: the statistics used by the entire UNDO table space for a certain period of time and the results of automatic optimization of UNDO are saved. After the time is exceeded, it will go to DBA_HIST_UNDOSTAT.

6. DBA_UNDO_EXTENTS: saves the storage space allocation and usage of all the allocated data areas in the UNDO tablespace, which is an important view to get the current existing state of UNDO data.

SQL > select tablespace_name,status,sum (bytes) / 1024 from dba_undo_extents group by tablespace_name,status 1024 from dba_undo_extents group by tablespace_name,status

SQL > select owner,segment_name,extent_id,file_id,blocK_id from dba_undo_extents

OWN SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID

-

SYS _ SYSSMU20 $0 9 153

SYS _ SYSSMU20 $1 9 161

SYS _ SYSSMU19 $0 9 137

7. Information about V$TRANSACTION 's current ongoing transactions. Related to UNDO management are the UNDO segments involved in the current transaction, UNDO space occupancy and other information.

XIDUSN represents the rollback segment number

XIDSLOT represents the corresponding transaction slot

START_UBABLK represents the undo starting block used by the transaction

UBABLK represents the last undo block used

XIDSQN represents the incremental sequence number of transaction slot reuse

Incremental sequence number of UBASQN rollback segment reuse

The slots in the transaction table in the undo segment header

Are continuously being reused. Each time a slot it re-used

Its sequence number (xidsqn) goes up. (indicates the increment sequence number of transaction slot reuse)

Similarly, undo blocks are re-used (though it takes more

Time to get round to re-using them than transaction slots).

Each time an undo block is "newed" and reused, its

Sequence number UBASQN goes up. (incremental sequence number of rollback segment reuse)

8 、 v$undostat

This view can be used to view the start and end time of the rollback segment, the embezzled information of the rollback segment, insufficient space, or ORA-01555 errors.

Basic information

BEGIN_TIME: statistics start time

END_TIME: end time of statistics

Usage information

UNDOTSN: the ID of the UNDO tablespace of the last reported activity.

UNDOBLKS: the total number of UNDO blocks generated during the period.

TXNCOUNT: the total number of transactions executed during the period.

MAXQUERYLEN: the length of the longest execution time, in seconds, of a single query completed during the period. This time is calculated by the time it takes from the cursor opening to the last time the data is executed / extracted. Use this time to adjust the corresponding UNDO RETENTION. However, due to the situation that the cursor opens but waits for a long time without operation, the data is not necessarily accurate this time.

MAXQUERYID: the SQL ID of the query mentioned above.

MAXCONCURRENCY: the maximum number of concurrent transactions during the period.

Embezzlement of unexpired UNDO data

UNXPSTEALCNT: the number of unexpired UNDO data embezzlement that occurred during the period.

UNXPBLKRELCNT: the number of embezzled blocks of unexpired UNDO data that occurred during the period.

UNXPBLKREUCNT: the number of blocks reused after the embezzlement of unexpired UNDO data occurred during the period.

Expired UNDO data embezzlement

EXPSTEALCNT: the number of embezzlement that occurred during the period.

EXPBLKRELCNT: the number of embezzled UNDO blocks that occurred during the period.

EXPBLKREUCNT: the number of embezzled data blocks that were reused during the period.

Error occurrence information

SSOLDERRCNT: the number of ORA-1555 errors that occurred during the period.

NOSPACEERRCNT: the number of out-of-space errors during the period.

UNDO data usage information when sampling

ACTIVEBLKS: the number of UNDO blocks active at the sampling time.

UNEXPIREDBLKS: the number of UNDO blocks that have not expired at the sampling time.

EXPIREDBLKS: the number of UNDO blocks that have expired at the sampling time.

Automatic UNDO optimization result

The time, in seconds, of UNDO data that will not be recycled in the TUNED_UNDORETENTION:UNDO tablespace. By querying this field, we can know whether the UNDO data of the transaction completed at a specific time before still exists, which is very helpful to estimate the available time of Flashback.

Undo data embezzlement refers to the embezzlement of data blocks caused by the size of undo tablespaces or the setting of undo_retention when undo data has not expired.

It can be divided into unexpired undo data embezzlement and expired undo data embezzlement. All the information can be viewed in the v$undostat view:

Embezzlement of unexpired UNDO data

UNXPSTEALCNT: the number of unexpired UNDO data embezzlement that occurred during the period.

UNXPBLKRELCNT: the number of embezzled blocks of unexpired UNDO data that occurred during the period.

UNXPBLKREUCNT: the number of blocks reused after the embezzlement of unexpired UNDO data occurred during the period.

Expired UNDO data embezzlement

EXPSTEALCNT: the number of embezzlement that occurred during the period.

EXPBLKRELCNT: the number of embezzled UNDO blocks that occurred during the period.

EXPBLKREUCNT: the number of embezzled data blocks that were reused during the period.

Related, there are dba_extents, a table similar to dba_undo_extents, and dba_esgments and dba_data_files, but the undo tablespace needs to be specified when querying.

The dictionaries V$ROLLSTAT and V$UNDOSTAT look a little similar in name, but in fact, the difference between the two views is very big. V$ROLLSTAT records the statistics of the usage of each rollback segment in the entire UNDO table space, which belongs to horizontal, while V$UNDOSTAT records the statistics of the usage of the entire UNDO in each time period, belonging to vertical.

Generally speaking, for an insert operation, it only needs to record the inserted rowid, so it produces the least undo, while the update operation is relatively more, while the delete operation consumes the most undo space, and once it is rolled back, it takes the most time.

On the one hand, oracle does not allow other users to read unsubmitted data, on the other hand, it ensures that the data read by users comes from the same point in time.

Several parameters of the undo tablespace:

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS3

Undo_management: used to define whether the rollback segment used by the database is managed automatically or manually (AUTO/MANUAL)

Undo_tablespace: defines which undo table space is used by the current instance in automatic management mode.

Undo_retention: in automatic management mode, when the rollback segment is inactive, the retention time of the rollback segment is 900s by default

Undo_retention only specifies the expiration time of undo data, it does not mean that the data in undo must be saved in the undo table space for 15 minutes. For example, at the beginning of a new transaction, if the undo table space is already full, the data of the new transaction will automatically overwrite the committed transaction data, regardless of whether the data has expired or not, but if the undo_retention time has passed. Then the original data in the committed transaction is immediately inaccessible, but it is just invalid, as long as it is not overwritten, it will still exist.

There is only one case in which undo tablespaces can ensure that the data in undo is valid until the time specified by undo_retention expires, that is, the Retention parameter is specified for undo tablespaces. After specifying, oracle does not overwrite unexpired undo data in undo tablespaces, for example:

SQL > Alter tablespace undotbs1 retention guarantee

Disable undo tablespace retention guarantee, for example:

SQL > Alter tablespace undotbs1 retention noguarantee

In general, when we look at the undo tablespace size, we can view the tablespace allocation size with dba_data_files and the usage size with dba_undo_extents (including expired and unexpired)

V $sysstat and vails rollnamevolutionrollstatstatstat can view undo statistics, dba_rollback_segs and v$transaction can view undo usage information, and through v$transaction combined with v$session, you can also find the sid corresponding to the rollback segment.

When the rollback tablespace is full:

Generally speaking, we can do the following:

1, add undo tablespace data files, which should be carefully studied, unless your undo tablespaces are created when the settings are really too small to meet the needs, to add data files.

2. Switch undo tablespaces, which is a long-to-do approach. In general, only tablespaces are used very much and consume too much disk space.

-replace tablespaces:

Follow the steps: 1. Create a new tablespace-2, modify the default undo information of the parameter file-3, and view the usage of the rollback segment (it can only be deleted if no callback is used using undo. View with dba_rollback_segs or v$rollstat)-offline original undo tablespace-drop tablespace

When the rollback segment is damaged:

In general, once the undo is corrupted, the database cannot be opened properly. Simulate the environment and physically delete the undo data file:

SQL > startup

ORACLE instance started.

Total System Global Area 218103808 bytes

Fixed Size 1266680 bytes

Variable Size 79694856 bytes

Database Buffers 134217728 bytes

Redo Buffers 2924544 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2-see DBWR trace file

ORA-01110: data file 2:'/ oracle/app/oradata/orcl/undotbs1.dbf'

Database startup error

At this time, we can change the management of the rollback segment of the database to manual, AUTO---MANL and change the rollback tablespace to the system rollback segment to start the database.

SQL > create pfile='/oracle/initorcl.ora' from spfile

File created.

Modify the parameter file:

# * .undo _ tablespace='UNDOTBS1'

# * .undo _ management='AUTO'

Undo_management='MANUAL'

Rollback_segments='SYSTEM'

After modification, start to the mount phase, offline and delete the corrupted undo data file and open the database.

SQL > startup mount pfile='/oracle/initorcl.ora'

SQL > alter database datafile'/ oracle/app/oradata/orcl/undotbs1.dbf' offline drop

SQL > alter database open

Delete the original undo table space and create a new undo table space.

SQL > drop tablespace undotbs1 including contents and datafiles

Tablespace dropped.

SQL > create undo tablespace undotbs1 datafile'/ oracle/app/oradata/orcl/undotbs1.dbf' size 50m autoextend on

Tablespace created.

Re-change the parameter file parameters back to start the database.

SQL > select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC from v$transaction

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

22 12 66 10 175 98 28

Indicates that the rollback segment number is 22 and the transaction slot is 12

You can also see the corresponding rollback segment information from the v$rollstat. XACTS is the transaction that represents the activity.

SQL > select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat

USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS

0 7620 385024 0 385024 0 1

21 267816 2088960 0 2088960 0 0

22 36122 253952 1 253952 0 0

The v$rollname view can be used to get the rollback segment name.

SQL > select * from v$rollname where usn=22

USN NAME

22 _ SYSSMU22 $

Dump rollback segment header information:

SQL > alter system dump undo header'_ SYSSMU22 $'

System altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ oracle/app/admin/orcl/udump/orcl_ora_5949.trc

For files that come out of dump:

Unix process pid: 5949, image: oracle@linux-4.7 (TNS V1-V3)

* * 2011-11-01 21 purl 52purl 05.679

* ACTION NAME: () 2011-11-01 21 purl 52purl 05.666

* MODULE NAME: (sqlplus@linux-4.7 (TNS V1-V3)) 2011-11-01 21 purl 52purl 05.666

* SERVICE NAME: (SYS$USERS) 2011-11-01 21 purl 52purl 05.666

* SESSION ID: (159.10) 2011-11-01 21 purl 52purl 05.666

*

Undo Segment: _ SYSSMU22 $(22)

Index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

-

0x00 9 0x00 0x0042 0x0001 0x0000.000c4af7 0x028000af 0x0000.000.00000000 0x00000001 0x00000000 1320152385

0x01 9 0x00 0x0042 0x0006 0x0000.000c4b1a 0x028000af 0x0000.000.00000000 0x00000001 0x00000000 1320152431

0x02 9 0x00 0x0041 0x0005 0x0000.000c4581 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x03 9 0x00 0x0041 0x000a 0x0000.000c44eb 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x04 9 0x00 0x0042 0x002e 0x0000.000c4a7c 0x028000af 0x0000.000.00000000 0x00000001 0x00000000 1320152138

0x05 9 0x00 0x0041 0x0012 0x0000.000c458b 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x06 9 0x00 0x0042 0x002b 0x0000.000c4b38 0x028000af 0x0000.000.00000000 0x00000001 0x00000000 1320152432

0x07 9 0x00 0x0041 0x001a 0x0000.000c464d 0x028000ad 0x0000.000.00000000 0x00000001 0x00000000 1320149924

0x08 9 0x00 0x0041 0x0023 0x0000.000c47e2 0x028000ad 0x0000.000.00000000 0x00000001 0x00000000 1320150762

0x09 9 0x00 0x0041 0x0014 0x0000.000c45b0 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x0a 9 0x00 0x0041 0x000b 0x0000.000c44ff 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x0b 9 0x00 0x0041 0x000e 0x0000.000c4513 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x0c 10 0x80 0x0042 0x0002 0x0000.00000000 0x028000af 0x0000.000.00000000 0x00000001 0x00000000 0

0x0d 9 0x00 0x0041 0x0008 0x0000.000c47ae 0x028000ad 0x0000.000.00000000 0x00000001 0x00000000 1320150644

0x0e 9 0x00 0x0041 0x000f 0x0000.000c4527 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x0f 9 0x00 0x0041 0x0010 0x0000.000c453b 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

0x10 9 0x00 0x0041 0x0013 0x0000.000c454e 0x028000ac 0x0000.000.00000000 0x00000001 0x00000000 1320149738

First of all, 0x0c stands for hexadecimal, and converting to binary means 12, which means it occupies the 12th transaction slot. Stat is state, 9 is inactive transaction, 10 is active transaction.

Dba is data block address, which means that the address of the block containing the previous mirror of the transaction is converted to binary by 0x028000af, that is, 0000 0010 1000 0000 0000 0000 1010 1111.

The first 10 bits represent the file address, and the last 22 bits represent the block address, that is, the pre-mirror information is located on the 0000 block of document 10.

This is consistent with the query results in the transaction.

Now let's simulate the process of another rollback segment transaction:

Previous transactions of commit, re-modify multiple records:

Select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 4000 30

7782 CLARK MANAGER 7839 09-JUN-81 4000 10

7788 SCOTT ANALYST 7566 19-APR-87 4000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 3000

SQL > update emp set sal=2000 where empno=7934

SQL > update emp set sal=2000 where empno=7902

SQL > update emp set sal=2000 where empno=7900

SQL > update emp set sal=2000 where empno=7876

SQL > select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC from v$transaction

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

23 8 65 10 901 67 39

We dump out the block 901 of the corresponding file No. 10 in the rollback segment:

SQL > alter system dump datafile 10 block 901

SQL > oradebug setmypid

SQL > oradebug tracefile_name

/ oracle/app/admin/orcl/udump/orcl_ora_6091.trc

View information:

UNDO BLK:

Xid: 0x0017.008.00000041 seq: 0x43 cnt: 0x27 irb: 0x27 icl: 0x0 flg: 0x000

0

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset

0x01 0x1f3c 0x02 0x1ed0 0x03 0x1e64 0x04 0x1de8 0x05 0x1d7c

0x06 0x1d10 0x07 0x1ca4 0x08 0x1bf8 0x09 0x1b8c 0x0a 0x1b08

0x0b 0x1aa8 0x0c 0x1a10 0x0d 0x198c 0x0e 0x1910 0x0f 0x183c

0x10 0x17b8 0x11 0x1734 0x12 0x1698 0x13 0x1644 0x14 0x15c0

0x15 0x1538 0x16 0x14b4 0x17 0x1460 0x18 0x13c4 0x19 0x1340

0x1a 0x12c4 0x1b 0x1240 0x1c 0x11b4 0x1d 0x1108 0x1e 0x1060

0x1f 0x0fac 0x20 0x0f60 0x21 0x0e7c 0x22 0x0e14 0x23 0x0dbc

0x24 0x0d24 0x25 0x0ccc 0x26 0x0c70 0x27 0x0c18

The irb field refers to the start of the most recent uncommitted changes recorded in the rollback segment:

Irb: 0x27

We can also find that the last offset address happens to be the 0x27 information.

Find information about 0x27:

* Rec # 0x27 slt: 0x08 objn: 52173 (0x0000cbcd) objd: 52173 tblspc: 4 (0x000000

04)

* Layer: 11 (Row) opc: 1 rci 0x26

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

Rdba: 0x00000000

*--

KDO undo record:

KTB Redo

Op: 0x02 ver: 0x01

Op: C uba: 0x02800385.0043.26

KDO Op code: URP row dependencies Disabled

Xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x010000fc hdba: 0x010000fb

Itli: 1 ispac: 0 maxfr: 4858

Tabn: 0 slot: 10 (0xa) flag: 0x2c lock: 0 ckix: 191

Ncol: 8 nnew: 1 size: 0

Vector content:

Col 5: [2] c2 0c

For c20c to decimal to 1100, that is, the front mirror of my last modified data.

Rci represents a later offset. (in the same transaction)

* Rec # 0x26 slt: 0x08 objn: 52173 (0x0000cbcd) objd: 52173 tblspc: 4 (0x000000

04)

* Layer: 11 (Row) opc: 1 rci 0x25

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

Rdba: 0x00000000

*--

KDO undo record:

KTB Redo

Op: 0x02 ver: 0x01

Op: C uba: 0x02800385.0043.25

KDO Op code: URP row dependencies Disabled

Xtype: XA flags: 0x00000000 bdba: 0x010000fc hdba: 0x010000fb

Itli: 1 ispac: 0 maxfr: 4858

Tabn: 0 slot: 11 (0xb) flag: 0x2c lock: 0 ckix: 191

Ncol: 8 nnew: 1 size: 1

Col 5: [3] c2 0a 33

Similarly, c20a 33 represents 950 in base 10; that is, the front mirror of the record updated from the penultimate entry.

Jump in turn until the rci of the last undo chain is 0x00

When there are other users to read the data, oracle will construct a consistent read, through the front mirror to read the data before the change to the user.

After reading the above, do you have any further understanding of how to understand undo tablespaces? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.