oracle11g查看表数据文件,oracle11g文件块损坏的测试与修复

oracle11g文件块损坏的测试与修复

# 环境

OS: Red Hat Enterprise Linux Server release 6.4

DB: Oracle Database 11.2.0.4.0

1.连接数据库,查询departments表数据所在的文件号和块号

$ sqlplus /nolog

SQL> CONN / AS SYSDBA

SQL> SELECT rowid,dbms_rowid.rowid_relative_fno(rowid)

rel_fno,dbms_rowid.rowid_block_number(rowid) blk_no FROM

hr.departments;

ROWID REL_FNO BLK_NO

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

AAAVPcAAFAAAACvAAA 5 175

AAAVPcAAFAAAACvAAB 5 175

AAAVPcAAFAAAACvAAC 5 175

AAAVPcAAFAAAACvAAD 5 175

...

2.登陆RMAN，对块数据执行CLEAR

$ rman target /

RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 175 CLEAR;

RMAN> EXIT

3.异常关闭数据库

SQL> SHUTDOWN ABORT

4.操作系统上验证文件的块损坏情况

$ dbv file=example01.dbf

DBVERIFY - Verification starting : FILE =

/u01/app/oracle/oradata/sztech1/example01.dbf

Page 175 is influx - most likely media corrupt

Corrupt block relative dba: 0x014000af (file 5, block

175)

Fractured block found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x014000af

last change scn: 0x0000.0009b40c seq: 0x92

flg: 0x06

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

consistency value in tail: 0xb40c0601

check value in block header: 0xd80b

computed block checksum: 0x93

...

Total Pages Marked Corrupt : 1

...

5.正常启动数据库

SQL> STARTUP

查询部门表数据

SQL> SELECT * FROM hr.departments;

SELECT * FROM hr.departments

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block #

175)

ORA-01110: data file 5:

'/u01/app/oracle/oradata/sztech1/example01.dbf'

6.登陆RMAN

$ rman target /

查看数据库错误

RMAN> LIST FAILURE;

Failure ID Priority Status Time Detected Summary

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

541 HIGH OPEN 25-OCT-17 Datafile 5:

'/u01/app/oracle/oradata/sztech1/example01.dbf' contains one or

more corrupt blocks

查看提供的建议

RMAN> ADVISE FAILURE;

...

Automated Repair Options

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

Option Repair Description

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

1 Perform block media recovery of block 175 in file

5

Strategy: The repair includes complete

media recovery with no data loss

Repair script:

/u01/app/oracle/diag/rdbms/sztech1/sztech1/hm/reco_2572027459.hm

修复错误

RMAN> REPAIR FAILURE;

再次查看错误

RMAN> LIST FAILURE;

no failures found that match specification

7.操作系统上验证文件的块损坏情况

$ dbv file=example01.dbf

...

Total Pages Marked Corrupt : 0

...

8.查询部门表数据

SQL> SELECT * FROM hr.departments;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

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

-----------

10

Administration 200 1700

20

Marketing 201 1800

30

Purchasing 114 1700

40 Human

Resources 203 2400

50

Shipping 121 1500

...

!!The End!!