坏块处理一

最新推荐文章于 2021-04-06 11:26:00 发布

anddyhua

最新推荐文章于 2021-04-06 11:26:00 发布

阅读量1.9k

点赞数

分类专栏： Note For DB

本文链接：https://blog.csdn.net/anddyhua/article/details/25967323

版权

Note For DB 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

查看oracle incident 有

adrci> show incident -orderby CREATE_TIME desc -last 3

ADR Home = /u02/app/diag/tnslsnr/asm1/asm:
*************************************************************************
0 rows fetched

ADR Home = /u02/app/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched

ADR Home = /u02/app/diag/rdbms/ora11g/ora11g:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
43614                ORA 1578                                                    2014-05-13 03:55:22.592000 +08:00
43613                ORA 1578                                                    2014-05-13 03:55:16.843000 +08:00
43612                ORA 1578                                                    2014-05-13 03:55:11.351000 +08:00
First 3 rows fetched (*** more available ***)

adrci> show incident -mode detail -p "incident_id=43614"

DR Home = /u02/app/diag/tnslsnr/asm1/asm:
*************************************************************************
0 rows fetched
<INCIDENT_INFO mode="detail">
<ADR_HOME name="/u02/app/diag/tnslsnr/asm1/asm">

ADR Home = /u02/app/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched
</ADR_HOME>
<ADR_HOME name="/u02/app/diag/asm/+asm/+ASM">

ADR Home = /u02/app/diag/rdbms/ora11g/ora11g:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   43614
   STATUS                        ready
   CREATE_TIME                   2014-05-13 03:55:22.592000 +08:00
   PROBLEM_ID                    2
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  1578
   ERROR_ARG1                    2
   ERROR_ARG2                    32903
   ERROR_ARG3                    <NULL>
   ERROR_ARG4                    <NULL>
   ERROR_ARG5                    <NULL>
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          CACHE_RCV
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 1578
   FIRST_INCIDENT                11053
   FIRSTINC_TIME                 2014-02-12 08:26:46.733000 +08:00
   LAST_INCIDENT                 45042
   LASTINC_TIME                  2014-05-16 04:40:38.523000 +08:00
   IMPACT1                       34668552
   IMPACT2                       34668545
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@asm1.20238_3077392064
   KEY_NAME                      PQ
   KEY_VALUE                     (16777220, 1399924521)
   KEY_NAME                      ProcId
   KEY_VALUE                     37.1
   KEY_NAME                      SID
   KEY_VALUE                     48.1
   OWNER_ID                      1
   INCIDENT_FILE                 /u02/app/diag/rdbms/ora11g/ora11g/incident/incdir_43614/ora11g_j002_20238_i43614.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u02/app/diag/rdbms/ora11g/ora11g/trace/ora11g_j002_20238.trc
1 rows fetched

tail -f /u02/app/diag/rdbms/ora11g/ora11g/trace/ora11g_j002_20238.trc

DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
Byte offset to file# 2 block# 32903 is 269541376
sskgds_getexecname: using /proc/self/status and $ORACLE_HOME/bin to get /u02/app/oracle/bin/oracle

