设置10231事件并结合导入导出工具恢复坏块

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>