oracle壞塊模擬處理（筆記）

邱東陽發表於2014-03-19
Oracle筆記

先使用的是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進行塊的恢復，這是在大量塊損壞時或全部塊損壞時使用

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1125210/，如需轉載，請註明出處，否則將追究法律責任。

相關文章