分类目录归档：数据库

DEFERRED_SEGMENT_CREATION 参数相关说明

发表于 2012 年 6 月 6 日 由 惜分飞

DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
这句话的意思是 DEFERRED_SEGMENT_CREATION 参数的作用是:创建表的时候延迟创建这个表相关的segment(包括lobs,indexes),直到第一次插入数据的时候才创建segment.补充说明:DEFERRED_SEGMENT_CREATION 参数从11.2.0.1引进,默认值为true;如果要使其恢复老版本功能,设置该参数为false.

DEFERRED_SEGMENT_CREATION默认值

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "WWW.XIFENFEI.COM" FROM DUAL;

WWW.XIFENFEI.COM
--------------------------------------
2012-06-01 05:31:03

SQL> show parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE       VALUE
------------------------------------ ---------- --------
deferred_segment_creation            boolean    TRUE

DEFERRED_SEGMENT_CREATION效果验证

SQL> create table t_xifenfei (id number,name varchar2(30));

Table created.

SQL> create index ind_t_xifenfei on t_xifenfei(id);

Index created.

SQL> select segment_name,segment_type from dba_segments where
  2   segment_name in('T_XIFENFEI','IND_T_XIFENFEI') AND OWNER='CHF';

no rows selected
--未创建segment

SQL> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFENFEI.COM');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name,segment_type from dba_segments where 
  2  segment_name in('T_XIFENFEI','IND_T_XIFENFEI') AND OWNER='CHF';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------------------------
IND_T_XIFENFEI       INDEX
T_XIFENFEI           TABLE
--创建segment

SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table t_xifenfei_2 (id number,name varchar2(30));

Table created.

SQL> select segment_name,segment_type from dba_segments where segment_name='T_XIFENFEI_2';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------------------------
T_XIFENFEI_2         TABLE
--创建segment

问题1(朋友疑惑为什么它没有给相关表空间分配配额但是创建表成功)

SQL> create user xifenfei identified by xifenfei default tablespace users;

User created.

SQL> grant connect,resource to xifenfei;

Grant succeeded.

SQL> revoke unlimited tablespace from xifenfei;

Revoke succeeded.

SQL> alter user xifenfei quota unlimited on users;

User altered.

SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei (id number,name varchar2(30)) tablespace system;

Table created.
--在system表空间无配额,但是创建表成功

SQL> insert into t_xifenfei values(1,'www.xifenfei.com');
insert into t_xifenfei values(1,'www.xifenfei.com')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
--插入数据库失败,因为在system上创建segment失败


SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table t_xifenfei_2 (id number,name varchar2(30)) tablespace system;
create table t_xifenfei_2 (id number,name varchar2(30)) tablespace system
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
--deferred_segment_creation设置为false后,创建表直接失败

问题2(exp未导segment不存在表)
该问题帮朋友解决过.因为暂时无11.2.0.1版本数据库,直接摘录MOS

In 11.2 the deferred storage segment feature is enabled by default. 
Conventional export (exp) silently skips tables with deferred segment
creation if no segment has yet been created. ie: If the table does
not yet contain any rows. In some cases "exp" will report EXP-11 for 
the table.
 
eg:
 create table t(c1 int) tablespace sysaux;
 select segment_created from user_tables where table_name='T';
  SEG
  ---
  NO
 
 Table level export:
   exp scott/tiger file=/tmp/scott.dmp tables=t
   ^
   EXP-11 SCOTT.T does not exist
 
 Schema level export:
   exp scott/tiger file=/tmp/scott.dmp owner=scott statistics=none
   ^
   Export completes successfully but silently does not export
   table "T".
 
Rediscovery Notes:
  Tables that may be affected by this can be found thus:
    select owner, table_name from dba_tables 
     where segment_created='NO';
 
  EXP-11 on export for tables with no data.
  Tables missing after exp/imp
 
Workaround
  Re-create the missing table at the export site from DDL.
  (the table did not contain rows otherwise it would have
   had a segment created for it)

In 11.2 the deferred storage segment feature is enabled by default. 
Conventional export (exp) silently skips tables with deferred segment
creation if no segment has yet been created. ie: If the table does
not yet contain any rows. In some cases "exp" will report EXP-11 for 
the table.
 
eg:
 create table t(c1 int) tablespace sysaux;
 select segment_created from user_tables where table_name='T';
  SEG
  ---
  NO
 
 Table level export:
   exp scott/tiger file=/tmp/scott.dmp tables=t
   ^
   EXP-11 SCOTT.T does not exist
 
 Schema level export:
   exp scott/tiger file=/tmp/scott.dmp owner=scott statistics=none
   ^
   Export completes successfully but silently does not export
   table "T".
 
