Oracle Database error ORA-01122 ORA-01210 fault recovery

There are friends. data file header error ORA-01122 and ORA-01210 error, the database is not normal open.

Because the platform is win, they seek me to consult win bbed, because the home computer did not bring, can not provide win bbed. I passed the DD part of the file header, and then the Linux platform analysis found that the file header block a large number of bad blocks

Bbed Analysis of bad blocks

Bbed> show all         FILE#            0         BLOCK#           1         OFFSET           0         DBA              0x00000000  (0 0,1)  

       filename        /tmp/30.dbf         BIFILE           BIFILE.BBD         LISTFILE      

          blocksize       8192         mode             browse         EDIT             unrecoverable         IBASE            dec         obase            dec          width           80          count           512          LOGFILE         LOG.BBD          SPOOL            no &nbsp Bbed> set count 64         COUNT   

        64   Bbed> map  File: /tmp/30.dbf  (0)  Block: 1                                       dba:0x00000000------------------------------------------------------------BBED-00400:  invalid blocktype  (a)     Bbed> d  File: /tmp/30.dbf  (0)   block: 1                 offsets:    0 to   63            dba:0x00000000------------------------------------------------------------------------ 1ba20000 03004400 bffd8a1d 0000000c acba0000 008f4500 00003455  fc020000   02040000 00000000 00008001 04000000 00000000 00000000  949400b4 94514005     <32 bytes per line>   bbed> set  Block +1         BLOCK#           2   Bbed> map  File: /tmp/30.dbf  (0)  Block: 2                                       dba:0 x00000000------------------------------------------------------------Bbed-00400: invalid blocktype   (a)     Bbed> d  File: /tmp/30.dbf  (0)  Block: 2                Offsets:     0 to   63           dba : 0x00000000------------------------------------------------------------------------ 1ba20000  04004400 bffd8a1d 0000000c a6e00000 008f4500 00003455 fc020000    0204e81f 00000000 0000241e 05000000 00000000 00000000 11fc297f b426fe2b      <32 bytes per line>   bbed> set block +1  &

Nbsp;      block#          3   Bbed> d  File: /tmp/30.dbf  (0)  Block: 3                 offsets:    0 to    63           dba:0x00000000------------------------ ------------------------------------------------ 1ba20000 05004400 bffd8a1d 0000000c  780a0000 008f4500 00003455 fc020000   0204e81f 00000000 0000c001  06000000 00000000 00000000 2969a0d2 d30168a2     <32 bytes per  line>   Bbed> set block +1         block#           4   Bbed> d  File: /tmp/30.dbf   (0)  Block: 4                 Offsets:    0 to   63            dba:0x00000000------------------------------------------------------------------------ 1ba20000 06004400 bffd8a1d 0000000c 6c5a0000 008f4500  00003455 fc020000   0204e81f 00000000 0000f81d 07000000 00000000  00000000 7b51d409 6dc7ca4d     <32 bytes per line>   bbed>& Nbsp;set block +1         BLOCK#           5   Bbed> d  File: /tmp/30.dbf  (0)  block:  5                offsets :    0 to   63            dba:0x00000000------------------------------------------------------------------------  1ba20000 07004400 bffd8a1d 0000000c c5600000 008f4500 00003455 fc020000   &NBSP;02040000 00000000 0000c001 08000000 00000000 00000000 14514005 25145200     <32 bytes per line>   Bbed> set block +1   

      block#          6   Bbed> d  File: /tmp/30.dbf  (0)  Block: 6                 Offsets:    0 to    63           dba:0x00000000---------------- -------------------------------------------------------- 1ba20000 08004400 bffd8a1d  0000000c 60480000 008f4500 00003455 fc020000   0204e81f 00000000  0000c301 09000000 00000000 00000000 c2a1606a 7615130a     <32  Bytes per line>   Bbed> set block +1          block#          7   Bbed> d  File:  /tmp/30.dbf  (0)  Block: 7                 Offsets:    0 to   63            dba:0x00000000------------------------------------------ ------------------------------ 1ba20000 09004400 bffd8a1d 0000000c e3430000  008f4500 00003455 fc020000   0204e81f 00000000 00000002 0a000000 

00000000 00000000 00a28a28 00a28a28     <32 bytes per line>   Bbed> set block +1         BLOCK#    &nbsP;      8   Bbed> d  File: /tmp/30.dbf  (0)  block:& Nbsp;8                offsets:     0 to   63            dba:0x00000000------------------------------------------------------------------------  1ba20000 0a004400 07fe8a1d 0000000c fc000000 008f4500 00003455 fc020000    0205e81f 00000000 0000f41d 00000000 00000000 00000000 ffd8ffe0  00104a46     <32 bytes per line>   Bbed> set block +1 & nbsp;       block#           9   Bbed> d  File: /tmp/30.dbf  (0) &NBSP;BLOCK:&NBSP;9&NBSP;&NBSP;&NBSP;&NBSp;            offsets:    0  to   63           dba:0x00000000-- ---------------------------------------------------------------------- 1ba20000 0b004400  07fe8a1d 0000000c 48da0000 008f4500 00003455 fc020000   0205e81f  00000000 0000c601 01000000 00000000 00000000 b47d69d3 7fa96a6f      <32 bytes per line>   Bbed> set block +1      &NBSP;&NBSP;&NBSP;&NBSP;BLOCK#&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;10   BBED>  d  File: /tmp/30.dbf  (0)  Block: 10                offsets:    0 to   63            dba:0x00000000------------------------------------------- ----------------------------- 1ba20000 0c004400 07fe8a1d 0000000c be0f0000  008f4500 00003455 fc020000   0205e81f 00000000 0000181d 02000000 

