【UNDO】還原資料
管理還原資料
Undo表空間畢竟是需要空間來儲存資料的，既然如此，在儲存undo資料的時候，只要能滿足回滾就可以了。
insert：undo中只記錄了rowid，如果回退，只需要將改記錄透過rowid刪除即可；
update：undo中只記錄舊值，如果回退，透過舊值覆蓋新值；
delete：undo中記錄整行記錄，如果回退，透過反向操作恢復其值。
透過上述描述我們可以得出，delete操作是最佔用undo空間的。
1.1. 檢視undo相關引數
SYS@ORA11GR2>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@ORA11GR2>
undo_management：自動管理回滾段模式（AUM）
undo_retention：900秒（15分鐘）
OLTP系統：15分鐘（建議值）
DSS系統：1-2小時（建議值）
undo_tablespace：當前使用的undo表空間
1.2.切換表空間
建立一個新的undo表空間，表空間名稱為UNDOTBS2，100M，切換資料庫的undo表空間為UNDOTBS2
--檢視當前undo表空間的名稱
SYS@ORA11GR2>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@ORA11GR2>
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ -----------
undo_tablespace string UNDOTBS1
--建立一個新的undo表空間UNDOTBS2
SYS@ORA11GR2>create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORA11GR2/undotbs2_01.dbf' size 100m autoextend on next 10m;
Tablespace created.
--切換當前undo表空間為UNDOTBS2
SYS@ORA11GR2>alter system set undo_tablespace=undotbs2;
System altered.
--驗證
SYS@ORA11GR2>show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ -----------
undo_tablespace string UNDOTBS2
--刪除已經無用的undo表空間UNDOTBS1
SYS@ORA11GR2>drop tablespace undotbs1 including contents and datafiles;
(連表空間的資料及其對應的物理檔案一併刪除)
Tablespace dropped.
驗證：
[oracle@wang ORA11GR2]$ ll
total 2115448
-rw-r----- 1 oracle oinstall 9748480 Sep 21 16:18 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Sep 21 16:18 control02.ctl
-rw-r----- 1 oracle oinstall 363077632 Sep 21 16:03 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Sep 20 17:00 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep 20 22:00 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 21 16:18 redo03.log
-rw-r----- 1 oracle oinstall 608182272 Sep 21 16:18 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Sep 21 16:17 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Sep 21 16:02 temp01.dbf
-rw-r----- 1 oracle oinstall 10493952 Sep 20 18:51 tmp_grp01.dbf
-rw-r----- 1 oracle oinstall 104865792 Sep 21 16:03 ts_users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Sep 21 16:18 undotbs2_01.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 21 16:03 users01.dbf
注：建立undo表空間的時候，初始可以設定為自動擴充套件，當系統穩定的執行一段時間後，需要手工調整一下undo表空間，將自動擴充套件取消，為的是避免某使用者忽略了提交事務而無意識的佔用大量空間。
1.3.設定undo資料保留期限及強制保留
透過引數undo_retention來設定undo資料的保留期限，這個引數的含義就是undo資料在undo表空間中的保留時限，預設為900秒。
更改undo表空間，儲存2個小時
--檢視引數undo_retention的值，預設為15分鐘（900秒）
SYS@ORA11GR2>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SYS@ORA11GR2>
--將引數undo_retention修改為7200秒，此引數為動態引數
SYS@ORA11GR2>alter system set undo_retention=7200;
System altered.
--驗證，修改成功
SYS@ORA11GR2>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 7200
undo_tablespace string UNDOTBS2
SYS@ORA11GR2>
1）檢視目前undo表空間保留模式（預設為非強制保留）
SYS@ORA11GR2>desc dba_tablespaces
Name Null? Type
----------------------------------------- --------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SYS@ORA11GR2>select TABLESPACE_NAME,RETENTION from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS2 NOGUARANTEE
3）開啟/關閉undo表空間的確保保留期（guarantee以後，就要求確保7200的資料必須在undo中）
——開啟確保保留期：
SYS@ORA11GR2>alter tablespace undotbs2 retention guarantee;
Tablespace altered.
SYS@ORA11GR2>select tablespace_name,retention from dba_tablespaces where contents = 'UNDO';
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS2 GUARANTEE
——關閉確保保留期
SYS@ORA11GR2>alter tablespace undotbs2 retention noguarantee;
Tablespace altered.
SYS@ORA11GR2>select tablespace_name,retention from dba_tablespaces where contents = 'UNDO';
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS2 NOGUARANTEE
注：確保保留期的開啟與關閉，只適合undo表空間
嘗試對非還原表空間設定保留期會產生以下錯誤：
SYS@ORA11GR2>alter tablespace example retention guarantee;
alter tablespace example retention guarantee
*
ERROR at line 1:
ORA-30044: 'Retention' can only specified for undo tablespace
1.4.常用的查詢語句
1）檢視undo資料狀態統計
SYS@ORA11GR2>desc dba_undo_extents
Name Null? Type
----------------------------------------- --------
OWNER CHAR(3)
SEGMENT_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NOT NULL NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
COMMIT_JTIME NUMBER
COMMIT_WTIME VARCHAR2(20)
STATUS VARCHAR2(9)
SYS@ORA11GR2>select status,count(*) from dba_undo_extents group by status;
STATUS COUNT(*)
--------- ----------
UNEXPIRED 21
EXPIRED 4
1） 檢視undo表空間的名稱
SYS@ORA11GR2>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 7200
undo_tablespace string UNDOTBS2
或者
SYS@ORA11GR2>select tablespace_name,contents from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME CONTENTS
------------------------------ ---------
UNDOTBS2 UNDO
SYS@ORA11GR2>
2） 檢視undo表空間的大小
SYS@ORA11GR2>desc dba_data_files
Name Null? Type
----------------------------------------- --------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SYS@ORA11GR2>select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files where tablespace_name = 'UNDOTBS2' group by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
UNDOTBS2 100
4）檢視UNDO表空間的使用情況
SYS@ORA11GR2>desc dba_segments
Name Null? Type
----------------------------------------- --------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
SEGMENT_SUBTYPE VARCHAR2(10)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
RETENTION VARCHAR2(7)
MINRETENTION NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
SYS@ORA11GR2>select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS2';
OWNER SEGMENT_NAME MB
------------------------------ ------------------------------ ----------
SYS _SYSSMU20_2245862642$ .375
SYS _SYSSMU19_2541524102$ .375
SYS _SYSSMU18_1854327302$ .25
SYS _SYSSMU17_1933769234$ .25
SYS _SYSSMU16_3070775964$ .25
SYS _SYSSMU15_3761990754$ .375
SYS _SYSSMU14_2468498718$ .25
SYS _SYSSMU13_3675639839$ .625
SYS _SYSSMU12_323142514$ .3125
SYS _SYSSMU11_1866964104$ 2
10 rows selected.
5）哪些會話的事務佔用了undo表空間
SYS@ORA11GR2>select s.sid,
2 s.serial#,
3 s.sql_id,
4 v.usn,
5 segment_name,
6 r.status,
7 v.rssize / 1024 / 1024 mb
8 FROM dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
9 WHERE r.segment_id = v.usn
10 AND v.usn = t.xidusn
11 AND t.addr = s.taddr
12 ORDER BY segment_name;
no rows selected
6）Undo表空間段的狀態
在更換undo表空間時，檢視段的狀態，當被替換的undo表空間的段的狀態都為OFFLINE時，就可以將其刪除了，否則，在刪除的過程中會提示錯誤。
SYS@ORA11GR2>set lines 200
SYS@ORA11GR2>select segment_name,tablespace_name,r.status,(next_extent/1024) nextextent,max_extents,v.curext curextent from dba_rollback_segs r,v$rollstat v where r.segment_id = v.usn(+) order by segment_name;（右外連線）
SEGMENT_NAME TABLESPACE_NAME STATUS NEXTEXTENT MAX_EXTENTS CUREXTENT
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE 56 32765 5
_SYSSMU11_1866964104$ UNDOTBS2 ONLINE 64 32765 17
_SYSSMU12_323142514$ UNDOTBS2 ONLINE 64 32765 5
_SYSSMU13_3675639839$ UNDOTBS2 ONLINE 64 32765 15
_SYSSMU14_2468498718$ UNDOTBS2 ONLINE 64 32765 3
_SYSSMU15_3761990754$ UNDOTBS2 ONLINE 64 32765 5
_SYSSMU16_3070775964$ UNDOTBS2 ONLINE 64 32765 11
_SYSSMU17_1933769234$ UNDOTBS2 ONLINE 64 32765 5
_SYSSMU18_1854327302$ UNDOTBS2 ONLINE 64 32765 3
_SYSSMU19_2541524102$ UNDOTBS2 ONLINE 64 32765 5
_SYSSMU20_2245862642$ UNDOTBS2 ONLINE 64 32765 6
11 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126928/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 還原資料庫資料庫
- 資料庫還原資料庫
- ebs 還原資料
- 還原SQL Server資料庫SQLServer資料庫
- oracle資料恢復還原Oracle資料恢復
- Mysql資料備份和還原MySql
- MSSQL 備份資料庫還原SQL資料庫
- RMAN資料庫還原測試資料庫
- 還原Android彩信資料庫Android資料庫
- oracle資料還原與備份Oracle
- 通過資料庫檔案還原資料庫資料庫
- SQL Server還原資料庫，修改還原後的物理檔名稱SQLServer資料庫
- 【Mongodb】資料庫備份與還原MongoDB資料庫
- sqlserver資料庫的備份還原SQLServer資料庫
- 「MySQL」資料庫備份和還原MySql資料庫
- 「Oracle」Oracle 資料庫備份還原Oracle資料庫
- 批量備份和還原資料庫資料庫
- 恢復之還原資料檔案
- MySQL的資料備份與還原MySql
- 還原點和閃回資料庫資料庫
- MySQL資料庫備份與還原MySql資料庫
- sql server 資料庫還原問題SQLServer資料庫
- Oracle匯出資料庫與還原Oracle資料庫
- sqlserver資料庫備份,還原操作SQLServer資料庫
- Sqlserver資料庫使用 .bak 檔案還原資料庫SQLServer資料庫
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- 資料庫單表備份還原shell資料庫
- 使用RMAN還原和恢復資料庫資料庫
- Mysql 5.7透過mysqldump還原資料庫MySql資料庫
- 【RMAN】利用備份片還原資料庫資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- 織夢資料庫_織夢還原資料庫_織夢資料庫很卡資料庫
- SQLSERVER完整資料庫還原（完整恢復模式）SQLServer資料庫模式
- DM7使用dmrestore工具還原資料庫REST資料庫
- mysql資料庫-備份與還原實操MySql資料庫
- java mysql 資料庫備份和還原操作JavaMySql資料庫
- Oracle 11g用impdp還原資料庫Oracle資料庫
- Oracle資料庫備份還原詳解XKUSOracle資料庫