undo表空间

一、定义作用

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='*';