oracle坏块模拟处理（笔记）

oracle坏块模拟处理（笔记）

cspmi08604 2014-03-19 18:31:53 10 收藏

 

先使用的是ASM测试的，最后想将修改了坏块的文件替换到ASM 磁盘管理中没有成功。

然后直接使用文件系统测试的。

 ASM测试

确认试验表的文件ID以及块ID

 

SQL> select file_id, block_id from dba_extents

  2  where segment_name = 'DEPARTMENTS';

 

   FILE_ID   BLOCK_ID

---------- ----------

         5         49

 

SQL> select file_name from dba_data_files where file_id = 5;

 

FILE_NAME

--------------------------------------------------------------------------------

+DATA1/dbca/datafile/example.294.840626861

 

SQL>

上面的如果查询BLOCK_ID不准确，可以使用一下方法

 

SQL> select dbms_rowid.rowid_block_number(rowid,'smallfile') block_id from DEPATRMENTS;

 

 

 

将ASM 管理中的datafile5 拷贝到OS 中

 

 

RMAN> shutdown immediate

 

database closed

database dismounted

Oracle instance shut down

 

RMAN> startup mount;

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     583008256 bytes

 

Fixed Size                     2022504 bytes

Variable Size                226493336 bytes

Database Buffers             352321536 bytes

Redo Buffers                   2170880 bytes

 

修改数据库，重命名 数据文件 为/u01/ example.dbf 让数据库识别（如果是坏块时不能使用rman copy 回到ASM 中的）完好的文件才可以使用这种方法识别

SQL> alter database rename file '+DATA1/dbca/datafile/example.294.840626861' to '/u01/example.dbf';

 

Database altered.

 

SQL>

 

 

 

RMAN> copy datafile 5 to '/u01/example.dbf';

 

Starting backup at 2014-03-04 09:49:43

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=142 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=+DATA1/dbca/datafile/example.294.840626861

output filename=/u01/example.dbf tag=TAG20140304T094944 recid=44 stamp=841312199

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 2014-03-04 09:50:00

 

Starting Control File and SPFILE Autobackup at 2014-03-04 09:50:00

piece handle=+DATA1/dbca/autobackup/2014_03_04/s_841312200.346.841312201 comment=NONE

Finished Control File and SPFILE Autobackup at 2014-03-04 09:50:08

 

RMAN>

 

 

使用BBED模拟坏块

配置BBED

 

