月归档：八月 2011

SQL> startup
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
提示数据文件2需要恢复，首选是recover datafile 2;，如果失败，可以考虑bbed修改scn的办法

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1  107374278108
         2  107374278108
         3  107374278108
         4  107374278108
         5  107374278108
         6  107374278108
         7  107374278108
         8  107374278108
         9  107374278108
        10  107374278108
        11  107374278108

11 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         2 ONLINE   107374277136

[oracle@localhost tmp]$ bbed parfile=/tmp/parfile.cnf
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 22 06:05:34 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /opt/oracle/oradata/xifenfei/system01.dbf                         1280
     2  /opt/oracle/oradata/xifenfei/xff01.dbf                            1280

BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @140     
   struct kcvcpscn, 8 bytes                 @140     
*    ub4 kscnbas                           @140      0x000175dc
      ub2 kscnwrp                           @144      0x0019
* ub4 kcvcptim                             @148      0x2d49fbbb
   ub2 kcvcpthr                             @152      0x0001
   union u, 12 bytes                        @156     
      struct kcvcprba, 12 bytes             @156     
         ub4 kcrbaseq                       @156      0x00000005
         ub4 kcrbabno                       @160      0x00008e05
         ub2 kcrbabof                       @164      0x0010
      struct kcvcptr, 12 bytes              @156     
         struct kcrtrscn, 8 bytes           @156     
            ub4 kscnbas                     @156      0x00000005
            ub2 kscnwrp                     @160      0x8e05
         ub4 kcrtrtim                       @164      0x09110010
   ub1 kcvcpetb[0]                          @168      0x02
   ub1 kcvcpetb[1]                          @169      0x00
   ub1 kcvcpetb[2]                          @170      0x00
   ub1 kcvcpetb[3]                          @171      0x00
   ub1 kcvcpetb[4]                          @172      0x00
   ub1 kcvcpetb[5]                          @173      0x00
   ub1 kcvcpetb[6]                          @174      0x00
   ub1 kcvcpetb[7]                          @175      0x00

BBED> p kcvfhcpc
*ub4 kcvfhcpc                                @176      0x0000007a

BBED> p kcvfhccc
*ub4 kcvfhccc                                @184      0x00000079
星号表示使用bbed修改datafile header scn需要考虑的地方

SQL> select to_char(to_number('19000175dc','xxxxxxxxxxxx'),'999999999999') from dual;

