管理还原数据
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)查看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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126928/