Rediscovery Notes:
  Tables that may be affected by this can be found thus:
    select owner, table_name from dba_tables 
     where segment_created='NO';
 
  EXP-11 on export for tables with no data.
  Tables missing after exp/imp
 
Workaround
  Re-create the missing table at the export site from DDL.
  (the table did not contain rows otherwise it would have
   had a segment created for it)
 
This issue is fixed in	
•12.1 (Future Release) 
•11.2.0.2 (Server Patch Set)
发表在 Oracle | 标签为 | 评论关闭

动态修改PGA_AGGREGATE_TARGET 导致ORA-600[723]

发表于 2012 年 6 月 6 日 由 惜分飞

在以前分析过ORA-600[729](SGA内存泄露),这次遇到ORA-600[723](PGA内存泄露)
操作系统数据库信息

ORACLE V9.2.0.3.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: dsdata

alert报错ORA-600[723]

Tue Jun 05 12:16:35 2012
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 274
Tue Jun 05 12:16:40 2012
alter database close normal 
Tue Jun 05 12:16:40 2012
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Jun 05 12:16:40 2012
Shutting down archive processes
Archiving is disabled
Tue Jun 05 12:16:40 2012
ARCH shutting down
Tue Jun 05 12:16:40 2012
ARCH shutting down
ARC1: Archival stopped
Tue Jun 05 12:16:40 2012
ARC0: Archival stopped
Tue Jun 05 12:16:40 2012
Thread 1 closed at log sequence 406
Successful close of redo thread 1.
Tue Jun 05 12:16:41 2012
Completed: alter database close normal
Tue Jun 05 12:16:41 2012
alter database dismount
Completed: alter database dismount
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jun 05 12:16:43 2012
Errors in file d:\oracle\admin\dsdata\udump\dsdata_ora_504.trc:
ORA-00600: internal error code, arguments: [723], [20664], [20664], [memory leak], [], [], [], []

通过alert日志可以知道,数据库shutdown immediate的时候报ORA-600[723]

分析trace文件

…………
EXTENT 147 addr=062ACCBC
  Chunk  62accc4 sz=     1252    free      "               "
  Chunk  62ad1a8 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ad9b4 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ae1c0 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  62ae9cc sz=     2060    freeable  "qesmmaLogInitia"
…………
EXTENT 153 addr=04232414
  Chunk  423241c sz=     4476    perm      "perm           "  alo=2868
  Chunk  4233598 sz=    18516    free      "               "
  Chunk  4237dec sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  42385f8 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4238e04 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4239610 sz=     2060    freeable  "qesmmaLogInitia"
  Chunk  4239e1c sz=     2060    freeable  "qesmmaLogInitia"
…………
--查询发现没有释放的内容都是在qesmmaLogInitia部分

*** 2012-06-05 12:16:43.000
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [723], [20664], [20664], [memory leak], [], [], [], []
Current SQL information unavailable - no SGA.
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedmp+147          CALLrel  _ksedst+0            
_ksfdmp.108+e        CALLrel  _ksedmp+0            3
_kgeriv+89           CALLreg  00000000             217190 3
_kgesiv+4e           CALLrel  _kgeriv+0            217190 0 2D3 3 418FC2C
_ksesic3+3b          CALLrel  _kgesiv+0            217190 0 2D3 3 418FC2C 2D3 3
                                                   418FC2C
__VInfreq__ksmdpg+e  CALLrel  _ksesic3+0           2D3 0 50B8 0 50B8 1 B 26A3F28
f                                                  
_opidcl+1db          CALLrel  _ksmdpg+0            
_opidrv+3bf          CALLrel  _opidcl+0            21D328 0
_sou2o+19            CALLrel  _opidrv+0            
_opimai+150          CALLrel  _sou2o+0             418FE20 32 0 0
_BackgroundThreadSt  CALLrel  _opimai+0            
art@4+164                                          
77E6482C             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

通过查询MOS发现[ID 242260.1]上的Stack Trace比较匹配.上面说到通过sql来直接修改pga_aggregate_target导致,查找alert日志,果然发现:

Mon May 21 15:18:33 2012
ALTER SYSTEM SET pga_aggregate_target='1048576000' SCOPE=MEMORY;
Mon May 21 15:18:33 2012
ALTER SYSTEM SET pga_aggregate_target='1048576000' SCOPE=SPFILE;

