oracle undo 表空間
Undo的作用：
1、事務處理回退
2、事務處理恢復
3、讀一致性
4、閃回資料
Oracle開始一個事務，當要修改資料時，會先將修改前的資料儲存到undo表空間的undo段中。儲存這些修改前的資料的原因下面這些場合需要undo資料：1）事務的回滾、2）例項恢復(回滾)、3）一致性讀時需要構造CR塊；
檢視使用的undo表空間
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO ---使用自動管理
undo_retention integer 900 --保留臨時資料的時間為900秒
undo_tablespace string UNDOTBS1 --預設表空間為undotbs1
SQL>
那麼undo表空間 中的段分為3種。
第一種為系統段： 由系統表空間產生的映象資料放在系統段中
第二種為非系統段：由非系統表空間產生的映象資料
第三種為臨時段(DEFERRED)：離線的表空間都將產生一個臨時undo段，這個段就是用來存放離線之後資料檔案所有的變化的，當聯機之後會寫回對應資料檔案中。（這個段是由system表空間生成的）
|
系統段與非系統段 SQL> select * from v$rollname;
USN NAME ---------- ------------------------------ 0 SYSTEM ---系統段 1 _SYSSMU1$ --- 2 _SYSSMU2$ | 3 _SYSSMU3$ | 4 _SYSSMU4$ | 5 _SYSSMU5$ | => 這些為非系統段（至少要存在一個） 6 _SYSSMU6$ | 7 _SYSSMU7$ | 8 _SYSSMU8$ | 9 _SYSSMU9$ | 10 _SYSSMU10$ ---
11 rows selected.
SQL>
DEFERRED臨時段 （只有表空間離線才會生成臨時段） 沒有離線表空間下檢視是否有臨時段
SQL> select status,enabled from v$datafile;
STATUS ENABLED ------- ---------- SYSTEM READ WRITE ONLINE READ WRITE ONLINE READ WRITE ONLINE READ WRITE
SQL> SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';
no rows selected
SQL>
離線實驗 SQL> alter tablespace users offline;
Tablespace altered.
SQL> select status,enabled from v$datafile;
STATUS ENABLED ------- ---------- SYSTEM READ WRITE ONLINE READ WRITE ONLINE READ WRITE OFFLINE DISABLED
SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';
SEGMENT_NAME SEGMENT_TYPE -------------------------------------------------------------------------------- 1.60529 DEFERRED ROLLBACK
SQL> 將表空間聯機檢視（online之後自動消失） SQL> alter tablespace users online;
Tablespace altered.
SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEFE%';
no rows selected
SQL>
|
Undo表空間的建立、刪除、修改
|
建立語句 SQL> create undo tablespace undo1 datafile '+data/fengzi/datafile/undo1.dbf' size 100m autoextend on;
刪除undo表空間 SQL> show parameter undo_t
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 （因為正在使用，所以刪不掉） SQL>
SQL> alter system set undo_tablespace=undo1; SQL>drop tablespace undotbs1；（有時候是刪不掉的，需要重啟刪除）
對於修改undo表空間，只需要建立一個新的undo表空間修改引數替換就可以。
SQL> create undo tablespace undotbs1 datafile '+data/fengzi/datafile/undotbs1.dbf' size 100m autoextend on; SQL> alter system set undo_tablespace=undotbs1
重新命名當前預設的undo表空間 SQL> show parameter undo_t
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 SQL> alter tablespace undotbs1 to undo1; 檢視OS 引數檔案 [oracle@dongyang ~]$ cd $ORACLE_HOME [oracle@dongyang dbs]$ strings spfilefengzi.ora |grep undo_t *.undo_tablespace='UNDO1' [oracle@dongyang dbs]$ 當重新命名預設的undo表空間名時，同時也修改了引數檔案中的預設undo表空間名
|
估算undo表空間大小
|
計算公式 undo space=(undo_retention*(undo blocks per second*db blocksize))+db_ block_size
相關引數值 SQL> show parameter undo_retention
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 SQL> SQL> desc v$undostat Name Null? Type ----------------------------------------- -------- ---------------------------- BEGIN_TIME DATE END_TIME DATE UNDOTSN NUMBER UNDOBLKS NUMBER TXNCOUNT NUMBER MAXQUERYLEN NUMBER MAXQUERYID VARCHAR2(13) MAXCONCURRENCY NUMBER UNXPSTEALCNT NUMBER UNXPBLKRELCNT NUMBER UNXPBLKREUCNT NUMBER EXPSTEALCNT NUMBER EXPBLKRELCNT NUMBER EXPBLKREUCNT NUMBER SSOLDERRCNT NUMBER NOSPACEERRCNT NUMBER ACTIVEBLKS NUMBER UNEXPIREDBLKS NUMBER EXPIREDBLKS NUMBER TUNED_UNDORETENTION NUMBER
SQL>
SQL> select sum(undoblks)/sum((end_time-begin_time)*10800) from v$undostat;
SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*10800) ---------------------------------------------- .340940335
SQL> show parameter block_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 SQL>
根據公式算出大小
SQL> select 900*0.340940335*8192 from dual;
900*0.340940335*8192 -------------------- 2513684.9 或者
SQL>select (rt*(ups*bl))/1024/1024/1024 GB from (select value rt from v$parameter where name='undo_retention'),(select (sum(undoblks)/sum((end_time-begin_time)*10800)) ups from v$undostat),(select value bl from v$parameter where name='db_block_size'); GB ---------- .002020201
|
事務的回退
在開始一個事務之後，執行一些DML語句沒有手動或者隱式提交，那麼可以使用rollback進行回退，把映象的資料更新到原塊中
|
SQL> select * from shangdong;
ID ---------- 100 200 300 400
SQL> 開始一個事務，對資料修改以及建立回退儲存點 SQL> update shangdong set id=id+10;
4 rows updated.
SQL> select * from shangdong;
ID ---------- 110 210 310 410
SQL> savepoint q1;
Savepoint created. SQL> SQL> delete shangdong where id=110;
1 row deleted.
SQL> savepoint q2;
Savepoint created.
SQL> delete shangdong where id=210;
1 row deleted.
SQL> savepoint q3;
Savepoint created.
SQL> delete shangdong where id=310;
1 row deleted.
SQL> savepoint q4;
Savepoint created.
SQL> 回退事務 SQL> rollback to q3;
Rollback complete.
SQL> SQL> rollback to q4; rollback to q4; * ERROR at line 1: ORA-01086: savepoint 'Q4' never established SQL> rollback to q1;
Rollback complete. （可以看到，回退到前一時間點是可行的，但是回退到未知時間點是不行的。Q4儲存點就是Q3的未知 時間點） SQL> rollback to q2; ERROR at line 1: ORA-01086: savepoint 'Q2' never established
SQL> rollback; 回退到事務開始前
Rollback complete.
SQL> SQL> select * from shangdong;
ID ---------- 100 200 300 400
SQL>
|
已提交的事務回退
|
SQL> delete shangdong;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from shangdong as of timestamp sysdate-2/1440; （2分鐘前的資料）
ID ---------- 100 200 300 400 如果找到想要的資料，直接插入原表就好 SQL> SQL> insert into shangdong select * from shangdong as of timestamp sysdate-2/1440;
4 rows created.
SQL> select * from shangdong;
ID ---------- 100 200 300 400
SQL>
|
Undo表空間的的備份與恢復
在歸檔模式下是用RMAN 進行全備backup database比較直接
|
[oracle@dongyang ~]$ rman target / RMAN> backup database;
對於undo丟失或損壞恢復 SQL> show parameter undo_t
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1 SQL>
RMAN> restore tablespace undotbs1; RMAN>recover tablespace undotbs1; SQL> alter database datafile 2 online; SQL> alter database open;
|
對於undo表空間沒有進行備份的恢復
檢視系統中是否存在其他的undo，如果是則修改為其他的undo，把損壞的離線，然後在啟動資料庫到open，刪除損壞的表空間並建立新的
系統中沒有其他的undo表空間，使用隱藏引數，讓我們的undo離線，就可以開啟資料庫然後刪除undo表空間並建立undo表空間。
|
SQL> create undo tablespace undotbs datafile '+data/fengzi/datafile/undotbs.dbf' size 100m autoextend on;
Tablespace created.
SQL> 關閉資料庫刪除原undo表空間資料檔案啟動資料庫 SQL> startup ORACLE instance started.
Total System Global Area 1124073472 bytes Fixed Size 2020224 bytes Variable Size 704646272 bytes Database Buffers 402653184 bytes Redo Buffers 14753792 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181' 修改引數檔案重啟資料庫 SQL> alter system set undo_tablespace=undotbs scope=spfile;
System altered.
SQL> shutdown immediate; ORA-01109: database not open
Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 1124073472 bytes Fixed Size 2020224 bytes Variable Size 704646272 bytes Database Buffers 402653184 bytes Redo Buffers 14753792 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181'
SQL> 將資料檔案離線啟動資料庫 SQL> alter database datafile 2 offline;
Database altered.
SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> 檢視報警日誌 [oracle@dongyang ~]$ vi /u01/app/oracle/admin/fengzi/bdump/alert_fengzi.log GG 進入最後一行 Successful open of redo thread 1 Mon Apr 14 11:17:35 2014 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Apr 14 11:17:35 2014 SMON: enabling cache recovery Mon Apr 14 11:17:35 2014 ARC0: STARTING ARCH PROCESSES Mon Apr 14 11:17:35 2014 ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2 started with pid=22, OS id=7651 Mon Apr 14 11:17:36 2014 ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the heartbeat ARCH Mon Apr 14 11:17:38 2014 Errors in file /u01/app/oracle/admin/fengzi/udump/fengzi_ora_7641.trc: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181' Mon Apr 14 11:17:38 2014 Error 376 happened during db open, shutting down database USER: terminating instance due to error 376 Instance terminated by USER, pid = 7641 ORA-1092 signalled during: alter database open...
[oracle@dongyang ~]$ vi /u01/app/oracle/admin/fengzi/udump/fengzi_ora_7641.trc
/u01/app/oracle/admin/fengzi/udump/fengzi_ora_7641.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: dongyang Release: 2.6.32-200.13.1.el5uek Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011 Machine: x86_64 Instance name: fengzi Redo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 7641, image: oracle@dongyang (TNS V1-V3)
*** 2014-04-14 11:17:35.552 *** SERVICE NAME:(SYS$USERS) 2014-04-14 11:17:35.552 *** SESSION ID:(324.3) 2014-04-14 11:17:35.552 tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1) （表示是有事務的，這種事務決定了不能根據當前日誌檔案處理 tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1) 需要用到datafile 2 的所以離線也是不行的） ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '+DATA/fengzi/datafile/undotbs1.278.842187181'
這時候就需要使用到隱藏引數了 SQL> startup nomount; ORACLE instance started.
Total System Global Area 1124073472 bytes Fixed Size 2020224 bytes Variable Size 704646272 bytes Database Buffers 402653184 bytes Redo Buffers 14753792 bytes 檢視需要的隱藏引數 SQL> select ksppinm from x$ksppi where ksppinm like '%roll%';
KSPPINM -------------------------------------------------------------------------------- transactions_per_rollback_segment rollback_segments _rollback_segment_initial _rollback_segment_count _offline_rollback_segments _corrupted_rollback_segments _cleanup_rollback_entries _rollback_stopat fast_start_parallel_rollback _mv_rolling_inv
10 rows selected.
SQL> 使用隱藏引數 SQL> alter system set "_offline_rollback_segments"=true scope=spfile;
System altered.
SQL> SQL> startup force mount ORACLE instance started.
Total System Global Area 1124073472 bytes Fixed Size 2020224 bytes Variable Size 704646272 bytes Database Buffers 402653184 bytes Redo Buffers 14753792 bytes Database mounted. 確認undo的狀態 SQL> show parameter undo_t
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS SQL> show parameter undo_ma
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO 修改undo管理為手動 SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL> SQL> startup force mount ORACLE instance started.
Total System Global Area 1124073472 bytes Fixed Size 2020224 bytes Variable Size 704646272 bytes Database Buffers 402653184 bytes Redo Buffers 14753792 bytes Database mounted. SQL> 檢視資料檔案的狀態 SQL> select name,status,enabled from v$datafile;
NAME -------------------------------------------------------------------------------- STATUS ENABLED ------- ---------- +DATA/fengzi/datafile/system.277.842187103 SYSTEM READ WRITE
+DATA/fengzi/datafile/undotbs1.278.842187181 OFFLINE READ WRITE
+DATA/fengzi/datafile/sysaux.279.842187235 ONLINE READ WRITE
NAME -------------------------------------------------------------------------------- STATUS ENABLED ------- ---------- +DATA/fengzi/datafile/users2.dbf ONLINE READ WRITE
+DATA/fengzi/datafile/undotbs.dbf ONLINE READ WRITE
SQL> 開啟資料庫 SQL> alter database open;
Database altered.
SQL>
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE _SYSSMU10$ OFFLINE _SYSSMU9$ OFFLINE _SYSSMU8$ OFFLINE _SYSSMU7$ OFFLINE _SYSSMU6$ OFFLINE _SYSSMU5$ OFFLINE _SYSSMU4$ OFFLINE _SYSSMU3$ OFFLINE _SYSSMU2$ OFFLINE _SYSSMU1$ OFFLINE
SEGMENT_NAME STATUS ------------------------------ ---------------- _SYSSMU20$ OFFLINE _SYSSMU19$ OFFLINE _SYSSMU18$ OFFLINE _SYSSMU17$ OFFLINE _SYSSMU16$ OFFLINE _SYSSMU15$ OFFLINE _SYSSMU14$ OFFLINE _SYSSMU13$ OFFLINE _SYSSMU12$ OFFLINE _SYSSMU11$ OFFLINE 21 rows selected. SQL> （如果有recover狀態的回滾段，需要使用_corrupted_rollback_segments隱藏引數標記否則刪除不了 如：alter system set “_corrupted_rollback_segments”=_SYSSMU10$ scope=spfile; ）
重建undo表空間 SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '+data/fengzi/datafile/undotbs1.dbf' size 250m autoextend on;
Tablespace created.
SQL> 修改undo表空間預設表空間以及管理方式 SQL> alter system set undo_tablespace=undotbs1 scope=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL> 取消隱藏引數 SQL> alter system reset "_offline_rollback_segments" scope=spfile sid='*';
System altered.
SQL> 重啟資料庫 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 1124073472 bytes Fixed Size 2020224 bytes Variable Size 704646272 bytes Database Buffers 402653184 bytes Redo Buffers 14753792 bytes Database mounted. Database opened. SQL> 總結：對於undo表空間最好要存在於2個undo表空間，出現狀態也好進行切換處理。
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1174667/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE線上切換undo表空間Oracle
- Oracle切換undo表空間操作步驟Oracle
- undo表空間容量
- oracle中undo表空間丟失處理方法Oracle
- Innodb：Undo 表空間巨大
- 更改undo表空間大小
- UNDO表空間空間回收及切換
- 【UNDO】Oracle undo表空間使用率過高，因為一個查詢Oracle
- MySQL InnoDB Undo表空間配置MySql
- Oracle 19c 線上縮減 UNDO 表空間 容量Oracle
- 2.5.5 使用自動Undo管理: 建立 Undo 表空間
- Oracle表空間Oracle
- oracle 表空間Oracle
- 18_深入解析Oracle undo原理(2)_undo表空間使用率100%問題處理Oracle
- 增加oracle表空間Oracle
- oracle temp 表空間Oracle
- MySQL UNDO表空間獨立和截斷MySql
- [20210527]rman與undo表空間備份.txt
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- 檢查及設定合理的undo表空間
- undo表空間使用率過高解決
- oracle表空間的整理Oracle
- Oracle 批量建表空間Oracle
- Oracle清理SYSAUX表空間OracleUX
- undo表空間使用率100%的原因檢視
- Oracle Temp 表空間切換Oracle
- Oracle 表空間增加檔案Oracle
- Oracle OCP(49)：表空間管理Oracle
- Oracle表空間收縮方案Oracle
- Oracle RMAN 表空間恢復Oracle
- Oracle新建使用者、表空間、表Oracle
- MySQL 5.7新特性之線上收縮undo表空間MySql
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- Oracle的表空間quota詳解Oracle
- oracle臨時表空間相關Oracle
- oracle sql 表空間利用率OracleSQL
- Oracle OCP(47)：表空間的建立Oracle
- 【Oracle 恢復表空間】 實驗Oracle