------------------------模拟坏块---------------------------------
查询某条数据的rowid
SQL> select
object_id,object_name,object_type,rowid from my_user.tb_test where
object_name='DBA_OBJECTS'
OBJECT_ID OBJECT_NAME OBJECT_TYPE ROWID
----------
-------------------- ------------------- ------------------
3310 DBA_OBJECTS VIEW AAAD9zAAJAAAACvAAS
3311 DBA_OBJECTS SYNONYM AAAD9zAAJAAAACvAAT
查询某条数据的rowid,通过rowid查看行数据的文件号,块号
SQL> select
rowid, dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno
from my_user.tb_test where object_id=3311;
ROWID
REL_FNO BLOCKNO
------------------
---------- ----------
AAAD9zAAJAAAACvAAT 9
175
查询对应数据文件
SQL> select
file_name from dba_data_files where file_id=9;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf
bbed修改对应数据块
[oracle@rac-dg Desktop]$ bbed
password=blockedit
filename=/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf
BBED: Release 2.0.0.0.0 - Limited
Production on Tue Sep 20 16:34:37 2016
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
************* !!! For Oracle Internal Use
only !!! ***************
BBED> set block 175
BLOCK# 175
BBED> show
FILE# 0
BLOCK# 175
OFFSET 0
DBA 0x00000000
(0 0,175)
FILENAME /u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf
BIFILE bifile.bbd
LISTFILE
BLOCKSIZE 8192
MODE Browse
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> dump
File:
/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf (0)
Block: 175 Offsets: 0 to
511 Dba:0x00000000
------------------------------------------------------------------------
06a20000 af004002 b2740a00 00000104 0bc70000
01000000 733f0000 a1740a00
00000000 03003201 a0004002 ffff0000 00000000
00000000 00000000 00800000
a1740a00 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00015100
ffffb400 e3032f03 2f030000 5100211f c81e691e
181ec11d 6c1d111d b61c561c
fc1b9c1b 421be21a 901a381a e9199819 4119f018
99184018 e1178c17 3117dd16
83162f16 d5157e15 2115cc14 71141d14 c3136f13
1513c612 73121a12 c7116e11
1c11c410 71102210 c90f690f 050fad0e 5b0e030e
b20d5b0d 0b0db30c 550c000c
a50b550b ff0aa80a 4b0af709 9d094d09 f708a008
4308ef07 95074207 e9069706
3f06ed05 95053f05 e3049304 3e04e303 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> set mode edit
MODE Edit
BBED> modify /x 1234
Warning: contents of previous BIFILE will
be lost. Proceed? (Y/N) y
File:
/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf (0)
Block: 175 Offsets: 0 to
511 Dba:0x00000000
------------------------------------------------------------------------
12340000 af004002 b2740a00 00000104 0bc70000
01000000 733f0000 a1740a00
00000000 03003201 a0004002 ffff0000 00000000
00000000 00000000 00800000
a1740a00 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00015100
ffffb400 e3032f03 2f030000 5100211f c81e691e
181ec11d 6c1d111d b61c561c
fc1b9c1b 421be21a 901a381a e9199819 4119f018
99184018 e1178c17 3117dd16
83162f16 d5157e15 2115cc14 71141d14 c3136f13
1513c612 73121a12 c7116e11
1c11c410 71102210 c90f690f 050fad0e 5b0e030e
b20d5b0d 0b0db30c 550c000c
a50b550b ff0aa80a 4b0af709 9d094d09 f708a008
4308ef07 95074207 e9069706
3f06ed05 95053f05 e3049304 3e04e303 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000
<32 bytes="" per="" line="">
查询坏块报错
SQL> select
count(*) from my_user.tb_test;
select count(*)
from my_user.tb_test
*
ERROR at line
1:
ORA-01578:
ORACLE data block corrupted (file # 9, block # 175)
ORA-01110: data
file 9:
'/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf'
dbv检查坏块数量
[oracle@rac-dg Desktop]$ dbv
file=/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production
on Tue Sep 20 16:44:30 2016
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE =
/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf
Page 175 is marked corrupt
Corrupt block relative dba: 0x024000af
(file 9, block 175)
Bad header found during dbv:
Data in bad block:
type: 18 format: 4 rdba: 0x024000af
last
change scn: 0x0000.000a74b2 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x2700
consistency value in tail: 0x74b20601
check value in block header: 0x760e
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Pages Examined : 384
Total Pages Processed (Data) : 205
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 139
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg)
: 0
Total Pages Empty : 39
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 685266 (0.685266)
------------------------------------------跳过坏块方法---------------------------
方法一,10231事件跳过坏块,只对全表扫有用,建议CTAS方式跳过坏块创建新表
SQL> alter
session set events '10231 trace name context forever,level 10' ;
Session
altered.
SQL> select
count(*) from my_user.tb_test;
COUNT(*)
----------
15529
方法二,调用DBMS_REPAIR包跳过坏块
创建坏块记录表,记录表名必须以REPAIR_开头
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => '&tablespace_name');
END;
/ 2
3 4 5
6 7 8
Enter value for
tablespace_name: MY_TABLESPACE
old 6:
TABLESPACE => '&tablespace_name');
new 6:
TABLESPACE => 'MY_TABLESPACE');
PL/SQL
procedure successfully completed.
检查指定对象坏块,并将坏块信息写入记录表REPAIR_TABLE
SQL> set
serveroutput on
DECLARE
num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' ||
TO_CHAR (num_corrupt));
END;
/SQL> 2
3 4 5
6 7 8
9 10 11
Enter value for
schema_name: MY_USER
old 5:
SCHEMA_NAME => '&schema_name',
new 5:
SCHEMA_NAME => 'MY_USER',
Enter value for
object_name: TB_TEST
old 6:
OBJECT_NAME => '&object_name',
new 6:
OBJECT_NAME => 'TB_TEST',
number corrupt:
1
PL/SQL
procedure successfully completed.
查询记录表数据
SQL> select
object_id,BLOCK_ID,CORRUPT_TYPE,MARKED_CORRUPT,CHECK_TIMESTAMP,FIX_TIMESTAMP
from repair_table;
OBJECT_ID
BLOCK_ID CORRUPT_TYPE MARKED_COR CHECK_TIMESTAMP FIX_TIMESTAMP
----------
---------- ------------ ---------- ------------------- -------------------
16243 175 6148 TRUE 2016-09-20 17:30:35
标记坏块
SQL> DECLARE
num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' ||
to_char(num_fix));
END;
/ 2
3 4 5
6 7 8
9 10 11
12
Enter value for
schema_name: MY_USER
old 5:
SCHEMA_NAME => '&schema_name',
new 5:
SCHEMA_NAME => 'MY_USER',
Enter value for
object_name: TB_TEST
old 6:
OBJECT_NAME=> '&object_name',
new 6:
OBJECT_NAME=> 'TB_TEST',
num fix: 0
PL/SQL
procedure successfully completed.
设置DML语句跳过坏块
SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/ 2
3 4 5
6 7 8
Enter value for
schema_name: MY_USER
old 3:
SCHEMA_NAME => '&schema_name',
new 3:
SCHEMA_NAME => 'MY_USER',
Enter value for
object_name: TB_TEST
old 4:
OBJECT_NAME => '&object_name',
new 4:
OBJECT_NAME => 'TB_TEST',
PL/SQL
procedure successfully completed.
查询数据,已经跳过坏块
SQL> select
count(*) from my_user.tb_test;
COUNT(*)
----------
15529
当有跳块操作时,系统会记录一条记录在trace文件中
*** 2016-09-20 17:44:23.376
table scan: segment: file# 9 block# 130
skipping corrupt block file# 9 block# 175
table scan: segment: file# 9 block# 130
*** 2016-09-20 18:00:50.744
skipping corrupt block file# 9 block# 175
table scan: segment: file# 9 block# 130
*** 2016-09-21 10:21:45.395
skipping corrupt block file# 9 block# 175
*** 2016-09-21 10:23:17.978
table scan: segment: file# 9 block# 130
skipping corrupt block file# 9 block# 175
*** 2016-09-21 10:32:46.715
table scan: segment: file# 9 block# 130
skipping corrupt block file# 9 block# 175
*** 2016-09-21 10:33:06.150
table scan: segment: file# 9 block# 130
skipping corrupt block file# 9 block# 175
查询坏块中的数据无结果,没有报错
SQL> select
rowid, dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno
from my_user.tb_test where object_id=3311;
no rows
selected
SQL> select
owner,table_name,SKIP_CORRUPT,TABLESPACE_NAME from dba_tables where
owner='MY_USER';
OWNER
TABLE_NAME SKIP_COR TABLESPACE_NAME
------------------------------
------------------------------ -------- ------------------------------
MY_USER TB_TEST ENABLED
MY_TABLESPACE
取消跳过坏块操作
SQL> select
owner,table_name,SKIP_CORRUPT from dba_tables where table_name='TB_TEST';
OWNER
TABLE_NAME SKIP_COR
------------------------------
------------------------------ --------
MY_USER TB_TEST ENABLED
SQL> execute
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'MY_USER',object_name=>'TB_TEST',object_type=>dbms_repair.table_object,flags=>dbms_repair.noskip_flag);
PL/SQL
procedure successfully completed.
SQL> select
owner,table_name,SKIP_CORRUPT from dba_tables where table_name='TB_TEST';
OWNER
TABLE_NAME SKIP_COR
------------------------------
------------------------------ --------
MY_USER TB_TEST DISABLED
SQL> select
count(*) from my_user.tb_test;
select count(*)
from my_user.tb_test
*
ERROR at line
1:
ORA-01578:
ORACLE data block corrupted (file # 9, block # 175)
ORA-01110: data
file 9:
'/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf'
附:还可以使用数据泵导入导出的方法跳过坏块。