测试环境:11.2.0.4
1.1 创建测试表
- --Create Table t_test
- conn jingyu/jingyu
- drop table t_test purge;create tablet_test(id number, namechar(2000));
- --Insert data
- insertintot_testvalues(1,'alfred 1');insertintot_testvalues(2,'alfred 2');insertintot_testvalues(3,'alfred 3');insertintot_testvalues(4,'alfred 4');insertintot_testvalues(5,'alfred 5');insertintot_testvalues(6,'alfred 6');insertintot_testvalues(7,'alfred 7');insertintot_testvalues(8,'alfred 8');insertintot_testvalues(9,'alfred 9');insertintot_testvalues(10,'alfred 10');
- commit;
- --Create Index
- create index idx_t_test_1 ont_test(id);
- --Statistics
- --analyze table t_test compute statistics;
- EXEC DBMS_STATS.gather_table_stats('JINGYU','T_TEST', cascade => TRUE);
1.2 查询表中每一行对应的文件号和块号
- --查询t_test表中每一行对应的文件和块号selectblocksfromuser_tableswheretable_name ='T_TEST';
- selectdbms_rowid.rowid_relative_fno(rowid) R_FNO,
- dbms_rowid.rowid_block_number(rowid) b_no, idfromt_testorder by 1,2;
- --这里对象占用块数的查询前后不匹配,与书中不符,但不影响实验,结果如下:
- SQL>selectblocksfromuser_tableswheretable_name ='T_TEST';BLOCKS
- ----------5SQL>selectdbms_rowid.rowid_relative_fno(rowid) R_FNO,2dbms_rowid.rowid_block_number(rowid) b_no, id3 fromt_test4 order by 1,2;
- R_FNO B_NO ID
- ---------- ---------- ----------6 3892 1
- 6 3892 2
- 6 3892 3
- 6 3893 4
- 6 3893 5
- 6 3893 6
- 6 3894 7
- 6 3894 8
- 6 3894 9
- 6 3895 10
- 10rows selected.
1.3 使用 bbed 工具模拟破坏 6 号数据文件的 3893 数据块
关于 bbed 的编译和使用可参考
1.3.1 准备 bbed 配置文件:编辑 / tmp/bbed.par 参数配置文件
- --编辑/tmp/bbed.par参数配置文件
- blocksize=8192listfile=/tmp/listfile.txt
- mode=edit
- --编辑/tmp/listfile.txt文件
- SQL> select file#||' '||name||' '||bytes from v$datafile ;FILE#||''||NAME||''||BYTES--------------------------------------------------------------------------------1+DATA1/jyzhao/datafile/system.256.919998779 786432000
- 2+DATA1/jyzhao/datafile/sysaux.257.919998781 891289600
- 3+DATA1/jyzhao/datafile/undotbs1.258.919998783 125829120
- 4+DATA1/jyzhao/datafile/users.259.919998789 13107200
- 5+DATA1/jyzhao/datafile/undotbs2.264.919999419 78643200
- 6+DATA1/jyzhao/datafile/dbs_d_jingyu.268.927427887 104857600
- 7+DATA1/jyzhao/datafile/dbs_i_jingyu.270.927427891 31457280
- 8+DATA1/jyzhao/datafile/soe.278.939295201 104857600
- 9+DATA1/jyzhao/datafile/dbs_d_jingyu.277.939295229 104857600
由于 bbed 不能直接操作 ASM 里面的数据文件,所以需把对应的数据文件转储出来:
- RMAN> backupascopy datafile6format'/opt/app/Oracle/datafile/dbs_d_jingyu01.dbf';
- --mount
- RMAN>switchdatafile6to copy;
- RMAN> recover datafile6;
- RMAN> alter database open;
- --编辑/tmp/listfile.txt,添加6号文件信息
- vi/tmp/listfile.txt6 /opt/app/oracle/datafile/dbs_d_jingyu01.dbf104857600SQL> alter system checkpoint;
测试调用 bbed 正常:
- --调用bbedcd /tmp
- bbed parfile=bbed.par
1.3.2 破坏数据文件 6 的 3893 数据块
- [oracle@jyrac1 tmp]$ bbed parfile=bbed.par
- Password:
- BBED:Release 2.0.0.0.0- Limited ProductiononWed Mar22 15:28:00 2017Copyright (c)1982,2011,Oracle and/orits affiliates. All rights reserved.
- ************* !!!For OracleInternalUse only!!! ***************
- BBED>setdba6,3893DBA0x01800f35(25169717 6,3893)
- BBED> find /calfredFile: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:2170 to 2681Dba:0x01800f35
- ------------------------------------------------------------------------
- 616c667265642036 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020<32 bytesper line>
- BBED>f
- File: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:4179 to 4690Dba:0x01800f35
- ------------------------------------------------------------------------
- 616c667265642035 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020<32 bytesper line>
- BBED>f
- File: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:6188 to 6699Dba:0x01800f35
- ------------------------------------------------------------------------
- 616c667265642034 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020<32 bytesper line>
- BBED>fBBED-00212:search string not foundBBED> dump /v dba6,3893 offset 2170 count 64
- File: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:2170 to 2233Dba:0x01800f35
- -------------------------------------------------------
- 616c667265642036 20202020 20202020 lalfred6
- 20202020 20202020 20202020 20202020 l
- 20202020 20202020 20202020 20202020 l
- 20202020 20202020 20202020 20202020 l<16 bytesper line>
- BBED> dump /v dba6,3893 offset 4179 count 64
- File: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:4179 to 4242Dba:0x01800f35
- -------------------------------------------------------
- 616c667265642035 20202020 20202020 lalfred5
- 20202020 20202020 20202020 20202020 l
- 20202020 20202020 20202020 20202020 l
- 20202020 20202020 20202020 20202020 l<16 bytesper line>
- BBED> dump /v dba6,3893 offset 6188 count 64
- File: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:6188 to 6251Dba:0x01800f35
- -------------------------------------------------------
- 616c667265642034 20202020 20202020 lalfred4
- 20202020 20202020 20202020 20202020 l
- 20202020 20202020 20202020 20202020 l
- 20202020 20202020 20202020 20202020 l<16 bytesper line>
- BBED>modify 901010dba6,3893
- Warning:contents ofprevious BIFILE will be lost. Proceed? (Y/N) yFile: /opt/app/oracle/datafile/dbs_d_jingyu01.dbf (6)Block:3893Offsets:6188 to 6251Dba:0x01800f35
- ------------------------------------------------------------------------
- 0dbf927265642034 20202020 20202020 20202020 20202020 20202020 20202020
- 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020<32 bytesper line>
- BBED>exit
1.3.3 使用 dbv 检查文件
- [oracle@jyrac1 tmp]$ dbv file=/opt/app/oracle/datafile/dbs_d_jingyu01.dbf
- DBVERIFY:Release 11.2.0.4.0- ProductiononWed Mar22 15:31:50 2017Copyright (c)1982,2011,Oracle and/orits affiliates. All rights reserved.
- DBVERIFY - Verificationstarting:FILE= /opt/app/oracle/datafile/dbs_d_jingyu01.dbf
- Page3893 ismarked corrupt
- Corruptblock relativedba:0x01800f35(file 6,block 3893)
- Badcheck value foundduring dbv:Data inbadblock:type:6 format:2rdba:0x01800f35
- last change scn:0x0000.003b68be seq:0x1flg:0x06spare1:0x0spare2:0x0spare3:0x0consistencyvalue intail:0x68be0601
- check value in blockheader:0x6485computedblock checksum:0xd398DBVERIFY - VerificationcompleteTotal Pages Examined :12800Total Pages Processed (Data) :2512Total Pages Failing (Data) :0Total Pages Processed (Index):989Total Pages Failing (Index):0Total Pages Processed (Other):9025Total Pages Processed (Seg) :0Total Pages Failing (Seg) :0Total PagesEmpty:273Total Pages Marked Corrupt :1Total Pages Influx :0Total Pages Encrypted :0Highestblock SCN:3893454(0.3893454)
- [oracle@jyrac1 tmp]$
数据库有有效的 RMAN 备份,那么很简单,直接恢复损害数据块即可。RMAN> blockrecover datafile 6 block 3893;
常规恢复输出类似下面这样:
- RMAN> blockrecover datafile6block3893;
- Starting recover at22-MAR-17using target database control file instead of recovery catalog
- allocatedchannel:ORA_DISK_1
- channelORA_DISK_1:SID=148instance=jyzhao1 device type=DISK
- channelORA_DISK_1:restoring block(s)
- channelORA_DISK_1:specifying block(s) to restore from backup set
- restoring blocks of datafile00006channelORA_DISK_1:reading from backup piece +FRA1/jyzhao/backupset/2017_03_22/nnndf0_tag20170322t123922_0.463.939299963channelORA_DISK_1:piece handle=+FRA1/jyzhao/backupset/2017_03_22/nnndf0_tag20170322t123922_0.463.939299963tag=TAG20170322T123922
- channelORA_DISK_1:restored block(s) from backup piece1channelORA_DISK_1:block restore complete, elapsedtime: 00:00:01starting media recovery
- media recovery complete, elapsedtime: 00:00:04Finished recover at22-MAR-17
恢复完成后可以正常访问。
3.1 查看 AFN 和 RFN
绝对数据文件号:AFN 是数据文件在整个系统范围内的编号。相对数据文件号:RFN 是数据文件在表空间范围内的编号。两个文件可能有相同的 RFN,但是不会有相同的 AFN。
获取普通文件的 AFN 和 RFN:select tablespace_name, file_id "AFN", relative_fno "RFN" from dba_data_files;
- SQL>selecttablespace_name, file_id"AFN", relative_fno"RFN" fromdba_data_files;
- TABLESPACE_NAME AFN RFN
- ------------------------------ ---------- ----------
- USERS4 4UNDOTBS13 3SYSAUX2 2SYSTEM1 1UNDOTBS25 5DBS_D_JINGYU6 6DBS_I_JINGYU7 7SOE8 1024DBS_D_JINGYU9 9
- 9rows selected.
注意:实验发现,大文件表空间的 RFN 固定为 1024。
获取临时文件的 AFN 和 RFN:select tablespace_name, file_id + value "AFN", relative_fno "RFN" from dba_temp_files, v$parameter where name = 'db_files';
- SQL>selecttablespace_name, file_id +value "AFN", relative_fno"RFN" fromdba_temp_files, v$parameterwherename ='db_files';
- TABLESPACE_NAME AFN RFN
- ------------------------------ ---------- ----------
- TEMP_JINGYU202 1TEMP201 1
3.2 创建 REPAIR_TABLE 和 ORPHAN_KEY_TABLEREPAIR_TABLE 用来记录错误检查结果,ORPHAN_KEY_TABLE 用来记录表坏块中记录在索引中对应键值。
- --通过如下存储过程创建 REPAIR_TABLE 和 ORPHAN_KEY_TABLE
- --Repair Table
- declare
- begindbms_repair.admin_tables(
- table_name =>'REPAIR_TABLE',
- table_type => dbms_repair.repair_table,action=> dbms_repair.create_action,tablespace=>'USERS');end;
- /selectowner, object_name, object_typefromdba_objectswhereobject_namelike '%REPAIR_TABLE';--Orphan Key Table
- declare
- begindbms_repair.admin_tables(
- table_name =>'ORPHAN_KEY_TABLE',
- table_type => dbms_repair.orphan_table,action=> dbms_repair.create_action,tablespace=>'USERS');end;
- /selectowner, object_name, object_typefromdba_objectswhereobject_namelike '%ORPHAN_KEY_TABLE';--不再需要时,可以通过下面的存储过程删除 REPAIR_TABLE 和 ORPHAN_KEY_TABLE 这两个表:
- --DROP REPAIR_TABLE
- BEGINDBMS_REPAIR.ADMIN_TABLES (
- TABLE_NAME =>'REPAIR_TABLE',
- TABLE_TYPE => dbms_repair.repair_table,ACTION=> dbms_repair.drop_action);END;
- /--DROP ORPHAN_KEY_TABLE
- BEGINDBMS_REPAIR.ADMIN_TABLES (
- TABLE_NAME =>'ORPHAN_KEY_TABLE',
- TABLE_TYPE => dbms_repair.orphan_table,ACTION=> dbms_repair.drop_action);END;
- /
3.3 使用 CHECK_OBJECT 过程检测坏块
- setserveroutputon
- declarerpr_count int;
- begin
- rpr_count :=0;
- dbms_repair.check_object(
- schema_name =>'JINGYU',object_name =>'T_TEST',repair_table_name =>'REPAIR_TABLE',corrupt_count => rpr_count
- );
- dbms_output.put_line('repair count: ' || to_char(rpr_count));
- end;
- /selectobject_name, block_id, corrupt_type, marked_corrupt, corrupt_description, repair_descriptionfromrepair_table;
执行结果:
- SQL>setserveroutputonSQL>declare
- 2rpr_count int;3begin4rpr_count :=0;5dbms_repair.check_object(6schema_name =>'JINGYU',
- 7object_name =>'T_TEST',
- 8repair_table_name =>'REPAIR_TABLE',
- 9corrupt_count => rpr_count10);11dbms_output.put_line('repair count: ' || to_char(rpr_count));
- 12 end;13/
- repair count:1PL/SQL procedure successfully completed.
- SQL>selectobject_name, block_id, corrupt_type, marked_corrupt, corrupt_description, repair_descriptionfromrepair_table;
- OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
- ------------------------------ ---------- ------------ ----------
- CORRUPT_DESCRIPTION
- --------------------------------------------------------------------------------
- REPAIR_DESCRIPTION
- --------------------------------------------------------------------------------
- T_TEST3893 6148 TRUEmark block software corrupt
如果 marked_corrupt 不是 true,则需要使用 fix_corrupt_blocks 过程修复:
- declarefix_count int;
- begin
- fix_count :=0;
- dbms_repair.fix_corrupt_blocks(
- schema_name =>'JINGYU',
- object_name =>'T_TEST',
- object_type => dbms_repair.table_object,
- repair_table_name =>'REPAIR_TABLE',
- fix_count => fix_count
- );
- dbms_output.put_line('fix count: '|| to_char(fix_count));
- end;
- /
这里实验此步骤执行不执行都可以。
3.4 使用 DUMP_ORPHAN_KEYS 过程来保存坏块中的索引键值
select object_name, block_id, marked_corrupt from repair_table;
select index_name from dba_indexes where table_name in (select distinct object_name from repair_table);
- SQL>selectobject_name, block_id, marked_corruptfromrepair_table;
- OBJECT_NAME BLOCK_ID MARKED_COR
- ------------------------------ ---------- ----------
- T_TEST
来源: http://www.linuxidc.com/Linux/2017-04/143143.htm