[oracle@db2 ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@db2 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

 

Linking BBED utility (bbed)

rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed

gcc -o/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed-L/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/-L/u01/app/oracle/product/10.2.0/db_1/lib/-L/u01/app/oracle/product/10.2.0/db_1/lib/stubs/ -L/usr/lib -lirc  /u01/app/oracle/product/10.2.0/db_1/lib/s0main.o/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ssbbded.o/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10-lnnz10 -lnl10 /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/defopt.o-ldbtools10 -lclntsh  `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lnro10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10-lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lnro10 `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10  -lsnls10 -lnls10  -lcore10-lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10-lnls10 -lcore10 -lnls10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10-lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10   `cat/u01/app/oracle/product/10.2.0/db_1/lib/sysliblist`-Wl,-rpath,/u01/app/oracle/product/10.2.0/db_1/lib -lm    `cat /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist`-ldl -lm  -L/u01/app/oracle/product/10.2.0/db_1/lib

[oracle@db2 lib]$

 

       以上生成的bbed可执行文件在$ORACLE_HOME/rdbms/lib目录，可以复制到其他位置或者其他同Oracle版本的机器上运行。

 

也可通过以下命令将bbed生成到$ORACLE_HOME/bin目录

[oracle@db2 lib]$ make -f ins_rdbms.mk  BBED=$ORACLE_HOME/bin/bbed  $ORACLE_HOME/bin/bbed

 

Linking BBED utility (bbed)

rm -f /u01/app/oracle/product/10.2.0/db_1/bin/bbed

gcc -o/u01/app/oracle/product/10.2.0/db_1/bin/bbed-L/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/-L/u01/app/oracle/product/10.2.0/db_1/lib/-L/u01/app/oracle/product/10.2.0/db_1/lib/stubs/ -L/usr/lib -lirc  /u01/app/oracle/product/10.2.0/db_1/lib/s0main.o/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ssbbded.o/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/defopt.o-ldbtools10 -lclntsh  `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lnro10 `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10-lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lnro10 `cat/u01/app/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10-ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10  -lsnls10 -lnls10  -lcore10-lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 -lsnls10-lnls10 -lcore10 -lnls10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10  -lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10-lnls10 -lxml10 -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10   `cat/u01/app/oracle/product/10.2.0/db_1/lib/sysliblist`-Wl,-rpath,/u01/app/oracle/product/10.2.0/db_1/lib -lm    `cat/u01/app/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/10.2.0/db_1/lib

 

[oracle@db2 lib]$ ls -lrt bbed

-rwxr-xr-x 1 oracle oinstall 536161 Aug 1118:30 bbed

[oracle@db2 lib]$ cd $ORACLE_HOME/bin

[oracle@db2 bin]$ ls -lrt bbed

-rwxr-xr-x 1 oracle oinstall 536161 Aug 11 18:33bbed

 

BBED是Oracle 内部使用的命令，所以Oracle 不提供技术支持。 为了安全，BBED设置了口令保护，默认密码为blockedit。

 

[oracle@db2 bin]$ bbed

Password:

BBED-00113: Invalid password. Please rerunutility with the correct password.

 

[oracle@db2 bin]$ bbed

Password: blockedit

 

BBED: Release 2.0.0.0.0 - LimitedProduction on Thu Aug 11 18:51:47 2011

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

************* !!! For Oracle Internal Useonly !!! ***************

 

BBED>

 

       一般使用bbed，都是将一些配置信息写入到一个参数文本里，在调用bbed时，指定该参数文件。如：

       $bbedparfile=bbed.par

因为我使用的是ASM BBED是不识别ASM 安装的 所以需要需要上面步骤将需要的数据文件通过RMAN拷贝到OS系统

 

SQL>  select file#||' '||name||' '||bytes from v$datafile ;

 

FILE#||''||NAME||''||BYTES

--------------------------------------------------------------------------------

1 +DATA1/dbca/datafile/system.292.840626741 513802240

2 +DATA1/dbca/datafile/undotbs1.297.840626947 83886080

3 +DATA1/dbca/datafile/sysaux.293.840626817 283115520

4 +DATA1/dbca/datafile/users.299.840627007 11796480

5 +DATA1/dbca/datafile/example.294.840626861 104857600

6 +DATA1/dbca/datafile/hr_data.295.840626887 104857600

7 +DATA1/dbca/datafile/qqq_data.298.840626983 52428800

8 +DATA1/dbca/datafile/g_2014q4.300.840627015 10485760

9 +DATA1/dbca/datafile/fsdata.296.840626911 104857600

10 +DATA1/dbca/datafile/yyyy.318.840636041 10485760

 

10 rows selected.

 

SQL>

 

 

查看导出的数据文件大小

[oracle@dbca u01]$ ll

total 102552

drwxr-xr-x 3 oracle oinstall      4096 Feb 12 15:20 app

-rw-r--r-- 1 oracle oinstall        52 Mar  3 16:39 bbed.par

-rw-r----- 1 oracle oinstall 104865792 Mar  4 09:49 example.dbf

-rw-r--r-- 1 oracle oinstall        35 Mar  4 10:03 filelist.txt

drwx------ 2 oracle oinstall     16384 Jan 21 11:29 lost+found

drwxr-xr-x 3 oracle oinstall      4096 Mar  3 15:59 rmanbak

filelist.txt列出了需要使用BBED编辑的数据文件列表，格式为  文件编号 文件名字  文件大小

[oracle@dbca ~]$ vi /u01/filelist.txt

 

5 /u01/example.dbf 104865792

[oracle@dbca ~]$ vi /u01/bbed.par

 

blocksize=8192

listfile=/u01/filelist.txt

mode=edit

[oracle@dbca ~]$ bbed parfile=/u01/bbed.par

Password:

 

BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 4 10:04:18 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED>

BBED> show

        FILE#           5

        BLOCK#          1

        OFFSET          0

        DBA             0x01400001 (20971521 5,1)

        FILENAME        /u01/example.dbf

        BIFILE          bifile.bbd

        LISTFILE        /u01/filelist.txt

        BLOCKSIZE       8192

        MODE            Edit

        EDIT            Unrecoverable

        IBASE           Dec

        OBASE           Dec

        WIDTH           80

        COUNT           512

        LOGFILE         log.bbd

        SPOOL           No

 

BBED>

 

 

使用BBED修改块

 

BBED> set file 5 block 49

        FILE#           5

        BLOCK#          49

 

BBED> modify /x 8888 offset 4

 File: /u01/example.dbf (5)

 Block: 100              Offsets:    4 to  515           Dba:0x01400064

------------------------------------------------------------------------

 88884001 d6562000 00000104 194b0000 02002700 3cc80000 d6562000 0000e81f

 021f3200 61004001 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00008001 00000000 6b00fa00 5e1a6419 00000000 00000000 00000000 06000000

 601f0000 551f491f 3d1f311f 251f191f 0d1f011f f51ee91e dd1ed11e c51eb91e

 ad1ea11e 951e891e 7d1e711e 651e591e 4d1e411e 351e291e 1d1e111e 051ef91d

 ed1de11d d51dc91d bd1db11d a51d991d 8d1d811d 751d691d 5d1d511d 451d391d

 2d1d211d 151d091d fd1cf11c e51cd91c cd1cc11c b51ca91c 9d1c911c 851c791c

 6d1c611c 551c491c 3d1c311c 251c191c 0d1c011c f51be91b dd1bd11b c51bb91b

 ad1ba11b 951b891b 7d1b711b 651b591b 4d1b411b 351b291b 1d1b111b 051bf91a

 ed1ae11a d51ac91a bd1ab11a a61a9a1a 8e1a821a 761a6a1a 5e1a0000 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

 

 <32 bytes per line>

 

 

BBED>

 

 

使用DBV确认块已经损坏

 

 

[oracle@dbca u01]$ dbv file=/u01/example.dbf

 

DBVERIFY: Release 10.2.0.1.0 - Production on Tue Mar 4 10:35:56 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

DBVERIFY - Verification starting : FILE = /u01/example.dbf

Page 100 is marked corrupt

Corrupt block relative dba: 0x01400064 (file 5, block 100)

Bad header found during dbv:

Data in bad block:

 type: 6 format: 2 rdba: 0x01408888

 last change scn: 0x0000.002056d6 seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x56d60601

 check value in block header: 0xc3f5

 computed block checksum: 0x0

 

 

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 12800

Total Pages Processed (Data) : 4393

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 1321

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 1522

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 5563

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 2119892 (0.2119892)

[oracle@dbca u01]$

 

 

 

将文件拷贝回ASM 磁盘管理中

如果使用RMAN 会报错

 

 

RMAN> copy datafile  '/u01/example.dbf' to  '+DATA1/dbca/datafile/example.294.840626861';

 

Starting backup at 2014-03-04 11:40:13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=/u01/example.dbf

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/04/2014 11:40:20

ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/example.dbf

 

 

尝试了很多方法，最后都没成功，望大牛给出思路

 

 

使用文件系统实验

创建测试环境

 

 

SQL> create tablespace block

  2  logging

  3  datafile '/u01/block.dbf'

  4  size 10M

  5  extent management local;

 

Tablespace created.

 

SQL> create user lulu identified by lulu

  2  default tablespace block

  3  temporary tablespace temp;

 

User created.

 

SQL> grant connect,resource,CTXAPP,create view to lulu;

 

Grant succeeded.

 

SQL>

 

SQL> create table hehe (a number,name varchar2(10));

 

Table created.

 

SQL> insert into hehe values(1,'sws');

 

1 row created.

 

SQL> insert into hehe values(2,'weds');

 

1 row created.

 

SQL> insert into hehe values(3,'dsf');

 

1 row created.

 

SQL> commit;

 

确认试验表的文件ID以及块ID

 

 

SQL> conn / as sysdba

Connected.

SQL> select file_id, block_id from dba_extents

  2  where segment_name = 'HEHE';

 

   FILE_ID   BLOCK_ID

---------- ----------

        11          9

 

SQL>  select file_name from dba_data_files where file_id =11;

 

FILE_NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u01/block.dbf

 

SQL>

上面的如果查询BLOCK_ID不准确，可以使用一下方法

 

SQL> select dbms_rowid.rowid_block_number(rowid,'smallfile') block_id from lulu.hehe;

 

  BLOCK_ID

----------

        12

        12

        12

        12

        12

        12

 

6 rows selected.

 

SQL>

 

使用BBED模拟坏块

 

[oracle@dbca u01]$ cd /u01

[oracle@dbca u01]$ ll

total 112840

drwxr-xr-x 3 oracle oinstall      4096 Feb 12 15:20 app

-rw-r--r-- 1 oracle oinstall        52 Mar  3 16:39 bbed.par

-rw-r--r-- 1 oracle oinstall     18432 Mar  4 11:14 bifile.bbd

-rw-r----- 1 oracle oinstall  10493952 Mar  4 16:04 block.dbf

-rw-r----- 1 oracle oinstall 104865792 Mar  4 11:14 example.dbf

-rw-r--r-- 1 oracle oinstall        47 Mar  4 11:12 filelist.txt

-rw-r--r-- 1 oracle oinstall      1343 Mar  4 11:14 log.bbd

drwx------ 2 oracle oinstall     16384 Jan 21 11:29 lost+found

drwxr-xr-x 3 oracle oinstall      4096 Mar  3 15:59 rmanbak

[oracle@dbca u01]$ vi filelist.txt

 

11 /u01/block.dbf  10493952

 

[oracle@dbca u01]$ bbed parfile=/u01/bbed.par

Password: blockedit

 

BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 4 16:19:25 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> show

        FILE#           11

        BLOCK#          1

        OFFSET          0

        DBA             0x02c00001 (46137345 11,1)

        FILENAME        /u01/block.dbf

        BIFILE          bifile.bbd

        LISTFILE        /u01/filelist.txt

        BLOCKSIZE       8192

        MODE            Edit

        EDIT            Unrecoverable

        IBASE           Dec

        OBASE           Dec

        WIDTH           80

        COUNT           512

        LOGFILE         log.bbd

        SPOOL           No

 

 

BBED> set dba 11,12     --指定要编辑11号文件的地12个块

        DBA             0x02c0000c (46137356 11,12)

 

BBED> find /c fengfeng top  --查找block12中包含fengfeng字符 的具体位置

 File: /u01/block.dbf (11)

 Block: 12               Offsets: 7633 to 8144    （偏移量）       Dba:0x02c0000c

------------------------------------------------------------------------

 66656e67 66656e67 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 047a6861 6f2c0103 02c10564 62696562 69652020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 0477616e 672c0103 02c10464

 79616e67 7a616920 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 0479616e 672c0103 02c10364 79616e67 7a616920 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 04646f6e 672c0103 02c10264

 79616e67 7a616920 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 

 <32 bytes per line>

 

BBED> d /v dba 11,12 offset 7633  --显示11号文件12块中偏移量为7633的内容

 File: /u01/block.dbf (11)

 Block: 12      Offsets: 7633 to 8144  Dba:0x02c0000c

-------------------------------------------------------

 66656e67 66656e67 20202020 20202020 l fengfeng  --在7633开始找到了fengfeng     

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                 

 20202020 20202020 20202020 20202020 l                

 20202020 047a6861 6f2c0103 02c10564 l     .zhao,.....d

 62696562 69652020 20202020 20202020 l biebie         

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 0477616e 672c0103 02c10464 l     .wang,.....d

 79616e67 7a616920 20202020 20202020 l yangzai        

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 0479616e 672c0103 02c10364 l     .yang,.....d

 79616e67 7a616920 20202020 20202020 l yangzai        

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 04646f6e 672c0103 02c10264 l     .dong,.....d

 79616e67 7a616920 20202020 20202020 l yangzai        

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 20202020 20202020 20202020 20202020 l                

 

 <16 bytes per line>

BBED> modify /c qwertyui offset 7633  --从偏移量7633开始修改，依次替换为qwertyui

                                     （也就是将fengfeng字符修改为qwertyui）

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /u01/block.dbf (11)

 Block: 12               Offsets: 7633 to 8144           Dba:0x02c0000c

------------------------------------------------------------------------

 71776572 74797569 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 047a6861 6f2c0103 02c10564 62696562 69652020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 0477616e 672c0103 02c10464

 79616e67 7a616920 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 0479616e 672c0103 02c10364 79616e67 7a616920 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 04646f6e 672c0103 02c10264

 79616e67 7a616920 20202020 20202020 20202020 20202020 20202020 20202020

 20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020

 

 <32 bytes per line>

注：修改块最好在关库状态下，而且如果要模拟坏块切记最好不要使用

SUM … applye 命令使块生效，不然被修改的块仍然是一个好块。

 

刷新缓冲区高速缓存

 

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

 

System altered.

 

SQL>

 

 

验证结果

 

SQL> select * from lulu.hehe;

select * from lulu.hehe

                   *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 11, block # 12)

ORA-01110: data file 11: '/u01/block.dbf'

 

 

SQL>

 

 

检测跳过块（在没有备份的情况下-会丢失数据）

这种方法直接跳过损坏的块，在查询表的时候损坏块中的数据就丢失，完好的块数据显示。

首先用ADMIN_TABLES 过程建立修复表

 

SQL> show user

USER is "SYS"

SQL> BEGIN

  2  DBMS_REPAIR.ADMIN_TABLES (

  3  table_name => 'REPAIR_TABLE',

  4  table_type => DBMS_REPAIR.REPAIR_TABLE,

  5  action => DBMS_REPAIR.CREATE_ACTION,

  6  tablespace => 'USERS');

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

其次检测并报告损坏

 

SQL> DECLARE num_corrupt INT;

  2  BEGIN

  3  num_corrupt := 0;

  4  DBMS_REPAIR.CHECK_OBJECT (

  5  Schema_name => 'LULU',

  6  object_name => 'HEHE',

  7  repair_table_name => 'REPAIR_TABLE',

  8  corrupt_count => num_corrupt);

  9  END;

 10  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

 

查询建立的修复表

 

SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,        CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION      FROM REPAIR_TABLE;

 

OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR

------------------------------ ---------- ------------ ----------

CORRUPT_DESCRIPTION

--------------------------------------------------------------------------------

REPAIR_DESCRIPTION

--------------------------------------------------------------------------------

HEHE                                   12         6148 TRUE

 

mark block software corrupt

 

   MARKED_COR 显示TURE 表示为真，已经标记

SQL>

 

 

 

使对象变为可用

 

SQL> SET SERVEROUTPUT ON

DECLARE num_fix INT;

BEGIN

SQL> num_fix := 0;

  2    3    4  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

  5  schema_name => 'LULU',

  6  object_name => 'HEHE',

  7  object_type => DBMS_REPAIR.TABLE_OBJECT,

  8  repair_table_name => 'REPAIR_TABLE',

  9  fix_count => num_fix);

 10  END;

 11  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

