UNDO表空间的管理

技术2024-11-20 5

UNDO表空间的管理

1.概念

回滚段概述　　回滚段用于存放数据修改之前的值（包括数据修改之前的位置和值）。回滚段的头部包含正在使用的该回滚段事务的信息。一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。

回滚段的作用　　1。事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时， ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。

　　2。事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

　　3。读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）。当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。

事务级的读一致性　　ORACLE一般提供SQL语句级（SQL STATEMENT LEVEL）的读一致性，可以用以下语句来实现事务级的读一致性。　　SET TRANSACTION READ ONLY；　　或：　　SET TANNSACTION SERIALIZABLE；　　以上两个语句都将在事务开始后提供读一致性。需要注意的是，使用第二个语句对数据库的并发性和性能将带来影响。

回滚段的种类　　1。系统回滚段：当数据库创建后，将自动创建一个系统回滚段，该回滚段只用于存放系统表空间中对象的前影像。

　　2。非系统回滚段：拥有多个表空间的数据库至少应该有一个非系统回滚段，用于存放非系统表空间中对象的数据前影像。非系统回滚段又分为私有回滚段和公有回滚段，私有回滚段应在参数文件的ROLLBACK SEGMENTS参数中列出，以便例程启动时自动使其在线（ONLINE）。公有回滚段一般在 OPS（ORACLE并行服务器）中出现，将在例程启动时自动在线。

　　3。DEFERED回滚段：该回滚段在表空间离线（OFFLINE）时由系统自动创建，当表空间再次在线（ONLINE）时由系统自动删除，用于存放表空间离线时产生的回滚信息。

回滚段的使用　　分配回滚段：当事务开始时，ORACLE将为该事务分配回滚段，并将拥有最少事务的回滚段分配给该事务。事务可以用以下语句申请指定的回滚段：　　SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment

　　事务将以顺序，循环的方式使用回滚段的区（EXTENTS），当当前区用满后移到下一个区。几个事务可以写在回滚段的同一个区，但每个回滚段的块只能包含一个事务的信息。

　　例如（两个事务使用同一个回滚段，该回滚段有四个区）：　　1、事务在进行中，它们正在使用回滚段的第三个区；　　2、当两个事务产生更多的回滚信息，它们将继续使用第三个区；　　3、当第三个区满后，事务将写到第四个区，当事务开始写到一个新的区时，称为翻转（WRAP）；　　4、当第四个区用满时，如果第一个区是空闲或非活动（使用该区的所有事务完成而没有活动的事务）的，事务将接着使用第一个区。

回滚段的扩张（EXTEND）　　当当前回滚段区的所有块用完而事务还需要更多的回滚空间时，回滚段的指针将移到下一个区。当最后一个区用完，指针将移到第一个区的前面。回滚段指针移到下一个区的前提是下一个区没有活动的事务，同时指针不能跨区。当下一个区正在使用时，事务将为回滚段分配一个新的区，这种分配称为回滚段的扩展。回滚段将一直扩展到该回滚段区的个数到达回滚段的参数MAXEXTENTS的值时为止。

回滚段的回收和OPTIMAL参数　　OPTIMAL参数指明回滚段空闲时收缩到的位置，指明回滚段的OPTIMAL参数可以减少回滚段空间的浪费。

V$ROLLSTAT中的常用列 ? USN：回滚段标识 ? RSSIZE：回滚段默认大小 ? XACTS：活动事务数

在一段时间内增量用到的列 ? WRITES：回滚段写入数(单位:bytes) ? SHRINKS：回滚段收缩次数 ? EXTENDS：回滚段扩展次数 ? WRAPS：回滚段翻转(wrap)次数 ? GETS：获取回滚段头次数 ? WAITS：回滚段头等待次数

V$ROLLSTAT中的连接列 Column View Joined Column(s) -------------- ----------------------- ------------------------ USN V$ROLLNAME USN

注意：　　通过花费时间除以翻转次数，你可以得到一次回滚段翻转(wrap)的平均用时。此方法常用于在长查询中指定合适的回滚段大小以避免'Snapshot Too Old'错误。同时，通过查看extends和shrinks列可以看出optimal是否需要增加。

2.实践操作

和undo相关的视图:

v$rollstat v$rollname dba_segments DBA_ROLLBACK_SEGS v$transaction

