模擬oracle裡的各種型別的壞塊
V$DATABASE_BLOCK_CORRUPTION.corruption_type 有以下幾種
■ ALL ZERO - Block header on disk contained only zeros. The block may
be valid if it was never filled and if it is in an Oracle7 file. The buffer
will be reformatted to the Oracle8 standard for an empty block.
■ FRACTURED - Block header looks reasonable, but the front and back of
the block are different versions.
■ CHECKSUM - optional check value shows that the block is not
self-consistent. It is impossible to determine exactly why the check
value fails, but it probably fails because sectors in the middle of the
block are from different versions.
■ CORRUPT - Block is wrongly identified or is not a data block (for
example, the data block address is missing)
■ LOGICAL - Block is logically corrupt
■ NOLOGGING - Block does not have redo log entries (for example,
NOLOGGING operations on primary database can introduce this type of
corruption on a physical standby)
下面分別模擬一下這幾種corrupt情況
####建立測試表空間
create tablespace ts0608 datafile '/oradata06/testaaaaa/ts0608_1.dbf' size 32m;
create table scott.t0608_1 tablespace ts0608 as select * from all_users;
alter system flush buffer_cache;
set linesize 120 pagesize 30
select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.t0608_1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
9 131
col name format a50
set linesize 120
select ts.ts#,df.file#,df.name from v$datafile df,v$tablespace ts where ts.ts#=df.ts# and ts.name='TS0608';
TS# FILE# NAME
---------- ---------- --------------------------------------------------
15 9 /oradata06/testaaaaa/ts0608_1.dbf
###備份資料檔案供後面作恢復
backup datafile
■ 模擬 ALL ZERO
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
---使用dd
oracle@jq570322b:/home/oracle>dd if=/dev/zero of=/oradata06/testaaaaa/ts0608_1.dbf bs=8192 count=1 seek=131 conv=notrunc
1+0 records in.
1+0 records out.
SYS@tstdb1-SQL> alter system flush buffer_cache;
System altered.
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 ALL ZERO
■ 模擬 FRACTURED
---先恢復datafile 9
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
SYS@tstdb1-SQL> select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.t0608_1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
9 131
---使用bbed修改tailchk
BBED> set filename '/oradata06/testaaaaa/ts0608_1.dbf'
FILENAME /oradata06/testaaaaa/ts0608_1.dbf
BBED> set block 131;
BLOCK# 131
BBED> show
FILE# 0
BLOCK# 131
OFFSET 0
DBA 0x00000000 (0 0,131)
FILENAME /oradata06/testaaaaa/ts0608_1.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> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print tailchk
ub4 tailchk @8188 0xd96b0602
BBED> print bas_kcbh
ub4 bas_kcbh @8 0x637fd96b
BBED> print seq_kcbh
ub1 seq_kcbh @14 0x02
BBED> print type_kcbh
ub1 type_kcbh @0 0x06
tailchk=bas_kcbh(後4個數字)+type_kcbh+seq_kcbh
--修改tailchk
BBED> set offset 8188
OFFSET 8188
BBED> dump /v count 10
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 8188 to 8191 Dba:0x00000000
-------------------------------------------------------
d96b0602 l .k..
<16 bytes per line>
BBED> set mode edit
MODE Edit
BBED> modify /x d96a offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
d96a0602
<32 bytes per line>
BBED> sum block 131 apply
Check value for File 0, Block 131:
current = 0x0b40, required = 0x0b40
BBED> print tailchk
ub4 tailchk @8188 0xd96a0602
SYS@tstdb1-SQL> alter system flush buffer_cache;
System altered.
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
----validate觸發v$database_block_corruption的更新
RMAN> validate datafile 9;
Starting validate at 20150608 16:06:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=203 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 FAILED 0 61 4097 12723362453878
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 1
Index 0 0
Other 0 4034
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_1311996.trc for details
Finished validate at 20150608 16:06:46
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 FRACTURED
■ 模擬 CHECKSUM
---恢復datafile 9
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
---使用BBED修改chkval_kcbh
BBED> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print chkval_kcbh
ub2 chkval_kcbh @16 0x0b41
BBED> set offset 16
OFFSET 16
BBED> dump /v count 10
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 16 to 25 Dba:0x00000000
-------------------------------------------------------
0b410000 01000000 0000 l .A........
<16 bytes per line>
BBED> modify /x 0b42
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 16 to 25 Dba:0x00000000
------------------------------------------------------------------------
0b420000 01000000 0000
<32 bytes per line>
alter system flush buffer_cache;
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
RMAN> validate datafile 9;
Starting validate at 20150608 16:26:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 61 4097 12723362453878
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1
Index 0 0
Other 0 4034
Finished validate at 20150608 16:26:12
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 CHECKSUM
■ CORRUPT
---先恢復datafile 9
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
---BBED修改type_kcbh
BBED> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print type_kcbh
ub1 type_kcbh @0 0x06
BBED> set offset 0
OFFSET 0
BBED> dump /v count 10
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 0 to 9 Dba:0x00000000
-------------------------------------------------------
06a20000 02400083 637f l .....@..c.
<16 bytes per line>
BBED> modify /x ff
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x ff
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 0 to 9 Dba:0x00000000
------------------------------------------------------------------------
ffa20000 02400083 637f
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 131:
current = 0xf241, required = 0xf241
alter system checkpoint;
RMAN> validate datafile 9;
Starting validate at 20150608 16:39:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 FAILED 0 61 4097 12723362453878
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 1 4035
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_1311996.trc for details
Finished validate at 20150608 16:39:17
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 CORRUPT
■ 模擬LOGICAL
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
---使用BBED修改kdbhavsp
BBED> set filename '/oradata06/testaaaaa/ts0608_1.dbf'
FILENAME /oradata06/testaaaaa/ts0608_1.dbf
BBED> set block 131
BLOCK# 131
BBED> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print /x kdbhavsp
sb2 kdbhavsp @134 0x1d74
BBED> set mode edit
MODE Edit
BBED> modify /x 3000
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 134 to 645 Dba:0x00000000
------------------------------------------------------------------------
30001d74 00000015 1f661f4f 1f391f24 1f0f1efa 1ee81ed3 1eae1e96 1e7e1e69
BBED> sum apply
Check value for File 0, Block 131:
current = 0x2635, required = 0x2635
alter system flush buffer_cache;
---不加check logical的validate檢查結果一切正常
RMAN> validate datafile 9;
Starting validate at 20150609 05:41:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=334 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 57 4097 12723362475052
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 4034
Finished validate at 20150609 05:41:44
---只有check logical才能發現logical corruption
RMAN> validate check logical datafile 9;
Starting validate at 20150609 05:43:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=532 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 FAILED 0 57 4097 12723362475052
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 5
Index 0 0
Other 0 4034
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_43188356.trc for details
Finished validate at 20150609 05:43:21
---v$database_block_corrupt並沒有將CORRUPTION_TYPE標記為logical，依舊是corrupt
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
9 131 1 12723362453867 CORRUPT 0
---validate執行期間會將檢測到的corrupt block資訊更新到alert.log和trace file，從alert & tracefile的資訊清楚的表明是logical corruption
***alert.log
Tue Jun 09 05:43:20 2015
Error backing up file 9, block 131: logical corruption
***Trace file
Block Checking: DBA = 37748867, Block Type = KTB-managed data block
data header at 0x110c7b07c
kdbchk: avsp bad (12288)
Error backing up file 9, block 131: logical corruption
---validate命令是將corruption的具體資訊存放在.trc檔案，dbverify能直接在結果中輸出corrupt的原因
tstdb1@jq570322b:/home/tstdb1>dbv file=/oradata06/testaaaaa/ts0608_1.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Tue Jun 9 05:50:34 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata06/testaaaaa/ts0608_1.dbf
Block Checking: DBA = 37748867, Block Type = KTB-managed data block
data header at 0x11038707c
kdbchk: avsp bad (12288)
Page 131 failed with check code 6126
DBVERIFY - Verification complete
Total Pages Examined : 4096
Total Pages Processed (Data) : 5
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4034
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 57
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1669344300 (2962.1669344300)
■ NOLOGGING
alter table scott.t0608_1 nologging;
insert /*+ append_values */ into scott.t0608_1 values('NEWUSER',999,sysdate);
commit;
alter system flush buffer_cache;
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
22
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 136)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
RMAN> validate datafile 9;
Starting validate at 20150609 06:00:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 1 56 4097 12723362514981
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 4035
Finished validate at 20150609 06:00:19
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
9 136 1 12723362514962 NOLOGGING
■ ALL ZERO - Block header on disk contained only zeros. The block may
be valid if it was never filled and if it is in an Oracle7 file. The buffer
will be reformatted to the Oracle8 standard for an empty block.
■ FRACTURED - Block header looks reasonable, but the front and back of
the block are different versions.
■ CHECKSUM - optional check value shows that the block is not
self-consistent. It is impossible to determine exactly why the check
value fails, but it probably fails because sectors in the middle of the
block are from different versions.
■ CORRUPT - Block is wrongly identified or is not a data block (for
example, the data block address is missing)
■ LOGICAL - Block is logically corrupt
■ NOLOGGING - Block does not have redo log entries (for example,
NOLOGGING operations on primary database can introduce this type of
corruption on a physical standby)
下面分別模擬一下這幾種corrupt情況
####建立測試表空間
create tablespace ts0608 datafile '/oradata06/testaaaaa/ts0608_1.dbf' size 32m;
create table scott.t0608_1 tablespace ts0608 as select * from all_users;
alter system flush buffer_cache;
set linesize 120 pagesize 30
select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.t0608_1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
9 131
col name format a50
set linesize 120
select ts.ts#,df.file#,df.name from v$datafile df,v$tablespace ts where ts.ts#=df.ts# and ts.name='TS0608';
TS# FILE# NAME
---------- ---------- --------------------------------------------------
15 9 /oradata06/testaaaaa/ts0608_1.dbf
###備份資料檔案供後面作恢復
backup datafile
■ 模擬 ALL ZERO
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
---使用dd
oracle@jq570322b:/home/oracle>dd if=/dev/zero of=/oradata06/testaaaaa/ts0608_1.dbf bs=8192 count=1 seek=131 conv=notrunc
1+0 records in.
1+0 records out.
SYS@tstdb1-SQL> alter system flush buffer_cache;
System altered.
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 ALL ZERO
■ 模擬 FRACTURED
---先恢復datafile 9
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
SYS@tstdb1-SQL> select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from scott.t0608_1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
9 131
---使用bbed修改tailchk
BBED> set filename '/oradata06/testaaaaa/ts0608_1.dbf'
FILENAME /oradata06/testaaaaa/ts0608_1.dbf
BBED> set block 131;
BLOCK# 131
BBED> show
FILE# 0
BLOCK# 131
OFFSET 0
DBA 0x00000000 (0 0,131)
FILENAME /oradata06/testaaaaa/ts0608_1.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> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print tailchk
ub4 tailchk @8188 0xd96b0602
BBED> print bas_kcbh
ub4 bas_kcbh @8 0x637fd96b
BBED> print seq_kcbh
ub1 seq_kcbh @14 0x02
BBED> print type_kcbh
ub1 type_kcbh @0 0x06
tailchk=bas_kcbh(後4個數字)+type_kcbh+seq_kcbh
--修改tailchk
BBED> set offset 8188
OFFSET 8188
BBED> dump /v count 10
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 8188 to 8191 Dba:0x00000000
-------------------------------------------------------
d96b0602 l .k..
<16 bytes per line>
BBED> set mode edit
MODE Edit
BBED> modify /x d96a offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
d96a0602
<32 bytes per line>
BBED> sum block 131 apply
Check value for File 0, Block 131:
current = 0x0b40, required = 0x0b40
BBED> print tailchk
ub4 tailchk @8188 0xd96a0602
SYS@tstdb1-SQL> alter system flush buffer_cache;
System altered.
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
----validate觸發v$database_block_corruption的更新
RMAN> validate datafile 9;
Starting validate at 20150608 16:06:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=203 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 FAILED 0 61 4097 12723362453878
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 1
Index 0 0
Other 0 4034
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_1311996.trc for details
Finished validate at 20150608 16:06:46
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 FRACTURED
■ 模擬 CHECKSUM
---恢復datafile 9
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
---使用BBED修改chkval_kcbh
BBED> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print chkval_kcbh
ub2 chkval_kcbh @16 0x0b41
BBED> set offset 16
OFFSET 16
BBED> dump /v count 10
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 16 to 25 Dba:0x00000000
-------------------------------------------------------
0b410000 01000000 0000 l .A........
<16 bytes per line>
BBED> modify /x 0b42
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 16 to 25 Dba:0x00000000
------------------------------------------------------------------------
0b420000 01000000 0000
<32 bytes per line>
alter system flush buffer_cache;
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
RMAN> validate datafile 9;
Starting validate at 20150608 16:26:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 61 4097 12723362453878
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1
Index 0 0
Other 0 4034
Finished validate at 20150608 16:26:12
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 CHECKSUM
■ CORRUPT
---先恢復datafile 9
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
---BBED修改type_kcbh
BBED> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print type_kcbh
ub1 type_kcbh @0 0x06
BBED> set offset 0
OFFSET 0
BBED> dump /v count 10
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 0 to 9 Dba:0x00000000
-------------------------------------------------------
06a20000 02400083 637f l .....@..c.
<16 bytes per line>
BBED> modify /x ff
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
MODE Edit
BBED> modify /x ff
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 0 to 9 Dba:0x00000000
------------------------------------------------------------------------
ffa20000 02400083 637f
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 131:
current = 0xf241, required = 0xf241
alter system checkpoint;
RMAN> validate datafile 9;
Starting validate at 20150608 16:39:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 FAILED 0 61 4097 12723362453878
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 1 4035
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_1311996.trc for details
Finished validate at 20150608 16:39:17
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
9 131 1 0 CORRUPT
■ 模擬LOGICAL
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
21
---使用BBED修改kdbhavsp
BBED> set filename '/oradata06/testaaaaa/ts0608_1.dbf'
FILENAME /oradata06/testaaaaa/ts0608_1.dbf
BBED> set block 131
BLOCK# 131
BBED> map /v
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[21] @142
ub1 freespace[7540] @184
ub1 rowdata[464] @7724
ub4 tailchk @8188
BBED> print /x kdbhavsp
sb2 kdbhavsp @134 0x1d74
BBED> set mode edit
MODE Edit
BBED> modify /x 3000
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oradata06/testaaaaa/ts0608_1.dbf (0)
Block: 131 Offsets: 134 to 645 Dba:0x00000000
------------------------------------------------------------------------
30001d74 00000015 1f661f4f 1f391f24 1f0f1efa 1ee81ed3 1eae1e96 1e7e1e69
BBED> sum apply
Check value for File 0, Block 131:
current = 0x2635, required = 0x2635
alter system flush buffer_cache;
---不加check logical的validate檢查結果一切正常
RMAN> validate datafile 9;
Starting validate at 20150609 05:41:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=334 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 57 4097 12723362475052
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 4034
Finished validate at 20150609 05:41:44
---只有check logical才能發現logical corruption
RMAN> validate check logical datafile 9;
Starting validate at 20150609 05:43:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=532 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 FAILED 0 57 4097 12723362475052
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 5
Index 0 0
Other 0 4034
validate found one or more corrupt blocks
See trace file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_ora_43188356.trc for details
Finished validate at 20150609 05:43:21
---v$database_block_corrupt並沒有將CORRUPTION_TYPE標記為logical，依舊是corrupt
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
9 131 1 12723362453867 CORRUPT 0
---validate執行期間會將檢測到的corrupt block資訊更新到alert.log和trace file，從alert & tracefile的資訊清楚的表明是logical corruption
***alert.log
Tue Jun 09 05:43:20 2015
Error backing up file 9, block 131: logical corruption
***Trace file
Block Checking: DBA = 37748867, Block Type = KTB-managed data block
data header at 0x110c7b07c
kdbchk: avsp bad (12288)
Error backing up file 9, block 131: logical corruption
---validate命令是將corruption的具體資訊存放在.trc檔案，dbverify能直接在結果中輸出corrupt的原因
tstdb1@jq570322b:/home/tstdb1>dbv file=/oradata06/testaaaaa/ts0608_1.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Tue Jun 9 05:50:34 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oradata06/testaaaaa/ts0608_1.dbf
Block Checking: DBA = 37748867, Block Type = KTB-managed data block
data header at 0x11038707c
kdbchk: avsp bad (12288)
Page 131 failed with check code 6126
DBVERIFY - Verification complete
Total Pages Examined : 4096
Total Pages Processed (Data) : 5
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4034
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 57
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1669344300 (2962.1669344300)
■ NOLOGGING
alter table scott.t0608_1 nologging;
insert /*+ append_values */ into scott.t0608_1 values('NEWUSER',999,sysdate);
commit;
alter system flush buffer_cache;
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
COUNT(*)
----------
22
run
{sql 'alter database datafile 9 offline';
restore datafile 9;
recover datafile 9;
sql 'alter database datafile 9 online';
}
SYS@tstdb1-SQL> select count(*) from scott.t0608_1;
select count(*) from scott.t0608_1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 136)
ORA-01110: data file 9: '/oradata06/testaaaaa/ts0608_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SYS@tstdb1-SQL> select * from v$database_block_corruption;
no rows selected
RMAN> validate datafile 9;
Starting validate at 20150609 06:00:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=/oradata06/testaaaaa/ts0608_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 1 56 4097 12723362514981
File Name: /oradata06/testaaaaa/ts0608_1.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 4035
Finished validate at 20150609 06:00:19
SYS@tstdb1-SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
9 136 1 12723362514962 NOLOGGING
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1692361/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle壞塊模擬處理（筆記）Oracle筆記
- Oracle中模擬修復資料塊損壞Oracle
- UIModalPresentationStyle 各種型別的區別UI型別
- 胃炎的各種型別 - 肝氣犯胃型...型別
- 各種體虛型別的改善方式型別
- 各種失敗型別型別
- iOS模擬各種網路狀態iOS
- javascript 判斷各種資料的型別JavaScript型別
- 各種索引型別發生的條件索引型別
- Oracle上的邏輯壞塊和物理壞塊Oracle
- oracle裡long型別的總結Oracle型別
- 各種型別檔案頭型別
- oracle的三種型別的程式Oracle型別
- 各種型別的API介面，應有盡有型別API
- 模擬壞塊故障並搶救資料（一.普通表）
- Oracle supplemental log的兩種型別Oracle型別
- Oracle 失敗的6種型別Oracle型別
- 各種時間型別的轉換及比較型別
- 各種二埠濾波器網路模擬遇到的問題
- Oracle 的各種表Oracle
- 品類洞察 | 模擬經營品類有多少種玩法型別？型別
- 是用bbed工具模擬對塊的破壞，並使用rman bock recover進行塊恢復
- [20150811]模擬壞塊處理.txt
- ORA-01578 壞塊原因模擬及解決方法
- 圖解 SQL 裡的各種 JOIN圖解SQL
- CBO各種型別成本計算公式型別公式
- oracle壞塊(二)Oracle
- Oracle資料庫壞塊(corruption)-物理壞塊Oracle資料庫
- oracle redo各種狀態（inactive、active、current）損壞的處理方式Oracle Redo
- oracle壞塊問題的解決Oracle
- 域名字尾有哪些型別？各種域名字尾名的區別型別
- oracle裡long型別詳解Oracle型別
- C#模擬C++模板特化對型別的值的支援C#C++型別
- 面試題裡的那些各種手寫面試題
- golang — mgo解析各種資料型別分析Golang資料型別
- PHP memcached 各種資料型別儲存PHP資料型別
- 滿足各種型別的排版：Affinity Publisher 免啟用版型別
- python isinstance 判斷各種型別的小細節Python型別