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表空間替換現有的
方法一 有時候,無法收縮,最有效的方法是方法二,可以根本的解決空間回縮的方法
方法一:
1.
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>
2.
SQL> select file#,name from v$datafile;
FILE#
----------
NAME
-----------------------------------------------------------
.....
6
F:/ORACLE/PRODUCT/10.2.0/ORADATA/MOVO/UNDOTBS02.DBF
已選擇6行。
3.
SQL> select max(block_id) from dba_extents where file_id=6;
MAX(BLOCK_ID)
-------------
5625
4.
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
6.
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
14-4月 -08 14-4月 -08 0 83 179 3
14-4月 -08 14-4月 -08 2185 16 136 1
如果産生空間錯誤的問題,則參數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 ----
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)