造成數(shù)據(jù)塊損壞的原因多種多樣,可是因為物理原因?qū)е拢部赡苋藶樵蚧騉racle bug導(dǎo)致。
物理壞塊(也可以稱為介質(zhì)壞塊)指的是塊格式本身是壞的,塊內(nèi)的數(shù)據(jù)沒有任何意義。
邏輯壞塊,指的是塊內(nèi)的數(shù)據(jù)在邏輯是存在問題。(內(nèi)容)
物理一致性檢查利用校驗和字段工作,主要側(cè)重于檢查硬件故障并不關(guān)心內(nèi)容正確與否。
邏輯一致性檢查就是側(cè)重于內(nèi)容的檢查,內(nèi)容檢查要比校驗和檢查復(fù)雜的多。
壞塊不能避免,只能盡量減少發(fā)生壞塊。
>create tablespace crpt_ts datafile /u01/app/oracle/oradata/orcl/crpt_ts.dbf size 100M;
>create table scott.crpt tablespace crpt_ts as select * from dba_objects where rownum <3000;
> select distinct dbms_rowid.rowid_block_number(rowid) b_no ,dbms_rowid.rowid_relative_fno(rowid) f_no from scott.crpt order by 1;
B_NO F_NO
---------- ----------
131 7
132 7
……………………省略……………………………..
171 7
39 rows selected.
SYS@ orcl>select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b where a.file_id=b.file# and a.owner=SCOTT and a.segment_name=CRPT order by a.block_id;
FILE_ID BLOCK_ID BLOCKS NAME
---------- ---------- ---------- --------------------------------------------------
7 128 8 /u01/app/oracle/oradata/orcl/crpt_ts.dbf
7 136 8 /u01/app/oracle/oradata/orcl/crpt_ts.dbf
7 144 8 /u01/app/oracle/oradata/orcl/crpt_ts.dbf
7 152 8 /u01/app/oracle/oradata/orcl/crpt_ts.dbf
7 160 8 /u01/app/oracle/oradata/orcl/crpt_ts.dbf
7 168 8 /u01/app/oracle/oradata/orcl/crpt_ts.dbf
6 rows selected.
---破壞137,158 數(shù)據(jù)塊的內(nèi)容
seek=n從輸出文件開頭跳過 n個blocks 個塊后再開始復(fù)制。
conv=notrunc不截短輸出文件
dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/crpt_ts.dbf bs=8192 conv=notrunc seek=137 count=1
dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/crpt_ts.dbf bs=8192 conv=notrunc seek=158 count=1
也可以這樣:
? dd of=/u01/app/oracle/oradata/orcl/crpt_ts.dbf bs=8192 conv=notrunc seek=158 <? > Corrupt me!
> EOF
此時dbv命令已經(jīng)可以檢查出兩個壞塊了:
[oracle@cuug101 script]$ dbv file=/u01/app/oracle/oradata/orcl/crpt_ts.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue May 15 16:25:24 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
…………………省略………………….
Total Pages Empty : 12629
Total Pages Marked Corrupt : 2
Highest block SCN : 12592990 (0.12592990)
啟動數(shù)據(jù)庫已經(jīng)發(fā)現(xiàn)無法對有壞塊的對象進行全表掃描:
SYS@ orcl>select count(*) from scott.crpt;
select count(*) from scott.crpt
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 137)
ORA-01110: data file 7: /u01/app/oracle/oradata/orcl/crpt_ts.dbf
通過告警日志看出數(shù)據(jù)庫在file 7block 137中的checksum的值和讀取時重新計算的值已經(jīng)不同,由于兩次checksum值不同(即異或結(jié)果為非0),說明數(shù)據(jù)塊被修改過,數(shù)據(jù)塊為壞塊(corruption)。
ORA-01578: ORACLE data block corrupted (file # 7, block # 137)
ORA-01110: data file 7: /u01/app/oracle/oradata/orcl/crpt_ts.dbf
Hex dump of (file 7, block 158) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_259354/orcl_m000_5108_i259354_a.trc
Corrupt block relative dba: 0x01c0009e (file 7, block 158)
Completely zero block found during validation
SYS@ orcl>select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 137 1 0 ALL ZERO
7 158 1 0 ALL ZERO
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
FROM DBA_EXTENTS A
WHERE FILE_ID = 7
AND 137 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
/
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ -------------------- ------------------ ------------------------------
SCOTT CRPT TABLE CRPT_TS
[oracle@cuug101 ~]$ exp system/oracle file=crpt.dmp tables=scott.crpt
[oracle@cuug101 ~]$ expdp system/oracle dumpfile=crpt.dmp tables=scott.crpt
Export: Release 11.2.0.4.0 - Production on Tue May 15 23:49:52 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
. . exporting table TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 7, block # 137)
ORA-01110: data file 4: /u01/app/oracle/oradata/orcl/users01.dbf
Export terminated successfully with warnings.
SYS@ orcl>alter system set events=10231 trace name context forever,level 10;
alter system set events=10231 trace name context off;
System altered.
[oracle@cuug101 exp]$ expdp system/oracle dumpfile=crpt.dmp tables=scott.crpt
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."CRPT" 262.7 KB 2842 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
[oracle@cuug101 exp]$ impdp system/oracle dumpfile=crpt.dmp remap_table=scott.crpt:crpt1
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."CRPT1" 262.7 KB 2842 rows
SYS@ orcl>select table_name, skip_corrupt from dba_tables where table_name = CRPT AND owner=SCOTT;
TABLE_NAME SKIP_COR
------------------------------ --------
CRPT DISABLED
set serveroutput on
begin
dbms_repair.admin_tables (
table_name => REPAIR_TABLE,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => CRPT_TS);
end;
/
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => SCOTT,
object_name => CRPT,
repair_table_name => REPAIR_TABLE,
corrupt_count => rpr_count);
dbms_output.put_line(repair count: || to_char(rpr_count));
end;
repair count:2
SYS@ orcl>col object_name for a20
SYS@ orcl>col CORRUPT_DESCRIPTION for a50
SYS@ orcl>col REPAIR_DESCRIPTION for a40
SYS@ orcl>col CORRUPT_DESCRIPTION for a20
SYS@ orcl>select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description,repair_description from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION
-------------------- ---------- ------------ ---------- -------------------- ----------------------------------------
CRPT 137 6148 TRUE mark block software corrupt
CRPT 158 6148 TRUE mark block software corrupt
1. 定位壞塊:只有將壞塊信息寫入定義的REPAIR_TABLE后,才能處理壞塊。
(skip/noskip)
SYS@ orcl> declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => SCOTT,
object_name => CRPT,
object_type => dbms_repair.table_object,
repair_table_name => REPAIR_TABLE,
fix_count => fix_count);
dbms_output.put_line(fix count: || to_char(fix_count));
end;
SYS@ orcl>/
fix count: 0
PL/SQL procedure successfully completed.
begin
dbms_repair.skip_corrupt_blocks (
schema_name => SCOTT,
object_name => CRPT,
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
8 /
PL/SQL procedure successfully completed.
SYS@ orcl>select table_name, skip_corrupt from dba_tables where table_name = CRPT AND owner=SCOTT;
TABLE_NAME SKIP_COR
------------------------------ --------
CRPT ENABLED
select count(*) from crpt;
SYS@ orcl>select count(*) from scott.crpt;
COUNT(*)
----------
2842
backup check logical validate datafile 500;
RMAN> backup check logical validate datafile 500;
Starting backup at 02-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8190 instance=cxbdzxdb1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00500 name=+DG_DATA_SSD_2/CXBDZXDB/DATAFILE/tbs_index.257.1065130555
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
500 FAILED 0 145285 3932160 11215813018512
File Name: +DG_DATA_SSD_2/CXBDZXDB/DATAFILE/tbs_index.257.1065130555
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1657837
Index 1 2124520
Other 0 4518
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/cxbdzxdb/cxbdzxdb1/trace/cxbdzxdb1_ora_35457316.trc for details
Finished backup at 02-MAR-21
--執(zhí)行完成后執(zhí)行
set line 300
select * from V$database_block_corruption;
SQL> set line 300
SQL> select * from V$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE CON_ID
---------- ---------- ---------- ------------------ ------------------ ----------
500 1042393 1 1.1216E+13 CORRUPT 0
---查詢文件號對應(yīng)的數(shù)據(jù)文件
select
file_id,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,STATUS,AUT
OEXTENSIBLE,ONLINE_STATUS from dba_data_files where
file_id=856;
set linesize 300
col owner for a30
col table_name for a30
col index_name for a30
col partitioned for a30
select owner,table_name,index_name,status,partitioned,uniqueness,tablespace_name from dba_indexes where owner=POLICY and index_name=IDX_PLC_RATION_TOPID order by 3;
set long 3000
select
dbms_metadata.get_ddl(INDEX,IDX_PLC_RATION_TOPID,POLICY
) a from dual;
drop index policy.IDX_PLC_RATION_TOPID;
CREATE INDEX "POLICY"."IDX_PLC_RATION_TOPID" ON
"POLICY"."PLC_RATION" ("TOPID") TABLESPACE "TBS_INDEX"
parallel 12 ;
Fri Jul 2 12:41:36 2010
Hex dump of (file 12, block 2718618) in trace file /u01/app/oracle/admin/bi/udump/bi_ora_31213.trc
Corrupt block relative dba: 0x03297b9a (file 12, block 2718618)
Fractured block found during backing up datafile
Data in bad block:
type: 6 format: 2 rdba: 0x03297b9a
last change scn: 0x0002.482fc15b seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x77b20601
check value in block header: 0x253
computed block checksum: 0xb6e9
Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data
Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data
Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data
Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data
Reread of blocknum=2718618, file=/u01/oradata/BI/estaging_user01.712.714072365. found same corrupt data
(2)查詢數(shù)據(jù)庫,可知含有壞塊的對象:
SQL> col SEGMENT_NAME format a20
col PARTITION_NAME format a10
select owner,segment_name,partition_name from dba_extents where file_id = 12 and 2718618 between block_id and block_id + blocks-1;
OWNER SEGMENT_NAME PARTITION_
-------------------- -------------------- ----------
ESTAGING LOG_RECORD_DETAIL_4 P20100630
(3)但全表掃描卻沒有任何問題:
SQL> select count(*) from ESTAGING.LOG_RECORD_DETAIL_4 partition (P20100630);
COUNT(*)
----------
449937
SQL> select count(*) from ESTAGING.LOG_RECORD_DETAIL_4;
COUNT(*)
----------
42049608
(4)使用dbv檢查發(fā)現(xiàn)有一個壞塊(耗時較長):
$ dbv file=/u01/oradata/BI/estaging_user01.712.714072365 BLOCKSIZE=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Fri Jul 2 14:15:49 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oradata/BI/estaging_user01.712.714072365
Page 2718618 is influx - most likely media corrupt
Corrupt block relative dba: 0x03297b9a (file 12, block 2718618)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03297b9a
last change scn: 0x0002.482fc15b seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x77b20601
check value in block header: 0x253
computed block checksum: 0xb6e9
DBVERIFY - Verification complete
Total Pages Examined : 2748160
Total Pages Processed (Data) : 2462446
Total Pages Failing (Data) : 0
Total Pages Processed (Index):235234
Total Pages Failing (Index):0
Total Pages Processed (Other):24969
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 25510
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Highest block SCN : 1229607770 (2.1229607770)
(5)使用rman檢查含有壞塊的數(shù)據(jù)文件(耗時較長),, 期間觀察alert.log會發(fā)現(xiàn)同樣的提示:
RMAN> backup validate datafile 12;
這個時候訪問v$database_block_corruption可以看到詳細的壞塊的信息:
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
12 2718618 1 0 FRACTURED
RMAN> blockrecover datafile 12 block 2718618 from backupset;
RMAN> BLOCKRECOVER CORRUPTION LIST;
SQL> select * from v$database_block_corruption;
no rows selected
(9)再使用dbv檢查發(fā)現(xiàn)沒有壞塊了(耗時較長):
$ dbv file=/u01/oradata/BI/estaging_user01.712.714072365 BLOCKSIZE=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Fri Jul 2 15:38:15 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oradata/BI/estaging_user01.712.714072365
DBVERIFY - Verification complete
Total Pages Examined : 2749440
Total Pages Processed (Data) : 2463763
Total Pages Failing (Data) : 0
Total Pages Processed (Index):235250
Total Pages Failing (Index):0
Total Pages Processed (Other):24981
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 25446
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1230819157 (2.1230819157)
注意如果沒有rman的備份,無法執(zhí)行上面的語句,退一步講,如果這是你有手工的熱備,可以將熱備catalog datafilecopy xxxxxx;轉(zhuǎn)換成一個rman備份。
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/129729.html
摘要:微軟正在繼續(xù)向提交潛在的新規(guī)范。微軟方面表示,預(yù)計在未來幾個月內(nèi)完成規(guī)范,并在今年晚些時候推出。此外,微軟還在推進另一個開放計算貢獻的項目。去年年底,微軟推出了加密微控制器標準。微軟正在繼續(xù)向Open Compute Project提交潛在的新規(guī)范。在美國圣何塞舉行的Open Compute Project(OCP)美國峰會上,微軟推出了Project Denali,一項用于SSD固件接口標...
摘要:為可恢復(fù)的錯誤使用檢查型異常,為編程錯誤使用非檢查型錯誤。檢查型異常保證你對錯誤條件提供異常處理代碼,這是一種從語言到強制你編寫健壯的代碼的一種方式,但同時會引入大量雜亂的代碼并導(dǎo)致其不可讀。在編程中選擇檢查型異常還是運行時異常。 異常處理是Java 開發(fā)中的一個重要部分。它是關(guān)乎每個應(yīng)用的一個非功能性需求,是為了處理任何錯誤狀況,比如資源不可訪問,非法輸入,空輸入等等。Java提供了...
摘要:異常處理的個最佳實踐原文地址翻譯出處在中,異常處理是個很麻煩的事情。使用描述性消息拋出異常這個最佳實踐背后的想法與前兩個類似。當你以錯誤的格式提供時,它將被類的構(gòu)造函數(shù)拋出。類提供了特殊的構(gòu)造函數(shù)方法,它接受一個作為參數(shù)。 Java 異常處理的 9 個最佳實踐 原文地址:https://dzone.com/articles/9-...翻譯出處:https://www.oschina.n...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20