跳過Oracle資料庫壞塊方法
------------------------模擬壞塊---------------------------------
查詢某條資料的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'
附：還可以使用資料泵匯入匯出的方法跳過壞塊。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29324876/viewspace-2135030/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORA-01578(資料塊損壞)跳過壞塊
- Oracle資料庫壞塊(corruption)-物理壞塊Oracle資料庫
- ORACLE資料庫壞塊的處理 (通過re-create table方法)Oracle資料庫
- Oracle資料庫壞塊修復Oracle資料庫
- ORA-01578(資料塊損壞)跳過壞塊處理辦法
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- ORACLE資料庫壞塊的處理 (一次壞快處理過程)Oracle資料庫
- Oracle資料庫壞塊典型案例擴充Oracle資料庫
- Oracle___專題研究__資料庫壞塊Oracle資料庫
- PostgreSQL 恢復大法 - 恢復部分資料庫、跳過壞塊、修復無法啟動的資料庫SQL資料庫
- Oracle資料庫塊的物理損壞與邏輯損壞Oracle資料庫
- 資料庫壞塊Corrupt block的處理方法資料庫BloC
- 用ORACLE8i修復資料庫壞塊的三種方法Oracle資料庫
- 資料庫壞塊處理資料庫
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- oracle檢查資料庫是否有壞塊的命令Oracle資料庫
- 教你如何處理Oracle資料庫中的壞塊Oracle資料庫
- oracle 資料庫中壞塊概念和檢查指令碼Oracle資料庫指令碼
- 使用DBMS_REPAIR確定和跳過壞塊AI
- 【故障分析】通過壞塊提示資訊確定損壞的資料庫物件資訊資料庫物件
- 修復資料庫壞塊之五資料庫
- 修復資料庫壞塊之四資料庫
- 修復資料庫壞塊之三資料庫
- 修復資料庫壞塊之二資料庫
- 修復資料庫壞塊之一資料庫
- ORACLE中修復資料塊損壞Oracle
- 如何處理Oracle資料庫中的壞塊問題(轉)Oracle資料庫
- [zt] 如何處理Oracle資料庫中的壞塊[final]Oracle資料庫
- 設定 EVENT 10231 跳過壞塊
- 資料庫檔案壞塊損壞導致開啟時報錯的恢復方法資料庫
- ora_01578 資料庫壞塊資料庫
- 資料庫壞塊解決案例一則資料庫
- 11g資料庫出現壞塊資料庫
- 怎樣檢查資料庫壞塊(DBV)資料庫
- Oracle 11.2.0.4.4 ADG 備庫資料檔案壞塊處理Oracle
- 學習這篇Oracle資料庫檔案壞塊損壞的恢復方法,擴充你的知識面Oracle資料庫
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle