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

在此搭建完成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

2、alter 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.dbf的665和705块有异常。需要清除这两个块的事务状态。
在“参考资料（1）”中只给出了清掉ITL槽的事务状态，而没有清除数据行的事务状态，所以导致后面创建undo表空间失败。

将itl的ktbitflg设置为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字段修改为1（Invalid）。

注意：运行脚本前，先把数据库关掉

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/720091/viewspace-1096703/