现在基本上可以确定引起整个ORA-600[723]的原因是:用户直接修改pga_aggregate_target参数,然后关闭数据库引起Bug:2975617导致

处理建议
Don’t alter the pga_aggregate_target dynamically Change it in init.ora file
针对本库,再次开启数据库应该处于正常状态(spfile已经修改),无需继续关注该问题.

发表在 ORA-xxxxx | 标签为 , | 2 条评论

找出dbv相关dba值在数据文件中对应位置

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

一个朋友数据库因异常断电,数据库不能正常启动,使用dbv检测错误如下:

C:\Users\XIFENFEI\Downloads>dbv file=users01.dbf end=5

DBVERIFY: Release 11.2.0.3.0 - Production on 星期二 6月 5 18:17:27 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = C:\USERS\XIFENFEI\DOWNLOADS\USERS01.DBF
页 1 标记为损坏
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0x2f1f
 computed block checksum: 0x0

页 2 标记为损坏
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv:
Data in bad block:
 type: 29 format: 2 rdba: 0x01000002
 last change scn: 0x0000.0018c7fa seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc7fa1d02
 check value in block header: 0x82ca
 computed block checksum: 0x0

页 3 标记为损坏
Corrupt block relative dba: 0x00000003 (file 0, block 3)
Bad header found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000003
 last change scn: 0x0000.0018c7fa seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc7fa1e01
 check value in block header: 0x32c9
 computed block checksum: 0x0

页 4 标记为损坏
Corrupt block relative dba: 0x00000004 (file 0, block 4)
Bad header found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000004
 last change scn: 0x0000.00004adc seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4adc1e01
 check value in block header: 0x8199
 computed block checksum: 0x0

页 5 标记为损坏
Corrupt block relative dba: 0x00000005 (file 0, block 5)
Bad header found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000005
 last change scn: 0x0000.00004ade seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4ade1e01
 check value in block header: 0xc190
 computed block checksum: 0x0



DBVERIFY - 验证完成

检查的页总数: 5
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 0
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 0
标记为损坏的总页数: 5
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 0 (0.0)

对于这样的错误,特别是Corrupt block relative dba出现奇怪的提示(file 0),我第一反应就是数据文件header出现了问题.在eygle的耐心帮忙和提示下,使用bbed重现了该错误,并且找出了dbv中两个dba(Corrupt block relative dba和rdba)和bbed中相对应的值.通过实验重现相关结果.

dbv检查无坏块

[oracle@xifenfei tmp]$ dbv file=/u01/oracle/oradata/ora11g/xifenfei02.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jun 1 03:34:46 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/xifenfei02.dbf
Block Checking: DBA = 25179275, Block Type = KTB-managed data block
data header at 0xb526707c
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=3
Page 13451 failed with check code 6101
Block Checking: DBA = 25179287, Block Type = KTB-managed data block
data header at 0xb527f064
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=2
Page 13463 failed with check code 6101


DBVERIFY - Verification complete

Total Pages Examined         : 15360
Total Pages Processed (Data) : 12932
Total Pages Failing   (Data) : 2
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 291
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2137
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1551567 (0.1551567)

修改数据块rdba_kcbh

BBED> set block 150
        BLOCK#          150

BBED> map
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 150                                   Dba:0x00400096
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[71]                               @142     

 ub1 freespace[910]                         @284     

 ub1 rowdata[6994]                          @1194    

 ub4 tailchk                                @8188    


BBED> p kcbh
struct kcbh, 20 bytes                       @0       
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x01800096
   ub4 bas_kcbh                             @8        0x00131e6f
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x4aae
   ub2 spare3_kcbh                          @18       0x0000

BBED> m /x 00000000 offset 4
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 150              Offsets:    4 to  515           Dba:0x00400096
------------------------------------------------------------------------
 00000000 6f1e1300 00000104 ae4a0000 01000000 01280100 6f1e1300 00000000 
 03003200 90008001 ffff0000 00000000 00000000 00000000 00800000 6c781200 
 09001d00 05030000 8c02c000 b4000500 00800000 b0191300 02000900 fc020000 
 b011c000 0b011400 00800000 6c1e1300 00000000 00000000 00014700 ffffa000 
 2e048e03 8e030000 4700e90b 540cb30c 120d710d d00d2f0e 8e0eed0e 4c0fab0f 
 0a106910 c8102711 8611e711 4812aa12 0c136713 c2131f14 7c14df14 4115a015 
 ff156416 c9162517 8117de17 3b18a118 07196119 bb19191a 771ad61a 351b941b 
 f31b521c b11c101d 6f1dd71d 3f1eaa1e 151f2e04 99040405 6f05da05 4506b006 
 1b078607 f1075c08 c7082c09 9109f609 5b0ac00a 250b870b 48004900 4a004b00 
 4c004d00 4e004f00 5000ffff 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 2c000e03 53595308 565f245f 4c4f434b 
 ff03c20f 5eff0456 49455707 786f0912 12230407 786f0912 12230413 32303131 
 2d30392d 31383a31 373a3334 3a303305 56414c49 44014e01 4e014e02 c1022c00 
 0e065055 424c4943 0756245f 4c4f434b ff03c20f 5fff0753 594e4f4e 594d0778 
 6f091212 23040778 6f091212 23041332 3031312d 30392d31 383a3137 3a33343a 
 30330556 414c4944 014e014e 014e02c1 022c000e 03535953 07565f24 4c4f434b 

 <32 bytes per line>

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

