分类目录归档：数据库

dul恢复truncate表测试

发表于 2013 年 2 月 4 日 由 惜分飞

dul 恢复truncate 测试
准备dul测试

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     67854

SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> select object_id,data_object_id from user_objects where object_name='T_XIFENFEI';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     68332          68332

SQL> TRUNCATE TABLE T_XIFENFEI;

Table truncated.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> select object_id,data_object_id from user_objects where object_name='T_XIFE
NFEI';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     68332          68468

SQL> SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI';

TABLESPACE_NAME
------------------------------
USERS

SQL> SELECT FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS';

   FILE_ID
----------
         4


SQL> SELECT file#,RFILE#,NAME FROM V$DATAFILE;

     FILE#     RFILE# NAME
---------- ---------- --------------------------------------------------
         1          1 E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF
         2          2 E:\ORACLE\ORADATA\XIFENFEI\SYSAUX01.DBF
         3          3 E:\ORACLE\ORADATA\XIFENFEI\UNDOTBS01.DBF
         4          4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
         5          5 E:\ORACLE\ORADATA\XIFENFEI\UNDO01.DBF
         6          6 E:\ORACLE\ORADATA\XIFENFEI\CZUM01.DBF

6 rows selected.

备注说明：因为我们为了测试，所以直接查询出来了data_object_id，在实际的恢复中，我们需要使用logminer来找出来历史dataobj#

dul恢复truncate 表

e:\dul10>dul.exe

Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 00:20:08 2013
with 64-bit io functions

Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal use Only



DUL: Warning: Recreating file "dul.log"
Reading USER.dat 65 entries loaded
Reading OBJ.dat 67944 entries loaded and sorted 67944 entries
Reading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entries
Reading TAB.dat 1869 entries loaded
Reading COL.dat 77409 entries loaded and sorted 77409 entries
Reading SEG.dat 23 entries loaded
Reading EXT.dat 54 entries loaded and sorted 54 entries
Reading TABPART.dat 110 entries loaded and sorted 110 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 127 entries loaded and sorted 127 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 3729 entries loaded
Reading LOB.dat
DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries
 1109 entries loaded
Reading ICOL.dat 4868 entries loaded
Reading COLTYPE.dat 2823 entries loaded
Reading TYPE.dat 2082 entries loaded
Reading ATTRIBUTE.dat 8736 entries loaded
Reading COLLECTION.dat 591 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 24 entries loaded
Reading TS.dat 7 entries loaded
Reading PROPS.dat 36 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
Found db_id = 1422012639
Found db_name = XIFENFEI

DUL> SCAN DATAFILE 4;

DUL: Warning: Recreating file "EXT.dat"
DUL: Warning: Recreating file "SEG.dat"
DUL: Warning: Recreating file "COMPATSEG.dat"
DUL: Warning: Recreating file "SCANNEDLOBPAGE.dat"
Scanning tablespace 4, data file 4 ...
  23 segment header and 1601 data blocks
  tablespace 4, data file 4: 2079 blocks scanned
Reading EXT.dat 54 entries loaded and sorted 54 entries
Reading SEG.dat 23 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entries

DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered
DUL> scan extents;

DUL: Warning: Recreating file "seen_tab.dat"
DUL: Warning: Recreating file "seen_col.dat"
Scanning extents without segment header

Scanning extent id (dba 0, obj 68332)

Analyzing segment: data object id 68332
  heap organized table
Col    Seen  Max PCT  PRINT  NUMBERS DATES TIMESTAMP WITH TZ INTRVAL  ROWIDS LOB

 no   count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice
  1   67854   18   0 100 100   0   0   0   0   0   0   0   0   0   0  44   0   0

…………
 14   67854    2   0   0   0 100 100   0   0   0   0   0   0   0   0   0   0   0

|SYS| |C_FILE#_BLOCK#| || |8| |8| |CLUSTER| |20-JAN-2013 AD 17:13:18| |20-JAN-20
…………
:18| |2013-01-20:17:13:18| |VALID| |N| |N| |N| |1|