为USERS 表空间创建孤立的键表

 

SQL> BEGIN

  2  DBMS_REPAIR.ADMIN_TABLES (

  3  table_name => 'ORPHAN_KEY_TABLE',

  4  table_type => DBMS_REPAIR.ORPHAN_TABLE,

  5  action => DBMS_REPAIR.CREATE_ACTION,

  6  tablespace => 'USERS');

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

 

跳过坏块

 

SQL> BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  2    3   SCHEMA_NAME => 'LULU',

OBJECT_NAME => 'HEHE',

  4    5  OBJECT_TYPE => dbms_repair.table_object,

  6  FLAGS => dbms_repair.skip_flag);

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

 

 

检测是否跳过坏块

 

SQL> SELECT * FROM LULU.HEHE;

 

no rows selected

 

 

 

修复损坏并重建丢失的数据（做过没成功，这个应该是需要备份的吧）

 

SET SERVEROUTPUT ON

DECLARE num_orphans INT;

BEGIN

num_orphans := 0;

DBMS_REPAIR.DUMP_ORPHAN_KEYS (

schema_name => 'LULU',

object_name => 'HEHE',

object_type => DBMS_REPAIR.INDEX_OBJECT,

repair_table_name => 'REPAIR_TABLE',

orphan_table_name => 'ORPHAN_KEY_TABLE',

key_count => num_orphans);

