一、定义作用
undo表空间是一种重要的表空间,oracle为了精细管理,主要作用是撤销一条语句和一些列语句的操作。具体作用有:事务回退,事务恢复,读一致性,闪回数据。
二、查看
管理方式
SQL> show parameter undo_m
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
表空间
SQL> show parameter undo_t
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
回滚段类型
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1_592353410$
2 _SYSSMU2_967517682$
3 _SYSSMU3_1204390606$
4 _SYSSMU4_1003442803$
5 _SYSSMU5_538557934$
6 _SYSSMU6_2897970769$
7 _SYSSMU7_3517345427$
8 _SYSSMU8_3901294357$
9 _SYSSMU9_1735643689$
10 _SYSSMU10_4131489474$
11 rows selected
还有一种段是表空间脱机时候的deferred roolback段,在dba_segments 中可以查看到
查看保留时间,默认是15分钟
SQL> show parameter undo_r
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
三、建立删除
create smallfile| bigfile undo tablespace undo1 datafile 'XXX' size <> autoextend on ;
drio tabkespace undo1 including contents and datafiles;
alter system set undo_tablespace=undo1;
alter tablespace undo1 rename undo2; ----参数文件将跟着修改
大小计算
UndoSpace = UR * UPS * DBS + DBS * 24
其中ur是undo_retention,ups业务高峰期的每秒块数量,dbs是blocks大小。
SQL> select sum(undoblks)/sum((end_time-begin_time)*10800) from v$undostat;
SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*10800)
----------------------------------------------
.271927162
然后就可以算出来了0.272*8192*900/1024/1024
所以可以计算
SQL> SELECT (UR * UPS * DBS + DBS * 24) / 1024 / 1024 AS "undo size(M)"
2 FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
3 (SELECT max(undoblks / ((end_time - begin_time) * 24 * 3600)) AS UPS FROM v$undostat),
4 (SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size');
undo size(M)
------------
1.078125
这个值是推荐大小,业务高峰可能随着时间不一样
另外估算大小的是
SQL> SELECT ((SELECT MAX(undoblks)/600 * MAX(maxquerylen) FROM v$undostat)*(SELECT value FROM v$parameter
2 WHERE name = 'db_block_size'))/1024/1024 as Need_Size
3 FROM dual;
NEED_SIZE
----------
1.009375
查询用户使用的回滚段
SQL> SELECT s.username, u.name
2 FROM v$transaction t, v$rollstat r, v$rollname u, v$session s
3 WHERE s.taddr = t.addr
4 AND t.xidusn = r.usn
5 AND r.usn = u.usn
6 ORDER BY s.username;
no rows selected
更新scott一个表
SQL> /
USERNAME NAME
------------------------------ ------------------------------
SCOTT _SYSSMU9_1735643689$
四、备份恢复
备份
归档模式备份
恢复:
1、归档,有备份时候,直接rman恢复
2、没有备份
首先借助隐藏参数启动,
SQL> select ksppinm from x$ksppi where ksppinm like '%roll%' ;
KSPPINM
--------------------------------------------------------------------------------
_ksxp_disable_rolling_migration
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
_max_cr_rollbacks
KSPPINM
--------------------------------------------------------------------------------
_mv_rolling_inv
_optimizer_nested_rollup_for_gset
13 rows selected.
alter system set "_offline_rollback_segments"=true scope=spfile;
alter system set "_corrupted_rollback_segments" =true scope=spfile;
然后startup force mount
然后修改为手动管理undo
alter system set undo_management=manual scope=spfile;---以便能够open database
startup force mount;
然后查看undo表空间状态,如果脱机的话
alter database open
然后,查看dba_rollback_segs,查看是否有recover状态的段
如果没有创建新的undo表空间
然后更改表空间
alter system set undo_management=auto scope=spfile;
alter system set undo_tablespace=<> scoope=spfile;
然后重启db,如果能够打开,则恢复成功,然后重置两个隐藏参数
alter system reset "_offline_rollback_segments" scope=spfiel sid='*';