Oralce Undo表空间

Undo表空间管理

 

 

 

 

 

 

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>

 

undo表空间的使用情况

 

SELECT a.tablespace_name,
         ROUND (a.total_size) "total_size(MB)",
         ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
         ROUND (b.free_size, 3) "free_size(MB)",
         ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
  FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
              FROM dba_data_files
          GROUP BY tablespace_name) a,
         ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
              FROM dba_free_space
          GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+);

 

查看已经使用的used_size中过期和非过期比例

SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"
    FROM dba_undo_extents
GROUP BY tablespace_name, status;

unexpired 非过期

expired为过期

 

UNDO表空间满处理方法

1>模拟满的状态

SQL>alter system set undo_retention=10800

SQL>create undo tablespace undo datafile '/u01/undo.dbf' size 1m

SQL>alter tablespace undo retention guarantee;

SQL>alter system set undo_tablespace=undo

SQL>create table test_full(id number);

SQL> begin

  2  for i in 1 .. 100000 loop

  3  insert into dba values(i);

  4  commit;

  5  end loop;

  6  end;

  7  /

第 1 行出现错误:

ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDO' 中)

ORA-06512: 在 line 3

2>处理方式

两种：

第一种方式：增加undo表空间数据文件

SQL>alter tablespace unod add datafile '/u01/undo0n.dbf' size 100m reuse;

第二种方式：切换undo表空间

SQL>create tablespace datafile '/u01/newundo' size 500M;

SQL>alter system set undo_tablespace=newundo scope=both;  //默认就是以both参数

//将原来的tablespace undo offline

SQL>alter tablespace undo offline;

SQL>drop tablespace undo uncluding contents add datafile cascade contraints;

   drop tablespace undo （在未使用的情况下才能进行）则只会删除控制文件里的记录，并不会删除物理文件

 

 

UNDO表空间损坏的恢复方法

导致数据库无法打开

ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/d01/oramtest/proddata/undo01.dbf'

必须重建表空间，如果不能open，可以先使用该系统默认的undo表空间:system  rollback sement 来启动undo表空间

1>创建pfile

SQL>create pfile='/init.ora' from spfile;

2>修改pfile文件

#*.undo_tablespace='UNDOTBS1'

#*.undo_management='AUTO'

undo_management='MANUAL'

rollback_segments='SYSTEM'

3>启动数据库至mount

SQL>startup mount pfile='/u01/int.ora';

4>offline  drop undo

SQL>alter database datafile '/u01/..../undotbs01.dbf offline drop;

5>open databse

SQL>alter database open

6>删除旧的undo表空间

SQL>drop tablespace undotbs1 including contents

      //该命令不会删除物理文件，可以add datafiles cascade constraints

如 drop tablespace UNDOTBS1 including contents AND DATAFILES CASCADE CONSTRAINTS 7>创建新的undo表空间

SQL>create undo tablespace undotbs1 datafile '/u01/newundo.dbf' seize 100m reuse;

8>shutdown database ，并执行pfile，将参数改回，因为被改的参数是静态参数

9>

*.undo_tablespace='UNDOTBS1'

*.undo_management='AUTO'

#undo_management='MANUAL'

#rollback_segments='SYSTEM'

10> 用新的pfile重启数据库，并创建spfile

SQL>startup pfile='/u01/init.ora'

SQL>create spfile from pfile='/u01/init.ora';

11>shutdown,spfile
SQL>shutdown immediate;

SQL>startup

 

补充：

对损坏的数据文件，也可以将同理，对数据库启动到mount状态，drop数据文件，在startup