6. Oracle 復原(ROLLBACK)和撤銷(UNDO)

標籤：username   nts   tns   orm   lstat   form   資料表空間   delete   最大   

轉載自：http://blog.csdn.net/leshami/article/details/5731158

 

一、復原(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 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production--查看和UNDO相關的參數SQL> SHOW PARAMETER undo;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      MANUALundo_retention                       integer     900undo_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) 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 ORDER BY tablespace_name;SEGMENT_TYPE       TABLESPACE_NAME------------------ ------------------------------INDEX              EXAMPLEINDEX PARTITION    EXAMPLELOBINDEX           EXAMPLELOBSEGMENT         EXAMPLENESTED TABLE       EXAMPLETABLE              EXAMPLETABLE PARTITION    EXAMPLEINDEX              SYSAUXINDEX PARTITION    SYSAUXLOB PARTITION      SYSAUXLOBINDEX           SYSAUXLOBSEGMENT         SYSAUXNESTED TABLE       SYSAUXTABLE              SYSAUXTABLE PARTITION    SYSAUXCLUSTER            SYSTEMINDEX              SYSTEMLOBINDEX           SYSTEMLOBSEGMENT         SYSTEMNESTED TABLE       SYSTEMROLLBACK           SYSTEM       --與之前的版本相容的復原段TABLE              SYSTEMTABLE              TBS1TYPE2 UNDO         UNDOTBS1     --9i之後使用的撤銷段INDEX              USERSLOBINDEX           USERSLOBSEGMENT         USERSNESTED TABLE       USERSTABLE              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 bytesFixed Size                  1218796 bytesVariable Size              67110676 bytesDatabase Buffers          180355072 bytesRedo Buffers                2973696 bytesDatabase 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 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資料表空間undo2SQL> CREATE UNDO TABLESPACE undo2 DATAFILE ‘/u01/app/oracle/oradata/orcl/undotbs02.dbf‘ SIZE 3M 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資料表空間為UNDOTBS1SQL> SELECT name,value FROM v$parameter WHERE name LIKE ‘undo%‘;NAME                           VALUE------------------------------ --------------------------------------------------undo_management                AUTOundo_tablespace                UNDOTBS1undo_retention                 900--建立一張表tb_test用於示範，假定該會話為session1SQL> CREATE TABLE tb_test (ID INT, Name VARCHAR2(20));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      AUTOundo_retention                       integer     900undo_tablespace                      string      undo2--在session1中執行commitSQL> COMMIT;Commit complete.   --成功執行了commit,且下面的查詢看到了提交後的結果SQL> SELECT * FROM tb_test;        ID NAME---------- ------------------------------         1 Robinson     --登出系統後再次查看，結果依然存在，比較納悶        SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options[uniread] Saved history (716 lines)[[email protected] ~]$ sqlplus / as sysdba;SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 10 21:29:36 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> SELECT * FROM tb_test;        ID NAME---------- --------------------         1 Robinson --刪除UNDO資料表空間--在session1中插入一條新記錄SQL> INSERT INTO tb_test SELECT 2,‘Jack‘ FROM DUAL;1 row created.--在session2中將撤銷資料表空間切換為undotbs1SQL> 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 useSQL> SHOW PARAMETER undo   --查看的確是已切換到undotbs1NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      undotbs1--在session2中再次執行資料表空間切換到undotbs1SQL> 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.dbfcontrol02.ctl  redo04.log   redo3.log     tbs1_1.dbf     users01.dbfexample01.dbf  redo07.log   redo7.log     tbs1_2.dbfredo01.log     redo08.log   redo8.log     temp01.dbfredo02.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 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        174SQL> 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 createdSQL> 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 FROM v$tablespace t JOIN v$datafile d USING (ts#) 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 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 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             02010-07-12 19:02:18 2010-07-12 19:12:18          9           0           0             02010-07-12 18:52:18 2010-07-12 19:02:18         47           0           0             02010-07-12 18:42:18 2010-07-12 18:52:18       2136           0           0             12010-07-12 18:32:18 2010-07-12 18:42:18          6           0           0             02010-07-12 18:22:18 2010-07-12 18:32:18        413        1541           0             02010-07-12 18:12:18 2010-07-12 18:22:18        179         938           0             02010-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$TRANSACTIONV$SESSIONDBA_ROLLBACK_SEGS   --顯示所有的segmentsV$ROLLSTATV$UNDOSTATV$ROLLNAME          --顯示當前線上的segments

　　

6. Oracle 復原(ROLLBACK)和撤銷(UNDO)