--==================================

--Oracle 回滾(ROLLBACK)和撤銷(UNDO)

--==================================

一、回滾(ROLLBACK)和撤銷(UNDO)

回滾和前滾是保證Oracle資料庫中的資料處於一致性狀態的重要手段。

在9i版本以前

Oracle使用資料庫中的回滾段來實現未提交資料或因系統故障導致例項崩潰時進行回滾操作

每一個表空間需要建立回滾段，各個表空間對回滾段實現各自的管理

在9i及後續版本

提供了一種新的回滾資料的管理方式，即使用Oracle自動管理的撤銷(Undo)表空間

自動撤銷管理表空間統一管理所有DML的回滾操作，簡化了對於回滾工作的管理

在9i,10g中的回滾段僅僅用作保留向後相容

撤銷段代替了原有版本中的回滾段，因此本文所有描述均使用撤銷

撤銷的實質意味著將所作的修改退回到修改前的狀態，即倒退所有DML語句

二、撤銷段中的內容及相關特性

對於任何DML操作而言，必須同時處理資料塊和撤銷塊，並且還會生成重做資訊

在ACID中，A、C、I要求生成撤銷，D則要求生成重做

INSERT：

撤銷段記錄插入記錄的rowid,如果需要撤銷，則根據rowid將該記錄刪除即可

UPDATE:

撤銷段記錄被更新欄位的原始值，撤銷時將原始值覆蓋新值即可

DELETE：

撤銷段記錄整行的資料，撤銷時執行反向操作將該記錄插入原表

由上可知，UNDO段中的內容總結如下：

資料為修改之前的副本

從每個改變資料的事務中獲得

在事務結束前一直被保留

UNDO段中資料的作用：

用於回滾操作

讀一致性和閃回查詢

用於事務失敗時的恢復

UNDO段與事務：

一個事物的啟動，Oracle將為其分配僅僅一個UNDO段,若該段用完,則Oracle會自動為該UNDO段新增另一個區間(extent)

一個UNDO段能夠同時為多個事務服務

UNDO段與UNDO表空間：

UNDO段中的內容儲存在UNDO表空間

任意給定時刻只能使用一個UDNO表空間

UNDO表空間必須被建立為持久的、本地管理、可自動擴充套件的表空間

正在使用的UNDO表空間不能撤銷或刪除

UNDO表空間使用迴圈寫的方式，與聯機日誌檔案寫相似，不同的是UNDO中可以設定了undo_retention 保留時間

UNDO段的兩種管理方式：

AUTO 自動管理(推薦)

MANUAL 手動管理(僅保留)

三、與撤銷相關的幾個引數

--檢視本機中Oracle的版本

SQL> SELECT * FROM v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

--檢視和UNDO相關的引數

SQL> SHOW PARAMETER undo;

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string UNDOTBS1

undo_management：

設定資料庫的撤銷段是否使用自動管理模式，值可以為auto或manual，當為manual時將不使用撤銷段，即不使用自動管理模式

該引數為靜態引數，修改後需重啟例項才能生效

undo_retention：

指定撤銷段資料在undo段中為非活動狀態後被覆蓋前保留的時間，單位為秒。在undo_management位auto時生效，為動態引數

undo_tablespace：

指定使用哪個表空間來實現資料的撤銷，在undo_management位auto時生效，為動態引數

retention guarantee子句：

保證撤銷保留，使用下面的操作來實現

ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;

--下面的查詢中是當undo_management為manual時的結果集，可以看出撤銷表空間的撤銷段都處於offline狀態

SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS

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

SYSTEM SYSTEM ONLINE

_SYSSMU1$ UNDOTBS1 OFFLINE

_SYSSMU2$ UNDOTBS1 OFFLINE

_SYSSMU3$ UNDOTBS1 OFFLINE

_SYSSMU4$ UNDOTBS1 OFFLINE

_SYSSMU5$ UNDOTBS1 OFFLINE

_SYSSMU6$ UNDOTBS1 OFFLINE

_SYSSMU7$ UNDOTBS1 OFFLINE

_SYSSMU8$ UNDOTBS1 OFFLINE

_SYSSMU9$ UNDOTBS1 OFFLINE

_SYSSMU10$ UNDOTBS1 OFFLINE

--在undo_management 引數為manual時，對scott.emp插入一條新記錄，收到了錯誤提示

--非系統表空間不能夠使用回滾段

SQL> INSERT INTO scott.emp(empno,ename,salary)

2 VALUES(6666,'Jenney',3000);

INSERT INTO scott.emp(empno,ename,salary)

*

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

--檢視段的型別，發現僅僅system表空間存在ROLLBACK 段，所以前一條插入語句收到錯誤提示

