Corrupt Block
1)phyical corrupt block
1@@@@prepare phyical corrupt block.
SQL> select header_file,header_block from dba_segments 
where segment_name='DEPARTMENTS' AND OWNER='HR';
HEADER_FILE HEADER_BLOCK
----------- ------------
          5           51
SQL> select file_id,block_id from dba_extents  where 
segment_name='DEPARTMENTS' AND OWNER='HR';
   FILE_ID   BLOCK_ID
---------- ----------
         5         49
RMAN> backup tablespace example;
 
@@@begin broken block.
[oracle@station60 oracle]$ dd of=/u01/app/oracle/oradata/orcl/example01.dbf 
bs=8192 conv=notrunc seek=56
xxxx
EOF  (ctrl+d)
0+2 records in
0+2 records out
9 bytes (9 B) copied, 13.4559 seconds, 0.0 kB/s
@@@
SQL> select department_id,dbms_rowid.rowid_block_number(rowid) 
     from departments;
DEPARTMENT_ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------- ------------------------------------
           10                                   56
           20                                   56
           30                                   56
...........................
@@@
@@@dba_segments
SQL> col segment_name format a30 trunc
SQL> select segment_name,header_block from dba_segments 
     where owner='HR' and segment_name='DEPARTMENTS';
 
SEGMENT_NAME                   HEADER_BLOCK
------------------------------ ------------
DEPARTMENTS                              51
@@@
@@@dba_extents
SQL> select segment_name,block_id from dba_extents 
     where owner='HR' and segment_name='DEPARTMENTS';
 
SEGMENT_NAME                     BLOCK_ID
------------------------------ ----------
DEPARTMENTS                            49
 
@@@
@@@why?
[oracle@station61 orcl]$ dd of=/u01/app/oracle/oradata/orcl/example01.dbf 
bs=8192 conv=notrunc seek=56 << EOF
> corrupt
> EOF
SQL> select *  from hr.departments
  2  ;
select *  from hr.departments
                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 56)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
 
@@@
@@@why? 
[oracle@station61 orcl]$ dd of=/u01/app/oracle/oradata/orcl/example01.dbf 
bs=8192 conv=notrunc seek=51 << EOF
> corrupt
> EOF
SQL> select *  from hr.departments;
select *  from hr.departments
                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 51)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
 
@@@
@@@if db_block_checking=true,it would cost cpu very much.
SQL> show parameter block;  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     16
 
@@@
SQL> select * from departments;
select * from departments
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 56)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
 
@@@backup data could not allow corrupt blocks default.
RMAN> backup tablespace example;
...................................
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571: =================================================
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/
oracle/oradata/orcl/example01.dbf
 
@@@modify the default to 1,it would work.
RMAN> run{
2> set maxcorrupt for datafile 5  to 1;
3> backup tablespace example; 
4> }
 
2@@@@check and relative view
@@@this command would fill the v$backup_corruption
RMAN> backup validate check logical tablespace example;
 
@@@this command would fill the v$copy_corruption
RMAN> backup as copy validate check logical tablespace example;
 
@@@all the information about corruption would accumulate in this table.
SQL> select * from v$database_block_corruption
 
@@@see the alert_orcl.log
[root@station61 bdump]# grep corrupt alert_orcl.log 
Reread of rdba: 0x01400033 (file 5, block 51) found same corrupted data
Reread of rdba: 0x01400038 (file 5, block 56) found same corrupted data
Reread of rdba: 0x01400033 (file 5, block 51) found same corrupted data
Reread of blocknum=51, file=/u01/app/oracle/oradata/orcl/example01.dbf.
 found same corrupt data
Reread of blocknum=51, file=/u01/app/oracle/oradata/orcl/example01.dbf. 
found same corrupt data
Reread of blocknum=51, file=/u01/app/oracle/oradata/orcl/example01.dbf. 
found same corrupt data
Reread of blocknum=51, file=/u01/app/oracle/oradata/orcl/example01.dbf. 
found same corrupt data
Reread of blocknum=51, file=/u01/app/oracle/oradata/orcl/example01.dbf. 
found same corrupt data
....................................
Reread of rdba: 0x01400033 (file 5, block 51) found same corrupted data
 
3@@@@repair
@@@if you have a backup,then restore it as the following:
SQL> shutdown abort;
$rm -rf /u01/app/oracle/oradata/orcl/example01.dbf
SQL> startup 
$rman target / 
RMAN> restore datafile 5; 
RMAN> recover datafile 5; 
RMAN> alter database open; 
 
 
2)logical corrupt block(many secret)
1@@@@create a logical corruption block
SQL> create tablespace tbslogical datafile '/u01/tbslogical.dbf' 
     size 50M nologging;
