Oracle Undo tablespace恢复(无备份)

Oracle Undo tablespace恢复

系统环境:

操作系统:RedHat EL55

Oracle:  Oracle 11gR2

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

RMAN备份与恢复之undo表空间丢失

关于Oracle 释放过度使用的undo表空间

Oracle undo的一些理解

Oracle undo 镜像数据探究

Oracle 回滚(ROLLBACK)和撤销(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>

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/2d01211dd02be2589b48ca9708e2f5c8.html