《数据块逻辑损坏和物理损坏学习》

数据块的损坏分两种情况,第一种是物理性的,第二种是逻辑性的。物理错误一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下,数据的内容在含义上不正确,比如保存了不允许的字段值。
下面分别用两种情况说明数据块的物理错误和数据块的逻辑错误。

数据块的物理错误
以下通过dd命令模拟数据块物理错误
创建test表空间
SQL> create tablespace test datafile ‘/u01/app/oracle/oradata/PROD3/test01.dbf’ size 10m;
Tablespace created.
创建TOM用户
SQL> create user tom identified by oracle default tablespace test;
User created.
给TOM用户授予DBA权限
SQL> grant dba to tom;
Grant succeeded.
在TOM表空间创建test表
SQL> create table tom.test as select * from dba_objects where rownum <=100;
Table created.
SQL> select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b  where a.file_id=b.file# and a.owner=’TOM’ and a.segment_name=’TEST’;
SQL> col name for a40
SQL> /
查询test存放着128开始的8个块中(128-135)
   FILE_ID   BLOCK_ID     BLOCKS NAME
———- ———- ———- —————————————-
         7        128          8 /u01/app/oracle/oradata/PROD3/test01.dbf
 查询test的行存放的数据块
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from tom.test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
                                 132
                                 131
改变132数据块的内容
[oracle@PROD3 ~]$ dd of=/u01/app/oracle/oradata/PROD3/test01.dbf bs=8192 conv=notrunc seek=132 <<EOF
> abcdefg
> EOF
0+1 records in
0+1 records out
8 bytes (8 B) copied, 0.00447092 s, 1.8 kB/s
使用DBV检查数据块的物理错误
[oracle@PROD3 ~]$ dbv file=/u01/app/oracle/oradata/PROD3/test01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.4.0 – Production on Fri Jun 3 15:32:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/PROD3/test01.dbf
Page 132 is marked corrupt
Corrupt block relative dba: 0x01c00084 (file 7, block 132)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 2 rdba: 0x0a676665
 last change scn: 0x0000.00106c07 seq: 0x2 flg: 0x04
 spare1: 0x63 spare2: 0x64 spare3: 0x0
 consistency value in tail: 0x6c070602
 check value in block header: 0x5a7a
 computed block checksum: 0xc942
DBVERIFY – Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 130
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1148
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1076234 (0.1076234)
可以查看到Page 132 is marked corrupt,Total Pages Marked Corrupt   : 1信息,得到当前数据文件第132块出现物理损坏。
DBV是一个外部命令,能够执行物理结构的完整性检查,DBV只能检查可缓存管理的块,所以只能用于检查数据文件,不能用于检查控制文件和重做日志文件的检查。DBV适用于offline或者online的数据文件,也可以验证备份文件,但备份必须是通过backup as copy 或者是cp 命令得到的备份文件,同样DBV可以检查ASM管理的数据文件。
使用DBV检查ASM管理的数据文件,但是要求数据库是open状态,同时要使用USERID,例如
dbv userid=system/oracle file=+DATA/PROD3/datafile/user01.dbf blocksize=8192
使用DBV检查backup as copy 备份的数据文件,因为数据文件已经损坏,备份前需要先设定备份坏块容忍度
run{
set maxcorrupt for datafile 7 to 2;
backup tablespace test;
}
 run {
2> set maxcorrupt for datafile 7 to 2;
3> backup datafile 7;
4> }
executing command: SET MAX CORRUPT
Starting backup at 03-JUN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD3/test01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-16
channel ORA_DISK_1: finished piece 1 at 03-JUN-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T155454_co2fyg3g_.bkp tag=TAG20160603T155454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JUN-16
备份后使用dbv进行备份文件的检查
dbv file=/u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T155454_co2fyg3g
DBVERIFY – Verification complete
Total Pages Examined         : 138
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 136
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1076974 (0.1076974)
可能是由于刚才误操作导致出现大量的块损坏
刚才使用DBV检查数据文件的方法是可以在数据库关闭的情况下进行检查的,在数据库open的情况下我们可以使用段的检查方式(需要sysdba的权限进行检查),可以缩小检查的范围
SQL> select tablespace_id,header_file,header_block
  2   from sys_dba_segs
  3  where owner=’TOM’ and segment_name=’TEST’;
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
————- ———– ————
            8           7          130
dbv userid=system/oracle segment_id=8.7.130
DBVERIFY: Release 11.2.0.4.0 – Production on Fri Jun 3 16:06:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : SEGMENT_ID = 8.7.130
Page 132 is marked corrupt
Corrupt block relative dba: 0x01c00084 (file 7, block 132)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 2 rdba: 0x0a676665
 last change scn: 0x0000.00106c07 seq: 0x2 flg: 0x04
 spare1: 0x63 spare2: 0x64 spare3: 0x0
 consistency value in tail: 0x6c070602
 check value in block header: 0x5a7a
 computed block checksum: 0xc942
DBVERIFY – Verification complete
Total Pages Examined         : 8
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1076234 (0.1076234)
同样也可以使用dbms_hm.run_check存储过程进行坏块的检查,dbms_hm.run_check除了可以检查数据文件,也可以检查控制文件和日志文件(可以只检查一个区(extents))。
begin
dbms_hm.run_check(
check_name=>’Data Block Integrity Check’,
run_name=>’HM_CHECK132′,
        input_params=>’BLC_DF_NUM=7;BLC_BL_NUM=132′);
