数据库坏块Corrupt block的处理方法
Oracle进程在处理一个数据块时，首先将其读入物理内存空间，在处理完成后，再由特定进程将其写回磁盘；如果在这个过程中，出现内存故障，CPU计算失误，都会导致内存数据块的内容混乱，最后反映到写回磁盘的数据块内容有误，这...
·
Oracle进程在处理一个数据块时，首先将其读入物理内存空间，在处理完成后，再由特定进程将其写回磁盘；如果在这个过程中，出现内存故障，CPU计算失误，都会导致内存数据块的内容混乱，最后反映到写回磁盘的数据块内容有误，这样就表现为数据块坏块。
Oracle数据库出现坏块现象是指在Oracle数据库的一个或多个数据块内出现内容混乱的现象，导致数据库进程无法正常解析数据块的内容，进而使数据库进程异常，导致数据库实例异常。
数据库坏块有很多种表象，硬件异常、操作系统Bug、存储异常、IO错误或缓冲问题、Oracle软件Bug、非法操作、掉电等，掉电非法终止服务使进程异常终止，破坏数据块的完整性，导致坏块产生，这也是为什么掉电经常会导致数据库无法启动。
如果数据库出现坏块，数据库的告警日志文件里面会存在有如下的一些报错信息：
Ora-1578以及Ora-600 and trace file in bdump directory，其中Ora-600错误的第一个参数值的范围是[2000]-[8000]，不同的值代表着数据块的不同的层出现问题，具体的如下表所示：
Range block layer
---------------- ----------
Cache layer 2000-4000
Transaction layer 4000-6000
Data layer 6000-8000
下面模拟坏块的几种处理方式：
一、DBMS_REPIR处理未归档模式下坏块
SQL> create table ty.block_test tablespace tbs_ty as select owner,table_name,tablespace_name,status,last_analyzed from dba_tables;
Table created.
SQL> create index ty.idx_table_name on ty.block_test(table_name);
Index created.
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1522
SQL> select table_name from dba_tables where owner ='TY';
TABLE_NAME
------------------------------
BLOCK_TEST
-----检查数据块，现在数据块是好的。
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 00:46:04 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 13
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 540415 (0.540415)
----手动模拟坏块
SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name ='BLOCK_TEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- ----------- ------------ ----------
BLOCK_TEST 5 11 16
SQL> exit
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=23 < > Corrupt block!
> EOF
0+1 records in
0+1 records out
15 bytes (15 B) copied, 5.4e-05 seconds, 278 kB/s
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=12 < > Corrupt block
> EOF
0+1 records in
0+1 records out
14 bytes (14 B) copied, 0.0001 seconds, 140 kB/s
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 11:45:07 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0a6b.636f6c62 seq: 0x1 flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xf9e7
computed block checksum: 0xd5f2
Page 23 is marked corrupt
Corrupt block relative dba: 0x01400017 (file 5, block 23)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x216b.636f6c62 seq: 0xa flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xd3
computed block checksum: 0xcad6
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 11
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 541718 (0.541718)
SQL> analyze table ty.block_test validate structure;
analyze table ty.block_test validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
SQL> select count(*) from ty.block_test;
select count(*) from ty.block_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
-----坏块的对象是TABLE BLOCK_TEST
SQL> select owner,segment_name,segment_type,tablespace_name from dba_extents where file_id=5 and 12 between block_id and block_id + blocks -1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- -------------------- ------------------ ------------------------------
TY BLOCK_TEST TABLE TBS_TY
我们用dbms_repair来处理这个坏块（实际上如果只是checksum坏了，可以修改checksum为正确的值。但实际情况下，checksum坏了往往意味着坏内的数据已经坏了，大多数情况下只能丢弃）
SQL> begin
2 dbms_repair.admin_tables (
3 table_name => 'REPAIR_TABLE',
4 table_type => dbms_repair.repair_table,
5 action => dbms_repair.create_action,
6 tablespace => 'SYSTEM');
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> declare
2 rpr_blocks int;
3 begin
4 rpr_blocks := 0;
5 dbms_repair.check_object (
6 schema_name => 'TY',
7 object_name => 'BLOCK_TEST',
8 repair_table_name => 'REPAIR_TABLE',
9 corrupt_count => rpr_blocks);
10 dbms_output.put_line('repair blocks: ' || to_char(rpr_blocks));
11 end;
12 /
repair blocks: 2
SQL> col CORRUPT_DESCRIPTION for a20
SQL> col REPAIR_DESCRIPTION for a30
SQL> col OBJECT_NAME for a10
SQL> set linesize 200
SQL> select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description,repair_description from repair_table;
OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- -------------------- ------------------------
BLOCK_TEST 12 6148 TRUE mark block software corrupt
BLOCK_TEST 23 6148 TRUE mark block software corrupt
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'BLOCK_TEST' and owner='TY';
TABLE_NAME SKIP_COR
------------------------------ --------
BLOCK_TEST DISABLED
SQL>
SQL> begin
2 dbms_repair.skip_corrupt_blocks (
3 schema_name => 'TY',
4 object_name => 'BLOCK_TEST',
5 object_type => dbms_repair.table_object,
6 flags => dbms_repair.skip_flag);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'BLOCK_TEST' and owner='TY';
TABLE_NAME SKIP_COR
------------------------------ --------
BLOCK_TEST ENABLED
SQL> SQL> select table_name, skip_corrupt from dba_tables where table_name = 'BLOCK_TEST' and owner='TY';
TABLE_NAME SKIP_COR
------------------------------ --------
BLOCK_TEST ENABLED
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1522
SQL> analyze table ty.block_test validate structure;
analyze table ty.block_test validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 12:06:58 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0a6b.636f6c62 seq: 0x1 flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xf9e7
computed block checksum: 0xd5f2
Page 23 is marked corrupt
Corrupt block relative dba: 0x01400017 (file 5, block 23)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x216b.636f6c62 seq: 0xa flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xd3
computed block checksum: 0xcad6
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 11
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 541718 (0.541718)
[oracle@ty102ga ~]$
从上面可以看到，dbms_repair.fix_corrupt_blocks并不修复checksum错误，也不做坏块标记。通过dbv和用validate structure验证，没有发现任何变化。但是通过dbms_repair.skip_corrupt_blocks过程在数据字典中将表设置为跳过坏块，则在查询时会跳过该块。使Oracle能够读出的块其他好的数据块，是存在数据丢失的，而且如果在操作系统层read调用就失败的，不能跳过该块。
$ cat block_test.par
userid="/ as sysdba"
dumpfile=exp_block_test.dp
logfile=exp_block_test.log
DIRECTORY=DATA_PUMP_DIR
tables=(ty.block_test)
[oracle@ty102ga ~]$ expdp parfile=block_test.par
Export: Release 10.2.0.1.0 - 64bit Production on Friday, 03 January, 2014 12:58:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": parfile=block_test.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TY"."BLOCK_TEST" 68.46 KB 1345 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/exp_block_test.dp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 12:58:34
SQL> drop table ty.block_test purge;
Table dropped.
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 13:05:14 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0a6b.636f6c62 seq: 0x1 flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xf9e7
computed block checksum: 0xd5f2
Page 23 is marked corrupt
Corrupt block relative dba: 0x01400017 (file 5, block 23)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x216b.636f6c62 seq: 0xa flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xd3
computed block checksum: 0xcad6
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 11
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 568293 (0.568293)
[oracle@ty102ga ~]$
SQL> drop tablespace tbs_ty including contents and datafiles;
Tablespace dropped.
SQL> create tablespace tbs_ty datafile '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf' size 20m;
Tablespace created.
[oracle@ty102ga ~]$ impdp parfile=block_test.par
Import: Release 10.2.0.1.0 - 64bit Production on Friday, 03 January, 2014 13:08:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": parfile=block_test.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TY"."BLOCK_TEST" 68.46 KB 1345 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 13:08:33
[oracle@ty102ga ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 3 13:08:45 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1345
SQL>
------重新把数据导入以后发现数据已经从1522变成1345，丢失了177条记录。
二、RMAN处理归档模式下坏块
SQL> insert into ty.block_test select owner,table_name,tablespace_name,status,last_analyzed from dba_tables where table_name='BLOCK_TEST';
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno,dbms_rowid.rowid_row_number(rowid) rowno from ty.block_test where table_name='BLOCK_TEST';
ROWID FNO BNO ROWNO
------------------ ---------- ---------- ----------
AAAMkNAAFAAAAAWAAA 5 22 0
SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name ='BLOCK_TEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- ----------- ------------ ----------
BLOCK_TEST 5 11 16
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SQL>
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 524.81M DISK 00:00:32 03-JAN-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140103T163007
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/01ot5dof_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_system_9cg55gxf_.dbf
2 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_undotbs1_9cg55h6p_.dbf
3 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_sysaux_9cg55h2v_.dbf
4 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_users_9cg55h7h_.dbf
5 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.80M DISK 00:00:02 03-JAN-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140103T163007
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/02ot5dpi_1_1
Control File Included: Ckp SCN: 586243 Ckp time: 03-JAN-14
SPFILE Included: Modification time: 03-JAN-14
RMAN>
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=4 < > Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.000111 seconds, 108 kB/s
[oracle@ty102ga ~]$
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=4 < > Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 7.5e-05 seconds, 160 kB/s
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=14 < Corrupt me!
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 8.7e-05 seconds, 138 kB/s
[oracle@ty102ga ~]$
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 16:40:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 4 is marked corrupt
Corrupt block relative dba: 0x01400004 (file 5, block 4)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0000.0a21656d seq: 0x1 flg: 0x04
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0xe9151e01
check value in block header: 0x8159
computed block checksum: 0x683b
Page 14 is marked corrupt
Corrupt block relative dba: 0x0140000e (file 5, block 14)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0000.0a21656d seq: 0x1 flg: 0x04
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0xf2a90601
check value in block header: 0x62fb
computed block checksum: 0x7395
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 12
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 7
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2526
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 584573 (0.584573)
SQL> analyze table ty.block_test validate structure;
analyze table ty.block_test validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 4)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
SQL> select count(*) from ty.block_test;
select count(*) from ty.block_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 4)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
-----坏块的对象是TABLE BLOCK_TEST
SQL> select owner,segment_name,segment_type,tablespace_name from dba_extents where file_id=5 and 4 between block_id and block_id + blocks -1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- -------------------- ------------------ ------------------------------
TY BLOCK_TEST TABLE TBS_TY
SQL> select * from v$database_block_corruption where file#=5;
no rows selected
[oracle@ty102ga ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 3 16:47:26 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TYDB (DBID=4249981274)
RMAN> backup validate datafile 5;
Starting backup at 03-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=530 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-14
RMAN> exit
SQL> select * from v$database_block_corruption where file#=5;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 14 1 0 CORRUPT
5 4 1 0 CORRUPT
RMAN> blockrecover datafile 5 block 4,14 from backupset;
Starting blockrecover at 03-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/01ot5dof_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/01ot5dof_1_1 tag=TAG20140103T163007
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 03-JAN-14
SQL> select * from v$database_block_corruption where file#=5;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 14 1 0 CORRUPT
5 4 1 0 CORRUPT
RMAN> backup validate datafile 5;
Starting backup at 03-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-14
SQL> select * from v$database_block_corruption where file#=5;
no rows selected
$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 16:48:51 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 13
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 7
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2526
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 586715 (0.586715)
SQL> analyze table ty.block_test validate structure;
Table analyzed.
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1346
SQL>
--------------------------------End--------------------------------------------------------------
Oracle数据库出现坏块现象是指在Oracle数据库的一个或多个数据块内出现内容混乱的现象，导致数据库进程无法正常解析数据块的内容，进而使数据库进程异常，导致数据库实例异常。
数据库坏块有很多种表象，硬件异常、操作系统Bug、存储异常、IO错误或缓冲问题、Oracle软件Bug、非法操作、掉电等，掉电非法终止服务使进程异常终止，破坏数据块的完整性，导致坏块产生，这也是为什么掉电经常会导致数据库无法启动。
如果数据库出现坏块，数据库的告警日志文件里面会存在有如下的一些报错信息：
Ora-1578以及Ora-600 and trace file in bdump directory，其中Ora-600错误的第一个参数值的范围是[2000]-[8000]，不同的值代表着数据块的不同的层出现问题，具体的如下表所示：
Range block layer
---------------- ----------
Cache layer 2000-4000
Transaction layer 4000-6000
Data layer 6000-8000
下面模拟坏块的几种处理方式：
一、DBMS_REPIR处理未归档模式下坏块
SQL> create table ty.block_test tablespace tbs_ty as select owner,table_name,tablespace_name,status,last_analyzed from dba_tables;
Table created.
SQL> create index ty.idx_table_name on ty.block_test(table_name);
Index created.
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1522
SQL> select table_name from dba_tables where owner ='TY';
TABLE_NAME
------------------------------
BLOCK_TEST
-----检查数据块，现在数据块是好的。
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 00:46:04 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 13
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 540415 (0.540415)
----手动模拟坏块
SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name ='BLOCK_TEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- ----------- ------------ ----------
BLOCK_TEST 5 11 16
SQL> exit
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=23 < > Corrupt block!
> EOF
0+1 records in
0+1 records out
15 bytes (15 B) copied, 5.4e-05 seconds, 278 kB/s
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=12 < > Corrupt block
> EOF
0+1 records in
0+1 records out
14 bytes (14 B) copied, 0.0001 seconds, 140 kB/s
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 11:45:07 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0a6b.636f6c62 seq: 0x1 flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xf9e7
computed block checksum: 0xd5f2
Page 23 is marked corrupt
Corrupt block relative dba: 0x01400017 (file 5, block 23)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x216b.636f6c62 seq: 0xa flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xd3
computed block checksum: 0xcad6
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 11
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 541718 (0.541718)
SQL> analyze table ty.block_test validate structure;
analyze table ty.block_test validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
SQL> select count(*) from ty.block_test;
select count(*) from ty.block_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
-----坏块的对象是TABLE BLOCK_TEST
SQL> select owner,segment_name,segment_type,tablespace_name from dba_extents where file_id=5 and 12 between block_id and block_id + blocks -1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- -------------------- ------------------ ------------------------------
TY BLOCK_TEST TABLE TBS_TY
我们用dbms_repair来处理这个坏块（实际上如果只是checksum坏了，可以修改checksum为正确的值。但实际情况下，checksum坏了往往意味着坏内的数据已经坏了，大多数情况下只能丢弃）
SQL> begin
2 dbms_repair.admin_tables (
3 table_name => 'REPAIR_TABLE',
4 table_type => dbms_repair.repair_table,
5 action => dbms_repair.create_action,
6 tablespace => 'SYSTEM');
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> declare
2 rpr_blocks int;
3 begin
4 rpr_blocks := 0;
5 dbms_repair.check_object (
6 schema_name => 'TY',
7 object_name => 'BLOCK_TEST',
8 repair_table_name => 'REPAIR_TABLE',
9 corrupt_count => rpr_blocks);
10 dbms_output.put_line('repair blocks: ' || to_char(rpr_blocks));
11 end;
12 /
repair blocks: 2
SQL> col CORRUPT_DESCRIPTION for a20
SQL> col REPAIR_DESCRIPTION for a30
SQL> col OBJECT_NAME for a10
SQL> set linesize 200
SQL> select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description,repair_description from repair_table;
OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION
---------- ---------- ------------ ---------- -------------------- ------------------------
BLOCK_TEST 12 6148 TRUE mark block software corrupt
BLOCK_TEST 23 6148 TRUE mark block software corrupt
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'BLOCK_TEST' and owner='TY';
TABLE_NAME SKIP_COR
------------------------------ --------
BLOCK_TEST DISABLED
SQL>
SQL> begin
2 dbms_repair.skip_corrupt_blocks (
3 schema_name => 'TY',
4 object_name => 'BLOCK_TEST',
5 object_type => dbms_repair.table_object,
6 flags => dbms_repair.skip_flag);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'BLOCK_TEST' and owner='TY';
TABLE_NAME SKIP_COR
------------------------------ --------
BLOCK_TEST ENABLED
SQL> SQL> select table_name, skip_corrupt from dba_tables where table_name = 'BLOCK_TEST' and owner='TY';
TABLE_NAME SKIP_COR
------------------------------ --------
BLOCK_TEST ENABLED
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1522
SQL> analyze table ty.block_test validate structure;
analyze table ty.block_test validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 12:06:58 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0a6b.636f6c62 seq: 0x1 flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xf9e7
computed block checksum: 0xd5f2
Page 23 is marked corrupt
Corrupt block relative dba: 0x01400017 (file 5, block 23)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x216b.636f6c62 seq: 0xa flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xd3
computed block checksum: 0xcad6
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 11
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 541718 (0.541718)
[oracle@ty102ga ~]$
从上面可以看到，dbms_repair.fix_corrupt_blocks并不修复checksum错误，也不做坏块标记。通过dbv和用validate structure验证，没有发现任何变化。但是通过dbms_repair.skip_corrupt_blocks过程在数据字典中将表设置为跳过坏块，则在查询时会跳过该块。使Oracle能够读出的块其他好的数据块，是存在数据丢失的，而且如果在操作系统层read调用就失败的，不能跳过该块。
$ cat block_test.par
userid="/ as sysdba"
dumpfile=exp_block_test.dp
logfile=exp_block_test.log
DIRECTORY=DATA_PUMP_DIR
tables=(ty.block_test)
[oracle@ty102ga ~]$ expdp parfile=block_test.par
Export: Release 10.2.0.1.0 - 64bit Production on Friday, 03 January, 2014 12:58:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": parfile=block_test.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TY"."BLOCK_TEST" 68.46 KB 1345 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/exp_block_test.dp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 12:58:34
SQL> drop table ty.block_test purge;
Table dropped.
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 13:05:14 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0a6b.636f6c62 seq: 0x1 flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xf9e7
computed block checksum: 0xd5f2
Page 23 is marked corrupt
Corrupt block relative dba: 0x01400017 (file 5, block 23)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x216b.636f6c62 seq: 0xa flg: 0x06
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x3ee60601
check value in block header: 0xd3
computed block checksum: 0xcad6
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 11
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6360
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 568293 (0.568293)
[oracle@ty102ga ~]$
SQL> drop tablespace tbs_ty including contents and datafiles;
Tablespace dropped.
SQL> create tablespace tbs_ty datafile '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf' size 20m;
Tablespace created.
[oracle@ty102ga ~]$ impdp parfile=block_test.par
Import: Release 10.2.0.1.0 - 64bit Production on Friday, 03 January, 2014 13:08:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": parfile=block_test.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TY"."BLOCK_TEST" 68.46 KB 1345 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 13:08:33
[oracle@ty102ga ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 3 13:08:45 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1345
SQL>
------重新把数据导入以后发现数据已经从1522变成1345，丢失了177条记录。
二、RMAN处理归档模式下坏块
SQL> insert into ty.block_test select owner,table_name,tablespace_name,status,last_analyzed from dba_tables where table_name='BLOCK_TEST';
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno,dbms_rowid.rowid_row_number(rowid) rowno from ty.block_test where table_name='BLOCK_TEST';
ROWID FNO BNO ROWNO
------------------ ---------- ---------- ----------
AAAMkNAAFAAAAAWAAA 5 22 0
SQL> select segment_name,header_file,header_block,blocks from dba_segments where segment_name ='BLOCK_TEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- ----------- ------------ ----------
BLOCK_TEST 5 11 16
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
SQL>
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 524.81M DISK 00:00:32 03-JAN-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140103T163007
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/01ot5dof_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_system_9cg55gxf_.dbf
2 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_undotbs1_9cg55h6p_.dbf
3 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_sysaux_9cg55h2v_.dbf
4 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/o1_mf_users_9cg55h7h_.dbf
5 Full 586228 03-JAN-14 /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.80M DISK 00:00:02 03-JAN-14
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140103T163007
Piece Name: /u01/app/oracle/product/10.2.0/db_1/dbs/02ot5dpi_1_1
Control File Included: Ckp SCN: 586243 Ckp time: 03-JAN-14
SPFILE Included: Modification time: 03-JAN-14
RMAN>
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=4 < > Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.000111 seconds, 108 kB/s
[oracle@ty102ga ~]$
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=4 < > Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 7.5e-05 seconds, 160 kB/s
[oracle@ty102ga ~]$ dd of=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf bs=8192 conv=notrunc seek=14 < Corrupt me!
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 8.7e-05 seconds, 138 kB/s
[oracle@ty102ga ~]$
[oracle@ty102ga ~]$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 16:40:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
Page 4 is marked corrupt
Corrupt block relative dba: 0x01400004 (file 5, block 4)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0000.0a21656d seq: 0x1 flg: 0x04
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0xe9151e01
check value in block header: 0x8159
computed block checksum: 0x683b
Page 14 is marked corrupt
Corrupt block relative dba: 0x0140000e (file 5, block 14)
Bad header found during dbv:
Data in bad block:
type: 67 format: 7 rdba: 0x20747075
last change scn: 0x0000.0a21656d seq: 0x1 flg: 0x04
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0xf2a90601
check value in block header: 0x62fb
computed block checksum: 0x7395
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 12
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 7
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2526
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Highest block SCN : 584573 (0.584573)
SQL> analyze table ty.block_test validate structure;
analyze table ty.block_test validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 4)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
SQL> select count(*) from ty.block_test;
select count(*) from ty.block_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 4)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf'
-----坏块的对象是TABLE BLOCK_TEST
SQL> select owner,segment_name,segment_type,tablespace_name from dba_extents where file_id=5 and 4 between block_id and block_id + blocks -1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
---------- -------------------- ------------------ ------------------------------
TY BLOCK_TEST TABLE TBS_TY
SQL> select * from v$database_block_corruption where file#=5;
no rows selected
[oracle@ty102ga ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 3 16:47:26 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TYDB (DBID=4249981274)
RMAN> backup validate datafile 5;
Starting backup at 03-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=530 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-14
RMAN> exit
SQL> select * from v$database_block_corruption where file#=5;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 14 1 0 CORRUPT
5 4 1 0 CORRUPT
RMAN> blockrecover datafile 5 block 4,14 from backupset;
Starting blockrecover at 03-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/db_1/dbs/01ot5dof_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/01ot5dof_1_1 tag=TAG20140103T163007
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 03-JAN-14
SQL> select * from v$database_block_corruption where file#=5;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 14 1 0 CORRUPT
5 4 1 0 CORRUPT
RMAN> backup validate datafile 5;
Starting backup at 03-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JAN-14
SQL> select * from v$database_block_corruption where file#=5;
no rows selected
$ dbv file=/u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Jan 3 16:48:51 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/TYDB/datafile/tbs_ty01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 13
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 7
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2526
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 586715 (0.586715)
SQL> analyze table ty.block_test validate structure;
Table analyzed.
SQL> select count(*) from ty.block_test;
COUNT(*)
----------
1346
SQL>
--------------------------------End--------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24930246/viewspace-1066894/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24930246/viewspace-1066894/
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
0
0- 0
已为社区贡献4条内容
所有评论(0)