DBMS_OUTPUT.PUT_LINE('orphan key count: ' ||

TO_CHAR(num_orphans));

END;

 

 

 

使用RMAN 恢复

从警告日志和udump 目录下找到相应的跟踪文件

 

[oracle@dbca dbca]$ pwd

/u01/app/oracle/admin/dbca

[oracle@dbca dbca]$ ls

adump  bdump  cdump  dpdump  pfile  udump  wallet

[oracle@dbca dbca]$

[oracle@dbca dbca]$ less bdump/alert_dbca.log  (使用LESS 命令查看，进入按G最后一页)

 

Wed Mar  5 10:46:55 2014

Hex dump of (file 11, block 12) in trace file /u01/app/oracle/admin/dbca/udump/dbca_ora_3879.trc

Corrupt block relative dba: 0x02c0000c (file 11, block 12)

Bad check value found during buffer read

Data in bad block:

 type: 6 format: 2 rdba: 0x02c0000c

 last change scn: 0x0000.00211304 seq: 0x1 flg: 0x06

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x13040601

 check value in block header: 0x4ce4

 computed block checksum: 0x1515

Reread of rdba: 0x02c0000c (file 11, block 12) found same corrupted data

Wed Mar  5 10:46:55 2014

Corrupt Block Found

         TSN = 15, TSNAME = BLOCK

         RFN = 11, BLK = 12, RDBA = 46137356

         OBJN = 54757, OBJD = 54757, OBJECT = HEHE, SUBOBJECT =

         SEGMENT OWNER = LULU, SEGMENT TYPE = Table Segment