SQL> SELECT DISTINCT segment_type,tablespace_name FROM dba_segments

2 ORDER BY tablespace_name;

SEGMENT_TYPE TABLESPACE_NAME

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

INDEX EXAMPLE

INDEX PARTITION EXAMPLE

LOBINDEX EXAMPLE

LOBSEGMENT EXAMPLE

NESTED TABLE EXAMPLE

TABLE EXAMPLE

TABLE PARTITION EXAMPLE

INDEX SYSAUX

INDEX PARTITION SYSAUX

LOB PARTITION SYSAUX

LOBINDEX SYSAUX

SEGMENT_TYPE TABLESPACE_NAME

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

LOBSEGMENT SYSAUX

NESTED TABLE SYSAUX

TABLE SYSAUX

TABLE PARTITION SYSAUX

CLUSTER SYSTEM

INDEX SYSTEM

LOBINDEX SYSTEM

LOBSEGMENT SYSTEM

NESTED TABLE SYSTEM

ROLLBACK SYSTEM --與之前的版本相容的回滾段

TABLE SYSTEM

SEGMENT_TYPE TABLESPACE_NAME

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

TABLE TBS1

TYPE2 UNDO UNDOTBS1 --9i之後使用的撤銷段

INDEX USERS

LOBINDEX USERS

LOBSEGMENT USERS

NESTED TABLE USERS

TABLE USERS

--下面將undo_management改為支援自動管理，需要重啟例項

SQL> ALTER SYSTEM SET undo_management = 'auto' SCOPE = SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 1218796 bytes

Variable Size 67110676 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

--再次檢視dba_rollback_segs檢視所有的撤銷段全部處於online狀態

--注意第一行為system表空間的撤銷段，用於系統表空間的撤銷

SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS

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

SYSTEM SYSTEM ONLINE

_SYSSMU1$ UNDOTBS1 ONLINE

_SYSSMU2$ UNDOTBS1 ONLINE

_SYSSMU3$ UNDOTBS1 ONLINE

_SYSSMU4$ UNDOTBS1 ONLINE

_SYSSMU5$ UNDOTBS1 ONLINE

_SYSSMU6$ UNDOTBS1 ONLINE

_SYSSMU7$ UNDOTBS1 ONLINE

_SYSSMU8$ UNDOTBS1 ONLINE

_SYSSMU9$ UNDOTBS1 ONLINE

_SYSSMU10$ UNDOTBS1 ONLINE

由上面的示例可知：

ROLLBACK 段： --與之前的版本相容的回滾段

TYPE2 UNDO 段： --9i之後使用的撤銷段

關於回滾，一個時刻僅能使用一種類段型別，即要麼使用與以前版本相容的回滾段，要麼使用撤銷段

事實上，在9i之後僅僅支援撤銷段，從上面錯誤的提示即可證實

--檢視DML語句產生的事務

SQL> SHOW USER;

USER is "SYS"

SQL> SELECT * FROM scott.emp WHERE ename = 'SCOTT';

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

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

7788 SCOTT ANALYST 7566 19-APR-87 3500 20

SQL> UPDATE scott.emp SET sal = sal * 2 WHERE ename = 'SCOTT';

1 row updated.

SQL> SELECT addr,xidusn,status,start_time,used_ublk

2 FROM v$transaction;

ADDR XIDUSN STATUS START_TIME USED_UBLK

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

2DA2B17C 9 ACTIVE 07/10/10 20:29:08 1

--檢視當前哪些使用者使用撤銷段以及段的大小，啟動時間，活動狀態等

SQL> SELECT t.xidusn,t.start_time,t.used_ublk,t.status,

s.username,r.segment_name

FROM v$transaction t

JOIN v$session s

ON t.ses_addr = s.saddr

JOIN dba_rollback_segs r

ON r.segment_id = t.xidusn ;

XIDUSN START_TIME USED_UBLK STATUS USERNAME SEGMENT_NAME

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

9 07/10/10 20:29:08 1 ACTIVE SYS _SYSSMU9$

四、UNDO表空間的建立與管理

建立UNDO表空間

建立語法：

CREATE UNDO TABLESPACE tablespace_name DATAFILE '...' SIZE n

更多表空間的建立：

請參照：Oracle 表空間與數據文件

切換UNDO表空間

例項中允許多個UNDO表空間存在

可以從一個UNDO表空間切換到另外一個UNDO表空間

任一時刻只能有一個UNDO表空間被指定

使用ALTER SYSTEM SET undo_tablespace = undo_tablespace_name實現切換

刪除UNDO表空間

DROP TABLESPACE undo_tablespace_name

任意例項的UNDO表空間在非活動狀態可以刪除

