undo数据文件丢失的恢复

undo数据文件丢失的恢复试验

从自己的错误中学到知识是聪明，从别人的错误中学到知识是智慧

坛子里看到一个帖子，讨论的是undo表空间对应的数据文件丢失之后的恢复问题，为了加深印象，自己模拟一下：

1.关闭数据库

2.将undotbs1对应的数据文件重命名，或者删除，随便你，（测试库，可不要在生产库上做这个实验，当然，你最好有之前的一个备份再做删除操作）

3.重新启动数据库，会报找不到数据文件错：

sys@baal> startup
ORACLE 例程已经启动。

Total System Global Area  159383552 bytes
Fixed Size                  1295608 bytes
Variable Size              92277512 bytes
Database Buffers           62914560 bytes
Redo Buffers                2895872 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 2 – 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 2: ‘E:\ORA10G\ORADATA\BAAL\BAAL\UNDOTBS01.DBF’

4.但此时数据库已经mount，所以可以从v$datafile中查看undo表空间的详细目录信息等。

sys@baal> select name from v$datafile;

NAME
———————————————-
E:\ORA10G\ORADATA\BAAL\BAAL\SYSTEM01.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\UNDOTBS01.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\SYSAUX01.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\USERS01.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\BAAL.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\BIGTBS.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\BAAL1.DBF
E:\ORA10G\ORADATA\BAAL\BAAL\TEST.DBF

5.将undo数据文件offline drop,随后打开数据库此操作可能导致数据库的数据不一致，因为undo表空间中可能还有要恢复的数据。

sys@baal> alter database datafile ‘E:\ORA10G\ORADATA\BAAL\BAAL\UNDOTBS01.DBF’ offline drop;

数据库已更改。

已用时间:  00: 00: 00.31
sys@baal> alter database open;

数据库已更改。

6.为系统创建新的undo表空间，并且切换

sys@baal> create undo tablespace undotbs2 datafile ‘E:\ORA10G\ORADATA\BAAL\BAAL\UNDOTBS02.DBF’ size 500m autoextend off;

表空间已创建。

已用时间:  00: 00: 12.67
sys@baal> alter system set undo_tablespace=undotbs2;

系统已更改。

7.如果你认为此时工作已经完成，那就乐观太早了，因为尝试删除undotbs1会报错，而且此时尝试查询需要恢复操作的表时也会报错。

sys@baal> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
第 1 行出现错误:
ORA-01548: 已找到活动回退段 ‘_SYSSMU1$’, 终止删除表空间

8.可以查看一下回退段此时的状态，可以看到undotbs1的状态是need recovery

sys@baal> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS           FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
—————————— —— —————————— —————-
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU1$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU2$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU3$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU4$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU5$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU6$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU7$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU8$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU9$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU10$                     PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU11$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU12$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU13$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU14$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU15$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU16$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU17$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU18$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU19$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU20$                     PUBLIC UNDOTBS2                       ONLINE

9.此时在没有备份的情况下，可以尝试使用隐含参数将数据库打开，不过这个过程会破坏数据库的一致性，而且可能造成数据库出现逻辑错误。采用下面的方法打开数据库后，应该马上执行EXP备份，建重数据后库，再导入。

10.重新创建pfile

sys@baal> create pfile=’E:\ora10g\database\initbaal1.ora’ from spfile;

文件已创建。

11.关闭实例并且手工编辑pfile，修改或添加以下隐含参数，用修改后的pfile打开数据库。

*.undo_management=’MANUAL’
_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

sys@baal> startup pfile=E:\ora10g\database\initbaal1.ora
ORACLE 例程已经启动。

Total System Global Area  159383552 bytes
Fixed Size                  1295608 bytes
Variable Size              92277512 bytes
Database Buffers           62914560 bytes
Redo Buffers                2895872 bytes
数据库装载完毕。
数据库已经打开。

12.可以先查看一下数据库中回滚段的状态，发现undotbs2的回退段也变成offline了。由于设置了隐含参数，现在可以删除这些回滚段了。

sys@baal> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS           FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
—————————— —— —————————— —————-
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU1$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU2$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU3$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU4$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU5$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU6$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU7$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU8$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU9$                      PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU10$                     PUBLIC UNDOTBS1                       NEEDS RECOVERY
_SYSSMU11$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU12$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU13$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU14$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU15$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU16$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU17$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU18$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU19$                     PUBLIC UNDOTBS2                       OFFLINE
_SYSSMU20$                     PUBLIC UNDOTBS2                       OFFLINE

sys@baal> drop rollback segment “_SYSSMU1$”;

回退段已删除。

。。。。。

但是undotbs2中的回退段不能删除

sys@baal> drop rollback segment “_SYSSMU11$”;
drop rollback segment “_SYSSMU11$”
*
第 1 行出现错误:
ORA-30025: 不允许删除段 ‘_SYSSMU11$’ (在还原表空间中)

13.删除undotbs1

sys@baal> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

表空间已删除。

14.重启一下，去掉隐含参数

sys@baal> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@baal> startup
ORACLE 例程已经启动。

Total System Global Area  159383552 bytes
Fixed Size                  1295608 bytes
Variable Size              92277512 bytes
Database Buffers           62914560 bytes
Redo Buffers                2895872 bytes
数据库装载完毕。
数据库已经打开。
sys@baal> show parameter undo

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
sys@baal> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS           FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
—————————— —— —————————— —————-
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU11$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU12$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU13$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU14$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU15$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU16$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU17$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU18$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU19$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU20$                     PUBLIC UNDOTBS2                       ONLINE

已选择11行。

已用时间:  00: 00: 00.31

很郁闷的是我模拟了半天，却一直没有模拟出回滚段需要recovery needed状态。如果 哪位大侠知道请给我留言，谢谢