Oracle Undo tablespace恢复（无备份）
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 <4;
3 rows created.
Elapsed: 00:00:00.03
14:44:40 SYS@ prod>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>