1.查看回滚段的信息 select * from dba_rollback_segs

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS INSTANCE_NUM RELATIVE_FNO ------------------------------ ------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------- ---------------------------------------- ---------------------- SYSTEM SYS SYSTEM 0 1 9 114688 1 32765 ONLINE 1 _SYSSMU1$ PUBLIC UNDOTBS3 1 9 2825 131072 2 32765 ONLINE 9 _SYSSMU2$ PUBLIC UNDOTBS3 2 9 2841 131072 2 32765 ONLINE 9

MIN_EXTENTS :一个段中最小的区的数量(一般段为1,回滚段为2) MAX_EXTENTS :一个段中最大的区的数量(最大为32765)

有的人会疑问,那这个段最大值=32765*每个区中段的数量*段的大小,

2.当前库中创建了多少个回退段,回退段所在的表空间名,回退段的大小

SQL> select segment_name,tablespace_name,bytes,blocks from dba_segments where 2 segment_type='ROLLBACK';

SEGMENT_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME BYTES BLOCKS ------------------------------ ---------- ---------- SYSTEM SYSTEM 393216 48

3.使用回滚段的信息的查询

select r.name rollbackname, s.sid, s.serial#, s.username , t.cr_get, t.phy_io, t.used_ublk, t.noundo, substr(s.program,1,78) ops_program from v$rollname r,v$transaction t, v$session s where t.addr=s.taddr and t.xidusn=r.usn order by t.cr_get,phy_io

4.查看回滚段的竞争情况,如果命中率大于2%,那就存在回退段竞争,必须增加回退段的个数,

select rn.name,rs.gets,rs.waits,(rs.waits/rs.gets)*100 ratio from v$rollname rn,v$rollstat rs where rs.usn=rn.usn

gets: 回退段被访问的次数 waits:进程等待回退段的次数 ratio:回退段的命中率

5.undu表空间的大小计算方法

select (rd*(ups*overhead)+overhead)/1024/1024/1024 bytes from (select value rd from v$parameter where name ='undo_retention'), (select (sum(undoblks)/sum(((end_time-begin_time)*10800))) ups from v$undostat), (select value overhead from v$parameter where name='db_block_size');

Rd：undo_retention设置的时间； Ups：undo blocks per second； Overhead：rollback segment header；

估计undo tablespace大小的公式： Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;

6.查看系统使用的undo空间的情况

方法1: SQL> show parameter undo

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

方法二: SQL> select name ,value from v$parameter where name like 'un%';

NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- undo_management AUTO

undo_tablespace UNDOTBS1

undo_retention 900

在我们的数据库往往有undo tablespace占用空间过大，占用了磁盘60%的空间。如果发现此问题的话，应从应用着手了，什么事务能使用若大的undo tablespace.发现不能回收，可以考虑我如下的方法:

方法一: 用resize收缩表空间方法二: 用新健的undo表空间替换现有的

方法一有时候,无法收缩,最有效的方法是方法二,可以根本的解决空间回缩的方法

方法一:

SQL> select tablespace_name ,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name 2 union all 3 select tablespace_name ,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME GB ------------------------------ ---------- SYSAUX .263671875 UNDOTBS1 .034179688 USERS .067138672 SYSTEM .478515625 EXAMPLE .09765625 UNDOTBS2 .306640625 TEMP .01953125

已选择7行。

SQL>

SQL> select file#,name from v$datafile;

FILE# ---------- NAME -----------------------------------------------------------

.....

6 F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF

已选择6行。

SQL> select max(block_id) from dba_extents where file_id=6;

MAX(BLOCK_ID) ------------- 5625

SQL> show parameter db_block_size;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------ db_block_size integer 8192

5,查询已经用的undo空间

SQL> select 5625*8/1024 from dual;

5625*8/1024 ----------- 43.9453125

SQL> alter database datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF' resize 13m; alter database datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF' resize 13m * 第 1 行出现错误: ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

因为我给undo初始大小定义为10M,而现在是43m,说明是扩展的,所以都是用过的

SQL> select file_name ,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2';

FILE_NAME -------------------------------------------------------------------------------- BYTES/1024/1024 --------------- F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF 314

SQL> alter database datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF' resize 32

数据库已更改。

SQL> select file_name ,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2';

FILE_NAME -------------------------------------------------------------------------------- BYTES/1024/1024 --------------- F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF 320

SQL> alter database datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF' resize 31

数据库已更改。

SQL> select file_name ,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2';

FILE_NAME -------------------------------------------------------------------------------- BYTES/1024/1024 --------------- F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF 314

方法二:

1.查看数据库文件的路径

SQL> select name from v$datafile;

