資料塊恢復例項
1. 準備資料,
SQL> create table test as select * from all_objects;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select blocks from user_tables where table_name='TEST';
BLOCKS
----------
826
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from test
2.bbed破壞資料
[oracle@deer lib]$ ./bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 10 13:31:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 4,9644
DBA 0x010025ac (16786860 4,9644)
BBED> find /c LATCH
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6239 to 6750 Dba:0x010025ac
------------------------------------------------------------------------
4c415443 485f4d49 53534553 ff03c20f 03ff0753 594e4f4e 594d0778 6d080f01
12240778 6d080f01 12241332 3030392d 30382d31 353a3030 3a31373a 33350556
414c4944 014e014e 014e02c1 022c000e 06505542 4c49430d 56244c41 54434848
4f4c4445 52ff02c2 0fff0753 594e4f4e 594d0778 6d080f01 12240778 6d080f01
12241332 3030392d 30382d31 353a3030 3a31373a 33350556 414c4944 014e014e
014e02c1 022c000e 06505542 4c49430b 56244c41 5443484e 414d45ff 03c20e63
ff075359 4e4f4e59 4d07786d 080f0112 2407786d 080f0112 24133230 30392d30
382d3135 3a30303a 31373a33 35055641 4c494401 4e014e01 4e02c102 2c000e06
5055424c 49430e56 244c4154 43485f50 4152454e 54ff03c2 0e61ff07 53594e4f
4e594d07 786d080f 01122407 786d080f 01122413 32303039 2d30382d 31353a30
303a3137 3a333505 56414c49 44014e01 4e014e02 c1022c00 0e065055 424c4943
1056244c 41544348 5f434849 4c445245 4eff03c2 0e5fff07 53594e4f 4e594d07
786d080f 01122407 786d080f 01122413 32303039 2d30382d 31353a30 303a3137
3a333505 56414c49 44014e01 4e014e02 c1022c00 0e065055 424c4943 0756244c
41544348 ff03c20e 5dff0753 594e4f4e 594d0778 6d080f01 12240778 6d080f01
12241332 3030392d 30382d31 353a3030 3a31373a 33350556 414c4944 014e014e
<32 bytes="" per="" line="">
BBED> dump /v dba 4,9644 offset 6239 count 64
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6239 to 6302 Dba:0x010025ac
-------------------------------------------------------
4c415443 485f4d49 53534553 ff03c20f l LATCH_MISSES..R
03ff0753 594e4f4e 594d0778 6d080f01 l ...SYNONYM.xm...
12240778 6d080f01 12241332 3030392d l .$.xm....$.2009-
30382d31 353a3030 3a31373a 33350556 l 08-15:00:17:35.V
<16 bytes="" per="" line="">
BBED> f -------------------------------》尋找下一個
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6686 to 6749 Dba:0x010025ac
------------------------------------------------------------------------
4c415443 48ff03c2 0e5dff07 53594e4f 4e594d07 786d080f 01122407 786d080f
01122413 32303039 2d30382d 31353a30 303a3137 3a333505 56414c49 44014e01
<32 bytes="" per="" line="">
BBED> modify 10000 dba 4,9644
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6686 to 6749 Dba:0x010025ac
------------------------------------------------------------------------
27105443 48ff03c2 0e5dff07 53594e4f 4e594d07 786d080f 01122407 786d080f
01122413 32303039 2d30382d 31353a30 303a3137 3a333505 56414c49 44014e01
<32 bytes="" per="" line="">
3.DBC 檢查
[oracle@deer lib]$ dbv file=/u01/oradata/deer/users01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Sun Jun 10 13:38:49 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oradata/deer/users01.dbf
Page 9644 is marked corrupt
Corrupt block relative dba: 0x010025ac (file 4, block 9644)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x010025ac
last change scn: 0x0000.00af076f seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x076f0601
check value in block header: 0xaf97
computed block checksum: 0x516b
DBVERIFY - Verification complete
Total Pages Examined : 13760
Total Pages Processed (Data) : 3723
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8966
Total Pages Failing (Index): 0
Total Pages Processed (Other): 537
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 533
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 11470764 (0.11470764)
select * from test ------------------------------>報告查詢錯誤
ERROR:
ORA-01578: ORACLE data block corrupted (file # 4, block # 9644)
ORA-01110: data file 4: '/u01/oradata/deer/users01.dbf'
3.如上，跳出ORA-01578,說明資料塊已經被標識為'software corrupt' ,
如果遇到其他錯誤,ora-0600這時就需要把資料塊標誌為'software corrupt'
需要使用dbms_repair家標記。
先利用dbms_repair建立兩個表
declare
begin
dbms_repair.admin_tables(
table_name=>'repair_table',
table_type=>dbms_repaire.repaire_table,
action=>dbms_repaire.create_action,
tablespace=>'USER' )
end;
在建立orphan key table:
declare
begin
dbms_repair.admin_tables(
table_name=>'orphan_table',
table_type=>dbms_repaire.orphan_table,
action=>dbms_repaire.create_action,
tablespace=>'USER' )
end;
標記壞塊：
declare
fix_count int;
begin
fix_count:=0
dbms_repaire.fix_corrupt_blocks(
schema_name=>'SCOTT',
object_name=>'TEST',
object_type=>dbms_repaire.table_objects,
repaire_table_name=>'repaire_table',
fix_count=>fix_count);
dbms_output.put_line('fix count:'||to_char(fix_count)');
end;
之後查詢資料塊，會丟擲ORA-01578錯誤。
4.重建表
使用dbms_repaire.skip_corrupt_blocks設定為skip,或者使用10231事件，遮蔽ora-1578錯誤
alter session set events '10231 trace name context forever ,level 10'
執行這個之後發現user_table中skip_corrupt，還是disable，不知道為什麼這個樣（11g）
但是可以全表執行全表掃描。
利用CTAS重建table和相關index。
SQL> create table test as select * from all_objects;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select blocks from user_tables where table_name='TEST';
BLOCKS
----------
826
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from test
2.bbed破壞資料
[oracle@deer lib]$ ./bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 10 13:31:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 4,9644
DBA 0x010025ac (16786860 4,9644)
BBED> find /c LATCH
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6239 to 6750 Dba:0x010025ac
------------------------------------------------------------------------
4c415443 485f4d49 53534553 ff03c20f 03ff0753 594e4f4e 594d0778 6d080f01
12240778 6d080f01 12241332 3030392d 30382d31 353a3030 3a31373a 33350556
414c4944 014e014e 014e02c1 022c000e 06505542 4c49430d 56244c41 54434848
4f4c4445 52ff02c2 0fff0753 594e4f4e 594d0778 6d080f01 12240778 6d080f01
12241332 3030392d 30382d31 353a3030 3a31373a 33350556 414c4944 014e014e
014e02c1 022c000e 06505542 4c49430b 56244c41 5443484e 414d45ff 03c20e63
ff075359 4e4f4e59 4d07786d 080f0112 2407786d 080f0112 24133230 30392d30
382d3135 3a30303a 31373a33 35055641 4c494401 4e014e01 4e02c102 2c000e06
5055424c 49430e56 244c4154 43485f50 4152454e 54ff03c2 0e61ff07 53594e4f
4e594d07 786d080f 01122407 786d080f 01122413 32303039 2d30382d 31353a30
303a3137 3a333505 56414c49 44014e01 4e014e02 c1022c00 0e065055 424c4943
1056244c 41544348 5f434849 4c445245 4eff03c2 0e5fff07 53594e4f 4e594d07
786d080f 01122407 786d080f 01122413 32303039 2d30382d 31353a30 303a3137
3a333505 56414c49 44014e01 4e014e02 c1022c00 0e065055 424c4943 0756244c
41544348 ff03c20e 5dff0753 594e4f4e 594d0778 6d080f01 12240778 6d080f01
12241332 3030392d 30382d31 353a3030 3a31373a 33350556 414c4944 014e014e
<32 bytes="" per="" line="">
BBED> dump /v dba 4,9644 offset 6239 count 64
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6239 to 6302 Dba:0x010025ac
-------------------------------------------------------
4c415443 485f4d49 53534553 ff03c20f l LATCH_MISSES..R
03ff0753 594e4f4e 594d0778 6d080f01 l ...SYNONYM.xm...
12240778 6d080f01 12241332 3030392d l .$.xm....$.2009-
30382d31 353a3030 3a31373a 33350556 l 08-15:00:17:35.V
<16 bytes="" per="" line="">
BBED> f -------------------------------》尋找下一個
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6686 to 6749 Dba:0x010025ac
------------------------------------------------------------------------
4c415443 48ff03c2 0e5dff07 53594e4f 4e594d07 786d080f 01122407 786d080f
01122413 32303039 2d30382d 31353a30 303a3137 3a333505 56414c49 44014e01
<32 bytes="" per="" line="">
BBED> modify 10000 dba 4,9644
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oradata/deer/users01.dbf (4)
Block: 9644 Offsets: 6686 to 6749 Dba:0x010025ac
------------------------------------------------------------------------
27105443 48ff03c2 0e5dff07 53594e4f 4e594d07 786d080f 01122407 786d080f
01122413 32303039 2d30382d 31353a30 303a3137 3a333505 56414c49 44014e01
<32 bytes="" per="" line="">
3.DBC 檢查
[oracle@deer lib]$ dbv file=/u01/oradata/deer/users01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Sun Jun 10 13:38:49 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oradata/deer/users01.dbf
Page 9644 is marked corrupt
Corrupt block relative dba: 0x010025ac (file 4, block 9644)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x010025ac
last change scn: 0x0000.00af076f seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x076f0601
check value in block header: 0xaf97
computed block checksum: 0x516b
DBVERIFY - Verification complete
Total Pages Examined : 13760
Total Pages Processed (Data) : 3723
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 8966
Total Pages Failing (Index): 0
Total Pages Processed (Other): 537
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 533
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 11470764 (0.11470764)
select * from test ------------------------------>報告查詢錯誤
ERROR:
ORA-01578: ORACLE data block corrupted (file # 4, block # 9644)
ORA-01110: data file 4: '/u01/oradata/deer/users01.dbf'
3.如上，跳出ORA-01578,說明資料塊已經被標識為'software corrupt' ,
如果遇到其他錯誤,ora-0600這時就需要把資料塊標誌為'software corrupt'
需要使用dbms_repair家標記。
先利用dbms_repair建立兩個表
declare
begin
dbms_repair.admin_tables(
table_name=>'repair_table',
table_type=>dbms_repaire.repaire_table,
action=>dbms_repaire.create_action,
tablespace=>'USER' )
end;
在建立orphan key table:
declare
begin
dbms_repair.admin_tables(
table_name=>'orphan_table',
table_type=>dbms_repaire.orphan_table,
action=>dbms_repaire.create_action,
tablespace=>'USER' )
end;
標記壞塊：
declare
fix_count int;
begin
fix_count:=0
dbms_repaire.fix_corrupt_blocks(
schema_name=>'SCOTT',
object_name=>'TEST',
object_type=>dbms_repaire.table_objects,
repaire_table_name=>'repaire_table',
fix_count=>fix_count);
dbms_output.put_line('fix count:'||to_char(fix_count)');
end;
之後查詢資料塊，會丟擲ORA-01578錯誤。
4.重建表
使用dbms_repaire.skip_corrupt_blocks設定為skip,或者使用10231事件，遮蔽ora-1578錯誤
alter session set events '10231 trace name context forever ,level 10'
執行這個之後發現user_table中skip_corrupt，還是disable，不知道為什麼這個樣（11g）
但是可以全表執行全表掃描。
利用CTAS重建table和相關index。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24237320/viewspace-732382/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 恢復資料,資料塊恢復
- RAC資料庫恢復到單例項資料庫資料庫單例
- 【資料庫資料恢復】Oracle ASM例項無法掛載的資料恢復案例資料庫資料恢復OracleASM
- 【資料庫資料恢復】ASM例項不能掛載的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 判斷資料庫是否需要例項恢復資料庫
- 恢復RAC資料庫到單例項（ASM）資料庫單例ASM
- 硬碟資料恢復例項全解(1) (轉)硬碟資料恢復
- rman 恢復資料塊
- rman恢復資料塊
- Oracle例項恢復Oracle
- 單例項恢復RAC資料庫步驟（三）單例資料庫
- 單例項恢復RAC資料庫步驟（二）單例資料庫
- 單例項恢復RAC資料庫步驟（一）單例資料庫
- Oracle例項恢復和介質恢復Oracle
- 資料庫的備份與恢復分析及例項資料庫
- Oracle資料檔案損壞恢復例項二則Oracle
- 例項恢復擴充套件案例-手工產生髒塊套件
- SCN、Checkpoint、例項恢復介質恢復理解
- oracle database 例項恢復和介質恢復OracleDatabase
- RMAN blockrecover命令恢復資料塊BloC
- 基於資料塊的恢復
- RAC 資料庫恢復到單例項下並且基於時間點恢復資料庫單例
- 將RAC備份集恢復為單例項資料庫單例資料庫
- rac恢復到單例項單例
- Oracle例項恢復機制Oracle
- 單例項恢復至RAC單例
- Oracle 例項恢復詳解Oracle
- oracle壞塊修復例項Oracle
- bbed_recover:恢復資料塊資料庫資料庫
- oracle asm 資料塊重構恢復OracleASM
- 【RAC】將單例項備份集恢復為rac資料庫單例資料庫
- 【RAC】將RAC備份集恢復為單例項資料庫單例資料庫
- 單例項環境利用備份恢復RAC資料庫（四）單例資料庫
- 單例項環境利用備份恢復RAC資料庫（三）單例資料庫
- 單例項環境利用備份恢復RAC資料庫（二）單例資料庫
- 單例項環境利用備份恢復RAC資料庫（一）單例資料庫
- catalog損壞情況下的資料庫恢復例項資料庫
- --bbed_recover:恢復資料塊資料庫（mybbed）資料庫