分类目录归档：数据库

连续两次REMOTE_LISTENER 设置为null导致pmon和listener异常

发表于 2012 年 5 月 30 日 由 惜分飞

平台系统版本相关信息

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter cluster;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string      192.168.16.11

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-30 11:07:12

pmon和监听负载
pmon和LISTENER进程负载均比较高

  PID     %CPU ResSize    Char Command    
10617230  72.9  143924 21014  ora_pmon_ahunicom1  
22675560  49.9  142000  1547  oracleahunicom1 (LOCAL=NO)
 5243206  30.6   49728  2579  /oracle10/app/product/db/10.2.0/bin/tnslsnr LISTENER -inherit

监听日志
每秒钟很多类此pmon注册监听信息

27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0
27-FEB-2012 14:12:04 * service_update * ahunicom1 * 0

通过这两点可以确定是因为pmon在不停的动态注册监听导致监听日志,pmon,listener进程异常

查询MOS[ID 982068.1]
问题原因

After altering the value of the parameter REMOTE_LISTENER, 
excessive CPU is seen for the TNS listener process (TNSLSNR) and the listener.log file grows rapidly. 
Alert log confirms the REMOTE_LISTENER parameter in the SPFILE was altered.
Listener.log shows continuous service_update triggered from PMON to the TNS listener, 100's per second. 
REMOTE_LISTENER had been set to null twice

查询alert日志,果真发现:
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH SID='AHUNICOM1';
ALTER SYSTEM SET remote_listener='' SCOPE=BOTH;

解决方案

alter system set remote_listener = 'remote_rac' scope=memory sid = 'AHUNICOM1';
alter system set remote_listener = '' scope=memory sid = 'AHUNICOM1';
--然后重启节点,pmon和监听恢复正常
发表在 Oracle 监听 | 2 条评论

使用bbed修复损坏datafile header

发表于 2012 年 5 月 29 日 由 惜分飞

对于此类oracle的block编辑工作,可以通过自研的图形化小工具Oracle Recovery Tools工具快速修改
软件下载:OraRecovery下载
使用说明:使用说明
相关信息和准备工作

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-29 19:39:48

启动数据块异常