dbv检测数据文件
发现提示坏块文件的rdba就是我们刚刚修改的rdba_kcbh值

[oracle@xifenfei tmp]$ dbv file=/u01/oracle/oradata/ora11g/xifenfei02.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jun 1 03:40:44 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/xifenfei02.dbf
Page 150 is marked corrupt
Corrupt block relative dba: 0x01800096 (file 6, block 150)
Bad header found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00000000
 last change scn: 0x0000.00131e6f seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1e6f0601
 check value in block header: 0x4bb8
 computed block checksum: 0x0

Block Checking: DBA = 25179275, Block Type = KTB-managed data block
data header at 0xb52a807c
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=3
Page 13451 failed with check code 6101
Block Checking: DBA = 25179287, Block Type = KTB-managed data block
data header at 0xb52c0064
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=2
Page 13463 failed with check code 6101


DBVERIFY - Verification complete

Total Pages Examined         : 15360
Total Pages Processed (Data) : 12931
Total Pages Failing   (Data) : 2
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 291
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2137
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1551567 (0.1551567)

修改datafile header的kcvfhrfn值

BBED> set block 1
        BLOCK#          1

BBED> map
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 1                                     Dba:0x00400001
------------------------------------------------------------
 Data File Header

 struct kcvfh, 860 bytes                    @0       

 ub4 tailchk                                @8188    


BBED> p kcvfhrfn
ub4 kcvfhrfn                                @368      0x00000006

BBED> m /x 00000000
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 1                Offsets:  368 to  879           Dba:0x00400001
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 e3b38c2e 04a91100 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 797c1900 00000000 3e3bba2e 
 01000000 11000000 b40e0000 1000ba8a 02000000 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 0a000a00 0a000100 00000000 
 00000000 00000000 02008001 bb050c00 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>

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

dbv验证数据文件
这里如果验证所有数据文件快,会发现所有类此Corrupt block relative dba: 0×00000001 (file 0, block 1)提示.这里证明datafile header 的kcvfhrfn 影响dbv检查数据文件坏块的一个标准之一

[oracle@xifenfei tmp]$ dbv file=/u01/oracle/oradata/ora11g/xifenfei02.dbf blocksize=8192 start=1 end=4

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jun 1 03:43:27 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/xifenfei02.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad header found during dbv: 
Data in bad block:
 type: 11 format: 2 rdba: 0x01800001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0x1daf
 computed block checksum: 0x0

Page 2 is marked corrupt
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv: 
Data in bad block:
 type: 29 format: 2 rdba: 0x01800002
 last change scn: 0x0000.0017accf seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaccf1d01
 check value in block header: 0x2626
 computed block checksum: 0x0

Page 3 is marked corrupt
Corrupt block relative dba: 0x00000003 (file 0, block 3)
Bad header found during dbv: 
Data in bad block:
 type: 30 format: 2 rdba: 0x01800003
 last change scn: 0x0000.0017accf seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaccf1e01
 check value in block header: 0x4ef4
 computed block checksum: 0x0

Page 4 is marked corrupt
Corrupt block relative dba: 0x00000004 (file 0, block 4)
Bad header found during dbv: 
Data in bad block:
 type: 30 format: 2 rdba: 0x01800004
 last change scn: 0x0000.00119bf4 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9bf41e01
 check value in block header: 0x810a
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 4
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 4
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

总结说明
1.dbv检测数据文件坏块的时候会读取数据文件头的kcvfhrfn值,如果这个值出现问题,可能导致数据文件中的所有数据块都异常,具体表现就是Corrupt block relative dba项异常
2.dbv检查数据文件坏块中显示的rdba对应于数据块的rdba_kcbh值

发表在 Oracle | 评论关闭