SQL> create table logical(a number,b varchar2(20)) tablespace tbslogical;
SQL> insert into logical  values(3888,'Red Bull');
SQL> commit; 
 
@@@Note:directly path load would occupy a new block.check that
@@@assure first row and second row are in different blocks.
@@@obviously,first row is in 16# block,second row is in 17# block.
@@@as the following:
SQL> insert /*+ append */  into logical select * from logical;
SQL> commit;
SQL> select a,b,dbms_rowid.rowid_block_number(rowid) from logical;1
         A B                    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- -------------------- ------------------------------------
      3888 Red Bull                                               16
      3888 Red Bull                                               17
@@@make sure two raw is in different block#
 
@@@
@@@create two indexed on table logical.why?
@@@it is used for find the values back.
SQL> conn hr/hr
SQL> create index ilogical_a on logical(a); 
Index created.
SQL> create index ilogical_b on logical(b); 
Index created.
 
@@@
SQL> conn /as sysdba; 
SQL> alter tablespace tbslogical offline immediate;
@@@remove datafile on the disk.
[root@station61 u01]# rm -rf tbslogical.dbf 
@@@
SQL> alter database create datafile '/u01/tbslogical.dbf' ;
Database altered.
@@@
SQL> recover datafile 6; 
Media recovery complete.
@@@
SQL> alter tablespace tbslogical online; 
Tablespace altered.
 
@@@
SQL> select * from hr.logical ;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 17)
ORA-01110: data file 6: '/u01/tbslogical.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
 
2@@@@check
@@@this command would fill the v$backup_corruption
RMAN> backup validate check logical tablespace example;
 
@@@this command would fill the v$copy_corruption
RMAN> backup as copy validate check logical tablespace example;
 
@@@all the information about corruption would accumulate in this table.
SQL> select * from v$database_block_corruption
 
@@@tuning check DML statment using source.
SQL> ed
Wrote file afiedt.buf
  1  select s.sid,s.username,n.name,se.value
  2  from v$session s,v$sesstat se,v$statname n
  3  where s.terminal='pts/3'
  4  and se.sid=s.sid
  5  and se.statistic#=n.statistic#
  6* and n.name='redo szie'
SQL> /
 
3@@@@repair
@@@using package dbms_repair
BEGIN
 DBMS_REPAIR.ADMIN_TABLES (
   table_name => 'REPAIR_TABLE',
   table_type => DBMS_REPAIR.REPAIR_TABLE,
   action => DBMS_REPAIR.CREATE_ACTION,
   tablespace => 'USERS');
END;
 
BEGIN
 DBMS_REPAIR.ADMIN_TABLES (
   table_name => 'ORPHAN_KEY_TABLE',
   table_type => DBMS_REPAIR.ORPHAN_TABLE,
   action => DBMS_REPAIR.CREATE_ACTION,
   tablespace => 'USERS');
END;
 
DECLARE num_corrupt INT;
BEGIN
 num_corrupt := 0;
 DBMS_REPAIR.CHECK_OBJECT (
   schema_name => 'HR',
       object_name => 'DEPARTMENTS',
   repair_table_name => 'REPAIR_TABLE',
   corrupt_count => num_corrupt);
  dbms_output.put_line( 'Find  '|| num_corrupt||' Bolcks.'  );
END;
 
DECLARE num_fix INT;
BEGIN
 num_fix := 0;
 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
       schema_name => 'HR',
       object_name => 'DEPARTMENTS',
       object_type => DBMS_REPAIR.TABLE_OBJECT,
       repair_table_name => 'REPAIR_TABLE',
   fix_count => num_fix);
END;
 
 
@@@
@@@using index to find the values.
DECLARE num_orphans INT;
BEGIN
 num_orphans := 0;
 DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  schema_name => 'HR',
  object_name => 'ILOGICAL_A',
  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;
 
