1.模拟表、及表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/DBdb/test01.dbf' size 30m;
Tablespace created.
SQL> create table bbed tablespace test as select * from dba_tables;
Table created.
SQL> select file_id,tablespace_name,file_name,status from dba_data_files where tablespace_name='TEST';
FILE_ID TABLESPACE_NAME FILE_NAME STATUS
---------- ------------------------------ ------------------------------------------------------------ ---------
8 TEST /u01/app/oracle/oradata/DBdb/test01.dbf AVAILABLE
SQL> select count(*) from bbed;
COUNT(*)
----------
2877
SQL> col segment for a10
SQL> select segment_name,file_id,block_id from dba_extents where segment_name='BBED';
SEGMENT_NAME FILE_ID BLOCK_ID
--------------- ---------- ----------
BBED 8 128
BBED 8 136
BBED 8 144
BBED 8 152
BBED 8 160
BBED 8 168
BBED 8 176
BBED 8 184
BBED 8 192
BBED 8 200
BBED 8 208
BBED 8 216
BBED 8 224
BBED 8 232
14 rows selected.
SQL>
2.使用bbed产生坏块：
2.1 生成filelist文件：
SQL> select file#||' '||name||' '||bytes from v$datafile ;
FILE#||''||NAME||''||BYTES
-----------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/DBdb/system01.dbf 2936012800
2 /u01/app/oracle/oradata/DBdb/sysaux01.dbf 723517440
3 /u01/app/oracle/oradata/DBdb/undotbs01.dbf 2710568960
4 /u01/app/oracle/oradata/DBdb/users01.dbf 3207331840
5 /u01/app/oracle/oradata/DBdb/example01.dbf 355205120
6 /u01/app/oracle/oradata/DBdb/tbs.dbf 12582912
7 /u01/app/oracle/oradata/DBdb/tbs_tmp.dbf 12582912
8 /u01/app/oracle/oradata/DBdb/test01.dbf 31457280
8 rows selected.
2.2 粘贴上述需要的内容到filelist记录文件，如下
[oracle@wang ~]$ cat filelist.txt
6 /u01/app/oracle/oradata/DBdb/tbs.dbf 12582912
7 /u01/app/oracle/oradata/DBdb/tbs_tmp.dbf 12582912
8 /u01/app/oracle/oradata/DBdb/test01.dbf 31457280
2.3 编辑bbed的parfile文件，如下：
[oracle@wang ~]$ cat bbed.par
blocksize=8192
listfile=/home/oracle/filelist.txt
mode=edit
[oracle@wang ~]$
[oracle@wang ~]$
2.4 使用parfile文件进行bbed界面：
[oracle@wang lib]$ bbed parfile=/home/oracle/bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Feb 1 12:58:16 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show
FILE# 6
BLOCK# 1
OFFSET 0
DBA 0x01800001 (25165825 6,1)
FILENAME /u01/app/oracle/oradata/DBdb/tbs.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
--转换到file 8：
BBED> set file 8
FILE# 8
BBED> show
FILE# 8
BLOCK# 1
OFFSET 0
DBA 0x02000001 (33554433 8,1)
FILENAME /u01/app/oracle/oradata/DBdb/test01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/filelist.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED>
2.5 回滚误操作：如果操作中发生误操作，可以使用revert命令回滚
BBED> help modify
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
BBED> modify /x 0x02000001
File: /u01/app/oracle/oradata/DBdb/test01.dbf (8)
Block: 1 Offsets: 0 to 511 Dba:0x02000001
------------------------------------------------------------------------
02000001 01000002 00000000 00000104 bc1d0000 00000000 0004200b 440badc3
44424442 00000000 142e0000 000f0000 00200000 08000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 5a683a00 00000000 1e77a239 c7912c38 06200e00 00000000 00000000
00000000 00000000 00000400 02000000 00000000 01000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 09000000 04005445 53540000 00000000 00000000
00000000 00000000 00000000 00000000 08000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
7ac92131 01000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 5b683a00 00000000 1f77a239 01000000 50030000 91150000 10000000
<32 bytes per line>
BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/u01/app/oracle/oradata/DBdb/test01.dbf', block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
2.6 改写数据块(模拟坏块)
BBED> modify 1000 file 8 block 144
File: /u01/app/oracle/oradata/DBdb/test01.dbf (8)
Block: 144 Offsets: 0 to 511 Dba:0x02000090
------------------------------------------------------------------------
03e80000 90000002 87693a00 00000204 16cc0000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 04000000 ffffffff 00000000 01000000 10000000
02000100 00000000 00000000 00000000 00000000 10000000 00000000 00000000
00000000 01001c00 2e350000 02000000 81000002 01000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
2c5f0100 08200e00 00000000 90000002 08000000 00000000 98000002 08000000
08000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 11111111 11111111 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
2.6 检查数据块损坏
使用verify命令，可以发现刚才修改的file 8 block 144已经被标记为损坏。
BBED> help verify
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/DBdb/test01.dbf
BLOCK = 144
Block 144 is corrupt
Corrupt block relative dba: 0x02000090 (file 0, block 144)
Bad header found during verification
Data in bad block:
type: 3 format: 0 rdba: 0x02000090
last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x69872002
check value in block header: 0xcc16
computed block checksum: 0x4a23
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED>
2.7 使用dbv工具验证是否有坏块
[oracle@wang lib]$ dbv file=/u01/app/oracle/oradata/DBdb/test01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Thu Feb 1 13:09:03 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DBdb/test01.dbf
Page 144 is marked corrupt
Corrupt block relative dba: 0x02000090 (file 8, block 144)
Bad header found during dbv:
Data in bad block:
type: 3 format: 0 rdba: 0x02000090
last change scn: 0x0000.003a6987 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x69872002
check value in block header: 0xcc16
computed block checksum: 0x4a23
DBVERIFY - Verification complete
Total Pages Examined : 3840
Total Pages Processed (Data) : 100
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 135
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 3604
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3828105 (0.3828105)
[oracle@wang lib]$
3. 数据块损坏，需要恢复。
3.1 在这种情况下，如果有备份，需要从备份中恢复；如果没有备份，那么坏块部分的数据库就要丢失了。检查损坏的对象：
SELECT owner, segment_name, segment_type, relative_fno, tablespace_name
FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;
SQL> SELECT owner, segment_name, segment_type, relative_fno, tablespace_name
2 FROM dba_extents
3 WHERE file_id = &AFN
4 and &BL between block_id AND block_id + blocks - 1;
Enter value for afn: 8
old 3: WHERE file_id = &AFN
new 3: WHERE file_id = 8
Enter value for bl: 144
old 4: and &BL between block_id AND block_id + blocks - 1
new 4: and 144 between block_id AND block_id + blocks - 1
OWNER SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO TABLESPACE_NAME
------------------------------ --------------- ------------------ ------------ ------------------------------
SYS BBED TABLE 8 TEST
3.2 设置event10231事件
如果损失的是数据，可以设置内部事件，使得全表扫描跳过那些损坏的block
SQL> alter system set events='10231 trace name context forever,level 10';
3.3 将该用户下的数据导出，删除后，再重建该表，
导出数据： exp system/oracle file=bbed.dmp log=bbed.log tables=sys.bbed
[oracle@wang ~]$ exp system/oracle file=bbed.dmp log=bbed.log tables=sys.bbed
Export: Release 11.2.0.4.0 - Production on Thu Feb 1 13:38:13 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table BBED 2877 rows exported
Export terminated successfully without warnings.
[oracle@wang ~]$
--删除表
SQL> drop table bbed purge;
Table dropped.
SQL>
--执行导入：
imp \'\/ as sysdba\' file=bbed.dmp log=bbedimp.log TABLES=bbed
[oracle@wang ~]$ imp \'\/ as sysdba\' file=bbed.dmp log=bbedimp.log TABLES=bbed
Import: Release 11.2.0.4.0 - Production on Thu Feb 1 13:49:48 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SYSTEM, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYS
. importing SYS's objects into SYS
. . importing table "BBED" 2877 rows imported
Import terminated successfully without warnings.
[oracle@wang ~]$
--查询：
SQL> select count(*) from bbed;
COUNT(*)
----------
2877
3.4 取消10231事件：
SQL> alter system set events='10231 trace name context off';
System altered.
3.5 验证，不存在坏块：
RMAN> validate datafile 8;
Starting validate at 01-FEB-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=00008 name=/u01/app/oracle/oradata/DBdb/test01.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
---- ------ -------------- ------------ --------------- ----------
8 OK 0 3601 3840 3830998
File Name: /u01/app/oracle/oradata/DBdb/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 103
Index 0 0
Other 0 136
Finished validate at 01-FEB-18
RMAN>
0