Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

0

积分

1

好友

7

主题
1#
发表于 2013-1-15 12:15:30 | 查看: 4838| 回复: 3
本帖最后由 jacksan 于 2013-1-15 12:18 编辑

2012年12月發過一個這樣的帖子,可是現在怎麼也找不到了。
不知為何? (好像當時還不需要註冊用戶)
現在重寫一次吧:)

做RMAN備份時,提示:
ORA-01578: ORACLE data block corrupted (file # 4, block # 642)
ORA-01110: data file 4: '/u01/app/oracle/oradata/SID/users01.dbf'

我嘗試過修復這個壞塊,但是沒有任何備份,失敗告終。
用下面的sql也查不出來,顯示no row selected. 不知為何??
select segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where file_id = 4
and 642 between block_id and (block_id + blocks - 1)

我也dump了那個壞塊,可是不太清除如何分析這個dump。
Start dump data blocks tsn: 4 file#:4 minblk 642 maxblk 642
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777858
BH (0xdff6df18) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xdf0a2000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xe5f7a878,0x15c603620] lru: [0xe1fd1d30,0xe1fd1ad0]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c14285],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c142c3],[sfl: 0x0],[lc: 0x0.0]
  flags:
BH (0xe5f7a7c8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe51f4000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xe8fd1cf8,0xdff6dfc8] lru: [0xe5f7a650,0xe5f71570]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c1270b],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c12749],[sfl: 0x0],[lc: 0x0.0]
  flags:
BH (0xe8fd1c48) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe8b24000
  set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 77,19
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xedf76d18,0xe5f7a878] lru: [0xe8fd1e60,0xe8fd1c00]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c10b41],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c10b7f],[sfl: 0x0],[lc: 0x0.0]
  flags:
BH (0xedf76c68) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xed190000
  set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xeef7df18,0xe8fd1cf8] lru: [0xedf7bce0,0xedf76c20]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c0ef7c],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c0efba],[sfl: 0x0],[lc: 0x0.0]
  flags:
BH (0xeef7de68) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xee250000
  set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 76,19
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xdef90338,0xedf76d18] lru: [0xeef85c00,0xeef7a9e0]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c0d3a9],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c0d3e7],[sfl: 0x0],[lc: 0x0.0]
  flags:
BH (0xdef90288) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xde43c000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xe1fd1bc8,0xeef7df18] lru: [0xdef904a0,0xdef94f70]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c0b86c],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c0b86c],[sfl: 0x0],[lc: 0x0.0]
  flags:
BH (0xe1fd1b18) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe1b22000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xe5f72378,0xdef90338] lru: [0xeef99580,0xe5f723b0]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xe5f722c8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe5114000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xd7fd73a8,0xe1fd1bc8] lru: [0xe1fd1c00,0x15cc5a268]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xd7fd72f8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xd7bb6000
  set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 77,19
  dbwrid: 0 obj: 82235 objn: 0 tsn: 4 afn: 4 hint: f
  hash: [0xe9fb2578,0xe5f72378] lru: [0xd7fdce20,0xedfe5450]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xe9fb24c8) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe97d4000
  set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xedfe5418,0xd7fd73a8] lru: [0xd7f76890,0xdefde840]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xedfe5368) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xedd30000
  set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 77,19
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xeef99548,0xe9fb2578] lru: [0xd7fd73e0,0x15cc58d68]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xeef99498) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xee532000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xdefde808,0xedfe5418] lru: [0xd7fc29c0,0xe1fd1c00]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xdefde758) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xdec7a000
  set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xe1f855d8,0xeef99548] lru: [0xe9fb25b0,0xe1f85610]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xe1f85528) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xe1318000
  set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 72,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xf2f861b8,0xdefde808] lru: [0xdefde840,0x15cc59468]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
BH (0xf2f86108) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xf232c000
  set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 76,19
  dbwrid: 0 obj: 82235 objn: 0 tsn: 4 afn: 4 hint: f
  hash: [0x15c603620,0xe1f855d8] lru: [0x62fb13e0,0xecfceb50]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
