数据库恢复实例四:处理坏块

数据库恢复实例四:处理坏块

数据错误如下:

SQL> select count(*) from test;

select count(*) from test

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 2, block # 1095)

ORA-01110: data file 2: '/u02/oradata/sfcsys/test01.dbf'1.分析:

分析:

上面的错误由坏块引用

恢复方法:

若有备份可用:可以通过两种方法来恢复(1)恢复整个数据文件(2)只恢复坏块

若无备份可用:可以通过expdp/impdp重建表去掉坏块

有备份可用的情况:

(1)   恢复整个数据文件

1.为了不影响使用,offline有坏块的数据文件

SQL> alter database datafile 2 offline;

2.恢复数据文件

RMAN> restore datafile 2;

RMAN> recover datafile 2;

3.使用数据文件在线

SQL>  alter database datafile 2 online;

(2)   只恢复坏块

RMAN> blockrecover datafile 2 block 1095;

 

Starting blockrecover at 17-DEC-08

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=137 devtype=DISK

 

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00002

channel ORA_DISK_1: reading from backup piece /u09/orabackup/rman/SFCSYS_3658_1.dbfbk

channel ORA_DISK_1: restored block(s) from backup piece 1

piece handle=/u09/orabackup/rman/SFCSYS_3658_1.dbfbk tag=TAG20081217T120159

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

 

starting media recovery

media recovery complete, elapsed time: 00:00:03

 

Finished blockrecover at 17-DEC-08

 

无备份可用的情况:

1.在做破坏之前先记录共有多少笔数据

SQL> select count(*) from test1;

 

  COUNT(*)

----------

    109466

2.导出数据

expdp content=all  tables=oak.test1 directory=dump dumpfile= expdat.dmp

3.删除test1表

SQL> drop table test1;

4.导入数据

impdp content=all directory=dump dumpfile=expdat.dmp

再建立相应的索引,约束,包等即可

5.确认丢失数据量

SQL> select count(*) from test1;

 

  COUNT(*)

----------

    109394

109466-109394=72共有72笔数据丢失

 

注:可以通过下的语句来检查损坏的对object(感觉这条语句运行有点慢)

SQL> select tablespace_name ,segment_type ,owner ,segment_name

  2  from dba_extents where file_id = 2 and 1095 between block_id

  3  and block_id + blocks -1;

TABLESPACE_NAME SEGMENT_TYPE    OWNER           SEGMENT_NAME

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

TEST            TABLE           OAK             TEST

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

转载于:http://blog.itpub.net/7419833/viewspace-514299/