ITPub博客

首页 > 数据库 > Oracle > UNDO表空间数据文件丢失处理(三)异常关闭数据库

UNDO表空间数据文件丢失处理(三)异常关闭数据库

原创 Oracle 作者:csbin 时间:2014-02-27 22:23:01 8 删除 编辑

在此搭建完成UNDO表空间数据文件丢失,然后进行修复。按照本文件可以重现修复操作。

1 相关脚本

1.1 模拟undotbs1表空间文件被删除

脚本s.sql

host rm -rf /opt/oracle/oradata/orcl/*

host cp /opt/oracle/oradata/orcl_bk/* /opt/oracle/oradata/orcl/

host cp /opt/oracle/oradata/orcl/control01.ctl /opt/oracle/fast_recovery_area/orcl/control02.ctl

host cp /opt/oracle/oradata/spfileorcl.ora /opt/oracle/product/11.2.0.3/dbs/

startup

shutdown immediate

startup

delete from scott.emp;

shutdown abort

host mv /opt/oracle/oradata/orcl/undotbs01.dbf /opt/oracle/oradata/orcl/undotbs01.dbf.bak

 

1.2 修复脚本r.sql

startup mount

alter database datafile '/opt/oracle/oradata/orcl/undotbs01.dbf' offline drop;

prompt after instance terminate, please run ./recover_block_tx file_name 665

prompt eg: ./recover_block_tx "/opt/oracle/oradata/orcl/system01.dbf" 665

alter database open;

 

1.3 修复脚本r2.sql

startup


prompt create tablespace undotbs2

create undo tablespace undotbs2 datafile '/opt/oracle/oradata/orcl/undotbs02.dbf' size 100m autoextend on next 100m maxsize 30720m;


prompt set undo_tablespace='UNDOTBS2'

alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;


prompt after this script complete, Please run ./recover_undo_seg file_name 225

prompt eg: ./recover_undo_seg "/opt/oracle/oradata/orcl/system01.dbf" 225

shutdown immediate

 

1.4 修复脚本r3.sql

startup

prompt delete from seg$ where ts#=2;

delete from seg$ where ts#=2;

prompt delete from undo$ where ts#=2;

delete from undo$ where ts#=2;

commit;

prompt drop tablespace UNDOTBS1

drop tablespace UNDOTBS1 including contents and datafiles;

 

1.5 清除数据块事务脚本

 recover_block_tx.c

 

1.6 改回滚段事务脚本

 recover_undo_seg.c

 

2  验证方法

2.1 模拟undo表空间数据文件被删除

      运行脚本s.sql

2.2 修复步骤

      修复方法简述:
      ① 执行脚本r.sql,将undo表空间文件offline

      ② 执行程序修复数据块事务状态
          ./recover_block_tx "/opt/oracle/oradata/orcl/system01.dbf" 665

      ③ 执行脚本r2.sql,创建新的undo
表空间和数据文件

      ④ 执行程序调整回滚段状态

           ./recover_undo_seg "/opt/oracle/oradata/orcl/system01.dbf" 225

      ⑤ 执行脚本r3.sql,完成修复。



详细步骤

      运行脚本r.sql,完成步骤1和步骤2

1、在mount状态下,将undotbs1的数据文件offline

2alter database open

       这时oracle会报错,实例停掉。

SQL> startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.


SQL> alter database datafile '/opt/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Database altered.


SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/opt/oracle/oradata/orcl/undotbs01.dbf'

Process ID: 28304

Session ID: 170 Serial number: 3

 

3、调整数据块事务状态

这时使用10046事件跟踪不能启动的原因。在此发现system01.dbf665705块有异常。需要清除这两个块的事务状态。
在“参考资料(
1)”中只给出了清掉ITL槽的事务状态,而没有清除数据行的事务状态,所以导致后面创建undo表空间失败。


itlktbitflg设置为0x8000。数据行对应的ITL槽号置为0


在此我编写了一个小程序recover_block_tx.c,实现清除这两部分的事务状态。也可以使用
bbed处理。

[oracle@testlfy code2]$ ./recover_block_tx "/opt/oracle/oradata/orcl/system01.dbf" 665


4
、启动数据库

  这时实例已经停止。需要重新以sysdba登录,运行脚本r2.sql,完成步骤4和步骤5。

[oracle@testlfy oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 28 00:29:07 2014

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

Connected to an idle instance.


SQL> startup

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.

Database opened.


SQL> show parameter undo

NAME                               TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string        AUTO

undo_retention                       integer       900

undo_tablespace                      string        UNDOTBS1


5
、创建回滚表空间

SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/orcl/undotbs02.dbf' size 100m autoextend on next 100m maxsize 30720m;

create undo tablespace undotbs2 datafile '/opt/oracle/oradata/orcl/undotbs02.dbf' size 100m autoextend on next 100m maxsize 30720m

*

ERROR at line 1:

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/opt/oracle/oradata/orcl/undotbs01.dbf'

SQL> alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

在创建回滚表空间时,会报错,但是不影响使用。


6
、重启数据库,删除undotbs1回滚表空间

在此应该执行步骤7,修改回滚段状态。如果不修改,则会报下面的错误。

SQL> startup

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.

Database opened.


SQL> drop tablespace UNDOTBS1 including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU1_1240252155$' found, terminate

dropping tablespace

SQL>


7
、解决由于回滚段在用,不能删除undotbs1表空间问题

在参考资料(3)给出了几种方法,可以使用。

我根据bbed清除回滚段的原理实现一个小程序recover_undo_seg.c,修改回滚段回滚段状态。

将回滚段的status字段修改为1Invalid)。

注意:运行脚本前,先把数据库关掉

SQL> select file# from v$datafile where name = '/opt/oracle/oradata/orcl/undotbs02.dbf';

3

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,

           dbms_rowid.rowid_block_number(rowid) blk#

      from undo$ t

 where file# = 3;

1  225


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


[oracle@testlfy code2]$ ./recover_undo_seg "/opt/oracle/oradata/orcl/system01.dbf" 225



8
、启动数据库,删除表空间。

执行脚本r3.sql,可以完成回滚表空间数据文件的修复。也可以按下面的操作,体验处理过程

SQL> startup

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.

Database opened.


SQL> drop tablespace UNDOTBS1 including contents and datafiles;

drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-01561: failed to remove all objects in the tablespace specified

SQL>

说明:如果错误中还有ORA-01548: active rollback segment '_SYSSMU1_1240252155$' found之类的错误,说明回滚段状态清除未成功,不可进行后续操作。


9
、清除回滚段,删除undotbs1表空间

SQL> select ts# from v$tablespace where name = 'UNDOTBS1';

     2

SQL> delete from seg$ where ts#=2;

10 rows deleted.

SQL> delete from undo$ where ts#=2;

9 rows deleted.

SQL> commit;

Commit complete.


SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.


SQL> startup

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size                  2235208 bytes

Variable Size             822084792 bytes

Database Buffers         3439329280 bytes

Redo Buffers               12132352 bytes

Database mounted.

Database opened.

SQL>


10
、至此undo表空间文件丢失问题已经解决。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/720091/viewspace-1096703/,如需转载,请注明出处,否则将追究法律责任。

下一篇: index与nologging
请登录后发表评论 登录
全部评论

注册时间:2009-01-12

  • 博文量
    69
  • 访问量
    1461320