00000000 00000000 9de3e868 4782d83a     <32 bytes per line>   Bbed> set block +1         BLOCK#           11   Bbed> d  File: /tmp/30.dbf  (0) & nbsp block: 11                offsets:    0 to   63            dba:0x00000000------------------------------------------------------------------------ 1ba20000 0d004400 07fe8a1d 0000000c 9cd00000 008f4500 00003455 fc020000    0205e81f 00000000 0000241e 03000000 00000000 00000000 dead1259  5919e385     <32 bytes per line>   Bbed> set block +1 & nbsp;       block#           12   Bbed> d  File: /tmp/30.dbf  (0)  Block: 12                offsets:    0 to    63           dba:0x00000000---------- -------------------------------------------------------------- 1ba20000 0e004400 07fe8a1d  0000000c df450000 008f4500 00003455 fc020000   0205e81f 00000000 00004001 04000000 00000000 00000000 31d9a292 9698828a     <32  bytes per line>   Bbed> set block +1          block#          13   BBED> d   file: /tmp/30.dbf  (0)  Block: 13                Offsets:    0 to   63            dba:0x00000000------------------------------------------ ------------------------------ 1ba20000 0f004400 07fe8a1d 0000000c 18790000  008f4500 00003455 fc020000   02050000 00000000 00000002 05000000  00000000 00000000 b93f8235 5ea063b7     <32 bytes per line>

Take block 1 rdba (04004400? reverse storage) Analysis [win file copy to Linux use bbed to see the difference 1 Block] can be out block information for file=1, block=262148, obviously wrong.

Analysis of file header damage through Dul

Data unloader: 10.2.0.6.9 - internal only - on tue sep 29  22:15:22 2015 with 64-bit io functions   copyright  (c)  1994 2015

 bernard van duijnen all rights reserved.    strictly oracle internal use only     dul: warning:  recreating file  "Dul.log" Reading scannedlobpage.dat 1204 entries loaded and  sorted 1204 entries reading seg.dat 0 entries loaded Reading EXT.dat  44 entries loaded and sorted 44 entries Reading COMPATSEG.dat 0  entries loaded   dul: warning: wrong dba  0x00440004  (file=1,  block=262148)   (ignored) dul: error: while processing file# 30 block#  1 Dul: warning: found miSMATCH WHILE CHECKING FILE E:\TEMP\SHEBAO\30.DBF Dul: warning: dul osd_ Parameter or control.dul configuration error dul: warning: given file  Number (a)  IN CONTROL FILE DOES NOT MATCH FILE# IN DBA (1) DUL:  Warning: Wrong DBA  0X00440004  (file=1, block=262148)   (ignored) DUL:  error: while processing file# 30 block# 1 DUL>

The bbed and Dul prove that the header of the file is heavily damaged and that no physical backup of the file has been made, so it's a great recovery.

Analyzing Oracle Database Recovery Check Results

Through the analysis of the Oracle database exception Recovery Check script (Oracle DB Recovery check), we accidentally found that a good character, found the exception of the file creation time for 2015-09-26 19:39:33, further communication with customers, This file is stored as a picture, a small amount of loss can be allowed, priority recovery business

With this conclusion, it is easy to deal with, directly offline the exception file, and then analyze the missing table
This determines when a small number of extent data for the LOB dictionary is allocated to file 30

To avoid an error when querying for a lob, use the update to circumvent the LOB to avoid the problem

create table corrupt_lobs  (CORRUPT_ROWID ROWID,TABLE_NAME VARCHAR2);  DECLARE   n number;  begin   for cursor_lob in  (select rowid  r, xff_lob from xff.t_xifenfei)  loop    begin     n : =dbms_lob.instr (Cursor_lob.xff_lob,hextoraw (' 889911 '));    exception       when others then       insert into corrupt_lobs 

values  (CURSOR_LOB.R, ' Xff.t_xifenfei ');       commit;      end;    end loop;   end; /    update xff.t_xifenfei       set xff_lob& Nbsp;= empty_blob ()        where rowid in  (select  Corrupted_rowid from corrupt_lobs);

This recovery is due to good luck, encountered exception file is just recently joined, and are all pictures, customers allow a small amount of loss, if it is not allowed to lose data files, may need to find history of the backup of the file (Oracle 12C First Abnormal recovery-file header bad block), in some cases, If there is no such backup, only the Block 1 can be refactored via bbed (if there are any other exception blocks at once, if too many cannot be processed, at least the chunk 1 will need to be refactored), and then try the Open database or use tools such as Dul (because the file header is corrupted, Tool may not recognize file cannot be recovered)