数据块损坏块恢复

发生数据块损坏是，最典型的表现是遇到ORA-01578错误，也可能是ORA-600内部错误。

物理一致性检查：数据块校验和(Block Checksum)
alter system set db_block_checksum=true/false;

逻辑一致性检查：逻辑数据块检查(Logical Block Checking)
alter system set db_block_checking=true/false;

Block Change Tracking
启用Block Change Tracking之后，Oracle会启动一个ctwr进程，负责跟踪数据变化，每当数据块发生变化时，将相关信息同时记录到指定文件中，这样当RMAN进行增量备份时，就可

以获得需要备份的数据块列表。

1、查询功能是否启动
select * from v$block_change_tracking;

2、启动功能，指定文件
alter database enable block change tracking;
or
alter database enable block change tracking using file '/oracle/flash_recovery_area/ORA10/blockchange';

3、确认修改生效
select * from v$block_change_tracking;

4、关闭功能
alter database disable block change tracking;

5、确认修改生效
select * from v$block_change_tracking;
 

一、数据文件物理和逻辑一致性检查

1、使用DBV工具：

1.1、检查数据文件(ASM的文件必须带userid=sys/pwd)
$ dbv file=+DATA/ora10/datafile/users.259.703629283 userid=system/sys
$ dbv file=+DATA/ora10/datafile/users.259.703629283 userid=sys/password

1.2、检查单独的SEGMENTT
=================================================
SQL> select t.ts#,s.header_file,s.header_block
from v$tablespace t,dba_segments s
where s.segment_name='EMP'
and t.name=s.tablespace_name;
=================================================
TS# HEADER_FILE HEADER_BLOCK
--- ----------- ------------
4           4           27
=================================================
$ dbv userid=system/password segment_id=4.4.27

2、使用RMAN工具：

2.1、只进行一致性检查，不进行备份
RMAN> backup check logical validate database;

2.2、检查单个数据文件或几个数据文件
RMAN> backup check logical validate datafile 1,2;

2.3、查询检查过程的进度状态和结果
SQL> select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%_coplete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork;

SQL> select * from v$database_block_corruption;

2.4、检查数据库备份
RMAN> restore validate database;

2.5、检查最近的控制文件备份
RMAN> restore validate controlfile to '+RECOVERY/ora10/backupset/2009_12_10/ncsnf0_tag20091210t163353_0.261.705256461';

2.6、检查备份的日志文件
RMAN> restore validate archivelog from sequence ... until sequence ..;

二、模拟数据块不一致
1、配置BBED
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

确认在目录下生产了bbed的二进制文件

创建文件bbed.par和list
$ cat bbed.par
===================
blocksize=8192
listfile=list
mode=edit
===================

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

$ cat list
============================================================
1 /oracle/oradata/ora10/system01.dbf        513802240
2 /oracle/oradata/ora10/undotbs01.dbf        41943040
3 /oracle/oradata/ora10/sysaux01.dbf        272629760
4 /oracle/oradata/ora10/users01.dbf           5242880
5 /oracle/oradata/ora10/example01.dbf       104857600
6 /oracle/oradata/ora10/hjping.dbf           20971520
7 /oracle/oradata/ora10/test.dbf             10485760
============================================================

2、查询FILE_ID和BLOCK_ID
==========================================================================================
SQL> select rowid,
dbms_rowid.rowid_type(rowid) type,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_number
from test;

ROWID                    TYPE  OBJECT_ID    FILE_ID   BLOCK_ID ROW_NUMBER
------------------ ---------- ---------- ---------- ---------- ----------
AAAM19AABAAAO8qAAA          1      52605          1      61226          0
AAAM19AABAAAO8qAAB          1      52605          1      61226          1

注：
rowid_type：0，表示受限ROWID。1，表示扩展ROWID。
rowid_object：对应的数据对象的ID，即object_id，
select owner, object_name, object_id, object_type from dba_objects where object_name='TEST'；

relative_fno：对应的数据文件号，file#,
select file#,name,bytes from v$datafile;

rowid_block_number：所在数据块的数据块号。
rowid_row_number：在数据块中对应的行号。
==========================================================================================
or
==========================================================================================
SQL> select segment_name,file_id,block_id from dba_extents where segment_name='TEST';
SEGMENT_NA    FILE_ID   BLOCK_ID
---------- ---------- ----------
TEST                1      61225
==========================================================================================

3、应用BBED修改数据
$ ./bbed parfile=bbed.par
(blockedit)

BBED> set dba 1,61225
        DBA             0x0040ef29 (4255529 1,61225)

BBED> find /c hjp
BBED> f
BBED> modify 100 dba 1,61225
BBED> exit;

4、使用DBV检查数据文件
$ dbv file=/oracle/oradata/ora10/system01.dbf
==============================================================
Page 61226 is marked corrupt
Corrupt block relative dba: 0x0040ef2a (file 1, block 61226)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0040ef2a
 last change scn: 0x0000.000a7ba0 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x7ba00601
 check value in block header: 0x1a8b
 computed block checksum: 0xc00

DBVERIFY - Verification complete

Total Pages Examined         : 62720
Total Pages Processed (Data) : 37062
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 6893
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1899
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 16865
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 722123 (0.722123)
==============================================================

SQL> select file#,block#,blocks from v$database_block_corruption;
=============================
FILE#     BLOCK#     BLOCKS
------ ---------- ----------
    1      61226          1
=============================

SQL> select * from testtab;
select * from testtab
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 61226)
ORA-01110: data file 1: '/oracle/oradata/ora10/system01.dbf'

5、用RMAN恢复数据块
RMAN> blockrecover datafile 1 block 61226;

确认恢复成功
SQL> select * from testtab;

 ID DESCR
--- ----------
  1 hjp
  2 jds

 

不完全恢复
1、准备测试数据
SQL> create table test (id number,name char(2000));

SQL> insert into test values (1,'huang 1');
SQL> insert into test values (2,'huang 2');

SQL> insert into test values (10,'huang 10');
SQL> commit;

SQL> analyze table test compute statistics;

SQL> select blocks from user_tables where table_name='TEST';

SQL> select dbms_rowid.rowid_relative_fno(rowid) r_fno,
dbms_rowid.rowid_block_number(rowid) b_no,id
from test order by 1,2;

2、模拟数据块损坏
$ ./bbed parfile=bbed.par
BBED> set dba
BBED> find /c huang
BBED> dump /v dba    offset    count
BBED> f
BBED> dump /v dba    offset    count

BBED> modify 1000 dba

create tablespace hjping datafile '+DATA' size 10M;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693298/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693298/