undo表空间修复小结

黎俊杰的DBA生涯 2015-07-08

首先要知道回滚段在自动管理undo表空间下是不能被offline和删除的,可以先改成manual之后操作, 我们知道undo表空间是用来存储数据被改之前的前镜像,那么如果出现问题,可以分两种情况来处理;
第一种情况:如果损坏的回滚段没有正在执行的事务,那问题还相对简单,可以直接删除掉该回滚段即可,并且没有数据丢失。

具体过程:假设文件undotbs01.dbf丢失或者损坏。

1.先把数据文件offline,在mount状态下执行:

SQL>alter database datafile '/software/oradata/JLPROJCT/undotbs01.dbf' offline drop ; 

2,打开数据库

SQL>alter  database open;

3.我们知道一个数据文件对应n个undo段,所有现在已经有好多undo 段已经 offline了,我们先不对他做任何操作,先查看不是offline的undo段,你会发现他们是不是offline的这些undo段是需要恢复(need recover)

SQL> select status,count(*) from dba_rollback_segs group by status;

STATUS            COUNT(*)

---------------- ----------

ONLINE                  23

need recovery        5

OFFLINE                143

SQL>select segment_name,status from dba_rollback_segs where status<>'offline';  就会发现所有用户回滚段是需要恢复的,状态是need recovery.,这个语句不会显示由于数据文件损坏而出现offline的回滚段。

SEGMENT_NAME                  STATUS

------------------------------ ----------------

SYSTEM                                        ONLINE                    ###这是系统回滚段。

_SYSSMU154_3691636531$        need recovery

_SYSSMU155_3686385895$        need recovery

_SYSSMU156_3796802683$        need recovery

_SYSSMU157_2723916652$        need recovery

_SYSSMU158_1435464080$        need recovery

4.新建一个回滚表空间,

SQL>create  undo tablespace undo2  datafile  '/software/oradata/JLPROJCT/undotbs02.dbf'  size 100m ;
 

 tablespace created

5,把回滚段设置成人工管理,然后删除损坏的回滚段。

SQL>alter system set undo_tablespace= 'undo2'  scope=spfile;  ##指定成新建的undo表空间。
 

system altered 

SQL>alter system set undo_management='manual'  scope=spfile;

system altered
 

6,创建pfile

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

file created 

7,一致性关闭数据库,

SQL>shu  immediate
 

8,在pfile 文件中添加一个隐藏参数,把这些回滚段都列在这个参数值里,

*._offline_rollback_segment=('_SYSSMU154_3691636531$','_SYSSMU155_3686385895$','_SYSSMU156_3796802683$','_SYSSMU157_2723916652$','_SYSSMU158_1435464080$')

9,创建成spfile  然后启动数据库。

SQL>create spfile  from pfile;
 

spfile created 

SQL>startup

10,这时候回滚段数量并没有发生改变,

SQL>select segment_name,status from dba_rollback_segs where status<>'offline'; 

SEGMENT_NAME                  STATUS

------------------------------ ----------------

SYSTEM                                        ONLINE           

_SYSSMU154_3691636531$        need recovery

_SYSSMU155_3686385895$        need recovery

_SYSSMU156_3796802683$        need recovery

_SYSSMU157_2723916652$        need recovery

_SYSSMU158_1435464080$        need recovery
11,因为是手工管理,可以直接删除掉那些回滚段。
SQL> drop rollback segment  “_SYSSMU154_3691636531$”;

rollback segment droped
 

.

.

.

.

12,然后删掉原来的undo表空间。

SQL>drop tablespace undo1  including contents;

13,然后重启数据库,

shu immediate 

startup 

14,注意这时候你的undo 管理还是手工的,所以要把之前的修改改正会自动管理。并且把添加的隐含参数*._offline_rollback_segment删掉。

SQL>alter system set undo_management='auto'  scope=spfile;

第二种情况:当损坏的undo 表空间的回滚段上还有活动的事务,这种情况就要强行提交这些事务,就会造成一些数据的丢失。

1,启动数据库到mount状态,只能启动到这里,

2,把有问题的回滚段offline

SQL>alter database datafile '/software/oradata/JLPROJCT/undotbs01.dbf' offline drop ;

3,查看回滚段状态,和第一种情况略有不同,她没有offline的回滚段。

SQL>select usn,xacts from v$rollstat;

SQL> select status,count(*) from dba_rollback_segs group by status;

STATUS            COUNT(*)

---------------- ----------

ONLINE                  23

need recovery        5

QL>select segment_name,status from dba_rollback_segs where status<>'offline'; 

SEGMENT_NAME                  STATUS

------------------------------ ----------------         

_SYSSMU154_3691636531$        need recovery

_SYSSMU155_3686385895$        need recovery

_SYSSMU156_3796802683$        need recovery

_SYSSMU157_2723916652$        need recovery

_SYSSMU158_1435464080$        need recovery

  4,试图创建发现报错,真正工作中可以从这里来判断到底是那种情况,第一种情况是可以重新建立的。
 必须先禁止继续使用旧的回滚段和回滚空间: 

SQL>create pfile=/oracle/app/pfile.ora  from spfile
 

file created 

SQL>shutdown  immediate ;

在pfile中添加并修改以下内容:

*.undo_management='manual'  ###手动管理,才可以删除回滚段

*._offline_rollback_segments=('_SYSSMU154_3691636531$','_SYSSMU155_3686385895$','_SYSSMU156_3796802683$','_SYSSMU157_2723916652$','_SYSSMU158_1435464080$')      ###这样才能删除这些回滚段

*.undo_tablespace='undo2'    ###这样就会让以后的事务不在用旧的回滚段和undo表空间,

5.创建成spfile  然后启动数据库。

SQL>create spfile  from pfile;
 

spfile created 

SQL>startup 

6,删除旧的回滚段和回滚表空间:

SQL>drop rollbackup segment  '_SYSSMU154_3691636531$'  ;

'

'

'

'

把need recovery 的回滚段全部删除后,

SQL>drop undo tablespace undo1 including  contents;  ###因为已经丢失,就不必要添加数据文件了(and datafiles 选项了)

7,创建一个新的undo表空间,然后启动数据库,再修改undo_management 为自动。

注意这样后,所有未提交的事务都当做提交处理了。

总结:要是undo出现问题,你可以先把数据文件offline,然后修改undo_management 为manual,之后查出状态need recover 的回滚段,修改隐含参数 *._offline_rollback_segments ,把需要删除的(状态为need recover 的回滚段)添加进来,然后删除他们。重新创建undo表空间,然后undo_tablespace=新创建的表空间,打开数据库,最后把undo_management =auto ,即可。

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

相关推荐