本文共 9716 字，大约阅读时间需要 32 分钟。
oracle11g关于坏块的修复
一：bbed的命令简单介绍，后面用该工具构造块校验和不一致以达到模拟坏块目的show 显示当前所有配置选项
info：列出当前bbed能处理的文件 set dba fileid,block:设置当前要处理的数据文件id和块号 set dba fileid，block 也可以用 set file fileid 和set block blockno 代替 set offset xxx offset 以set block 块号的设置为基准偏移当前块号的字节数 dump /v 显示当前数据块的内容 默认从当前数据块设置的offset字节数处开始显示。/v 参数显示详细内容，详细到啥境界，后面就晓得了。 find /c 查找的内容 TOP /c 指定查找内容为字符 TOP指定从数据块头部偏移量为0处开始整块搜索搜到一处显示一处，如果要查找下一个该字符则指定f即可 modify /c 要修改内容 默认从set指定的文件、块号、offset偏移字节数处进行修改。 二： 创建测试用的表 SQL> create table jiujian(des varchar(30)) tablespace pos;Table created.
SQL> insert into jiujian values('zhangxu love oracle');
1 row created.
SQL> select * from jiujian;
DES
------------------------------ zhangxu love oracle2 查询表jiujian所在的块
语句：select rowid, dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_row_number(rowid) rowno from jiujian; SQL> select rowid, 2 dbms_rowid.rowid_relative_fno(rowid) rel_fno, 3 dbms_rowid.rowid_block_number(rowid) blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from jiujian;ROWID REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ---------- AAATqzAAFAAAACHAAA 5 135 0 字段BLOCKNO 135即为表jiujian所在的块三：创建bbed的配置文件
[oracle@oracle ~]$ cat bbed.par blocksize=8192 listfile=/oracle/file mode=edit 文本文件file 内容 [oracle@oracle ~]$ cat file 1 /oracle/CRM/system01.dbf 786432000 2 /oracle/CRM/sysaux01.dbf 566231040 3 /oracle/CRM/undotbs01.dbf 104857600 4 /backup/users01.dbf 1827143680 5 /oracle/CRM/pos.dbf 524288000 6 /oracle/CRM/erp.dbf 104857600 7 /oracle/CRM/user01.dbf 5242880 10 /oracle/CRM/undotbs02.dbf 104857600 file 内容由以下语句获取： SQL> select file#||' '||name||' '||bytes from v$datafile;FILE#||''||NAME||''||BYTES
-------------------------------------------------------------------------------- 1 /oracle/CRM/system01.dbf 786432000 2 /oracle/CRM/sysaux01.dbf 566231040 3 /oracle/CRM/undotbs01.dbf 104857600 4 /backup/users01.dbf 1827143680 5 /oracle/CRM/pos.dbf 524288000 6 /oracle/CRM/erp.dbf 104857600 7 /oracle/CRM/user01.dbf 5242880 10 /oracle/CRM/undotbs02.dbf 104857600四 开始用bbed构造坏块
[oracle@oracle ~]$ bbed parfile=bbed.par
Password:blockedit BBED> show 显示配置 FILE# 1 BLOCK# 1 OFFSET 0 DBA 0x00400001 (4194305 1,1) FILENAME /oracle/CRM/system01.dbf BIFILE bifile.bbd LISTFILE /oracle/file BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL NoBBED> info 显示bbed能处理的数据文件编号和数据文件位置
File# Name Size(blks) ----- ---- ---------- 1 /oracle/CRM/system01.dbf 96000 2 /oracle/CRM/sysaux01.dbf 69120 3 /oracle/CRM/undotbs01.dbf 12800 4 /backup/users01.dbf 223040 5 /oracle/CRM/pos.dbf 64000 6 /oracle/CRM/erp.dbf 12800 7 /oracle/CRM/user01.dbf 640 10 /oracle/CRM/undotbs02.dbf 12800BBED> set dba 5,135 设置当前数据文件号和块号
DBA 0x01400087 (20971655 5,135)BBED> show 用show命令确认下设置
FILE# 5 BLOCK# 135 OFFSET 0 DBA 0x01400087 (20971655 5,135) FILENAME /oracle/CRM/pos.dbf BIFILE bifile.bbd LISTFILE /oracle/file BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL NoBBED> find /c zhangxu TOP 查找当前数据块第一处字符zhangxu的位置
File: /oracle/CRM/pos.dbf (5) Block: 135 Offsets: 8169 to 8191 Dba:0x01400087 ------------------------------------------------------------------------ 7a68616e 67787520 6c6f7665 206f7261 636c6501 066c05<32 bytes per line>
从上面输出可看到当前字符串zhangxu位于偏移量8169处 BBED> set offset 8169 更改当前偏移量为 8169 OFFSET 8169BBED> dump /v 从指定偏移量处开始显示数据块内容
File: /oracle/CRM/pos.dbf (5) Block: 135 Offsets: 8169 to 8191 Dba:0x01400087 ------------------------------------------------------- 7a68616e 67787520 6c6f7665 206f7261 l zhangxu love ora 636c6501 066c05 l cle..l.<16 bytes per line>
从上句和下句便可看处dump /v 比dump多详细啊，能看到这个16进制相应的内容。 BBED> dump File: /oracle/CRM/pos.dbf (5) Block: 135 Offsets: 8169 to 8191 Dba:0x01400087 ------------------------------------------------------------------------ 7a68616e 67787520 6c6f7665 206f7261 636c6501 066c05 注意这里连毛都没有<32 bytes per line>
BBED> modify /c jiujian 从偏移量8169处开始用字符jiujian 进行替换。
File: /oracle/CRM/pos.dbf (5) Block: 135 Offsets: 8169 to 8191 Dba:0x01400087 ------------------------------------------------------------------------ 6a69756a 69616e20 6c6f7665 206f7261 636c6501 06b7f5<32 bytes per line>
BBED> dump /v 显示是否替换成功
File: /oracle/CRM/pos.dbf (5) Block: 135 Offsets: 8169 to 8191 Dba:0x01400087 ------------------------------------------------------- 6a69756a 69616e20 6c6f7665 206f7261 l jiujian love ora 636c6501 06b7f5 l cle..孵<16 bytes per line>
设置表空间offline再online或者重启数据库便可看到坏块效果 SQL> select * from jiujian; select * from jiujian * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 5, block # 135) ORA-01110: data file 5: '/oracle/CRM/pos.dbf'五 对坏块修复
1 查询坏块信息 SQL> select * from v$database_block_corruption;FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ --------- 5 135 1 0 CHECKSUM 2 修复该块 下面给出两种方法 运行 recover …. Block 命令恢复指定的块 [oracle@oracle ~]$ rman target /Recovery Manager: Release 11.2.0.2.0 - Production on Sun Sep 23 11:09:32 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: CRM (DBID=3599153036)
RMAN> recover datafile 5 block 135;
Starting recover at 2012-09-23 11:10:32
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=199 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=72 device type=DISKchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /backup/20120913_ebnl4bil_1_1 channel ORA_DISK_1: piece handle=/backup/20120913_ebnl4bil_1_1 tag=TAG20120913T195604 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35starting media recovery
archived log for thread 1 with sequence 82 is already on disk as file /oracle/archive/1_82_791488634.dbf
archived log for thread 1 with sequence 83 is already on disk as file /oracle/archive/1_83_791488634.dbf archived log for thread 1 with sequence 84 is already on disk as file /oracle/archive/1_84_791488634.dbf archived log for thread 1 with sequence 85 is already on disk as file /oracle/archive/1_85_791488634.dbf archived log for thread 1 with sequence 86 is already on disk as file /oracle/archive/1_86_791488634.dbf archived log for thread 1 with sequence 87 is already on disk as file /oracle/archive/1_87_791488634.dbf archived log for thread 1 with sequence 88 is already on disk as file /oracle/archive/1_88_791488634.dbf archived log for thread 1 with sequence 89 is already on disk as file /oracle/archive/1_89_791488634.dbf archived log for thread 1 with sequence 90 is already on disk as file /oracle/archive/1_90_791488634.dbf archived log for thread 1 with sequence 91 is already on disk as file /oracle/archive/1_91_791488634.dbf archived log for thread 1 with sequence 92 is already on disk as file /oracle/archive/1_92_791488634.dbf archived log for thread 1 with sequence 93 is already on disk as file /oracle/archive/1_93_791488634.dbf archived log for thread 1 with sequence 94 is already on disk as file /oracle/archive/1_94_791488634.dbf archived log for thread 1 with sequence 95 is already on disk as file /oracle/archive/1_95_791488634.dbf archived log for thread 1 with sequence 96 is already on disk as file /oracle/archive/1_96_791488634.dbf archived log for thread 1 with sequence 97 is already on disk as file /oracle/archive/1_97_791488634.dbf channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=81 channel ORA_DISK_1: reading from backup piece /backup/20120913_ednl4bp0_1_1 channel ORA_DISK_1: piece handle=/backup/20120913_ednl4bp0_1_1 tag=TAG20120913T195928 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 media recovery complete, elapsed time: 00:00:03 Finished recover at 2012-09-23 11:11:19SQL> select * from jiujian;
DES
------------------------------ zhangxu love oracleSQL> select * from v$database_block_corruption;
no rows selected
可看到运行 recover …. Block 命令恢复指定的块后清除视图v$database_block_corrutption中坏块内容2对视图V$DATABASE_BLOCK_CORRUPTION中的所有坏块进行修复 Recover corruption list;（此处已经重新构造了坏块）
[oracle@oracle ~]$ rman target /Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 24 21:41:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: CRM (DBID=3599153036)
RMAN> recover corruption list;
Starting recover at 2012-09-24 21:42:01
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=131 device type=DISKchannel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /backup/20120913_ebnl4bil_1_1 channel ORA_DISK_1: piece handle=/backup/20120913_ebnl4bil_1_1 tag=TAG20120913T195604 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35starting media recovery
media recovery complete, elapsed time: 00:00:07Finished recover at 2012-09-24 21:42:47
SQL>select * from v$database_block_corruption;no rows selected
总结：数据块恢复命令 recover datafile xxx block xxx 和命令 recover corruption list 都可以恢复坏块，并自动清除视图v$database_block_corruption 中关于坏块的记录