Oracle Undo tablespace恢复（无备份）

Oracle Undo tablespace恢复系统环境：操作系统：RedHat EL55Oracle： Oracle 11gR2Oracle 9i后，采用了undo tablespace管理undo数据，实现undo的自动管理，本案例演示了undo表空间被破坏后如何恢复；如果有备份，通过备份恢复非常容易，但在没有备份的情况下，就需要采用非常规手段来恢复了，呵呵。RMAN备份与恢复之undo表空间丢失 http://www.linuxidc.com/Linux/2014-01/95335.htm关于Oracle 释放过度使用的undo表空间 http://www.linuxidc.com/Linux/2013-09/90315.htmOracle undo的一些理解 http://www.linuxidc.com/Linux/2013-09/89701.htmOracle undo 镜像数据探究 http://www.linuxidc.com/Linux/2013-08/89074.htmOracle 回滚（ROLLBACK）和撤销（undo） http://www.linuxidc.com/Linux/2013-08/88792.htm1、案例应用环境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（*）---------- 1Elapsed: 00:00:00.0614:43:54 SYS@ prod>insert into scott.emp1 select * from scott.emp;14 rows created.Elapsed: 00:00:00.0814:44:04 SYS@ prod>commit;Commit complete.Elapsed: 00:00:00.0314:44:06 SYS@ prod>select count（*） from scott.emp1; COUNT（*）---------- 15Elapsed: 00:00:00.0114:44:20 SYS@ prod>insert into scott.emp1 select * from scott.emp where rownum <4;3 rows created.Elapsed: 00:00:00.0314:44:40 SYS@ prod>select count（*） from scott.emp1; COUNT（*）---------- 18Elapsed: 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 2014Copyright （c） 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options14:46:12 SYS@ prod>shutdown abort;ORACLE instance shut down.Undo tablesapce datafile被破坏：
[oracle@rh6 dbs]$ rm /u01/app/oracle/oradata/prod/undotbs01.dbf2、Open database时数据库报错：[oracle@rh6 prod]$ sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 14:47:26 2014Copyright （c） 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.14:47:26 SYS@ prod>startupORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 775948320 bytesDatabase Buffers 54525952 bytesRedo Buffers 2412544 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: "/u01/app/oracle/oradata/prod/undotbs01.dbf"告警日志：ALTER DATABASE OPENErrors 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 fileORA-01110: data file 3: "/u01/app/oracle/oradata/prod/undotbs01.dbf"ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors 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 fileORA-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 undoNAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS114:48:18 SYS@ prod>更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-06/103779p2.htm

MySQL5.6 Replication主从复制（读写分离）配置完整版Oracle中的ORA-01548: active rollback segment "_SYSSMU1$" found相关资讯 undo Oracle Undo

Oracle 11g undo_retention 以及（05月28日）
undo表空间使用率（07/23/2015 16:29:56）
undo表空间概述（02/24/2015 20:32:43）

Oracle中利用undo进行数据的恢复操（11/27/2015 09:31:30）
undo表空间修复小结（07/08/2015 08:43:13）
Oracle 11gR2 Database UNDO表空间（01/29/2015 11:30:59）

本文评论查看全部评论（0）