UNLOAD TABLE OBJNO68332 ( COL001 VARCHAR2(18), COL002 VARCHAR2(30), COL003 VARCH
AR2(27)
        , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(18), COL007 DATE
        , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(7), COL011 VARCHAR2(
1)
        , COL012 VARCHAR2(1), COL013 VARCHAR2(1), COL014 NUMBER )
    STORAGE( DATAOBJNO 68332 );

--自己拼接语句
DUL> UNLOAD TABLE t_xifenfei_1(OWNER VARCHAR2(30),OBJECT_NAME  VARCHAR2(128),SUB
OBJECT_NAME    VARCHAR2(30),OBJECT_ID  NUMBER,DATA_OBJECT_ID    NUMBER,OBJECT_TY
PE  VARCHAR2(19),CREATED    DATE,LAST_DDL_TIME DATE,TIMESTAMP    VARCHAR2(19),ST
ATUS VARCHAR2(7),TEMPORARY    VARCHAR2(1),GENERATED    VARCHAR2(1),SECONDARY
VARCHAR2(1),NAMESPACE    NUMBER,EDITION_NAME VARCHAR2(30))  STORAGE( DATAOBJNO 6
8332 );
. unloading table              T_XIFENFEI_1
DUL: Warning: Recreating file "T_XIFENFEI_1.ctl"
   67854 rows unloaded

补充说明：
1.在最新的dul 10.2.0.5.20中，不支持scan extents命令
2.在最新的dul 10.2.0.5.20中，挖出来的无数据字典数据，字符串转换为16进制值

发表在 非常规恢复 | 标签为 , | 评论关闭

table中各种类型block坏块是否能被跳过

发表于 2013 年 2 月 3 日 由 惜分飞

在table遇到的各种坏块中，大部分情况，我们都可以通过设置event 10231或者dbms_repair来跳过坏块，抢救其他数据；但是在部分情况下，我们设置了他们依然不能跳过坏块，数据库依然报ORA-01578,本文测试了table中各种类型的block，证明在哪些blog出现异常之后不能被跳过.
如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见后续blog)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)
创建测试表

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> create table t_xifenfei 
  2  tablespace users 
  3  as
  4  select * from dba_objects;

Table created.

SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

查询相关block信息

SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS 
  2  WHERE OWNER='CHF' AND SEGMENT_NAME='T_XIFENFEI';

SEGMENT_NAME    HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
--------------- ----------- ------------ ---------- ----------
T_XIFENFEI                4          378       1152         24

SQL>   select   
  2    dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3    max(dbms_rowid.rowid_block_number(rowid)) max_block,
  4    min(dbms_rowid.rowid_block_number(rowid)) min_block
  5    from chf.t_xifenfei 
  6    group by dbms_rowid.rowid_relative_fno(rowid);

   REL_FNO  MAX_BLOCK  MIN_BLOCK
---------- ---------- ----------
         4       1728        379

SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='CHF'
   2  AND SEGMENT_NAME='T_XIFENFEI';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          4        376          8
         1          4        640          8
         2          4        648          8
         3          4        656          8
         4          4        664          8
         5          4        672          8
         6          4        680          8
         7          4        688          8
         8          4        696          8
         9          4        704          8
        10          4        712          8
        11          4        720          8
        12          4        728          8
        13          4        736          8
        14          4        744          8
        15          4        752          8
        16          4        768        128
        17          4        896        128
        18          4       1024        128
        19          4       1152        128
        20          4       1280        128
        21          4       1408        128
        22          4       1536        128
        23          4       1664        128

通过这里可以知道:真正的存储数据是从block 379开始,至于block 376、377、378是什么，使用dump block分析

验证block类型

SQL> alter system dump datafile 4 block 376;

System altered.

SQL> alter system dump datafile 4 block 377;

System altered.

SQL> alter system dump datafile 4 block 378;

