坏块模拟实验1

********************************************************************************
windows  坏块模拟
********************************************************************************
1：建力测试表空间
SQL> select *from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

create tablespace test_block_corruption datafile 'd:\test_block_corruption01.dbf' size 25M reuse;

2：建立测试表
conn scott/tiger
drop table t1 purge;
create table t1(scn int) tablespace test_block_corruption;

3：插入测试数据
insert into t1
  select dbms_flashback.get_system_change_number
    from (select rownum from dba_objects where rownum <= 100) a,
         (select rownum from dba_objects where rownum <= 100) b;
 commit;

4：查看需要编辑的数据块位置

SQL> select owner,blocks,header_file,header_block,pct_increase from dba_segments where wner='SCOTT'
and segment_NAME='T1';

OWNER                              BLOCKS HEADER_FILE HEADER_BLOCK PCT_INCREASE
------------------------------ ---------- ----------- ------------ ------------
SCOTT                                  24           7           11
SQL> update t1 set scn='123' where rownum=1;

1） 用数据库寻址来确定位置 （18000 行）实验不成功 文件头

SQL> select dbms_rowid.rowid_block_number(rowid) block_no,t1.* from t1 where scn=123;

  BLOCK_NO        SCN
---------- ----------
        12        123

SQL> select to_char(12*8*1024,'xxxxxx') from dual;

TO_CHAR
-------
  18000

SQL>
2） 用dump 来确定位置

select dump(123,16) from dual;
16进制 搜索
进入16进制模式搜索03c2 0218
改成03c2 0216（为什么要修改18？？？）

地址为 18770h

16进制 搜索
进入16进制模式搜索03c2 0218
改成03c2 0216

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select  * from t1 where scn=123;
select  * from t1 where scn=123
               *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 7, 块号 12)
ORA-01110: 数据文件 7: 'D:\TEST_BLOCK_CORRUPTION01.DBF'

5： 用dbv 验证坏块
SQL>  host dbv file='d:\test_block_corruption01.dbf' blocksize=8192;

DBVERIFY: Release 10.2.0.1.0 - Production on 星期五 3月 4 17:17:58 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = d:\test_block_corruption01.dbf
页 12 标记为损坏
Corrupt block relative dba: 0x01c0000c (file 7, block 12)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x01c0000c
 last change scn: 0x0000.0028ddd5 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xddd50601
 check value in block header: 0x349a
 computed block checksum: 0xe00

 

DBVERIFY - 验证完成

检查的页总数: 3200
处理的页总数 (数据): 19
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其它): 12
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 3168
标记为损坏的总页数: 1
流入的页总数: 0
最高块 SCN            : 2677793 (0.2677793)

SQL>

6：用ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE 验证坏块
-- 设定trc 输出
SQL> alter session set events '10231 trace name context forever,level 16';

会话已更改。

SQL> analyze table t1 validate structure cascade;
analyze table t1 validate structure cascade
*
第 1 行出现错误:
ORA-01498: 块检查失败 - 请参阅跟踪文件

SQL>
SQL> SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
  2         p.spid || '.trc' trace_file_name
  3    FROM (SELECT p.spid
  4            FROM v$mystat m, v$session s, v$process p
  5           WHERE m.statistic# = 1
  6             AND s.SID = m.SID
  7             AND p.addr = s.paddr) p,
  8         (SELECT t.INSTANCE
  9            FROM v$thread t, v$parameter v
 10           WHERE v.NAME = 'thread'
 11             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
 12         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

TRACE_FILE_NAME
----------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\SMSDR\UDUMP/smsdr_ora_3356.trc

smsdr_ora_3356.trc 内容
Corrupt block relative dba: 0x01c0000c (file 7, block 12)
Bad check value found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x01c0000c
 last change scn: 0x0000.0028ddd5 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xddd50601
 check value in block header: 0x349a
 computed block checksum: 0xe00
Reread of rdba: 0x01c0000c (file 7, block 12) found same corrupted data
skipping corrupted block at rdba: 0x01c0000c

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13095129/viewspace-688447/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13095129/viewspace-688447/