logical 坏块恢复失败

fluttersnow

DB 10.2.0.4
OS  redhat 5.8

昨天在检查V$database_block_corruption发现有许多坏块，type都为logical。
查看block对应关系都为同一个表HB_PRODUCE_SOURCE.TEMP_POIPOOL_W  及相关的index。
这个表是4/12上午10点左右expdp导入的。
下面查询也可看出时间为4/12

1. SQL> select FILE#,UNRECOVERABLE_CHANGE#, UNRECOVERABLE_TIME,CREATE_BYTES ,FIRST_NONLOGGED_SCN, FIRST_NONLOGGED_TIME
   
2. from  v$datafile where file#=5;
   
3.      FILE# UNRECOVERABLE_CHANGE# UNRECOVER CREATE_BYTES FIRST_NONLOGGED_SCN FIRST_NON
   
4. ---------- --------------------- --------- ------------ ------------------- ---------
   
5.          5               9852189 12-APR-13   5368709120             9843767 12-APR-13
   
6. RMAN> list backup summary;

复制代码

尝试使用backup validate datafile，backup validate check logical database； 检测坏块任然存在

尝试坏块修复

1. RMAN> blockrecover corruption list;
   
2. 
   
3. Starting blockrecover at 21-APR-13
   
4. using channel ORA_DISK_1
   
5. RMAN-00571: ===========================================================
   
6. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   
7. RMAN-00571: ===========================================================
   
8. RMAN-03002: failure of blockrecover command at 04/21/2013 17:30:40
   
9. ORA-19680: some blocks not recovered. See trace file for details
   
10. ORA-04030: out of process memory when trying to allocate 8728 bytes (pga heap,iobuf_krbrblk)
    
11. 
    
12. backup validate datafile 5;

复制代码

再次检查V$database_block_corruption,坏块仍然存在

rman的trace如下

1. /u01/orabase/admin/averify/udump/averify_ora_27758.trc
   
2. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
   
3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
4. ORACLE_HOME = /u01/orabase/db
   
5. System name:    Linux
   
6. Node name:      3FORAverify
   
7. Release:        2.6.18-308.el5
   
8. Version:        #1 SMP Fri Jan 27 17:17:51 EST 2012
   
9. Machine:        x86_64
   
10. Instance name: averify
    
11. Redo thread mounted by this instance: 1
    
12. Oracle process number: 27
    
13. Unix process pid: 27758, image: oracle@3FORAverify (TNS V1-V3)
    
14. 
    
15. *** 2013-04-21 16:49:20.739
    
16. *** ACTION NAME:(0000037 FINISHED60) 2013-04-21 16:49:20.732
    
17. *** MODULE NAME:(rman@3FORAverify (TNS V1-V3)) 2013-04-21 16:49:20.732
    
18. *** SERVICE NAME:(SYS$USERS) 2013-04-21 16:49:20.732
    
19. *** SESSION ID:(1640.44) 2013-04-21 16:49:20.732
    
20. Recovery target incarnation = 2, activation ID = 0
    
21. Influx buffer limit = 860865 (50% x 1721730)
    
22. Successfully allocated 16 recovery slaves
    
23. Using 70 overflow buffers per recovery slave
    
24. Start recovery at thread 1 ckpt scn 10414190 logseq 67 block 4631
    
25. *** 2013-04-21 16:49:21.191
    
26. Media Recovery add redo thread 1
    
27. *** 2013-04-21 16:49:21.194
    
28. Recovery of Online Redo Log: Thread 1 Group 2 Seq 67 Reading mem 0
    
29. File 4 (stop scn 10415433) completed recovery at checkpoint scn 10415434

复制代码

Maclean Liu(刘相兵

1.
有多少坏块？

select count(*) from V$database_block_corruption;


2.

logical corruption 未必能rman blockrecover 解决

3.

4030 一般 是PGA不足，

不要用 corruption list

指定几个block试试

fluttersnow

Maclean Liu(刘相兵 发表于 2013-4-22 12:55
1.
有多少坏块？

1.

1. SQL>
   
2. SQL>  select count(*) from  V$database_block_corruption;
   
3. 
   
4.   COUNT(*)
   
5. ----------
   
6.        705
   

复制代码

3.单个block恢复测试
这个datafile，在4/12以后做过switch new location的操作
原来路径： /u01/orabase/oradata/averify/cxl_poi.dbf
现在路径： /oradata/averify/cxl_poi.dbf

1. RMAN> blockrecover datafile 5 block 857586;
   
2. 
   
3. Starting blockrecover at 22-APR-13
   
4. using target database control file instead of recovery catalog
   
5. allocated channel: ORA_DISK_1
   
6. channel ORA_DISK_1: sid=1634 devtype=DISK
   
7. 
   
8. channel ORA_DISK_1: restoring block(s)
   
9. channel ORA_DISK_1: specifying block(s) to restore from backup set
   
10. restoring blocks of datafile 00005
    
11. channel ORA_DISK_1: reading from backup piece /oradata/fra/AVERIFY/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T110043_8q99yw32_.bkp
    
12. channel ORA_DISK_1: restored block(s) from backup piece 1
    
13. piece handle=/oradata/fra/AVERIFY/backupset/2013_04_22/o1_mf_nnndf_TAG20130422T110043_8q99yw32_.bkp tag=TAG20130422T110043
    
14. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:36
    
15. failover to previous backup
    
16. 
    
17. channel ORA_DISK_1: restoring block(s)
    
18. channel ORA_DISK_1: specifying block(s) to restore from backup set
    
19. restoring blocks of datafile 00005
    
20. channel ORA_DISK_1: reading from backup piece /oradata/fra/AVERIFY/backupset/2013_04_21/o1_mf_nnnd0_20130421L0_8q77qb4g_.bkp
    
21. channel ORA_DISK_1: restored block(s) from backup piece 1
    
22. piece handle=/oradata/fra/AVERIFY/backupset/2013_04_21/o1_mf_nnnd0_20130421L0_8q77qb4g_.bkp tag=20130421L0
    
23. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:55
    
24. failover to previous backup
    
25. 
    
26. channel ORA_DISK_1: restoring block(s) from datafile copy /u01/orabase/oradata/averify/cxl_poi.dbf
    
27. RMAN-00571: ===========================================================
    
28. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    
29. RMAN-00571: ===========================================================
    
30. RMAN-03002: failure of blockrecover command at 04/22/2013 13:02:11
    
31. ORA-19505: failed to identify file "/u01/orabase/oradata/averify/cxl_poi.dbf"
    
32. ORA-27037: unable to obtain file status
    
33. Linux-x86_64 Error: 2: No such file or directory
    
34. Additional information: 3
    
35. ORA-19600: input file is datafile copy 0 (/u01/orabase/oradata/averify/cxl_poi.dbf)

复制代码

fluttersnow

这个表是个零时表，create时候加了NOLONGING，尝试过restore后recover，坏块仍旧存在
是否这个情况只能drop related object在重建了？