Oracle Undo tablespace恢复（无备份）

OracleUndotablespace恢复系统环境：操作系统：RedHatEL55Oracle：Oracle11gR2Oracle9i后，采用了undotablespace管理undo数据，实现undo的自动管理，本案例演示

Oracle Undo tablespace恢复

系统环境：

操作系统：RedHat EL55

Oracle： Oracle 11gR2

Oracle 9i后，采用了undo tablespace管理undo数据，实现undo的自动管理，本案例演示了undo表空间被破坏后如何恢复；如果有备份，通过备份恢复非常容易，但在没有备份的情况下，就需要采用非常规手段来恢复了，呵呵。

1、案例应用环境

undo表空间undo segments：

14:34:44 SYS@ prod>select segment_name,segment_type,tablespace_name from dba_segments 14:35:03 2 where tablespace_name='UNDOTBS1'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME -------------------- ------------------ ------------------------------ _SYSSMU10_3550978943$ TYPE2 UNDO UNDOTBS1 _SYSSMU9_1424341975$ TYPE2 UNDO UNDOTBS1 _SYSSMU8_2012382730$ TYPE2 UNDO UNDOTBS1 _SYSSMU7_3286610060$ TYPE2 UNDO UNDOTBS1 _SYSSMU6_2443381498$ TYPE2 UNDO UNDOTBS1 _SYSSMU5_1527469038$ TYPE2 UNDO UNDOTBS1 _SYSSMU4_1152005954$ TYPE2 UNDO UNDOTBS1 _SYSSMU3_2097677531$ TYPE2 UNDO UNDOTBS1 _SYSSMU2_2232571081$ TYPE2 UNDO UNDOTBS1 _SYSSMU1_3780397527$ TYPE2 UNDO UNDOTBS1 10 rows selected. Elapsed: 00:00:00.19

模拟应用环境：

14:43:16 SYS@ prod>select count(*) from scott.emp1; COUNT(*) ---------- 1 Elapsed: 00:00:00.06 14:43:54 SYS@ prod>insert into scott.emp1 select * from scott.emp; 14 rows created. Elapsed: 00:00:00.08 14:44:04 SYS@ prod>commit; Commit complete. Elapsed: 00:00:00.03 14:44:06 SYS@ prod>select count(*) from scott.emp1; COUNT(*) ---------- 15 Elapsed: 00:00:00.01 14:44:20 SYS@ prod>insert into scott.emp1 select * from scott.emp where rownum select count(*) from scott.emp1; COUNT(*) ---------- 18 Elapsed: 00:00:00.01 关库前，事务未提交！

开启新的会话，，模拟断电，将数据库非正常关闭：

[oracle@rh6 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 14:46:11 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:46:12 SYS@ prod>shutdown abort;

ORACLE instance shut down.

Undo tablesapce datafile被破坏：

[oracle@rh6 dbs]$ rm /u01/app/oracle/oradata/prod/undotbs01.dbf

2、Open database时数据库报错：

[oracle@rh6 prod]$ sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 14:47:26 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. 14:47:26 SYS@ prod>startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 775948320 bytes Database Buffers 54525952 bytes Redo Buffers 2412544 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/prod/undotbs01.dbf' 告警日志： ALTER DATABASE OPEN Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_dbw0_7481.trc: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/prod/undotbs01.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_7503.trc: ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/prod/undotbs01.dbf' ORA-1157 signalled during: ALTER DATABASE OPEN... 14:47:37 SYS@ prod>show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 14:48:18 SYS@ prod>

3、在没有备份的情况下进行修复：

创建pfile：

14:48:18 SYS@ prod>create pfile from spfile;

File created.

Elapsed: 00:00:00.09

查看undo tablespace使用的undo segments：

[oracle@rh6 dbs]$ cd /u01/app/oracle/oradata/prod/

[oracle@rh6 prod]$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

and substr(drs.segment_name,1,7) != '_SYSSMU'

D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );

_SYSSMU10_3550978943

_SYSSMU10_3904554333

_SYSSMU11_286947212

_SYSSMU12_3068564564

_SYSSMU13_2761193625

_SYSSMU1_3780397527

_SYSSMU14_2421411996

_SYSSMU15_1683924174

_SYSSMU16_2313212396

_SYSSMU17_2041439332

_SYSSMU1_783380902

_SYSSMU18_2800789714

_SYSSMU19_53723967

_SYSSMU20_3850939844

_SYSSMU2_2232571081

_SYSSMU2_3138176977

_SYSSMU3_1645411166

_SYSSMU3_2097677531

_SYSSMU4_1152005954

_SYSSMU4_870421980

_SYSSMU5_1527469038

_SYSSMU5_2525172762

_SYSSMU6_2443381498

_SYSSMU6_3753507049

_SYSSMU7_1260614213

_SYSSMU7_3286610060

_SYSSMU8_2012382730

_SYSSMU8_2806087761

_SYSSMU9_1424341975

_SYSSMU9_973944058

注意：通过system01.dbf查出了，正在使用的undo segment，以上按使用时间做了排序，注意只选择那些排在最前面的（相同回滚段）；默认每个undo tablespace 会应用10个undo segments。

编辑pfile，添加Oracle隐含参数，跳过undo segments的检测：

[oracle@rh6 dbs]$ cat initprod.ora

*.undo_tablespace='SYSTEM'

*.undo_management='MANUAL'