(END)

 

[oracle@dbca dbca]$ cd udump/

[oracle@dbca udump]$ ll     --找到对应trc

-rw-r----- 1 oracle oinstall  1344 Feb 26 08:47 dbca_ora_3703.trc

-rw-r----- 1 oracle oinstall   672 Feb 25 08:31 dbca_ora_3704.trc

-rw-r----- 1 oracle oinstall  1640 Feb 28 09:14 dbca_ora_3708.trc

-rw-r----- 1 oracle oinstall  2765 Feb 19 16:49 dbca_ora_3711.trc

-rw-r----- 1 oracle oinstall   738 Feb 26 08:48 dbca_ora_3714.trc

-rw-r----- 1 oracle oinstall   738 Feb 25 08:31 dbca_ora_3717.trc

-rw-r----- 1 oracle oinstall  1344 Feb 24 09:29 dbca_ora_3719.trc

-rw-r----- 1 oracle oinstall   672 Mar  5 09:02 dbca_ora_3721.trc

-rw-r----- 1 oracle oinstall  1230 Feb 17 09:30 dbca_ora_3730.trc

-rw-r----- 1 oracle oinstall   940 Feb 24 17:07 dbca_ora_3731.trc

-rw-r----- 1 oracle oinstall   738 Feb 20 11:10 dbca_ora_3734.trc

