题目部分
在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)
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
● QQ:646634621 QQ群:230161599、618766405
● 微信:lhrbestxh
● 微信公众号:DB宝
● 提供Oracle OCP、OCM、高可用(rac+dg+ogg)和MySQL最实用的技能培训
● 题目解答若有不当之处,还望各位朋友批评指正,共同进步