DECLARE num_orphans INT;
BEGIN
 num_orphans := 0;
 DBMS_REPAIR.DUMP_ORPHAN_KEYS (
  schema_name => 'HR',
  object_name => 'ILOGICAL_B',
  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;
 
@@@
@@@first: find lock the row.
@@@
@@@remember the index only occupy one column,
@@@there are many column consist one row
@@@the table logical have two colum:a and b,
@@@using second sql statment find the row,
select * from orphan_key_table where index_name='ILOGICAL_A' 
select * from orphan_key_table where index_name='ILOGICAL_A' AND 
keyrowid||' '='AAAM0YABEAAAIABAAA'||' '
select * from orphan_key_table where index_name='ILOGICAL_A' AND
 keyrowid='AAAM0YABEAAAIABAAA'
 
 
@@@
@@@second: translate the code to number or char.
@@@
SQL> select dump(key) from orphan_key_table;
DUMP(KEY)
--------------------------------------------------------------------------------
Typ=208 Len=16: 2,4,0,0,0,0,8,82,101,100,32,66,117,108,108,254
Typ=208 Len=11: 2,4,0,0,0,0,3,194,39,89,254
 
@@@
@@@char
SQL> select chr(82)||chr(101)||chr(100)||chr(32)||
     chr(66)||chr(117)||chr(108)||chr(108) from dual; 
CHR(82)|
--------
Red Bull   @@@find the original values.
@@@check the ascii table.so clever!!!!
#man ascii
Oct   Dec   Hex   Char
122   82    52    R
145   101   65    e
144   100   64    d
040   32    20    SPACE
102   66    42    B
165   117   75    u
154   108   6C    l
154   108   6C    l
 
@@@
@@@number
@@@according to the theory:
@@@100^(194-193-0)(39-1)=3800,100^(193-193)(89-1)=88
@@@=3888
@@@value-1 for avoiding null.
100^(193-193-0)(2-1)=1   =>select dump(1) from dual; =>193,2
100^(193-193-0)(11-1)=10 =>select dump(10) from dual; =>193,11
100^(194-193-0)(2-1)=100 =>select dump(100) from dual;...
100^(194-193-0)(11-1)=1000
100^(194-193-0)(2-1)+100^(194-193-1)(11-1)=110
100^(194-193-0)(12-1)=1100
 
100^(62-62-0)(101-100)=1
100^(62-62-0)(101-91)=10
100^(62-61-0)(101-100)=100
 
123456.789
100^(195-193-0)(13-1)+
100^(195-193-1)(35-1)+
100^(195-193-2)(57-1)+
100^(195-193-3)(79-1)+
100^(195-193-4)(91-1)=120000+3400+56+0.78+0.009=123456.789
 
-123456.789
100^(62-60-0)(101-89)+
100^(62-60-1)(101-67)+
100^(62-60-2)(101-45)+
100^(62-60-3)(101-23)+
100^(62-60-4)(101-11)=120000+3400+56+0.78+0.009=-123456.789
 
-123456.78901
100^(62-60-0)(101-89)+
100^(62-60-1)(101-67)+
100^(62-60-2)(101-45)+
100^(62-60-3)(101-23)+
100^(62-60-4)(101-11)+
100^(62-60-5)(101-91) 
=120000+3400+56+0.78+0.009+ 0.00001=-123456.78901
 
@@@
@@@add a number 102 for negative number sort
SQL> select dump(-17) from dual; 
DUMP(-17)
----------------------
Typ=2 Len=3: 62,84,102
SQL> select dump(-117) from dual; 
DUMP(-117)
--------------------------
Typ=2 Len=4: 61,100,84,102
 
@@@
SQL> select * from dict where table_name like '%CORRUP%'
V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION
GV$BACKUP_CORRUPTION
GV$COPY_CORRUPTION
GV$DATABASE_BLOCK_CORRUPTION
 
 
3)repair usage
1@@@@logical
@@@skip ogical corrupt block make that table useable
SQL> desc dbms_repair
PROCEDURE SKIP_CORRUPT_BLOCKS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA_NAME                    VARCHAR2                IN
 OBJECT_NAME                    VARCHAR2                IN
 OBJECT_TYPE                    BINARY_INTEGER          IN     DEFAULT
 FLAGS                          BINARY_INTEGER          IN     DEFAULT
 
@@@
@@@logical exist a logical corrupt blocks.
@@@the logical corrupt row would be missed.
@@@find the missing data using index.....
@@@then insert them back.just rowid change.
SQL> exec dbms_repair.skip_corrupt_blocks('HR','LOGICAL');
 
 
 
@@@
@@@logical would be marked in dictionary(tbs system)
SQL> select skip_corrupt from dba_tables 
     where table_name='LOGICAL';
 
 
2@@@@physical
@@@"minmony minimally invasive surgery"=>online
@@@or restore that tbs back=>offline
SQL> select * from departments; @@@ find the block location
SQL> analyze
RMAN> blockrecover datafile 5 block 56;
RMAN> BLOCKRECOVER DATAFILE 2 BLOCK 12, 13
> DATAFILE 7 BLOCK 5, 98, 99 DATAFILE 9 BLOCK 19;
@@@
RMAN>run{
> BLOCKRECOVER DATAFILE 3 BLOCK 1,2,3,4,5
> TABLESPACE sales DBA 4194405, 4194409, 4194412
> FROM DATAFILE COPY;
}
@@@
RMAN> BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405
> FROM TAG "weekly_backup";
@@@
RMAN> BLOCKRECOVER TABLESPACE SYSTEM DBA 4194404, 4194405 RESTORE
> UNTIL TIME 'SYSDATE-2';
@@@
RMAN> BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE
> UNTIL SCN 100;
@@@
RMAN> BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE
> UNTIL SEQUENCE 7024;
 
 
 