對於活動狀態的UNDO表空間，應當先將切換到其它表空間，在所有事務完成後再刪除該表空間

演示建立、切換及刪除UNDO表空間

--檢視當前系統中的表空間

SQL> SELECT file_name,tablespace_name FROM dba_data_files;

FILE_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/orcl/tbs1_2.dbf TBS1

/u01/app/oracle/oradata/orcl/tbs1_1.dbf TBS1

/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE

/u01/app/oracle/oradata/orcl/users01.dbf USERS

/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

--建立一個新的UNDO表空間undo2

SQL> CREATE UNDO TABLESPACE undo2

2 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 3M

3 AUTOEXTEND ON;

Tablespace created.

SQL> SELECT file_name,tablespace_name FROM dba_data_files WHERE tablespace_name LIKE 'UNDO%';

FILE_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs02.dbf UNDO2

--檢視當前系統使用的UNDO表空間為UNDOTBS1

SQL> SELECT name,value FROM v$parameter WHERE name LIKE 'undo%';

NAME VALUE

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

undo_management AUTO

undo_tablespace UNDOTBS1

undo_retention 900

--建立一張表tb_test用於演示，假定該會話為session1

SQL> CREATE TABLE tb_test

2 (

3 ID INT,

4 Name VARCHAR2(20)

5 );

Table created.

--插入一條記錄到tb_test，此時未提交將產生UNDO 資訊

SQL> INSERT INTO tb_test SELECT 1,'Robinson' FROM dual;

1 row created.

--此時開啟另外一個回話，假定為session2，在session2中切換表空間

SQL> ALTER SYSTEM SET undo_tablespace = 'undo2';

System altered. --undotbs1中有未提交的事務，竟然可以成功切換？如此這般閃回時估計會有問題

SQL> SHOW PARAMETER undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string undo2

--在session1中執行commit

SQL> COMMIT;

Commit complete. --成功執行了commit,且下面的查詢看到了提交後的結果

SQL> SELECT * FROM tb_test;

ID NAME

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

1 Robinson

--登出系統後再次檢視，結果依然存在，比較納悶

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[uniread] Saved history (716 lines)

[oracle@robinson ~]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 10 21:29:36 2010

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> SELECT * FROM tb_test;

ID NAME

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

1 Robinson

--刪除UNDO表空間

--在session1中插入一條新記錄

SQL> INSERT INTO tb_test SELECT 2,'Jack' FROM DUAL;

1 row created.

--在session2中將撤銷表空間切換為undotbs1

SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';

System altered.

--緊接著在該回話中刪除undo2，提示正在使用

SQL> DROP TABLESPACE undo2;

DROP TABLESPACE undo2

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDO2' is currently in use

--在session1中提交事務

SQL> COMMIT;

Commit complete.

--在session2中再次刪除表空間undo2，收到了相同的錯誤提示

SQL> /

DROP TABLESPACE undo2

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDO2' is currently in use

SQL> SHOW PARAMETER undo --檢視的確是已切換到undotbs1

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string undotbs1

--在session2中再次執行表空間切換到undotbs1

SQL> ALTER SYSTEM SET undo_tablespace = 'undotbs1';

System altered.

--此時undo2成功刪除，可以看出需要在將活動事務提交或回滾後，再切換之後才能成功刪除撤銷表空間

SQL> DROP TABLESPACE undo2;

Tablespace dropped.

SQL> ho ls $ORACLE_BASE/oradata/orcl;

control01.ctl redo03.log redo2.log system01.dbf undotbs02.dbf

control02.ctl redo04.log redo3.log tbs1_1.dbf users01.dbf

example01.dbf redo07.log redo7.log tbs1_2.dbf

redo01.log redo08.log redo8.log temp01.dbf

redo02.log redo1.log sysaux01.dbf undotbs01.dbf

--刪除UNDO表空間的物理檔案

SQL> ho rm $ORACLE_BASE/oradata/orcl/undotbs02.dbf;

--在session1中可以看到兩條記錄也被成功插入

SQL> SELECT * FROM tb_test;

ID NAME

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

1 Robinson

2 Jack

--檢視當前撤銷表空間的大小

SQL> SELECT tablespace_name,bytes/1024/1024 FROM dba_data_files

2 WHERE tablespace_name = 'UNDOTBS1';

TABLESPACE_NAME BYTES/1024/1024

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

UNDOTBS1 30

--迴圈插入記錄到tb_test後檢視undo表空間的使用情況

SQL> BEGIN

2 FOR i IN 1..20000

3 LOOP

4 INSERT INTO tb_test VALUES(i,'Unkown Name');

5 END LOOP;

6 END;

7 /

