模擬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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- UIModalPresentationStyle 各種型別的區別UI型別
- javascript 判斷各種資料的型別JavaScript型別
- oracle redo各種狀態（inactive、active、current）損壞的處理方式Oracle Redo
- oracle壞塊(二)Oracle
- 各種型別的API介面，應有盡有型別API
- Oracle壞塊處理Oracle
- 各種二埠濾波器網路模擬遇到的問題
- 圖解 SQL 裡的各種 JOIN圖解SQL
- 域名字尾有哪些型別？各種域名字尾名的區別型別
- 【slam】ubuntu中各種型別軟體包的安裝方法SLAMUbuntu型別
- 關於各種List型別特點以及使用的場景型別
- 滿足各種型別的排版：Affinity Publisher 免啟用版型別
- 品類洞察 | 模擬經營品類有多少種玩法型別？型別
- golang — mgo解析各種資料型別分析Golang資料型別
- 網站模擬登陸的滑塊驗證碼識別網站
- SQL稽核 | 如何快速使用 SQLE 稽核各種型別的資料庫SQL型別資料庫
- 一文看懂合約各種型別的交易系統模式型別模式
- Redis的各種資料型別到底能玩出什麼花兒？Redis資料型別
- 聊聊各種測試的區別
- C#模擬C++模板特化對型別的值的支援C#C++型別
- Unreal 各種指標型別是怎麼回事Unreal指標型別
- 如何選擇各種型別資料庫？- Raj型別資料庫
- Oracle日常問題-壞塊修復Oracle
- truncate操作消除ORACLE SEG壞塊解析Oracle
- 各種語言裡獲取當前模組的方法：ABAP，ABSL，C，nodejsNodeJS
- 面試題裡的那些各種手寫面試題
- 各種檔案用JS轉Base64之後的data型別JS型別
- 說說 Spring 表示式語言（SpEL）中的各種表示式型別Spring型別
- oracle知識整理(1) union和union all的區別,left join和right join的區別(各種join的區別)Oracle
- Oracle的number資料型別Oracle資料型別
- 一張圖解釋各種資料庫型別圖解資料庫型別
- 一張圖瀏覽資料庫各種型別資料庫型別
- 詳解Apache Hudi如何配置各種型別分割槽Apache型別
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- Dart 裡的型別系統Dart型別
- JS裡的資料型別JS資料型別
- Oracle各種版本下“示例資料庫的建立”的建立Oracle資料庫
- 如何處理Oracle資料庫中的壞塊問題(轉)Oracle資料庫