System altered.

SQL> alter system dump datafile 4 block 379;

System altered.

--该block是另外extent的开始，所以也尝试分析是否有特殊之处
SQL> alter system dump datafile 4 block 640;

System altered.

--dump 文件header信息
Start dump data blocks tsn: 4 file#:4 minblk 376 maxblk 376
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777592
Block dump from disk:
buffer tsn: 4 rdba: 0x01000178 (4/376)
scn: 0x0b8c.3bfc6517 seq: 0x04 flg: 0x04 tail: 0x65172004
frmt: 0x02 chkval: 0xc8b3 type: 0x20=FIRST LEVEL BITMAP BLOCK


Start dump data blocks tsn: 4 file#:4 minblk 377 maxblk 377
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777593
Block dump from disk:
buffer tsn: 4 rdba: 0x01000179 (4/377)
scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04 tail: 0x65172118
frmt: 0x02 chkval: 0x9e8c type: 0x21=SECOND LEVEL BITMAP BLOCK


Start dump data blocks tsn: 4 file#:4 minblk 378 maxblk 378
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777594
BH (0x2a7f7f0c) file#: 4 rdba: 0x0100017a (4/378) class: 4 ba: 0x2a742000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
  dbwrid: 0 obj: 76372 objn: 76372 tsn: 4 afn: 4 hint: f
  hash: [0x3150a748,0x3150a748] lru: [0x2a7f8094,0x2a7f7ee4]
  lru-flags: hot_buffer
  ckptq: [NULL] fileq: [NULL] objq: [0x2f72dc34,0x2f72dc34] objaq: [0x2f72dc2c,0x2f72dc2c]
  st: XCURRENT md: NULL fpin: 'ktewh25: kteinicnt' tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x0100017a (4/378)
scn: 0x0b8c.3bfc651b seq: 0x01 flg: 0x04 tail: 0x651b2301
frmt: 0x02 chkval: 0xb2ae type: 0x23=PAGETABLE SEGMENT HEADER

Start dump data blocks tsn: 4 file#:4 minblk 379 maxblk 379
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777595
Block dump from disk:
buffer tsn: 4 rdba: 0x0100017b (4/379)
scn: 0x0b8c.3bfc6494 seq: 0x01 flg: 0x04 tail: 0x64940601
frmt: 0x02 chkval: 0x0567 type: 0x06=trans data

Start dump data blocks tsn: 4 file#:4 minblk 640 maxblk 640
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777856
Block dump from disk:
buffer tsn: 4 rdba: 0x01000280 (4/640)
scn: 0x0b8c.3bfc6496 seq: 0x01 flg: 0x04 tail: 0x64960601
frmt: 0x02 chkval: 0x0efe type: 0x06=trans data

这里可以知道：
1.block 376、377为BITMAP BLOCK
2.block 378为SEGMENT HEADER(和dba_segments视图中一致)
3.除extent 0中有特殊(含BITMAP BLOCK和SEGMENT HEADER)block，其他extent只包含事务数据

测试block 640

--block 640包含条数
SQL> select   count(rowid)
  2    from chf.t_xifenfei 
  3     where dbms_rowid.rowid_block_number(rowid)=640
  4  and dbms_rowid.rowid_relative_fno(rowid)=4;

COUNT(ROWID)
------------
          79

--bbed修改tailchk
BBED> set filename '/u01/oracle/oradata/ora11g/users01.dbf'
        FILENAME        /u01/oracle/oradata/ora11g/users01.dbf

BBED> set block 640
        BLOCK#          640

BBED> set mode edit
        MODE            Edit

BBED> p tailchk
ub4 tailchk                                 @8188     0x64960601

BBED> m /x 64960602
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 640              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 64960602 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 640:
current = 0xf80b, required = 0xf80b

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 640

Block 640 is corrupt
Corrupt block relative dba: 0x01000280 (file 0, block 640)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x01000280
 last change scn: 0x0b8c.3bfc6496 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x02069664
 check value in block header: 0xf80b
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

