ITPub博客

首页 > 数据库 > Oracle > 没有备份undo segment出现问题的处理

没有备份undo segment出现问题的处理

原创 Oracle 作者:warehouse 时间:2014-02-11 09:22:49 4 删除 编辑

--为了增加可读性,先显示一些和undo有关的信息:
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ------------------------------
E:\ORADATA\TEST\SYSTEM01.DBF             SYSTEM
E:\ORADATA\TEST\UNDOTBS01.DBF            UNDOTBS1
E:\ORADATA\TEST\SYSAUX01.DBF             SYSAUX
E:\ORADATA\TEST\USERS01.DBF              USERS

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS1                       ONLINE
_SYSSMU17$                     UNDOTBS1                       ONLINE
_SYSSMU16$                     UNDOTBS1                       ONLINE
_SYSSMU15$                     UNDOTBS1                       ONLINE
_SYSSMU14$                     UNDOTBS1                       ONLINE
_SYSSMU13$                     UNDOTBS1                       ONLINE
_SYSSMU12$                     UNDOTBS1                       ONLINE
_SYSSMU11$                     UNDOTBS1                       ONLINE
_SYSSMU3$                      UNDOTBS1                       ONLINE
_SYSSMU2$                      UNDOTBS1                       ONLINE

已选择11行。

--创建一张测试表t
SQL> create table t(id int,name varchar2(10)) tablespace users;

表已创建。

SQL> insert into t values(1,'a');

已创建 1 行。

SQL> commit;

提交完成。

SQL> update t set name='b';

已更新 1 行。
--这里没有提交,使用360在线强制删除undo对应的数据文件,删除之后尝试访问t还是可以访问的,尝试commit了一下也没有问题。

SQL> select * from t;

        ID NAME
---------- ----------
         1 b

SQL> commit;

提交完成。

SQL> select * from t;

        ID NAME
---------- ----------
         1 b
--当发出checkpoint的时侯报错了。
SQL> alter system checkpoint;
alter system checkpoint
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束


SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on 星期二 2月 11 08:04:23 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

已连接到空闲例程。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1302416 bytes
Variable Size              75497584 bytes
Database Buffers          125829120 bytes
Redo Buffers                7086080 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 2 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 2: 'E:\ORADATA\TEST\UNDOTBS01.DBF'


SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     15
当前日志序列           17
SQL> alter database datafile 2 offline drop;

数据库已更改。

SQL> alter database open ;

数据库已更改。

SQL> select * from t;
select * from t
              *
第 1 行出现错误:
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'E:\ORADATA\TEST\UNDOTBS01.DBF'


SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU17$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU16$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU15$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU14$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU13$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU12$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU11$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       NEEDS RECOVERY

已选择11行。

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
第 1 行出现错误:
ORA-30013: 还原表空间 'UNDOTBS1' 当前正在使用中


SQL> create undo tablespace undotbs2 datafile 'E:\oradata\test\undotbs02.dbf' size 20m;

表空间已创建。

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_tablespace=undotbs2;

系统已更改。

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU17$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU16$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU15$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU14$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU13$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU12$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU11$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       NEEDS RECOVERY

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU30$                     UNDOTBS2                       ONLINE
_SYSSMU29$                     UNDOTBS2                       ONLINE
_SYSSMU28$                     UNDOTBS2                       ONLINE
_SYSSMU27$                     UNDOTBS2                       ONLINE
_SYSSMU26$                     UNDOTBS2                       ONLINE
_SYSSMU25$                     UNDOTBS2                       ONLINE
_SYSSMU24$                     UNDOTBS2                       ONLINE
_SYSSMU23$                     UNDOTBS2                       ONLINE
_SYSSMU22$                     UNDOTBS2                       ONLINE
_SYSSMU21$                     UNDOTBS2                       ONLINE

已选择21行。

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 '_SYSSMU1$', 终止删除表空间


SQL> drop rollback segment "_SYSSMU1$";
drop rollback segment "_SYSSMU1$"
*
第 1 行出现错误:
ORA-30025: 不允许删除段 '_SYSSMU1$' (在还原表空间中)


SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

--此时需要用到oracle的一个和undo有关的隐含参数_corrupted_rollback_segments
把状态为NEEDS RECOVERY的undo segment都放在参数_corrupted_rollback_segments的列表里面,
具体_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU17$,_SYSSMU16$,_SYSSMU15$,_SYSSMU14$,_SYSSMU13$,_SYSSMU12$,_SYSSMU11$,_SYSSMU3$,_SYSSMU2$)
由于我在启动实例的时侯使用的是init文件,因此直接编辑init文件把
_corrupted_rollback_segments=(_SYSSMU1$...)放进去之后重启实例就可以了,
如下图:

