Oracle块损坏恢复（有rman备份）

最新推荐文章于 2024-11-07 15:10:30 发布

conglushao5834

最新推荐文章于 2024-11-07 15:10:30 发布

阅读量237

点赞数

文章标签：数据库

数据库shutdown immediate后，使用vi修改相应的数据文件，或在rman状态下使用blockrecover datafile 7 block 139 clear;--清除指定的块，用来模拟数据文件有坏块。

重新启动数据库报错：

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2213776 bytes

Variable Size 939526256 bytes

Database Buffers 654311424 bytes

Redo Buffers 7360512 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/data/oracle/oradata/payment/tbs_test01.dbf'

SQL>

Alert日志文件：

ALTER DATABASE OPEN

Errors in file /home/oracle/app/diag/rdbms/payment/payment/trace/payment_dbw0_28505.trc:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/data/oracle/oradata/payment/tbs_test01.dbf'

ORA-27046: file size is not a multiple of logical block size

Additional information: 1

Errors in file /home/oracle/app/diag/rdbms/payment/payment/trace/payment_ora_28558.trc:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/data/oracle/oradata/payment/tbs_test01.dbf'

ORA-1157 signalled during: ALTER DATABASE OPEN...

Hex dump of (file 7, block 138) in trace file /home/oracle/app/diag/rdbms/payment/payment/trace/payment_m000_512.trc

Corrupt block relative dba: 0x01c0008a (file 7, block 138)

Bad check value found during buffer read

Data in bad block:

type: 35 format: 2 rdba: 0x01c0008a

last change scn: 0x0000.00d01f21 seq: 0x1 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x1f212301

check value in block header: 0xe533

computed block checksum: 0x6c6c

Reading datafile '/data/oracle/oradata/payment/tbs_test01.dbf' for corruption at rdba: 0x01c0008a (file 7, block 138)

Reread (file 7, block 138) found same corrupt data

Corrupt Block Found

TSN = 9, TSNAME = TBS_TEST

RFN = 7, BLK = 138, RDBA = 29360266

OBJN = -1, OBJD = 85609, OBJECT = TBS_TEST, SUBOBJECT =

SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment

Errors in file /home/oracle/app/diag/rdbms/payment/payment/trace/payment_m000_512.trc (incident=23445):

ORA-01578: ORACLE data block corrupted (file # 7, block # 138)

ORA-01110: data file 7: '/data/oracle/oradata/payment/tbs_test01.dbf'

--块校验

RMAN> backup validate datafile 7;

Starting backup at 08-APR-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=14 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=52 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/data/oracle/oradata/payment/tbs_test01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

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

7 FAILED 0 176 1280 13642832

File Name: /data/oracle/oradata/payment/tbs_test01.dbf

Block Type Blocks Failing Blocks Processed

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

Data 1 897

Index 0 0

Other 5 207

validate found one or more corrupt blocks

See trace file /home/oracle/app/diag/rdbms/payment/payment/trace/payment_ora_568.trc for details

Finished backup at 08-APR-15

--查看坏块

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

7 138 1 0 CHECKSUM

7 2 1 3.7494E+13 CORRUPT

7 10 1 12635052 CORRUPT

7 275 1 13641438 CHECKSUM

7 1280 1 0 FRACTURED

7 120 1 12635272 CHECKSUM

--恢复指定的块

RMAN> blockrecover datafile 7 block 275 from backupset;

Starting recover at 08-APR-15

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s)

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

restoring blocks of datafile 00007

channel ORA_DISK_1: reading from backup piece /home/oracle/app/flash_recovery_area/PAYMENT/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T085803_bl8z8vr3_.bkp

channel ORA_DISK_1: piece handle=/home/oracle/app/flash_recovery_area/PAYMENT/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T085803_bl8z8vr3_.bkp tag=TAG20150408T085803

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

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

starting media recovery

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

Finished recover at 08-APR-15

RMAN>

ORA-27046: file size is not a multiple of logical block size

造成这个问题的原因一般是因为数据文件从一个裸设备DD或是FTP到了文件系统，正好数据文件的大小不是按ORACLE的block size的整数倍。

网上找到解决方法如下

把数据文件resize到oracle的block size的整数倍

alter database datafile '' resize

如果这里的oracle的block size=8K,那么这个value一定要是8的整数倍了.