4)dbv quickly Check Corrupt Block
1@@@@using less resource to check corruption block.
@@@this situation is block 51 is corrupted.
[oracle@station60 orcl]$ dbv USERID=hr/hr segment_id=6.5.51
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Dec 21 13:02:23 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved
 
DBVERIFY - Verification starting : SEGMENT_ID = 6.5.51
Page 56 is marked corrupt
Corrupt block relative dba: 0x01400038 (file 5, block 56)   @@@find it.
Bad header found during dbv: 
Data in bad block:
 type: 120 format: 0 rdba: 0x464f450a
 last change scn: 0x0000.0006d10a seq: 0x1 flg: 0x06
 spare1: 0x78 spare2: 0x78 spare3: 0x0
 consistency value in tail: 0xd1670601
 check value in block header: 0x474b
 computed block checksum: 0xa056
 
DBVERIFY - Verification complete
Total Pages Examined         : 8
Total Pages Processed (Data) : 4
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            : 0
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 446817 (0.446817)
 
 
@@@
[oracle@station60 orcl]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf blocksize=8192
@@@
@@@physical:
SQL> select tablespace_id,header_file,header_block 
from sys_dba_segs 
where owner='HR' and segment_name='DEPARTMENTS';
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
            6           5           51
[oracle@station61 orcl]$ dbv userid=hr/hr segment_id=6.5.51
DBVERIFY - Verification starting : SEGMENT_ID = 6.5.51
DBV-00111: OCI failure (2494) (ORA-01002: fetch out of sequence
)
@@@
@@@logical:
@@@logical if influx is not equal to 0,it is meaning that
@@@many blocks are querying and so on,so do the command again
@@@until total pages influx=0.
SLQ>select tablespace_id,header_file,header_block 
from sys_dba_segs 
where owner='HR' and segment_name='LOGICAL'
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
            7           6           11
 
[oracle@station61 orcl]$ dbv userid=hr/hr segment_id=7.6.11
DBVERIFY - Verification starting : SEGMENT_ID = 7.6.11
DBV-00200: Block, dba 25165841, already marked corrupted
 
DBVERIFY - Verification complete
Total Pages Examined         : 16
Total Pages Processed (Data) : 5
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 7
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0   
Highest block SCN            : 549375 (0.549375)
 
 
5)flashback table about corrupt block(extracurricular)
1@@@@the same,not.
@@@physical:
SQL> alter table hr.departments enable row movement;
SQL> flashback table hr.departments to timestamp sysdate-1/24;
flashback table hr.departments to timestamp sysdate-1/24
                   *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 5, block # 51)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
 
@@@logical:
SQL> alter table hr.logical enable row movement;
Table altered.
SQL> flashback table hr.logical to timestamp sysdate-1/24;
flashback table hr.logical to timestamp sysdate-1/24
                   *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
 
 
6)exp and expdp about corrupt block(extracurricular)
1@@@@usage
@@@if you have a physical corrupt block 
@@@and a logical corrupt block
@@@
@@@logical corrupt block.
SQL> select * from hr.logical ;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 17)
ORA-01110: data file 6: '/u01/tbslogical.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
no rows selected
 
@@@physical corrupt block.
SQL> select * from hr.departments;
select * from hr.departments
                 *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 51)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
 
 
@@@exp
@@@some warnings ,but it could do.
[oracle@station61 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@station61 orcl]$ exp hr/hr tables=departments
 file=physical02.dmp
.....................
About to export specified tables via Conventional Path ...
. . exporting table                    DEPARTMENTS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 51)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
Export terminated successfully with warnings.
[oracle@station61 orcl]$ ls physi*
physical02.dmp
 
[oracle@station61 orcl]$ exp hr/hr tables=logical 
file=physical01.dmp
 
@@@expdp
@@@prompt:0k,it could do,but it is bad, no mean.
[oracle@station61 oracle]$ expdp hr/hr directory=dir61 
tables=departments dumpfile=expdp_phy.dmp
..............
[oracle@station61 dir61]$ du -sh * 
112K    expdp_phy.dmp
4.0K    export.log