NAME -------------------------------------------------------------------------------- F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/SYSTEM01.DBF F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/SYSAUX01.DBF F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/USERS01.DBF F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/EXAMPLE01.DBF F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF

2. 查看当前数据库的默认undo空间

SQL> show parameter undo;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2 SQL> create undo tablespace undotbs1 2 datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS01.DBF' size 10m;

表空间已创建。

3.修改参数文件

SQL> alter system set undo_tablespace=undotbs1 scope=both;

系统已更改。

4.查看回滚段的使用情况,要等没有是使用回滚段的时候.才能删除表空间

SQL> select usn,xacts,status,rssize/1024/1024/1024 ,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

USN XACTS STATUS RSSIZE/1024/1024/1024 ---------- ---------- --------------- --------------------- HWMSIZE/1024/1024/1024 SHRINKS ---------------------- ---------- 5 0 ONLINE .000114441 .000114441 0

1 0 ONLINE .000114441 .000114441 0

2 0 ONLINE .000114441 .000114441 0

USN XACTS STATUS RSSIZE/1024/1024/1024 ---------- ---------- --------------- --------------------- HWMSIZE/1024/1024/1024 SHRINKS ---------------------- ---------- 3 0 ONLINE .000114441 .000114441 0

4 0 ONLINE .000114441 .000114441 0

10 0 ONLINE .000114441 .000114441 0

USN XACTS STATUS RSSIZE/1024/1024/1024 ---------- ---------- --------------- --------------------- HWMSIZE/1024/1024/1024 SHRINKS ---------------------- ---------- 6 0 ONLINE .000114441 .000114441 0

7 0 ONLINE .000114441 .000114441 0

8 0 ONLINE .000114441 .000114441 0

USN XACTS STATUS RSSIZE/1024/1024/1024 ---------- ---------- --------------- --------------------- HWMSIZE/1024/1024/1024 SHRINKS ---------------------- ---------- 9 0 ONLINE .000114441 .000114441 0

0 0 ONLINE .000358582 .000358582 0

已选择11行。

5.删除undo空间

SQL> drop tablespace ubdotbs2 including contents and datafiles; //如果数据库在使用状态删除时,一般是无法物理删除数据文件的,因为还有事务在使用,有两个方法,一个另外建一个数据文件 ,等没有事务用的时候,在物理删除;第二个方法是重新启动下数据库,然后物理删除数据文件,(如果可以重启数据库,.执行这个语句会自动删除数据文件)

表空间已删除。

6.查看表空间的大小

SQL> select file_name,bytes/1024/1024 from dba_data_files 2 where tablespace_name like 'UNDOTBS1';

FILE_NAME ------------------------------------------------------------------------------ BYTES/1024/1024 --------------- F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS01.DBF 10

或者

SQL> select tablespace_name ,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_ 2 union all 3 select tablespace_name ,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_n

TABLESPACE_NAME GB ------------------------------ ---------- SYSAUX .263671875 UNDOTBS1 .009765625 USERS .067138672 SYSTEM .478515625 EXAMPLE .09765625 TEMP .01953125

已选择6行。

SQL>

---待续回滚段管理

1.监控回退段

SQL> select begin_time,end_time,maxquerylen len,undoblks,txncount,maxconcurrency maxcon ,nospaceerrcnt err from v$undostat where 2 begin_time > sysdate-(2/24);

BEGIN_TIME END_TIME LEN UNDOBLKS TXNCOUNT MAXCON -------------- -------------- ---------- ---------- ---------- ---------- ERR ---------- 14-4月 -08 14-4月 -08 0 14 268 1 0

14-4月 -08 14-4月 -08 0 83 179 3 0

14-4月 -08 14-4月 -08 2185 16 136 1 0

如果产生空间错误的问题,则参数nospaceerrcnt会增加,这是就要扩大undo表空间

2.重建undo表空间,更改系统的表空间

SQL> create undo tablespace undotbs2 2 datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS03.DBF' size 10m 3 extent management local autoallocate;

表空间已创建。

SQL> alter system set undo_tablespace=undotbs2;

系统已更改。

SQL> alter tablespace undotbs1 offline;

表空间已更改。

SQL> alter database datafile 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF','F:/ORACLE/PRODUC T/10.2.0/ORADATA/MOVO/UNDOTBS01.DBF' OFFLINE DROP;

数据库已更改。

SQL> commit;

提交完成。

SQL>

------待续空间的故障恢复

undo空间的案例恢复的方法

--- end ----

最新回复(0)