資料庫壞塊Corrupt block的處理方法
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 <
> 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 <
> 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 <
> 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 <
> 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!
> 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 <
> 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 <
> 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 <
> 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 <
> 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!
> 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle corrupt block壞塊處理OracleBloC
- Oracle RMAN備份中對壞塊(corrupt block)的處理OracleBloC
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- 資料庫壞塊處理資料庫
- BAD Block 壞塊的處理BloC
- ORA-19566 exceeded limit of 0 corrupt blocks資料壞塊處理MITBloC
- bad block表上壞塊的處理BloC
- ORACLE資料庫壞塊的處理 (一次壞快處理過程)Oracle資料庫
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- 教你如何處理Oracle資料庫中的壞塊Oracle資料庫
- ORACLE資料庫壞塊的處理 (通過re-create table方法)Oracle資料庫
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- 如何處理Oracle資料庫中的壞塊問題(轉)Oracle資料庫
- [zt] 如何處理Oracle資料庫中的壞塊[final]Oracle資料庫
- 一個簡單易用的資料庫壞塊處理方案資料庫
- 檢查資料塊損壞(Block Corruption)BloC
- 跳過Oracle資料庫壞塊方法Oracle資料庫
- Oracle 11.2.0.4.4 ADG 備庫資料檔案壞塊處理Oracle
- 對oracle中出現的壞塊的處理方法Oracle
- Oracle資料庫壞塊(corruption)-物理壞塊Oracle資料庫
- Oracle壞塊處理Oracle
- rootvg壞塊處理
- ORACLE 壞塊處理Oracle
- 處理塊損壞
- oracle資料庫改壞spfile引數重啟處理方法Oracle資料庫
- ORA-01578(資料塊損壞)跳過壞塊處理辦法
- Oracle資料庫出現ORA-19566 LOB壞塊的處理記錄Oracle資料庫
- oracle壞塊Block CorruptionsOracleBloC
- ORACLE壞塊(ORA-01578)處理方法Oracle
- MySQL資料庫InnoDB壞頁處理修復MySql資料庫
- 沒有備份的情況下如何處理logical & physical corrupt blockBloC
- DBA實踐---壞塊處理
- Oracle壞塊處理相關Oracle
- Oracle壞塊問題處理Oracle
- ORACLE壞塊(ORA-01578)處理方法（zt）Oracle
- Oracle資料庫壞塊修復Oracle資料庫
- informix資料庫頁故障的處理方法ORM資料庫
- 第7章 處理塊損壞