OracleUndotablespace恢复（无备份）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了OracleUndotablespace恢复（无备份），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5344字，纯文字阅读大概需要8分钟。

内容图文

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 <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>

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'

内容总结

以上是互联网集市为您收集整理的OracleUndotablespace恢复（无备份）全部内容，希望文章能够帮你解决OracleUndotablespace恢复（无备份）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---