Corrupt Block-白红宇

Corrupt Block

阅读量：6341 次

发布时间：2019-06-22

本文共 17324 字，大约阅读时间需要 57 分钟。

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

@@@

@@@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

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

转载于:https://blog.51cto.com/majesty/887025

你可能感兴趣的文章

EntityFramework 6.x多个上下文迁移实现分布式事务

flask ORM: Flask-SQLAlchemy【单表】增删改查

你好，C++（16）用表达式表达我们的设计意图——4.1 用操作符对数据进行运算...

查看>>

[转] Mac下快速写博客的软件 MarsEdit

查看>>

Unity的赛车游戏实现思路

查看>>

[Android UI] Shape详解 (GradientDrawable)

查看>>

边学边体验django--HttpRequest 对象

查看>>