init文件我编辑了2个地方,一个是增加了最后一行_corrupted_rollback_segments=...,另外倒数第三行
*.undo_tablespace='UNDOTBS2'由原来的UNDOTBS1改成了UNDOTBS2

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1302416 bytes
Variable Size              75497584 bytes
Database Buffers          125829120 bytes
Redo Buffers                7086080 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU17$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU16$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU15$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU14$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU13$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU12$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU11$                     UNDOTBS1                       NEEDS RECOVERY
_SYSSMU3$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU2$                      UNDOTBS1                       NEEDS RECOVERY

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU30$                     UNDOTBS2                       ONLINE
_SYSSMU29$                     UNDOTBS2                       ONLINE
_SYSSMU28$                     UNDOTBS2                       ONLINE
_SYSSMU27$                     UNDOTBS2                       ONLINE
_SYSSMU26$                     UNDOTBS2                       ONLINE
_SYSSMU25$                     UNDOTBS2                       ONLINE
_SYSSMU24$                     UNDOTBS2                       ONLINE
_SYSSMU23$                     UNDOTBS2                       ONLINE
_SYSSMU22$                     UNDOTBS2                       ONLINE
_SYSSMU21$                     UNDOTBS2                       OFFLINE

已选择21行。
--有了_corrupted_rollback_segments的作用,我们把undotbs1里面的undo segment以及undotbs1表空间都可以轻松删除了
SQL> drop rollback segment "_SYSSMU1$";

回退段已删除。

SQL> drop tablespace undotbs1;

表空间已删除。

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU30$                     UNDOTBS2                       ONLINE
_SYSSMU29$                     UNDOTBS2                       ONLINE
_SYSSMU28$                     UNDOTBS2                       ONLINE
_SYSSMU27$                     UNDOTBS2                       ONLINE
_SYSSMU26$                     UNDOTBS2                       ONLINE
_SYSSMU25$                     UNDOTBS2                       ONLINE
_SYSSMU24$                     UNDOTBS2                       ONLINE
_SYSSMU23$                     UNDOTBS2                       ONLINE
_SYSSMU22$                     UNDOTBS2                       ONLINE
_SYSSMU21$                     UNDOTBS2                       OFFLINE

已选择11行。

SQL> select * from t;

        ID NAME
---------- ----------
         1 b
--测试t表也可以正常访问了,这个我想是因为我在drop掉undotbs1表空间对应的数据文件
之后发出了一个commit的缘故,否则我不知道是否还能访问这张表,大家可以测试。在生产环境中
遇到的情况可能比这个还要复杂,到时候我们根据情况来处理就是了。在编辑init参数_corrupted_rollback_segments=...
的时侯为了测试需要我顺便把_SYSSMU21$也放在里面了,_SYSSMU21$属于undotbs2,是一个没有问题的undo segment,
,把没有问题的undo segment放在参数_corrupted_rollback_segments之后,实例启动之后其状态由原来的online变成
offline了,同时也可以对其删除。看下面的结果:这种办法可以回缩过段扩张导致undo数据文件很大的数据文件,当然
也可以通过重新创建新的undo来删除原来的undo来解决。


SQL> drop rollback segment "_SYSSMU21$";

回退段已删除。

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU30$                     UNDOTBS2                       ONLINE
_SYSSMU29$                     UNDOTBS2                       ONLINE
_SYSSMU28$                     UNDOTBS2                       ONLINE
_SYSSMU27$                     UNDOTBS2                       ONLINE
_SYSSMU26$                     UNDOTBS2                       ONLINE
_SYSSMU25$                     UNDOTBS2                       ONLINE
_SYSSMU24$                     UNDOTBS2                       ONLINE
_SYSSMU23$                     UNDOTBS2                       ONLINE
_SYSSMU22$                     UNDOTBS2                       ONLINE

已选择10行。

SQL>
--======================
上面测试中其实只要有undotbs1里面状态为NEEDS RECOVERY的undo segment没有被包含
在隐含参数_corrupted_rollback_segments当中,对应的这个undo segment以及undotbs1
这个表空间都是不能被删除的。即使状态为NEEDS RECOVERY的undo segment都被包含在
_corrupted_rollback_segments当中了,也有可能这些undo segment或者undotbs1还是不能
被删除,此时我们可以修改参数undo_management为manual,然后继续删除这些有问题的
undo segment或者这个有问题的undo表空间。另外在生产环境中可能会遇到的问题是某些undo段
出现问题,不是我们模拟的整个undo表空间对应的数据文件出现问题,此时_corrupted_rollback_segments
的作用就更加明显了。


 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1079279/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5145362