*** 2014-05-13 03:54:57.771
Incident 43610 created, dump file: /u02/app/diag/rdbms/ora11g/ora11g/incident/incdir_43610/ora11g_j002_20238_i43610.trc
ORA-01578: ORACLE data block corrupted (file # 2, block # 32903)
ORA-01110: data file 2: '+DATA/ora11g/datafile/sysaux.257.832151447'

*** 2014-05-13 03:55:07.497
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: '+DATA/ora11g/datafile/sysaux.257.832151447'
Byte offset to file# 2 block# 32903 is 269541376

*** 2014-05-13 03:55:08.166
Incident 43611 created, dump file: /u02/app/diag/rdbms/ora11g/ora11g/incident/incdir_43611/ora11g_j002_20238_i43611.trc
ORA-01578: ORACLE data block corrupted (file # 2, block # 32903)
ORA-01110: data file 2: '+DATA/ora11g/datafile/sysaux.257.832151447'

DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: '+DATA/ora11g/datafile/sysaux.257.832151447'
Byte offset to file# 2 block# 32903 is 269541376
Incident 43612 created, dump file: /u02/app/diag/rdbms/ora11g/ora11g/incident/incdir_43612/ora11g_j002_20238_i43612.trc
ORA-01578: ORACLE data block corrupted (file # 2, block # 32903)
ORA-01110: data file 2: '+DATA/ora11g/datafile/sysaux.257.832151447'

freeing in-flux r/w latch for process state: 34bcef84
... in-flux r/w latch 33aa83c8 Child cache buffers chains level=1 child#=959
        Location from where latch is held: No latch:
        Context saved from call: 0
        state=busy(shared) [value=0x40000001] wlstate=free [value=0]
    waiters [orapid (seconds since: put on list, posted, alive check)]:
     12 (2, 1399924515, 2)
     waiter count=1
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: '+DATA/ora11g/datafile/sysaux.257.832151447'
Byte offset to file# 2 block# 32903 is 269541376

[/home/oracle]$ dbv file=+DATA/ora11g/datafile/sysaux.257.832151447 userid=sys/oracle

DBVERIFY: Release 11.2.0.1.0 - Production on Fri May 16 04:50:02 2014

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

DBVERIFY - Verification starting : FILE = +DATA/ora11g/datafile/sysaux.257.832151447
csc(0x0000.00b2c0bb) higher than block scn(0x0000.00000000)
Page 32903 failed with check code 6054
Page 32911 is marked corrupt
Corrupt block relative dba: 0x0080808f (file 2, block 32911)
Bad header found during dbv:
Data in bad block:
type: 11 format: 2 rdba: 0x00c00001
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: 0x242a
computed block checksum: 0x0

DBV-00200: Block, DBA 8421511, already marked corrupt

DBVERIFY - Verification complete

Total Pages Examined         : 99080
Total Pages Processed (Data) : 31044
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 27669
Total Pages Failing   (Index): 0
Total Pages Processed (Lob) : 9356
Total Pages Failing   (Lob) : 0
Total Pages Processed (Other): 17044
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 13966
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

找到坏块对象

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         2      32911          1                  0 FRACTURED
         2      32903          1                  0 FRACTURED

SQL> Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=2 andblock_id between 32911 and 32911+blocks-1;

TABLESPACE_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYSAUX INDEX SYSMAN
MGMT_TARGETS_IDX_01

进行索引的重建

alter index MGMT_TARGETS_IDX_01 rebuild;

SQL> Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=2 andblock_id between 32911 and 32911+blocks-1;

no rows selected

dbv file=+DATA/ora11g/datafile/sysaux.257.832151447 userid=sys/oracle 有点慢

SQL> select tablespace_id,header_file, header_block from sys_dba_segs where segment_name = 'MGMT_TARGETS_IDX_01';

TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
1 2 64394

dbv segment_id=1.2.64394 userid=sys/oracle

no rows selected

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
2 32903 1 0 FRACTURED

32903不是在表上的坏块，也是在唯一索引上的，不是表上很难用DBMS_REPAIR去跳过修复，理想用备份，然后用rman blockrecover datafile x block xx

没有备份只能是BBED或者exp+10231

如果没有备份只能是

注：使用dbv工具检验数据文件是否有坏块

 dbv工具可以用来验证数据文件的有效性，在数据库恢复之前可以使用该命令对备份文件进行有效性检查，
防止因备份文件本身的问题导致数据库无法恢复。
当然，dbv命令也可以对在线的数据文件进行检查。

注意，dbv工具只可以对数据文件进行检查，无法使用它完成控制文件和日志文件的检查。

1.dbv命令语法
dbverify ::=

  dbv [ USERID=username/password ]
    FILE = filename
  | { START = block_address | END = block_address }
  | BLOCKSIZE = integer
  | HIGH_SCN = integer
  | LOGFILE = filename
  | FEEDBACK = integer
  | HELP  = { Y | N }
  | PARFILE = filename

End of description.

参考自Oracle官方文档http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/img_text/dbverify.htm

2.查看帮助文档
 从语法定义中我们看到“HELP  = { Y | N }”选项，我们可以使用它查看dbv的帮助信息。
 ticket@secDB /home/oracle$ dbv help=y

 DBVERIFY: Release 11.2.0.1.0 - Production on Wed Mar 31 19:47:36 2010

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

 Keyword     Description                    (Default)
 ----------------------------------------------------
 FILE        File to Verify                 (NONE)
 START       Start Block                    (First Block of File)
 END         End Block                      (Last Block of File)
 BLOCKSIZE   Logical Block Size             (8192)
 LOGFILE     Output Log                     (NONE)
 FEEDBACK    Display Progress               (0)
 PARFILE     Parameter File                 (NONE)
 USERID      Username/Password              (NONE)
 SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
 HIGH_SCN    Highest Block SCN To Verify    (NONE)
    (scn_wrap.scn_base OR scn)

 帮助信息中描述了dbv命令的使用方法，不赘述。

3.体验dbv工具的效果
 1）查看系统中的数据文件名称
 sys@ticket> col name for a60
 sys@ticket> select name from v$datafile;

 NAME
 ------------------------------------------------------------
 /oracle/ora11gR2/oradata/ticket/system01.dbf
 /oracle/ora11gR2/oradata/ticket/sysaux01.dbf
 /oracle/ora11gR2/oradata/ticket/undotbs01.dbf
 /oracle/ora11gR2/oradata/ticket/users01.dbf

 2）使用dbv工具对users01.dbf进行检查
 （1）使用最简单的参数
 sys@ticket> !dbv file=/oracle/ora11gR2/oradata/ticket/users01.dbf

 DBVERIFY: Release 11.2.0.1.0 - Production on Wed Mar 31 19:50:59 2010

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

 DBVERIFY - Verification starting : FILE = /oracle/ora11gR2/oradata/ticket/users01.dbf


 DBVERIFY - Verification complete

 Total Pages Examined         : 35520
 Total Pages Processed (Data) : 33029
 Total Pages Failing   (Data) : 0
 Total Pages Processed (Index): 20
 Total Pages Failing   (Index): 0
 Total Pages Processed (Other): 402
 Total Pages Processed (Seg)  : 0
 Total Pages Failing   (Seg)  : 0
 Total Pages Empty            : 2069
 Total Pages Marked Corrupt   : 0
 Total Pages Influx           : 0
 Total Pages Encrypted        : 0
 Highest block SCN            : 9291961 (0.9291961)

 在实际使用中重点关注以下信息：
 Total Pages Failing   (Data) : 0
 Total Pages Failing   (Index): 0
 Total Pages Failing   (Seg)  : 0
 Total Pages Marked Corrupt   : 0

 如果以上信息返回结果不为0，需要重点关注！及时排查原因。

 （2）如果指定logfile参数，检查结果将只记录在日志文件中，屏幕上不显示
 sys@ticket> !dbv file=/oracle/ora11gR2/oradata/ticket/users01.dbf logfile=dbv_users01.log

 DBVERIFY: Release 11.2.0.1.0 - Production on Wed Mar 31 19:52:20 2010

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

 此时可以使用SQL*Plus的edit命令查看生成的日志文件内容。
 sys@ticket> ed dbv_users01.log

anddyhua

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
坏块处理一

查看oracle日使用dbv工具检验数据文件是否有坏块 dbv工具可以用来验证数据文件的有效性，在数据库恢复之前可以使用该命令对备份文件进行有效性检查，防止因备份文件本身的问题导致数据库无法恢复。当然，dbv命令也可以对在线的数据文件进行检查。注意，dbv工具只可以对数据文件进行检查，无法使用它完成控制文件和日志文件的检查。1.dbv命令语法dbve
复制链接

扫一扫