【DB笔试面试791】在Oracle中，BBED模拟修复坏块。

AiDBA宝典

发布于 2020-05-07 15:32:33

5530

发布于 2020-05-07 15:32:33

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在Oracle中，BBED模拟修复坏块。

♣

答案部分

SYS@orclasm > create tablespace ts_bc_lhr datafile '/tmp/ts_bc_lhr.dbf' size 50M;

Tablespace created.

SYS@orclasm > create table t_bc_lhr  tablespace ts_bc_lhr as select * from dba_objects;

Table created.


SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,
       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_ID,
       COUNT(1) COUNTS
  FROM LHR.T_BC_LHR D
 GROUP BY DBMS_ROWID.ROWID_OBJECT(ROWID),
          DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
          DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
 ORDER BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

[oracle@rhel6lhr ~]$ echo "12 /tmp/ts_bc_lhr.dbf" > /home/oracle/file.txt
[oracle@rhel6lhr ~]$ bbed PASSWORD=blockedit  mode=edit blocksize=8192 listfile=/home/oracle/file.txt

BBED: Release 2.0.0.0.0 - Limited Production on Mon May 22 16:35:14 2017

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

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

BBED> show
        FILE#           12
        BLOCK#          1
        OFFSET          0
        DBA             0x03000001 (50331649 12,1)
        FILENAME        /tmp/ts_bc_lhr.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/file.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         ./log.bbd
        SPOOL           No


BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
    12  /tmp/ts_bc_lhr.dbf                                                   0

BBED> set dba 12,2443
        DBA             0x0300098b (50334091 12,2443)

BBED> modify /c HAHAH dba 12,2443 offset 0
 File: /tmp/ts_bc_lhr.dbf (12)
 Block: 2443             Offsets:    0 to  127           Dba:0x0300098b
------------------------------------------------------------------------
 48414841 48090003 c33cc703 00000204 26570000 01000000 c2760200 c13cc703 
 00000000 03003200 88090003 ffff0000 00000000 00000000 00000000 00800000 
 c13cc703 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00015800 

 <32 bytes per line>

BBED> dump /v dba 12,2443 offset 0 
 File: /tmp/ts_bc_lhr.dbf (12)
 Block: 2443    Offsets:    0 to  127  Dba:0x0300098b
-------------------------------------------------------
 48414841 48090003 c33cc703 00000204 l HAHAH....<......
 26570000 01000000 c2760200 c13cc703 l &W.......v...<..
 00000000 03003200 88090003 ffff0000 l ......2.........
 00000000 00000000 00000000 00800000 l ................
 c13cc703 00000000 00000000 00000000 l .<..............
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00015800 l ..............X.

 <16 bytes per line>

BBED> sum apply
Check value for File 12, Block 2443:
current = 0xf5e3, required = 0xf5e3

BBED> verify
DBVERIFY - Verification starting
FILE = /tmp/ts_bc_lhr.dbf
BLOCK = 2443

Block 2443 is corrupt
Corrupt block relative dba: 0x0300098b (file 0, block 2443)
Bad header found during verification
Data in bad block:
 type: 72 format: 1 rdba: 0x03000948
 last change scn: 0x0000.03c73cc3 seq: 0x2 flg: 0x04
 spare1: 0x48 spare2: 0x41 spare3: 0x0
 consistency value in tail: 0x3cc30602
 check value in block header: 0xf5e3
 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           : 0
Message 531 not found;  product=RDBMS; facility=BBED


BBED> 
[oracle@rhel6lhr ~]$ dbv file=/tmp/ts_bc_lhr.dbf blocksize=8192   

DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 22 16:51:26 2017

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

DBVERIFY - Verification starting : FILE = /tmp/ts_bc_lhr.dbf
Page 2443 is marked corrupt
Corrupt block relative dba: 0x0300098b (file 12, block 2443)
Bad header found during dbv: 
Data in bad block:
 type: 72 format: 1 rdba: 0x03000948
 last change scn: 0x0000.03c73cc3 seq: 0x2 flg: 0x04
 spare1: 0x48 spare2: 0x41 spare3: 0x0
 consistency value in tail: 0x3cc30602
 check value in block header: 0xf5e3
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 6400
Total Pages Processed (Data) : 2236
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 185
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3978
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 63388870 (0.63388870)
[oracle@rhel6lhr ~]$ 


SYS@orclasm > alter system flush BUFFER_CACHE;

System altered.

SYS@orclasm > select /*+FULL(T)*/ COUNT(1) FROM LHR.t_bc_lhr;
select /*+FULL(T)*/ COUNT(1) FROM LHR.t_bc_lhr
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 2443)
ORA-01110: data file 12: '/tmp/ts_bc_lhr.dbf'

SYS@orclasm > ANALYZE TABLE LHR.t_bc_lhr VALIDATE STRUCTURE;
ANALYZE TABLE LHR.t_bc_lhr VALIDATE STRUCTURE
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 12, block # 2443)
ORA-01110: data file 12: '/tmp/ts_bc_lhr.dbf'

SYS@orclasm > SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        12       2443          1                  0 CORRUPT


[oracle@rhel6lhr ~]$ exp lhr/lhr tables=lhr.T_BC_LHR file=T_BC_LHR.dmp

Export: Release 11.2.0.3.0 - Production on Mon May 22 17:40:52 2017

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                       T_BC_LHR
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 12, block # 131)
ORA-01110: data file 12: '/tmp/ts_bc_lhr.dbf'
Export terminated successfully with warnings.
[oracle@rhel6lhr ~]$ 

SYS@orclasm > select tablespace_id,header_file,header_block from sys.sys_dba_segs where owner='LHR' and segment_name='T_BC_LHR';

TABLESPACE_ID HEADER_FILE HEADER_BLOCK
------------- ----------- ------------
           36          12          130

[oracle@rhel6lhr ~]$ dbv userid=sys/lhr segment_id=36.12.130

DBVERIFY: Release 11.2.0.3.0 - Production on Mon May 22 17:35:33 2017

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

DBVERIFY - Verification starting : SEGMENT_ID = 36.12.130
Page 131 is marked corrupt
Corrupt block relative dba: 0x03000083 (file 12, block 131)
Bad header found during dbv: 
Data in bad block:
 type: 72 format: 1 rdba: 0x03000048
 last change scn: 0x0000.03c748c6 seq: 0x2 flg: 0x04
 spare1: 0x48 spare2: 0x41 spare3: 0x0
 consistency value in tail: 0x48c60602
 check value in block header: 0xefaf
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 8
Total Pages Processed (Data) : 2
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 63391953 (0.63391953)