-rw-r----- 1 oracle oinstall  1032 Feb 21 17:19 dbca_ora_3738.trc

-rw-r----- 1 oracle oinstall   672 Feb 18 09:31 dbca_ora_3740.trc

-rw-r----- 1 oracle oinstall   615 Feb 14 09:27 dbca_ora_3744.trc

-rw-r----- 1 oracle oinstall   672 Feb 28 09:14 dbca_ora_3745.trc

-rw-r----- 1 oracle oinstall  1230 Mar  4 09:45 dbca_ora_3749.trc

-rw-r----- 1 oracle oinstall   887 Feb 28 16:55 dbca_ora_3756.trc

-rw-r----- 1 oracle oinstall   615 Feb 12 09:22 dbca_ora_3762.trc

-rw-r----- 1 oracle oinstall   940 Feb 18 16:00 dbca_ora_3774.trc

-rw-r----- 1 oracle oinstall   672 Feb 17 09:30 dbca_ora_3776.trc

-rw-r----- 1 oracle oinstall   672 Feb 14 09:27 dbca_ora_3781.trc

-rw-r----- 1 oracle oinstall   672 Mar  4 09:45 dbca_ora_3786.trc

-rw-r----- 1 oracle oinstall   672 Feb 10 09:25 dbca_ora_3788.trc