SQL> startup
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
ORA-01115: IO error reading block from file 11 (block # 1)
ORA-27072: skgfdisp: I/O error

bbed检测datafile header

[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192  listfile=/home/oracle/bbed.file mode=edit

BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 05:29:37 2012

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

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

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /home/oracle/users01.dbf                                             0
     2  /home/oracle/system01.dbf.head                                       0
     3  /home/oracle/data11.ora                                              0
     4  /u01/oracle/oradata/xifenfei/system01.dbf                            0
     5  /u01/oracle/oradata/xifenfei/users01.dbf                             0
     6  /home/oracle/data11.ora.10                                           0
    11  /u01/oracle/oradata/xifenfei/bbed01.dbf                              0
    12  /u01/oracle/oradata/xifenfei/bbed02.dbf                              0

BBED> set file 11
        FILE#           11

BBED> set block 1
        BLOCK#          1

BBED> map
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                                     Dba:0x02c00001
------------------------------------------------------------
BBED-00400: invalid blocktype (00)

BBED> d
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:    0 to  511           Dba:0x02c00001
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>
--header 记录全部为0，证明数据文件header坏掉

拷贝数据块
为了方便,拷贝同一个表空间的数据块

BBED> set file 12
        FILE#           12

BBED> set block 1
        BLOCK#          1

BBED> d count 16
 File: /u01/oracle/oradata/xifenfei/bbed02.dbf (12)
 Block: 1                Offsets:    0 to   15           Dba:0x03000001
------------------------------------------------------------------------
 0b020000 01000003 00000000 00000104 

 <32 bytes per line>

BBED> copy dba 0x03000001 to dba 0x02c00001
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:    0 to   15           Dba:0x02c00001
------------------------------------------------------------------------
 0b020000 01000003 00000000 00000104 

 <32 bytes per line>

BBED> show
        FILE#           11
        BLOCK#          1
        OFFSET          0
        DBA             0x02c00001 (46137345 11,1)
        FILENAME        /u01/oracle/oradata/xifenfei/bbed01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/bbed.file
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           16
        LOGFILE         log.bbd
        SPOOL           No

BBED> map
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                                     Dba:0x02c00001
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0       

 ub4 tailchk                                @8188

修改数据块内容

BBED>  p kcvfh
struct kcvfh, 360 bytes                     @0       
   struct kcvfhbfh, 20 bytes                @0       
      ub1 type_kcbh                         @0        0x0b
      ub1 frmt_kcbh                         @1        0x02
      ub1 spare1_kcbh                       @2        0x00
      ub1 spare2_kcbh                       @3        0x00
      ub4 rdba_kcbh                         @4        0x03000001
      ub4 bas_kcbh                          @8        0x00000000
      ub2 wrp_kcbh                          @12       0x0000
      ub1 seq_kcbh                          @14       0x01
      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
      ub2 chkval_kcbh                       @16       0xb10a
      ub2 spare3_kcbh                       @18       0x0000
   struct kcvfhhdr, 76 bytes                @20      
      ub4 kccfhswv                          @20       0x09200000
      ub4 kccfhcvn                          @24       0x08000000
      ub4 kccfhdbi                          @28       0x5314b4cd
      text kccfhdbn[0]                      @32      X
      text kccfhdbn[1]                      @33      I
      text kccfhdbn[2]                      @34      F
      text kccfhdbn[3]                      @35      E
      text kccfhdbn[4]                      @36      N
      text kccfhdbn[5]                      @37      F
      text kccfhdbn[6]                      @38      E
      text kccfhdbn[7]                      @39      I
      ub4 kccfhcsq                          @40       0x000001d8
      ub4 kccfhfsz                          @44       0x00001400
      s_blkz kccfhbsz                       @48       0x00
      ub2 kccfhfno                          @52       0x000c
      ub2 kccfhtyp                          @54       0x0003
      ub4 kccfhacid                         @56       0x00000000
      ub4 kccfhcks                          @60       0x00000000
      text kccfhtag[0]                      @64       
      text kccfhtag[1]                      @65       
      text kccfhtag[2]                      @66       
      text kccfhtag[3]                      @67       
      text kccfhtag[4]                      @68       
      text kccfhtag[5]                      @69       
      text kccfhtag[6]                      @70       
      text kccfhtag[7]                      @71       
      text kccfhtag[8]                      @72       
      text kccfhtag[9]                      @73       
      text kccfhtag[10]                     @74       
      text kccfhtag[11]                     @75       
      text kccfhtag[12]                     @76       
      text kccfhtag[13]                     @77       
      text kccfhtag[14]                     @78       
      text kccfhtag[15]                     @79       
      text kccfhtag[16]                     @80       
      text kccfhtag[17]                     @81       
      text kccfhtag[18]                     @82       
      text kccfhtag[19]                     @83       
      text kccfhtag[20]                     @84       
      text kccfhtag[21]                     @85       
      text kccfhtag[22]                     @86       
      text kccfhtag[23]                     @87       
      text kccfhtag[24]                     @88       
      text kccfhtag[25]                     @89       
      text kccfhtag[26]                     @90       
      text kccfhtag[27]                     @91       
      text kccfhtag[28]                     @92       
      text kccfhtag[29]                     @93       
      text kccfhtag[30]                     @94       
      text kccfhtag[31]                     @95       
   ub4 kcvfhrdb                             @96       0x00000000
   struct kcvfhcrs, 8 bytes                 @100     
      ub4 kscnbas                           @100      0xc00a3405
      ub2 kscnwrp                           @104      0x0b2c
   ub4 kcvfhcrt                             @108      0x2ebeb8c3
   ub4 kcvfhrlc                             @112      0x2e51408f
   struct kcvfhrls, 8 bytes                 @116     
      ub4 kscnbas                           @116      0x0002ab78
      ub2 kscnwrp                           @120      0x0000
   ub4 kcvfhbti                             @124      0x00000000
   struct kcvfhbsc, 8 bytes                 @128     
      ub4 kscnbas                           @128      0x00000000
      ub2 kscnwrp                           @132      0x0000
   ub2 kcvfhbth                             @136      0x0000
   ub2 kcvfhsta                             @138      0x0000 (NONE)
   struct kcvfhckp, 36 bytes                @140     
      struct kcvcpscn, 8 bytes              @140     
         ub4 kscnbas                        @140      0xc00b6467
         ub2 kscnwrp                        @144      0x0b2c
      ub4 kcvcptim                          @148      0x2ebf0c07
      ub2 kcvcpthr                          @152      0x0001
      union u, 12 bytes                     @156     
         struct kcvcprba, 12 bytes          @156     
            ub4 kcrbaseq                    @156      0x00000015
            ub4 kcrbabno                    @160      0x0000429a
            ub2 kcrbabof                    @164      0x0010
         struct kcvcptr, 12 bytes           @156     
            struct kcrtrscn, 8 bytes        @156     
               ub4 kscnbas                  @156      0x00000015
               ub2 kscnwrp                  @160      0x429a
            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
   ub4 kcvfhcpc                             @176      0x0000000d
   ub4 kcvfhrts                             @180      0x2ebeea4f
   ub4 kcvfhccc                             @184      0x0000000c
   struct kcvfhbcp, 36 bytes                @188     
      struct kcvcpscn, 8 bytes              @188     
         ub4 kscnbas                        @188      0x00000000
         ub2 kscnwrp                        @192      0x0000
      ub4 kcvcptim                          @196      0x00000000
      ub2 kcvcpthr                          @200      0x0000
      union u, 12 bytes                     @204     
         struct kcvcprba, 12 bytes          @204     
            ub4 kcrbaseq                    @204      0x00000000
            ub4 kcrbabno                    @208      0x00000000
            ub2 kcrbabof                    @212      0x0000
         struct kcvcptr, 12 bytes           @204     
            struct kcrtrscn, 8 bytes        @204     
               ub4 kscnbas                  @204      0x00000000
               ub2 kscnwrp                  @208      0x0000
            ub4 kcrtrtim                    @212      0x00000000
      ub1 kcvcpetb[0]                       @216      0x00
      ub1 kcvcpetb[1]                       @217      0x00
      ub1 kcvcpetb[2]                       @218      0x00
      ub1 kcvcpetb[3]                       @219      0x00
      ub1 kcvcpetb[4]                       @220      0x00
      ub1 kcvcpetb[5]                       @221      0x00
      ub1 kcvcpetb[6]                       @222      0x00
      ub1 kcvcpetb[7]                       @223      0x00
   ub4 kcvfhbhz                             @224      0x00000000
   struct kcvfhxcd, 16 bytes                @228     
      ub4 space_kcvmxcd[0]                  @228      0x00000000
      ub4 space_kcvmxcd[1]                  @232      0x00000000
      ub4 space_kcvmxcd[2]                  @236      0x00000000
      ub4 space_kcvmxcd[3]                  @240      0x00000000
   word kcvfhtsn                            @244      12
   ub2 kcvfhtln                             @248      0x0004
   text kcvfhtnm[0]                         @250     B
   text kcvfhtnm[1]                         @251     B
   text kcvfhtnm[2]                         @252     E
   text kcvfhtnm[3]                         @253     D
   text kcvfhtnm[4]                         @254      
   text kcvfhtnm[5]                         @255      
   text kcvfhtnm[6]                         @256      
   text kcvfhtnm[7]                         @257      
   text kcvfhtnm[8]                         @258      
   text kcvfhtnm[9]                         @259      
   text kcvfhtnm[10]                        @260      
   text kcvfhtnm[11]                        @261      
   text kcvfhtnm[12]                        @262      
   text kcvfhtnm[13]                        @263      
   text kcvfhtnm[14]                        @264      
   text kcvfhtnm[15]                        @265      
   text kcvfhtnm[16]                        @266      
   text kcvfhtnm[17]                        @267      
   text kcvfhtnm[18]                        @268      
   text kcvfhtnm[19]                        @269      
   text kcvfhtnm[20]                        @270      
   text kcvfhtnm[21]                        @271      
   text kcvfhtnm[22]                        @272      
   text kcvfhtnm[23]                        @273      
   text kcvfhtnm[24]                        @274      
   text kcvfhtnm[25]                        @275      
   text kcvfhtnm[26]                        @276      
   text kcvfhtnm[27]                        @277      
   text kcvfhtnm[28]                        @278      
   text kcvfhtnm[29]                        @279      
   ub4 kcvfhrfn                             @280      0x0000000c
   struct kcvfhrfs, 8 bytes                 @284     
      ub4 kscnbas                           @284      0x00000000
      ub2 kscnwrp                           @288      0x0000
   ub4 kcvfhrft                             @292      0x2ebee9f9
   struct kcvfhafs, 8 bytes                 @296     
      ub4 kscnbas                           @296      0x00000000
      ub2 kscnwrp                           @300      0x0000
   ub4 kcvfhbbc                             @304      0x00000000
   ub4 kcvfhncb                             @308      0x00000000
   ub4 kcvfhmcb                             @312      0x00000000
   ub4 kcvfhlcb                             @316      0x00000000
   ub4 kcvfhbcs                             @320      0x00000000
   ub2 kcvfhofb                             @324      0x0000
   ub2 kcvfhnfb                             @326      0x0000
   ub4 kcvfhprc                             @328      0x00000000
   struct kcvfhprs, 8 bytes                 @332     
      ub4 kscnbas                           @332      0x00000000
      ub2 kscnwrp                           @336      0x0000
   struct kcvfhprfs, 8 bytes                @340     
      ub4 kscnbas                           @340      0x00000000
      ub2 kscnwrp                           @344      0x0000
   ub4 kcvfhtrt                             @356      0x00000000

/*需要修改内容
ub4 rdba_kcbh                         @4        0x03000001
ub4 kccfhfsz                          @44       0x00001400
ub2 kccfhfno                          @52       0x000c
struct kcvfhcrs, 8 bytes                 @100     
      ub4 kscnbas                           @100      0xc00a3405
      ub2 kscnwrp                           @104      0x0b2c
ub4 kcvfhrfn                             @280      0x0000000c
/
/*修改值(通过错误提示结合file$表)
rdba_kcbh  02c00001
kccfhfsz  00000500
kccfhfno 000b
kscnbas c00a32b8
kcvfhrfn 0000000b
/

BBED> set offset 4
        OFFSET          4

BBED> m /x 0100c002
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:    4 to   19           Dba:0x02c00001
------------------------------------------------------------------------
 0100c002 00000000 00000104 0ab10000 

 <32 bytes per line>

BBED> set offset 44
        OFFSET          44

BBED> m /x 00050000
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:   44 to   59           Dba:0x02c00001
------------------------------------------------------------------------
 00050000 00200000 0c000300 00000000 

 <32 bytes per line>

BBED> set offset 52
        OFFSET          52

BBED> m /x 
BBED-00203: incomplete/malformed command


BBED> m /x 0b00                                                                                                        
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:   52 to   67           Dba:0x02c00001
------------------------------------------------------------------------
 0b000300 00000000 00000000 00000000 

 <32 bytes per line>

BBED> set offset 100
        OFFSET          100

BBED> m /x b8320ac0
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:  100 to  115           Dba:0x02c00001
------------------------------------------------------------------------
 b8320ac0 2c0b0000 c3b8be2e 8f40512e 

 <32 bytes per line>

BBED> set offset 280
        OFFSET          280

BBED> m /x 0b000000
 File: /u01/oracle/oradata/xifenfei/bbed01.dbf (11)
 Block: 1                Offsets:  280 to  295           Dba:0x02c00001
------------------------------------------------------------------------
 0b000000 00000000 00000000 f9e9be2e 

 <32 bytes per line>

BBED> sum apply
Check value for File 11, Block 1:
current = 0xa777, required = 0xa777

重建控制文件open数据库

SQL> alter database backup controlfile to trace as '/tmp/t_xifenfie.ctl';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 '/u01/oracle/oradata/xifenfei/redo01.log'  SIZE 100M,
  9    GROUP 2 '/u01/oracle/oradata/xifenfei/redo02.log'  SIZE 100M,
 10    GROUP 3 '/u01/oracle/oradata/xifenfei/redo03.log'  SIZE 100M
 11  DATAFILE
 12    '/u01/oracle/oradata/xifenfei/system01.dbf',
 13    '/u01/oracle/oradata/xifenfei/undotbs01.dbf',
 14    '/u01/oracle/oradata/xifenfei/cwmlite01.dbf',
 15    '/u01/oracle/oradata/xifenfei/drsys01.dbf',
 16    '/u01/oracle/oradata/xifenfei/example01.dbf',
 17    '/u01/oracle/oradata/xifenfei/indx01.dbf',
 18    '/u01/oracle/oradata/xifenfei/odm01.dbf',
 19    '/u01/oracle/oradata/xifenfei/tools01.dbf',
 20    '/u01/oracle/oradata/xifenfei/users01.dbf',
 21    '/u01/oracle/oradata/xifenfei/xdb01.dbf',
 22    '/u01/oracle/oradata/xifenfei/bbed01.dbf',
 23    '/u01/oracle/oradata/xifenfei/bbed02.dbf'
 24  CHARACTER SET ZHS16GBK
 25  ;

Control file created.

SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open;

Database altered.

至此通过拷贝相同表空间的datafile header修复损坏的datafile header

发表在 非常规恢复 | 标签为 | 3 条评论

系统中数据文件第一个数据块和oracle 中第一个数据块关系

发表于 2012 年 5 月 29 日 由 惜分飞

数据文件第一个数据块到底有没有纳入数据块的数据块计算中,也就是我们通常所说的rdba(file#,block),是否真的是从数据文件的第一个数据块开始计算的？下面通过实验验证
相关信息和准备工作

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-29 19:39:48

SQL> select name,block_size from v$datafile where file#=9;

NAME                                                         BLOCK_SIZE
------------------------------------------------------------ ----------
/u01/oracle/oradata/xifenfei/users01.dbf                           8192


--dd出来数据文件第一和第二个数据块
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.01 bs=8192 count=1
1+0 records in
1+0 records out
[oracle@xifenfei ~]$ dd if=/u01/oracle/oradata/xifenfei/users01.dbf of=user.02 bs=8192 count=1 skip=1
1+0 records in
1+0 records out

[oracle@xifenfei ~]$ ll user.*
-rw-r--r--  1 oracle oinstall 8192 May 26 04:43 user.01
-rw-r--r--  1 oracle oinstall 8192 May 26 04:44 user.02

bbed验证

[oracle@xifenfei ~]$ bbed password=blockedit blocksize=8192  listfile=/home/oracle/bbed_new.file mode=edit

BBED: Release 2.0.0.0.0 - Limited Production on Sat May 26 04:56:49 2012

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

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

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/xifenfei/users01.dbf                             0
     2  /home/oracle/user.01                                                 0
     3  /home/oracle/user.02                                                 0

--users01.dbf(完整数据文件,第一个数据块)
BBED> set file 1
        FILE#           1

BBED> set block 1
        BLOCK#          1

BBED> d /v count 128
 File: /u01/oracle/oradata/xifenfei/users01.dbf (1)
 Block: 1       Offsets:    0 to  127  Dba:0x00400001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........

 <16 bytes per line>

--直接设置file 2错误(后续提供其他方法)
BBED> set file 2
BBED-00307: incorrect blocksize (8192) or truncated file

--查看users01.dbf(第二个数据块)
BBED> set file 3
        FILE#           3

BBED> set block 1
        BLOCK#          1

BBED> d /v count 128
 File: /home/oracle/user.02 (3)
 Block: 1       Offsets:    0 to  127  Dba:0x00c00001
-------------------------------------------------------
 0b020000 01004002 00000000 00000104 l ......@.........
 7f4b0000 00002009 00000008 cdb41453 l .K.... ........S
 58494645 4e464549 c7010000 800c0000 l XIFENFEI........
 00200000 09000300 00000000 00000000 l . ..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 47180000 00000000 cf4d851e l ....G........M..
 8f40512e 78ab0200 00000000 00000000 l .@Q.x...........

 <16 bytes per line>

--查看users01.dbf(真正第一个数据块)
BBED> set filename 'user.01'
        FILENAME        user.01

BBED> d /v count 128
 File: user.01 (0)
 Block: 1       Offsets:    0 to  127  Dba:0x00000000
-------------------------------------------------------
 00020000 00200000 800c0000 5d5c5b5a l ..... ......]\[Z
 00000000 86280000 00000000 00000000 l .....(..........
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

通过这个对比可以知道:当我们直接使用bbed查看数据块内容的时候,自动屏蔽了数据文件上真正的第一个数据块.其实block 1是数据文件上的第二个数据块

hexdump验证

--users01.dbf(完整文件)
[oracle@xifenfei ~]$ hexdump -C /u01/oracle/oradata/xifenfei/users01.dbf|head -20
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00002010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00002020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00002030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00002040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00002070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00002080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00002090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000020a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000020b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000020c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000020f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00002100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

--users01.dbf(第一个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.01
00000000  00 02 00 00 00 20 00 00  80 0c 00 00 5d 5c 5b 5a  |..... ......]\[Z|
00000010  00 00 00 00 86 28 00 00  00 00 00 00 00 00 00 00  |.....(..........|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000

--users01.dbf(第二个数据块文件)
[oracle@xifenfei ~]$ hexdump -C user.02|head -20
00000000  0b 02 00 00 01 00 40 02  00 00 00 00 00 00 01 04  |......@.........|
00000010  7f 4b 00 00 00 00 20 09  00 00 00 08 cd b4 14 53  |.K.... ........S|
00000020  58 49 46 45 4e 46 45 49  c7 01 00 00 80 0c 00 00  |XIFENFEI........|
00000030  00 20 00 00 09 00 03 00  00 00 00 00 00 00 00 00  |. ..............|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00000060  00 00 00 00 47 18 00 00  00 00 00 00 cf 4d 85 1e  |....G........M..|
00000070  8f 40 51 2e 78 ab 02 00  00 00 00 00 00 00 00 00  |.@Q.x...........|
00000080  00 00 00 00 00 00 00 00  00 00 04 00 58 0d 0b c0  |............X...|
00000090  2c 0b 00 00 5a ea be 2e  01 00 aa bd 15 00 00 00  |,...Z...........|
000000a0  02 00 00 00 10 00 ff bf  02 00 00 00 00 00 00 00  |................|
000000b0  5a 00 00 00 4f ea be 2e  59 00 00 00 00 00 00 00  |Z...O...Y.......|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
000000f0  00 00 00 00 09 00 00 00  05 00 55 53 45 52 53 00  |..........USERS.|
00000100  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000110  00 00 00 00 00 00 00 00  09 00 00 00 00 00 00 00  |................|
00000120  00 00 00 00 f9 e9 be 2e  00 00 00 00 00 00 00 00  |................|
00000130  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

通过hexdump对三个文件的对比可以知道users01.dbf的头两个数据文件确实是由第一和第二个数据块组成.然后结合上面bbed dump出来的结果.可以再次证明数据文件第一个数据块,不能被bbed识别(从第二个数据文件开始)

实验总结
我们的数据文件其实是从文件的第二个数据块开始记录起(该数据块为block 1).也就是说系统的数据块和oracle中的rdba标示的数据块不是一致.而是系统数据块比oracle数据块多1.
因这个原因解释了以前的一个疑问:Oracle数据文件大小的限制为什么指定数据文件最大值为(2^22-1*block_size),而不是根据rowid的2^22*block_size
关于类此问题在windows验证请见:在UltraEdit中定位数据文件内容

发表在 Oracle | 一条评论