sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

案例:Oracle报错ORA-01578 用rman或dbv验证跳过坏块后读取文件中数据

时间:2016-06-11 10:27   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 生产环境数据库数据文件出现坏块,SQL语句无法运行。使用dbv或rman验证坏块后跳过坏块读取文件中的数据

1.建立Oracle数据表

SQL> create table t_rep as
2 ?select * from all_objects;
Table created.
SQL> select count(*) from ?t_rep;
COUNT(*)
———-
49857
 

2.ora错误ora-01578 ora-01110

1、sqlplus窗口
SQL> select count(*) from ?t_rep;
select count(*) from ?t_rep
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/oracleplus01.dbf’
2、alert.log文件中
Sun Aug 14 22:01:14 2011
Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0×0000.001328ef seq: 0×2 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0x28ef0602
check value in block header: 0×493
computed block checksum: 0x44b9
Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data
Sun Aug 14 22:01:15 2011
Corrupt Block Found
TSN = 6, TSNAME = XFF
RFN = 6, BLK = 1477, RDBA = 25167301
OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =
SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
 

3.dbv和rman工具验证oracle坏块

1、dbv验证坏块
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/oracleplus01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011
Copyright (c) 1982, 2007, Oracle. ?All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/oracleplus01.dbf
Page 1477 is marked corrupt
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0×0000.001328ef seq: 0×2 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0x28ef0602
check value in block header: 0×493
computed block checksum: 0x44b9
DBVERIFY – Verification complete
Total Pages Examined ? ? ? ? : 2560
Total Pages Processed (Data) : 1371
Total Pages Failing ? (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing ? (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) ?: 0
Total Pages Failing ? (Seg) ?: 0
Total Pages Empty ? ? ? ? ? ?: 1140
Total Pages Marked Corrupt ? : 1
Total Pages Influx ? ? ? ? ? : 0
Highest block SCN ? ? ? ? ? ?: 1256043 (0.1256043)
2、rman验证坏块
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:09:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/oracleplus01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:09:53
RMAN> exit
Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011
Copyright (c) 1982, 2007, Oracle. ?All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ?select file#,block#,blocks from v$database_block_corruption;
FILE# ? ? BLOCK# ? ? BLOCKS
———- ———- ———-
6 ? ? ? 1477 ? ? ? ? ?1
 

4.跳过坏块读取数据文件中数据

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,'T_REP’);
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;
SKIP_COR
——–
ENABLED
SQL> select count(*) from t_rep;
COUNT(*)
———-
49794
说明:数据发生丢失6号文件的1477块中的数据丢失

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01578 用rman或dbv验证跳过坏块后读取文件中数据

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/350.html

Oracle研究中心

关键词:

Oracle坏块

Oracle报错ORA-01578

用rman或dbv验证坏块跳过坏块后读取坏块中数据