--查询坏块
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 640)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

--跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';

SKIP_COR
--------
ENABLED

SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74584

--修复坏块
BBED> m /x 01069664
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 640              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01069664 

 <32 bytes per line>

BBED> p tailchk
ub4 tailchk                                 @8188     0x64960601

BBED> sum apply
Check value for File 0, Block 640:
current = 0x0efe, required = 0x0efe

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 640


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

--除掉标记表坏块
SQL> BEGIN
  2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  3  SCHEMA_NAME => 'CHF',
  4  OBJECT_NAME => 'T_XIFENFEI',
  5  OBJECT_TYPE => dbms_repair.table_object,
  6  FLAGS => dbms_repair.NOSKIP_FLAG);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';

SKIP_COR
--------
DISABLED

--查询表记录正常
SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

在后续的操作中，也是按照类似步骤操作，考虑到篇幅有限，部分过程不再贴出来

测试block 379

SQL>  select count(*) from chf.t_xifenfei;
 select count(*) from chf.t_xifenfei
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 379)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'


SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74575

测试block 378

BBED> set block 378
        BLOCK#          378

--segment header 不支持bbed查看结构
BBED> p tailchk
BBED-00400: invalid blocktype (35)

BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 378                                   Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (35)


BBED> set offset 8188
        OFFSET          8188

BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 378              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01231b65 

 <32 bytes per line>

BBED> m /x 651b2302
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 378              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 651b2302 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 378:
current = 0xedf2, required = 0xedf2

--验证坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 378

Block 378 is corrupt
Corrupt block relative dba: 0x0100017a (file 0, block 378)
Fractured block found during verification
Data in bad block:
 type: 35 format: 2 rdba: 0x0100017a
 last change scn: 0x0b8c.3bfc651b seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x02231b65
 check value in block header: 0xedf2
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED


SQL>  select count(*) from chf.t_xifenfei;
 select count(*) from chf.t_xifenfei
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 378)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

--标记跳过坏块
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL procedure successfully completed.

--查询依然失败
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 378)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

测试block 377

BBED> m /x 18211766
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 377              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 18211766 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 377:
current = 0x9d8c, required = 0x9d8c

--bbed验证为坏块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 377

Block 377 is corrupt
Corrupt block relative dba: 0x01000179 (file 0, block 377)
Fractured block found during verification
Data in bad block:
 type: 33 format: 2 rdba: 0x01000179
 last change scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x66172118
 check value in block header: 0x9d8c
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

--dbv验证为坏块
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 18 03:32:18 2013

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

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Page 377 is influx - most likely media corrupt
Corrupt block relative dba: 0x01000179 (file 4, block 377)
Fractured block found during dbv: 
Data in bad block:
 type: 33 format: 2 rdba: 0x01000179
 last change scn: 0x0b8c.3bfc6517 seq: 0x18 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x66172118
 check value in block header: 0x9d8c
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 2560
Total Pages Processed (Data) : 1434
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 10
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 213
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 902
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 1
Total Pages Encrypted        : 0
Highest block SCN            : 1006486374 (2956.1006486374)

--查询表记录
SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI' AND OWNER='CHF';

SKIP_COR
--------
DISABLED

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

测试block 376

BBED> m /x 04201766
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 376              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 04201766 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 376:
current = 0xcbb3, required = 0xcbb3

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 376

Block 376 is corrupt
Corrupt block relative dba: 0x01000178 (file 0, block 376)
Fractured block found during verification
Data in bad block:
 type: 32 format: 2 rdba: 0x01000178
 last change scn: 0x0b8c.3bfc6517 seq: 0x4 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x66172004
 check value in block header: 0xcbb3
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

SQL>  select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     74663