end;
/
select dbms_hm.get_run_report(‘HM_CHECK132’) from dual;
删除所有的报告
exec dbms_hm.drop_schema();
exp/expdp在导出数据时会扫描每个数据块,所以也会检查每个数据块的物理错误
[oracle@PROD3 ~]$ exp tom/oracle owner=tom
Export: Release 11.2.0.4.0 – Production on Fri Jun 3 16:23:03 2016
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 US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOM
About to export TOM’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TOM’s tables via Conventional Path …
. . exporting table                           TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 7, block # 132)
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/PROD3/test01.dbf’
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
所有损坏的数据块都可以通过v$database_block_corruption查询到
SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
         4        210          2                  0 CORRUPT
         4        524          1                  0 CORRUPT
         7        132          1                  0 CORRUPT
损坏存放行数据块的恢复
dbv file=/u01/app/oracle/oradata/PROD3/test02.dbf blocksize=8192
DBVERIFY: Release 11.2.0.4.0 – Production on Fri Jun 3 17:19:36 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/PROD3/test02.dbf
Page 139 is marked corrupt
Corrupt block relative dba: 0x0200008b (file 8, block 139)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 2 rdba: 0x0a676665
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x63 spare2: 0x64 spare3: 0x0
 consistency value in tail: 0x00000001
 check value in block header: 0xa78b
 computed block checksum: 0xc88b
DBVERIFY – Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1152
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1080609 (0.1080609)
使用备份的数据进行块的恢复
RMAN> recover datafile 8 block 139
2> ;
Starting recover at 03-JUN-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T171747_co2lsw49_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T171747_co2lsw49_.bkp tag=TAG20160603T171747
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-JUN-16
————-recover 命令不支持增量备份———————–
RMAN备份时的受损块,db_block_checking打开时的受损块和dbms_hm.run_check发现的受损块都可以通过v$database_block_corruption视图查询
—————-恢复方法
recover corruption list;
数据块的逻辑错误
创建range分区表
SQL> create table scott.emp1 (empno number(4),ename varchar2(10),deptno number(2))
  2  partition by range (deptno)
  3  (partition p1 values less than (10) tablespace users,
  4  partition p2 values less than (20) tablespace users,
  5  partition p3 values less than (30)) tablespace users;
Table created.
SQL> create table scott.dummy_y as select empno,ename,deptno
  2  from scott.emp
  3  where deptno < 10;
SQL> create table scott.dummy_x as select empno,ename,deptno
  2  from scott.emp
  3  where deptno < 20;
SQL> create table scott.dummy_z as select empno,ename,deptno
  2  from scott.emp
  3  where deptno >= 20 and deptno < 30;
SQL> alter table scott.emp1 exchange partition p1
  2  with table scott.dummy_y;
SQL> alter table scott.emp1 exchange partition p2
  2  with table scott.dummy_x;
alter table scott.emp1 exchange partition p3
  2  with table scott.dummy_z;
emp1表的数据存放在240,248开始的16个块中,数据文件为4号
SQL> col name for a40
SQL> select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b  where a.file_id=b.file# and a.owner=’SCOTT’ and a.segment_name=’EMP1′;
  FILE_ID   BLOCK_ID     BLOCKS NAME
———- ———- ———- —————————————-
         4        240          8 /u01/app/oracle/oradata/PROD3/users01.db
                                 f
         4        248          8 /u01/app/oracle/oradata/PROD3/users01.db
                                 f
deptno是30的记录不能插入emp1表
SQL> insert into scott.emp1 values(1000,’SCOTT’,30);
insert into scott.emp1 values(1000,’SCOTT’,30)
                  *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
使用交换分区的方法把deptno=30的值插入emp1表
SQL> create table scott.emp2 (empno number(4),ename varchar2(10),deptno number(2)) tablespace users;
Table created.
SQL> insert into scott.emp2 values(1000,’SCOTT’,30);
1 row created.
SQL> alter table scott.emp1 exchange partition p3 with table scott.emp2 without validation;
Table altered.
deptno 为30的值已经插入到emp1
SQL> select * from scott.emp1 partition (p3);
     EMPNO ENAME          DEPTNO
———- ———- ———-
      1000 SCOTT              30
使用analyze字句可以检查出上述模拟的逻辑块错误
analyze语句要求有一张保存分析结果的表,执行utlvalid.sql脚本建立invalid_row表保存分析结果
SQL> @?/rdbms/admin/utlvalid.sql
Table created.
SQL> desc invalid_rows
 Name                                      Null?    Type
 —————————————– ——– —————————-
 OWNER_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 HEAD_ROWID                                         ROWID
 ANALYZE_TIMESTAMP                                  DATE
SQL> analyze table scott.emp1 validate structure;
Table analyzed.
SQL> select table_name,partition_name,head_rowid from invalid_rows;
TABLE_NAME                     PARTITION_NAME                 HEAD_ROWID
—————————— —————————— ——————
EMP1                           P3                             AAAVWAAAEAAAAEEAAA
analyze分析结果发现一条rowid为AAAVWAAAEAAAAEEAAA信息,这条记录就是deptno=30的记录
SQL> select * from scott.emp1 where rowid=’AAAVWAAAEAAAAEEAAA’;
     EMPNO ENAME          DEPTNO
———- ———- ———-
      1000 SCOTT              30
SQL>
关于逻辑坏块的修复,是不是直接删除就可以了?先谢过大神,帮我解答疑问吧。

发表评论

电子邮件地址不会被公开。 必填项已用*标注