造数
--1、创建表空间 create tablespace TBS_BLK datafile '/u01/app/oracle/oradata/ORCL/blk.dbf' size 256M; --2、创建用户 create user blk identified by blk default tablespace TBS_BLK quota unlimited on TBS_BLK; --3、授权 grant connect,resource to blk; --4、建表 create table blk.tb_blk(id number primary key,name varchar2(20),savetime date); --5、插入测试数据 begin for i in 1..3000 loop insert into blk.tb_blk values (i,'aaaa'||i,sysdate); end loop; end; --6、提交 commit; --7、统计信息收集 exec dbms_stats.gather_table_stats(/*min sig:*/ownname=>'BLK'/*varchar2*/,tabname=>'tb_blk'/*varchar2*/); |
造损坏的块
SQL> select dbms_rowid.rowid_relative_fno(rowid) R_FNO,dbms_rowid.rowid_block_number(rowid) b_no,id,name from blk.tb_blk where id=1500 order by 1,2;
SQL> select file#,name ,bytes from v$datafile where file#=9;
SQL> show parameter db_block_size;
#cd /tmp/bbed #vi bbed.par blocksize=8192 listfile=/tmp/bbed/listfile.txt mode=edit #vi listfile.txt --根据第4步查询结果 9 /tmp/bbed/blk.dbf 268435456 --如果在ASM中需要通过附2
# cd /tmp/bbed # bbed parfile=/tmp/bbed/bbed.par --输入密码 blockedit BBED> set dba 9,146 --根据显示的Offsets: 6780 to 6787,确认字符串是否为aaaa1500 BBED> dump /v offset 6780 count 8 --破坏块 BBED> modify 801010 dba 9,146 --检查文件是否已破坏 #> dbv /tmp/bbed/blk.dbf Total Pages Marked Corrupt : 1 --查询报错 SQL> select count(1) from blk.tb_blk; ERROR at line 1: ORA-03135: connection lost contact Process ID: 16336 Session ID: 18 Serial number: 875 |
恢复
RMAN> blockrecover datafile 9 block 146; --单个块恢复 RMAN> blockrecover corruption list; --许多块一起恢复(v$database_block_corruption) |
验证
#dbv file=/tmp/blk.dbf Total Pages Marked Corrupt : 0 SQL> select count(1) from blk.tb_blk; |
附1:bbed配置
免积分获取地址:https://download.csdn.net/download/weixin_47745154/21460997 将bbedus.msb、sbbdpt.o、ssbbded.o拷贝到目录 /u01/bbed
#cd /u01/bbed #cp sbbdpt.o ssbbded.o $ORACLE_HOME/rdbms/lib/ #cp bbedus.msb $ORACLE_HOME/rdbms/mesg/ #make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
#bbed -h
|
附2:移动ASM中的数据文件到常规目录
--备份快照 RMAN> backup as copy datafile 9 format ‘/tmp/blk.dbf’; --下线9号数据文件 RMAN> sql 'alter database datafile 9 offline'; --切换到copy RMAN> switch datafile 9 to copy; --打开数据库 RMAN> recover datafile 9; RMAN> sql 'alter database datafile 9 online'; |
附3:另一种破坏数据块
# dd of=/u02/database/usbo/oradata/tbs_tmp.dbf bs=8192 conv=notrunc seek=153 <<EOF New corrupted block! > EOF --153为要破坏的块 |