通过测试证明，如果是事务数据块出现坏块可以通过dbms_repair.skip_corrupt_blocks来标记坏块(也可以使用event 10231)来实现；对于BITMAP BLOCK如果出现坏块不会对于读取数据无影响(至于其他操作,请见table中各种坏块对select/dml操作影响)；SEGMENT HEADER如果出现坏块，无法通过跳过坏块来实现获取其他数据(正常block)

发表在 Oracle备份恢复 | 标签为 | 评论关闭

操作系统级别做systemstate

发表于 2013 年 2 月 1 日 由 惜分飞

在有些时候，我们需要通过systemstate或者hanganalyze来收集数据库hang信息，但是当我们的数据库不能登录的时候，无法通过常规手段来使用该方法来收集信息。ORACLE的开发者也考虑到了类似情况，让我们可以通过dbx或者gdb来收集数据库信息.这里我通过在linux平台上模拟一个新会话，然后通过gdb直接对该会话进行做systemstate,然后操作该会话(证明gdb操作后正常),来实现类似系统hang住，无法登陆的时候怎么做systemstate.
模拟会话
就是假设我们hang住的系统中的数据库进程(为了后面gdb和验证对会话影响而使用,不然可以直接使用oracle 后台进程来完成该操作)

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 17 18:28:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

查找数据库进程
在另外一个shell窗口找出数据库进程

[root@xifenfei trace]# ps -ef|grep LOCAL
oracle    7476  7473  0 18:28 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      7487  7326  0 18:28 pts/2    00:00:00 grep LOCAL

gdb做systemstate

[oracle@xifenfei trace]$ gdb $ORACLE_HOME/bin/oracle 7476
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-37.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "i386-redhat-linux-gnu".
…………
Reading symbols from /u01/oracle/oracle/product/11.2.0/db_1/lib/libnque11.so...
Loaded symbols for /u01/oracle/oracle/product/11.2.0/db_1/lib/libnque11.so
0xb789c424 in __kernel_vsyscall ()
(gdb)  print ksudss(10)  <--输入print ksudss(10)
$1 = 0
(gdb) quit
A debugging session is active.

        Inferior 1 [process 7476] will be detached.

Quit anyway? (y or n) y
Detaching from program: /u01/oracle/oracle/product/11.2.0/db_1/bin/oracle, process 7476

查看trace文件

[oracle@xifenfei trace]$ ls -l *7476*.trc
-rw-r----- 1 oracle oinstall 742438 Jan 17 18:28 ora11g_ora_7476.trc
[oracle@xifenfei trace]$ more ora11g_ora_7476.trc
Trace file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_7476.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      xifenfei
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine:        i686
VM name:        VMWare Version: 6
Instance name: ora11g
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7476, image: oracle@xifenfei (TNS V1-V3)


*** 2013-01-17 18:28:59.225
*** SESSION ID:(143.23) 2013-01-17 18:28:59.225
*** CLIENT ID:() 2013-01-17 18:28:59.225
*** SERVICE NAME:(SYS$USERS) 2013-01-17 18:28:59.225
*** MODULE NAME:(sqlplus@xifenfei (TNS V1-V3)) 2013-01-17 18:28:59.225
*** ACTION NAME:() 2013-01-17 18:28:59.225
 
===================================================
SYSTEM STATE (level=10)  <---systemstate levle 10
------------
System global information:
     processes: base 0x32bc5b38, size 150, cleanup 0x32bd5990
     allocation: free sessions 0x32085b84, free calls (nil)
     control alloc errors: 0 (process), 0 (session), 0 (call)
     PMON latch cleanup depth: 0
     seconds since PMON's last scan for dead processes: 693
     system statistics:

这里证明使用gdb–>print ksudss(10)对数据库做的是systemstate level 10

验证做gdb的进程

SQL> select * from dual;

D
-
X

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

证明被gdb处理过的进程还是正常(未收到额外破坏),所以我们可以确定在系统hang住，而且新会话不能正常建立连接的时候，我们可以尝试着使用ksudss(10)来对系统做systemstate然后分析

发表在 Oracle | 一条评论