Block dump from disk:
buffer tsn: 4 rdba: 0x01000282 (4/642)
scn: 0x0000.00a791fc seq: 0x01 flg: 0x04 tail: 0x80852301
frmt: 0x02 chkval: 0xb664 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of corrupt header 2 = BROKEN
Dump of memory from 0x00002AC14E7ADA00 to 0x00002AC14E7ADA14
2AC14E7ADA00 0000A223 01000282 00A791FC 04010000  [#...............]
2AC14E7ADA10 0000B664                             [d...]            
Hex dump of corrupt block
Dump of memory from 0x00002AC14E7ADA14 to 0x00002AC14E7AF9FC
2AC14E7ADA10          00000000 00000000 00000000      [............]
2AC14E7ADA20 00000000 00000001 00000008 00000A9C  [................]
2AC14E7ADA30 00000000 00000008 00000008 01000288  [................]
2AC14E7ADA40 00000000 00000000 00000000 00000005  [................]
2AC14E7ADA50 0006000A 00015C7E 00000001 00000000  [....~\..........]
2AC14E7ADA60 00000008 00000008 01000288 00000000  [................]
2AC14E7ADA70 00000000 00000000 00000005 01000280  [................]
2AC14E7ADA80 01000280 00000000 00000000 00000000  [................]
2AC14E7ADA90 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
2AC14E7ADAD0 00000001 00002000 00000000 00001434  [..... ......4...]
2AC14E7ADAE0 00000000 01000281 00000001 01000280  [................]
2AC14E7ADAF0 01000281 00000000 00000000 00000000  [................]
2AC14E7ADB00 00000000 00000000 00000001 00000000  [................]
2AC14E7ADB10 0001413B 10000000 01000280 00000008  [;A..............]
2AC14E7ADB20 00000000 00000000 00000000 00000000  [................]
        Repeat 152 times
2AC14E7AE4B0 01000280 01000283 00000000 00000000  [................]
2AC14E7AE4C0 00000000 00000000 00000000 00000000  [................]
        Repeat 151 times
2AC14E7AEE40 00000000 00000000 01000281 00000000  [................]
2AC14E7AEE50 00000000 00000000 00000000 00000000  [................]
        Repeat 185 times
2AC14E7AF9F0 00000000 00000000 00000000           [............]   
End dump data blocks tsn: 4 file#: 4 minblk 642 maxblk 642


當時Maclean做了回答的,可是忘記是怎麼回答的了。

2#
发表于 2013-1-15 12:43:10
alter system dump datafile 4 block 642 ;

上传生成的dump 文件

不属于任何 segment的可能是 游离块


对于这种快 可以通过 创建一张空的 大的表来覆盖该块 解决该问题


例如

create table tv( t1 int) tablespace xxx;


alter table tv allocate extent (datafile 4 size 9999M);

回复 只看该作者 道具 举报

3#
发表于 2013-1-15 13:52:03
我記得那天是這個dump文件。(删了一些客戶信息)

xxxx_ora_11755.zip

1.86 KB, 下载次数: 1304

回复 只看该作者 道具 举报

4#
发表于 2013-1-15 20:47:05
Dump of buffer cache at level 4 for tsn=4 rdba=16777858
BH (0xdff6df18) file#: 4 rdba: 0x01000282 (4/642) class: 4 ba: 0xdf0a2000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 79,28
  dbwrid: 0 obj: 82235 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0xe5f7a878,0x15c603620] lru: [0xe1fd1d30,0xe1fd1ad0]
  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  st: CR md: NULL tch: 0
  cr: [scn: 0x0.c14285],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.c142c3],[sfl: 0x0],[lc: 0x0.0]
  flags:

Block dump from disk:
buffer tsn: 4 rdba: 0x01000282 (4/642)
scn: 0x0000.00a791fc seq: 0x01 flg: 0x04 tail: 0x80852301
frmt: 0x02 chkval: 0xb664 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of corrupt header 2 = BROKEN
Dump of memory from 0x00002AC14E7ADA00 to 0x00002AC14E7ADA14
2AC14E7ADA00 0000A223 01000282 00A791FC 04010000  [#...............]
2AC14E7ADA10 0000B664                             [d...]            
Hex dump of corrupt block




obj: 82235

看下 object_Id 82235的对象

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-26 03:20 , Processed in 0.051844 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569