-rw-r----- 1 oracle oinstall   672 Feb 13 09:30 dbca_ora_3793.trc

-rw-r----- 1 oracle oinstall  6144 Feb 12 09:22 dbca_ora_3799.trc

-rw-r----- 1 oracle oinstall   940 Feb 17 14:57 dbca_ora_3802.trc

-rw-r----- 1 oracle oinstall  5472 Mar  5 09:10 dbca_ora_3803.trc

-rw-r----- 1 oracle oinstall   738 Feb 13 09:30 dbca_ora_3807.trc

-rw-r----- 1 oracle oinstall  2292 Feb 12 17:18 dbca_ora_3810.trc

-rw-r----- 1 oracle oinstall  1025 Feb 14 17:16 dbca_ora_3817.trc

-rw-r----- 1 oracle oinstall  6516 Mar  5 10:47 dbca_ora_3879.trc  三月：5日 10:47

 

[oracle@dbca udump]$ less dbca_ora_3879.trc

Corrupt block relative dba: 0x02c0000c (file 11, block 12)

Bad check value found during buffer read

Data in bad block:

 type: 6 format: 2 rdba: 0x02c0000c

 last change scn: 0x0000.00211304 seq: 0x1 flg: 0x06

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0x13040601

 check value in block header: 0x4ce4

 computed block checksum: 0x1515

Reread of rdba: 0x02c0000c (file 11, block 12) found same corrupted data

table scan: segment: file# 11 block# 11

            skipping corrupt block file# 11 block# 12

table scan: segment: file# 11 block# 11

            skipping corrupt block file# 11 block# 12

*** 2014-03-05 10:47:21.400

table scan: segment: file# 11 block# 11

            skipping corrupt block file# 11 block# 12

table scan: segment: file# 11 block# 11

            skipping corrupt block file# 11 block# 12

 

用RMAN 恢复

（1）只能进行完全恢复。必需使用备份以来的所有重做日志。

（2）必须具有0级备份，而不是增量备份（只备份改变过的块）

 

 

[oracle@dbca ~]$ rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 5 11:28:04 2014

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

connected to target database: DBCA (DBID=2620122031)

 

RMAN>                   

RMAN>blockrecover datafile 11 block 12；

….

….

如果只有少数块损坏 使用这种方法，如果大量块损坏使用blockrecover corruption list进行块的恢复，这是在大量块损坏时或全部块损坏时使用.

 

做法：

RMAN> backup validate database;

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        14        276          1                  0 CHECKSUM
        14        517          1                  0 CHECKSUM
        14        107          1                  0 CHECKSUM
        14         56          1                  0 CHECKSUM

RMAN> blockrecover datafile 14 block 56,107,276,517;

。。

大量恢复时

先执行RMAN>backup validate database；--更新列表，使用下面语句恢复列表所有。

RMAN> blockrecover corruption list进行块的恢复，这是在大量块损坏时或全部块损坏时使用