TO_CHAR(TO_NU
-------------
 107374278108
证实system01.dbf的scn为107374278108和v$datafile查询到的一致

BBED> set dba 2,1
        DBA             0x00800001 (8388609 2,1)

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @140     
   struct kcvcpscn, 8 bytes                 @140     
      ub4 kscnbas                           @140      0x00017210
      ub2 kscnwrp                           @144      0x0019
   ub4 kcvcptim                             @148      0x2d49fa27
   ub2 kcvcpthr                             @152      0x0001
   union u, 12 bytes                        @156     
      struct kcvcprba, 12 bytes             @156     
         ub4 kcrbaseq                       @156      0x00000005
         ub4 kcrbabno                       @160      0x00006f18
         ub2 kcrbabof                       @164      0x0010
      struct kcvcptr, 12 bytes              @156     
         struct kcrtrscn, 8 bytes           @156     
            ub4 kscnbas                     @156      0x00000005
            ub2 kscnwrp                     @160      0x6f18
         ub4 kcrtrtim                       @164      0x09110010
   ub1 kcvcpetb[0]                          @168      0x02
   ub1 kcvcpetb[1]                          @169      0x00
   ub1 kcvcpetb[2]                          @170      0x00
   ub1 kcvcpetb[3]                          @171      0x00
   ub1 kcvcpetb[4]                          @172      0x00
   ub1 kcvcpetb[5]                          @173      0x00
   ub1 kcvcpetb[6]                          @174      0x00
   ub1 kcvcpetb[7]                          @175      0x00

BBED> p kcvfhcpc
ub4 kcvfhcpc                                @176      0x00000034

BBED> p kcvfhccc
ub4 kcvfhccc                                @184      0x00000033


SQL> select to_char(to_number('1900017210','xxxxxxxxxxxx'),'999999999999') from dual;

TO_CHAR(TO_NU
-------------
 107374277136
和v$recover_file视图中查询出来一致

BBED> set dba 2,1 offset 140
        DBA             0x00800001 (8388609 2,1)
        OFFSET          140

BBED> show 
        FILE#           2
        BLOCK#          1
        OFFSET          140
        DBA             0x00800001 (8388609 2,1)
        FILENAME        /opt/oracle/oradata/xifenfei/xff01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /tmp/list
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           32
        LOGFILE         log.bbd
        SPOOL           No

BBED> m /x dc750100
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  140 to  171           Dba:0x00800001
------------------------------------------------------------------------
 dc750100 19000000 27fa492d 01000000 05000000 186f0000 10001109 02000000 

 <32 bytes per line>

BBED> m /x bbfb492d offset 158
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  158 to  189           Dba:0x00800001
------------------------------------------------------------------------
 bbfb492d 00001000 11090200 00000000 00003400 00004cc0 492d3300 00000000 

 <32 bytes per line>

BBED> m /x 7a000000 offset 176
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  176 to  207           Dba:0x00800001
------------------------------------------------------------------------
 7a000000 4cc0492d 33000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 79000000 offset 184
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 1                Offsets:  184 to  215           Dba:0x00800001
------------------------------------------------------------------------
 79000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>
注意：p打印出来的值和m修改的值可能不是完全一致（和cpu的计算类型有关，常用的intel cpu中是倒序存储），可以选择先dump正确的相关值，然后m修改
如：p打印出来的值为：80084d1f时，m修改时要为： 1f4d0880

BBED> sum apply dba 2,1
Check value for File 2, Block 1:
current = 0x7ece, required = 0x7ece


SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile
提示控制文件太老，需要重建控制文件

SQL> shutdown abort
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/opt/oracle/oradata/xifenfei/redo01.log'  SIZE 100M,
  GROUP 2 '/opt/oracle/oradata/xifenfei/redo02.log'  SIZE 100M,
  GROUP 3 '/opt/oracle/oradata/xifenfei/redo03.log'  SIZE 100M
DATAFILE
  '/opt/oracle/oradata/xifenfei/system01.dbf',
  '/opt/oracle/oradata/xifenfei/xff01.dbf',
  '/opt/oracle/oradata/xifenfei/cwmlite01.dbf',
  '/opt/oracle/oradata/xifenfei/drsys01.dbf',
  '/opt/oracle/oradata/xifenfei/example01.dbf',
  '/opt/oracle/oradata/xifenfei/indx01.dbf',
  '/opt/oracle/oradata/xifenfei/odm01.dbf',
  '/opt/oracle/oradata/xifenfei/tools01.dbf',
  '/opt/oracle/oradata/xifenfei/users01.dbf',
  '/opt/oracle/oradata/xifenfei/xdb01.dbf',
  '/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   
Control file created.

SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/xifenfei/temp01.dbf'
  2       SIZE 32505856  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
重建控制文件后，需要添加临时文件

补充说明：
1、datafile 的file header 存储在第一个block里
2、Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）
（1）kscnbas (at offset 140) – SCN of last change to the datafile.
（2）kcvcptim (at offset 148) -Time of the last change to the datafile.
（3）kcvfhcpc (at offset 176) – Checkpoint count.
（4）kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误
使用Oracle Recovery Tools一键式解决给问题

BBED> map /v
 File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
 Block: 530                                   Dba:0x00800212
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    b2 ktbbhict                             @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @92      
    ub1 kdbhflag                            @92      
    b1 kdbhntab                             @93      
    b2 kdbhnrow                             @94      
    sb2 kdbhfrre                            @96      
    sb2 kdbhfsbo                            @98      
    sb2 kdbhfseo                            @100     
    b2 kdbhavsp                             @102     
    b2 kdbhtosp                             @104     

 struct kdbt[1], 4 bytes                    @106     
    b2 kdbtoffs                             @106     
    b2 kdbtnrow                             @108     

 sb2 kdbr[101]                              @110     

 ub1 freespace[4270]                        @312     

 ub1 rowdata[3606]                          @4582    

 ub4 tailchk                                @8188    


BBED> 


////////////////////////////////////////////////////////////////////////////////////////
//////////// Block Header Structure,  20 bytes//////////////////////////////////////////
BBED> p kcbh
struct kcbh, 20 bytes                       @0       
    ub1 type_kcbh                           @0    -- Block Type 
                                                  -- 01 - Undo segment header
                                                  -- 02 - Undo data block
                                                  -- 03 - Save undo header
                                                  -- 04 - Save undo data block
                                                  -- 05 - Data segment header  
                                                  -- 06 - Trans data, KTB managed data block(with ITL)
                                                  -- 07 - Temp table data block (no ITL)
                                                  -- 08 - Sort key
                                                  -- 09 - Sort Run
                                                  -- 10 - Segment free list block
                                                  -- 11 - Data file header
    ub1 frmt_kcbh                           @1    -- Block Format 1=Oracle7, 2=Oracle8+   
    ub1 spare1_kcbh                         @2    -- Not used, filler field   
    ub1 spare2_kcbh                         @3    -- Not used, filler field   
    ub4 rdba_kcbh                           @4    -- RDBA (4 bytes) - Relative Data Block Address   
    ub4 bas_kcbh                            @8    -- SCN Base (4 bytes)  
    ub2 wrp_kcbh                            @12   -- SCN Wrap (2 bytes)   
    ub1 seq_kcbh                            @14   -- Sequence Number, incremented for every change made to the block at the same SCN    
    ub1 flg_kcbh                            @15   -- Flag:   
                                                  -- 0x01 New Block
                                                  -- 0x02 Delayed Logging Chang advanced SCN/seq 
                                                  -- 0x04 Check value saved - block XOR's to Zero
                                                  -- 0x08 Temporary block
    ub2 chkval_kcbh                         @16   -- Optional block checksum (if DB_BLOCK_CHECKSUM=TRUE)   
    ub2 spare3_kcbh                         @18   -- Not used, filler field  
 
 /////////////////////////////////////////////////////////////////////////////////////////
/////////Transaction Fixed Header Structure, 72 Bytes////////////////////////////////////
BBED> p ktbbh
 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20     -- Block type (1=DATA, 2=INDEX) 
    union ktbbhsid, 4 bytes                 @24     -- Segment/Object ID
    struct ktbbhcsc, 8 bytes                @28     -- SCN at last block cleanout 
    b2 ktbbhict                             @36     -- Number of ITL slots 
    ub1 ktbbhflg                            @38     -- 0=on the freelist 
    ub1 ktbbhfsl                            @39     -- ITL TX freelist slot 
    ub4 ktbbhfnx                            @40     -- DBA of next block on the freelist 
    struct ktbbhitl[2], 48 bytes            @44     -- ITL list index, each ITL takes up 24 bytes 

//////////////////////////////////////////////////////////////////////////////////////////
///////////////Data Header Structure, 14 bytes////////////////////////////////////////////
BBED> p kdbh
 struct kdbh, 14 bytes                      @100     
    ub1 kdbhflag                            @100    -- N=pctfree hit(clusters)
                                                    -- F=do not put on freelist
                                                    -- K=flushable cluster keys 
    b1 kdbhntab                             @101    -- Number of tables (>1 in clusters) 
    b2 kdbhnrow                             @102    -- Number of rows (2 bytes)
    sb2 kdbhfrre                            @104    -- First free row entry index; -1=you have to add one 
    sb2 kdbhfsbo                            @106    -- Freespace begin offset 
    sb2 kdbhfseo                            @108    -- Freespace end offset 
    b2 kdbhavsp                             @110    -- Available space in the block 
    b2 kdbhtosp                             @112    -- Total available space when all TXs commit 

////////////////////////////////////////////////////////////////////////////////////////
/////////////////////Table Directory Entry Structure, 4 bytes///////////////////////////
BBED> p kdbt
 struct kdbt[1], 4 bytes                    @114     
    b2 kdbtoffs                             @114     
    b2 kdbtnrow                             @116     

////////////////////////////////////////////////////////////////////////////////////////
////////////////// Row Directory ///////////////////////////////////////////////////////
BBED> p kdbr[100]
 sb2 kdbr[100]                                @310     


////////////////////////////////////////////////////////////////////////////////////////
///////////////// Free Space ///////////////////////////////////////////////////////////
BBED> p freespace[4269]
 ub1 freespace[4269]                        @4581     

///////////////////////////////////////////////////////////////////////////////////////
/////////////////////Row Data//////////////////////////////////////////////////////////
BBED> p rowdata[3605]
ub1 rowdata[3605]                           @8187     0x00   
 
//////////////////////////////////////////////////////////////////////////////////////
/////////////////////Block Tail Check, 4 bytes////////////////////////////////////////
BBED> p tailchk
ub4 tailchk                                 @8188     0x24500601  

说明事宜：
1、tailchk=Lower order two bytes of SCN Base(bas_kcbh) + Block Type(type_kcbh) + SCN Seq(seq_kcbh)
2、块的scn为：scn=wrp_kcbh+bas_kcbh
求scn语句：select to_char(to_number(‘scn’,’xxxxxxxxxx’),’999999999999′) from dual;
3、dba求文件号，块号为：

set serveroutput on
declare
   p_dba   VARCHAR2 (255) :='0x00800212';
   l_str   VARCHAR2 (255) DEFAULT NULL;
BEGIN
    l_str :=
         'datafile# is:'
      || DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
      || chr(10)||'datablock is:'
      || DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
   dbms_output.put_line(l_str);
END;

一、创建模拟环境
SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;

Table created.

SQL> insert into hr.xifenfei values(1,’xifenfei’);

1 row created.

SQL> insert into hr.xifenfei values(2,’xff’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.xifenfei;

ID NAME
———- ——————–
1 xifenfei
2 xff

SQL> select rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from hr.xifenfei;

ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查询file#，block，后面恢复要用

SQL> delete from hr.xifenfei where id=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from hr.xifenfei;

ID NAME
———- ——————–
1 xifenfei

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

二、bbed恢复删除数据
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:

BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0×00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No

BBED> set dba 2,530
DBA 0×00800212 (8389138 2,530)

BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2

<32 bytes per line>

BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….

<16 bytes per line>

BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0×00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …<......xff,.. 02c10208 78696665 6e666569 010680e2 l ....xifenfei.... <16 bytes per line>

BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0×00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….

<16 bytes per line>

BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0×00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .<......xff,.... 02087869 66656e66 65690106 80e2 l ..xifenfei.... <16 bytes per line>

BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l <......xff,..... 08786966 656e6665 69010680 e2 l .xifenfei.... <16 bytes per line>
通过尝试，推断出来3c的offset

BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2

<32 bytes per line>
修改3c为2c

BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9

三、核对结果
SQL> startup
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;

ID NAME
———- ——————–
1 xifenfei
2 xff

说明：
1）如果数据未删除：row flag的值为 32+8+4=44或者0x2c
2）如果数据被删除：row flag的值为 32+16+8+4=60或者0x3c