处理测试环境的一个坏块问题,具体如下:
使用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS处理后,dbv检测仍然报告有坏块;
即使把表drop表,还是报告有坏块,请教各位,要怎么做,dbv才能检测没有坏块呢?
一、坏块信息
测试环境alert日志提示有坏块:
Mon Feb 25 13:08:18 2013
Hex dump of (file 20, block 108406) in trace file /usr/oracle/admin/edu/udump/edu_ora_14291.trc
Corrupt block relative dba: 0x0501a776 (file 20, block 108406)
Bad header found during buffer read
Data in bad block:
type: 100 format: 2 rdba: 0x0501a776
last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x54cc0602
check value in block header: 0x964e
computed block checksum: 0x62
Reread of rdba: 0x0501a776 (file 20, block 108406) found same corrupted data
Mon Feb 25 13:08:25 2013
Corrupt Block Found
TSN = 6, TSNAME = EDUCATION
RFN = 20, BLK = 108406, RDBA = 83994486
OBJN = 58838, OBJD = 58838, OBJECT = TEST, SUBOBJECT =
SEGMENT OWNER = EDU, SEGMENT TYPE = Table Segment
从文件号和块号查询对象:
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id = 20
4 and 108406 between block_id AND block_id + blocks - 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ --------------------
EDUCATION TABLE EDU TEST
使用rdba查询文件号和块号:
SQL> select to_number('0501a776','xxxxxxxx') from dual;
TO_NUMBER('0501A776','XXXXXXXX')
--------------------------------
83994486
SQL> select dbms_utility.data_block_address_file(83994486) filenum from dual;
FILENUM
----------
20
SQL> select dbms_utility.data_block_address_block(83994486) blocknum from dual;
BLOCKNUM
----------
108406
二、坏块的检测方法:
1、查询有坏块的表:
SQL> select count(1) from edu.test;
select count(1) from edu.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)
ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'
2、使用analyze命令检测:
SQL> analyze table edu.test validate structure cascade;
analyze table edu.test validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)
ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'
3、使用exp检测:
[oracle@testdb ~]$ exp file=test.dmp tables=test
Export: Release 10.2.0.4.0 - Production on 星期一 2月 25 15:40:20 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: edu
Password:
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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)
ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'
Export terminated successfully with warnings.
注意expdp导出不会报错,不能用于检测。(ps:这里因为该表中有效数据是0行,所以导出0行,并不是无法导出数据)
[oracle@testdb ~]$ expdp directory=DUMPDIR dumpfile=test.dmp tables=test
Export: Release 10.2.0.4.0 - 64bit Production on 星期一, 25 2月, 2013 15:41:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: edu
Password:
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
Starting "EDU"."SYS_EXPORT_TABLE_01": edu/******** directory=DUMPDIR dumpfile=test.dmp tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "EDU"."TEST" 5.195 KB 0 rows
Master table "EDU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EDU.SYS_EXPORT_TABLE_01 is:
/usr/oracle/test.dmp
Job "EDU"."SYS_EXPORT_TABLE_01" successfully completed at 15:41:57
4、使用dbv检测坏块:
[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 13:02:43 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf
Page 108406 is marked corrupt
Corrupt block relative dba: 0x0501a776 (file 20, block 108406)
Bad header found during dbv:
Data in bad block:
type: 100 format: 2 rdba: 0x0501a776
last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x54cc0602
check value in block header: 0x964e
computed block checksum: 0x62
DBVERIFY - Verification complete
Total Pages Examined : 1310720
Total Pages Processed (Data) : 1198465
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 97720
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10019
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4515
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1839489834 (271.1839489834)
5、使用rman检测整个数据库:
RMAN> backup validate database;
Starting backup at 25-2月 -13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/25/2013 14:27:55
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/25/2013 14:27:55
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup validate database;
Starting backup at 25-2月 -13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=323 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
......
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-2月 -13
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
20 108406 1 0 CORRUPT
6、使用dbms_repair检测:
使用dbms_repair.admin_tables创建需要的表
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 => 'EDUCATION');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> desc repair_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
使用dbms_repair.check_object检测坏块:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE num_corrupt INT;
2 BEGIN
3 num_corrupt := 0;
4 DBMS_REPAIR.CHECK_OBJECT (
5 schema_name => 'EDU',
6 object_name => 'TEST',
7 repair_table_name => 'REPAIR_TABLE',
8 corrupt_count => num_corrupt);
9 dbms_output.put_line(num_corrupt);
10 END;
11 /
1
PL/SQL procedure successfully completed.
查询repair_table表:
SQL> exec edu.print_table('select * from repair_table');
OBJECT_ID : 58838
TABLESPACE_ID : 6
RELATIVE_FILE_ID : 20
BLOCK_ID : 108406
CORRUPT_TYPE : 6148
SCHEMA_NAME : EDU
OBJECT_NAME : TEST
BASEOBJECT_NAME :
PARTITION_NAME :
CORRUPT_DESCRIPTION :
REPAIR_DESCRIPTION : mark block software corrupt
MARKED_CORRUPT : TRUE
CHECK_TIMESTAMP : 25-2月 -2013 16:26:33
FIX_TIMESTAMP :
REFORMAT_TIMESTAMP :
-----------------
PL/SQL procedure successfully completed.
三、解决方法:
1、删除包含坏块的对象:
如果块块对象是索引,可以删除索引,然后重建。
2、使用rman备份恢复块,即Block media recovery:
RMAN> blockrecover datafile 20 block 108406;
3、使用其他物理备份恢复包含坏块的数据文件(影响较大):
SQL> recover datafile 20;
4、使用dbms_repair.skip_corrupt_blocks跳过坏块:
SQL> BEGIN
2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
3 schema_name => 'EDU',
4 object_name => 'TEST',
5 object_type => DBMS_REPAIR.TABLE_OBJECT,
6 flags => DBMS_REPAIR.SKIP_FLAG);
7 END;
8 /
PL/SQL procedure successfully completed.
此时访问表,不再报错:
SQL> select count(1) from edu.test;
COUNT(1)
----------
0
后来测试使用dbv进行检测,仍然报告有坏块。
[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 17:35:23 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf
Page 108406 is marked corrupt
Corrupt block relative dba: 0x0501a776 (file 20, block 108406)
Bad header found during dbv:
Data in bad block:
type: 100 format: 2 rdba: 0x0501a776
last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x54cc0602
check value in block header: 0x964e
computed block checksum: 0x62
DBVERIFY - Verification complete
Total Pages Examined : 1310720
Total Pages Processed (Data) : 1198446
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 97727
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10031
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4515
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1839591990 (271.1839591990)
当然也可以设置回来:
SQL> BEGIN
2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
3 schema_name => 'EDU',
4 object_name => 'TEST',
5 object_type => DBMS_REPAIR.TABLE_OBJECT,
6 flags => DBMS_REPAIR.NOSKIP_FLAG);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select count(1) from edu.test;
select count(1) from edu.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)
ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'
使用expdp/impdp导出导入:
SQL> insert into edu.test values (1111,'abc');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from edu.test where id=1111;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 20, block # 108406)
ORA-01110: data file 20: '/usr/oracle/oradata/edu/edu07.dbf'
no rows selected
expdp能够导出正常的数据:
[oracle@testdb ~]$ expdp directory=DUMPDIR dumpfile=test.dmp tables=test
Export: Release 10.2.0.4.0 - 64bit Production on 星期一, 25 2月, 2013 17:03:13
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: edu
Password:
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
Starting "EDU"."SYS_EXPORT_TABLE_01": edu/******** directory=DUMPDIR dumpfile=test.dmp tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "EDU"."TEST" 5.218 KB 1 rows
Master table "EDU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EDU.SYS_EXPORT_TABLE_01 is:
/usr/oracle/test.dmp
Job "EDU"."SYS_EXPORT_TABLE_01" successfully completed at 17:03:28
使用impdp以truncate方式导入:
[oracle@testdb ~]$ impdp directory=DUMPDIR dumpfile=test.dmp tables=test table_exist_action=truncate
LRM-00101: unknown parameter name 'table_exist_action'
[oracle@testdb oracle]$ impdp directory=DUMPDIR dumpfile=test.dmp tables=test table_exists_action=truncate
Import: Release 10.2.0.4.0 - 64bit Production on 星期一, 25 2月, 2013 17:06:18
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Username: edu
Password:
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
Master table "EDU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EDU"."SYS_IMPORT_TABLE_01": edu/******** directory=DUMPDIR dumpfile=test.dmp tables=test table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "EDU"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "EDU"."TEST" 5.218 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "EDU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:06:30
SQL> select count(1) from edu.test;
COUNT(1)
----------
1
使用dbv再次检测,仍然报告有坏块:
[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 17:09:09 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf
Page 108406 is marked corrupt
Corrupt block relative dba: 0x0501a776 (file 20, block 108406)
Bad header found during dbv:
Data in bad block:
type: 100 format: 2 rdba: 0x0501a776
last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x54cc0602
check value in block header: 0x964e
computed block checksum: 0x62
DBVERIFY - Verification complete
Total Pages Examined : 1310720
Total Pages Processed (Data) : 1198449
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 97728
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10027
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4515
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1839590591 (271.1839590591)
但是访问表不报错:
SQL> select * from edu.test;
ID NAME
---------- ----------
1111 abc
drop表后,进行dbv检测,还是报告有坏块。
SQL> drop table edu.test;
Table dropped.
[oracle@testdb ~]$ dbv file=/usr/oracle/oradata/edu/edu07.dbf blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on 星期一 2月 25 17:14:40 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /usr/oracle/oradata/edu/edu07.dbf
Page 108406 is marked corrupt
Corrupt block relative dba: 0x0501a776 (file 20, block 108406)
Bad header found during dbv:
Data in bad block:
type: 100 format: 2 rdba: 0x0501a776
last change scn: 0x0106.4d8954cc seq: 0x2 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x54cc0602
check value in block header: 0x964e
computed block checksum: 0x62
DBVERIFY - Verification complete
Total Pages Examined : 1310720
Total Pages Processed (Data) : 1198449
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 97728
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10027
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4515
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1839590643 (271.1839590643)