PL/SQL procedure successfully completed.

--可以看到UNDO 表空間只用了個塊

SQL> SELECT addr,xidusn,used_ublk FROM v$transaction;

ADDR XIDUSN USED_UBLK

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

2D9FC160 6 174

SQL> SELECT 174 * 8 || 'KB' FROM dual;

174*8|

------

1392KB

五、計算UNDO表空間的大小

計算公式：

MAX(undoblks)/600 * MAX(maxquerylen) 位於v$undostat

* db_block_size 位於v$parameter

--建立演示環境

SQL> INSERT INTO tb_test SELECT employee_id,first_name FROM hr.employees;

107 rows created

SQL> INSERT INTO tb_test SELECT * from tb_test;

109 rows created.

--多次執行上述命令，下面是的tb_test表中的記錄數

SQL> /

892928 rows created.

SQL> COMMIT;

Commit complete.

--檢視當前undo表空間的大小

SQL> SELECT t.name,d.name,d.bytes/1024/1024 as TotalSize ,t.flashback_on,d.status

2 FROM v$tablespace t

3 JOIN v$datafile d

4 USING (ts#)

5 WHERE t.name LIKE 'UNDO%';

NAME NAME TOTALSIZE FLA STATUS

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

UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 30 YES ONLINE

--將undo表空間修改為RETENTION GUARANTEE及關閉自動擴充套件

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Tablespace altered.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' AUTOEXTEND OFF;

Database altered.

SQL> SELECT tablespace_name,contents,retention FROM dba_tablespaces

2 WHERE tablespace_name LIKE 'UNDO%';

TABLESPACE_NAME CONTENTS RETENTION

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

UNDOTBS1 UNDO GUARANTEE

--修改保留時間為分鐘

SQL> ALTER SYSTEM SET undo_retention = 120;

System altered.

--迴圈刪除tb_test中的記錄，提示undo表空間空間容量不夠

SQL> BEGIN

2 FOR i IN 1..1000

3 LOOP

4 DELETE FROM tb_test WHERE rownum < 1001;

5 COMMIT;

6 END LOOP;

7 END;

8 /

BEGIN

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

ORA-06512: at line 4

--修改回話的時間引數

SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd HH24:MI:SS';

Session altered.

--檢視v$undostat檢視，獲得相關資訊

SQL> SELECT begin_time,end_time,undoblks,maxquerylen, ssolderrcnt,nospaceerrcnt

2 FROM v$undostat;

BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT

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

2010-07-12 19:12:18 2010-07-12 19:22:18 6 0 0 0

2010-07-12 19:02:18 2010-07-12 19:12:18 9 0 0 0

2010-07-12 18:52:18 2010-07-12 19:02:18 47 0 0 0

2010-07-12 18:42:18 2010-07-12 18:52:18 2136 0 0 1

2010-07-12 18:32:18 2010-07-12 18:42:18 6 0 0 0

2010-07-12 18:22:18 2010-07-12 18:32:18 413 1541 0 0

2010-07-12 18:12:18 2010-07-12 18:22:18 179 938 0 0

2010-07-12 18:02:18 2010-07-12 18:12:18 6 0 0 0

--計算undo表空間所需的大小

SQL> SELECT (

2 (SELECT MAX(undoblks)/600 * MAX(maxquerylen) FROM v$undostat) *

3 (SELECT value FROM v$parameter WHERE name = 'db_block_size'))/1024/1024 as Need_Size

4 FROM dual;

NEED_SIZE

----------

42.8590625

--取消撤銷保留選項

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

Tablespace altered

六、UNDO配額

對於超長的事務或不當的SQL指令碼將耗用大量的UNDO表空間，使用UNDO表空間配額可以提高資源的利用率

對於不同組的使用者可以分配不同的最大UNDO表空間配額

當某個組超出了最大的資源限制，則該組不允許新的事務產生，直到當前組的UNDO表空間釋放或終止

七、撤銷常見的兩個錯誤

1.ORA-1555 snapshot too old 快照過舊錯誤的解決

配置合適的保留時間(undo_retention)

調整undo表空間的大小

考慮保證撤銷保留的使用(retention guarantee)

2.ORA-30036 unable to extend segment in undo tablespace 無法擴充套件撤銷表空間內的撤銷段

調整undo表空間的大小

確保大量的事務能夠週期性的提交

八、UNDO涉及的幾個相關檢視：

V$TRANSACTION

V$SESSION

DBA_ROLLBACK_SEGS --顯示所有的segments

V$ROLLSTAT

V$UNDOSTAT

V$ROLLNAME --顯示當前線上的segments

Oracle 回滾(ROLLBACK)和撤銷(UNDO)

相關文章