oracle中11gR2 dataguard 备库文件损坏解决案例
某客户的一套11gR2 dataguard环境出现异常，检查发现是备库出现文件损坏，且无法正常情况，已经超过1个多月没同步了。 我们先来看下备库的日志：
.......省略部分内容
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception
Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjnew/trace/crjnew_pr0p_9892.trc:
ORA-00600: internal error code, arguments: [3020], [3], [6118], [12589030], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 6118, file offset is 50118656 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_undotbs1_859l2yrm_.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception
Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjnew/trace/crjnew_pr1p_9964.trc:
ORA-00600: internal error code, arguments: [3020], [3], [3740], [12586652], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 3740, file offset is 30638080 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_undotbs1_859l2yrm_.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
Recovery interrupted!
Recovered data files to a consistent state at change 12331596958128
MRP0: Background Media Recovery process shutdown (crjnew)
.....省略部分内容
Tue May 27 19:30:03 2014
Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjnew/trace/crjnew_pr1e_21956.trc (incident=444672):
ORA-00600: internal error code, arguments: [3020], [16], [1016759], [68125623], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 16, block# 1016759, file offset is 4034322432 bytes)
ORA-10564: tablespace CRJ
ORA-01110: data file 16: '/u01/app/oracle/oradata/CRJNEW/datafile/crj_data09.dbf'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 77037
Incident details in: /u01/app/oracle/diag/rdbms/crjnew/crjnew/incident/incdir_444672/crjnew_pr1e_21956_i444672.trc
Tue May 27 19:30:06 2014
Dumping diagnostic data in directory=[cdmp_20140527193006], requested by (instance=1, osid=21956 (PR1E)), summary=[incident=444672].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception
Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjnew/trace/crjnew_pr1e_21956.trc:
ORA-00600: internal error code, arguments: [3020], [16], [1016759], [68125623], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 16, block# 1016759, file offset is 4034322432 bytes)
ORA-10564: tablespace CRJ
ORA-01110: data file 16: '/u01/app/oracle/oradata/CRJNEW/datafile/crj_data09.dbf'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 77037
Tue May 27 19:30:06 2014
Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjnew/trace/crjnew_mrp0_21854.trc (incident=444262):
ORA-00600: internal error code, arguments: [3020], [16], [1016759], [68125623], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 16, block# 1016759, file offset is 4034322432 bytes)
ORA-10564: tablespace CRJ
ORA-01110: data file 16: '/u01/app/oracle/oradata/CRJNEW/datafile/crj_data09.dbf'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 77037
Incident details in: /u01/app/oracle/diag/rdbms/crjnew/crjnew/incident/incdir_444262/crjnew_mrp0_21854_i444262.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Recovery Slave PR1E previously exited with exception 600
Tue May 27 19:30:07 2014
MRP0: Background Media Recovery terminated with error 448
Errors in file /u01/app/oracle/diag/rdbms/crjnew/crjnew/trace/crjnew_pr00_21856.trc:
ORA-00448: normal completion of background process
Recovery interrupted!
Recovered data files to a consistent state at change 12331596967112
MRP0: Background Media Recovery process shutdown (crjnew)
Tue May 27 19:30:11 2014
Sweep [inc][444672]: completed
Sweep [inc][444262]: completed
Sweep [inc2][444672]: completed
Sweep [inc2][444262]: completed
Tue May 27 19:32:08 2014
Primary database is in MAXIMUM PERFORMANCE mode
你会看到，当你手工发起recover managed standby database disconnect from session后，会出现上述的错误。我们也可以清楚
的看到，之所以MRP经常无法正常启动，是因为有文件存在坏块。对于数据文件坏块，通过dbv检查你会发现是这么一种情况：
[oracle@gscrj01 ~]$ dbv file=/u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_sysaux_859l29lq_.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Tue May 27 18:02:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_sysaux_859l29lq_.dbf
Page 121298 is influx - most likely media corrupt
Corrupt block relative dba: 0x0081d9d2 (file 2, block 121298)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0081d9d2
last change scn: 0x0b37.2c742a38 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x441f0601
check value in block header: 0xf89f
computed block checksum: 0x2281
DBVERIFY - Verification complete
Total Pages Examined : 655360
Total Pages Processed (Data) : 77609
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 66328
Total Pages Failing (Index): 0
Total Pages Processed (Lob) : 9344
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 108285
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 393793
Total Pages Marked Corrupt : 1
Total Pages Influx : 1
Total Pages Encrypted : 0
Highest block SCN : 745850569 (2871.745850569)
[oracle@gscrj01 ~]$ dbv file=/u01/app/oracle/oradata/CRJNEW/datafile/crj_data07.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Tue May 27 18:12:41 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/CRJNEW/datafile/crj_data07.dbf
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 47043
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 22456
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3862660
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 745794635 (2871.745794635)
我这里检查了2个报错的文件，发现sysaux的文件有一个坏块，然而另外一个数据dbv检查并没有提示坏块，但是为什么会报错呢？
这里的错误基本上都是类似ORA-10567: Redo is inconsistent with data block 的问题，这可能不是block本身的问题，可能是
日志写的内容和块的内容不一致了。
开始我看只有3个文件有报错，那我就想，能否直接从主库scp 这3个文件到备库，然后直接recover就行了呗？ 大概是这样一个操作：
--备库
alter database datafile n offline drop;
mv xxxx.dbf xxxx.dbf.bak
--主库
scp /xxx/xxxx/xxxx.dbf [email protected]:/xxx/xxx/xxx.dbf
--备库
alter database datafile n online;
alter database recover managed standby database disconnect from session;
这种操作本身没有问题，然而有问题的是，这3个文件处理了之后，恢复发行又报错其他的数据文件了，我檫。
整个数据库一共2.2TB，80个30g的文件。 我不可能给他全库scp过去。
那么怎么弄呢 ？
其实很简单，我很早之前也讲过利用rman增量的方式来恢复dataguard环境中缺少日志导致gap的情况。 我们也可以使用类似
这个方法来做，下面是我的基本操作：
—定位备库同步的scn
SQL> col FIRST_CHANGE# for 9999999999999999999
SQL> col next_change# for 9999999999999999
SQL> /
SEQUENCE# APPLIED FIRST_CHANGE# NEXT_CHANGE#
---------- --------- -------------------- -----------------
6141 YES 12331596661580 12331596717210
6142 YES 12331596717210 12331596758421
6143 YES 12331596758421 12331596805008
6144 YES 12331596805008 12331596838849
6145 YES 12331596838849 12331596901470
6146 YES 12331596901470 12331596958127
6147 NO 12331596958127 12331597090365
6148 NO 12331597090365 12331597133130
6149 NO 12331597133130 12331597176234
6150 NO 12331597176234 12331597220783
6151 NO 12331597220783 12331597276144
。。。。。省略部分内容
—主库进行增量备份（基于scn）
rman target / << OEF
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup as compressed backupset incremental from SCN 12331596958127 database format '/oraclenew/datadir3/rmanback双击查看原图_incr_%d_%T_%U.bak'
include current controlfile for standby filesperset=5 tag 'forstandby0527';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
exit
EOF
—-将主库的备份文件scp到备库，并注册到catalog
RMAN> catalog start with '/oraclenew/datadir3/temp/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oraclenew/datadir3/temp/
List of Files Unknown to the Database
=====================================
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0sp9btk8_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0kp9botr_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0op9brdj_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0mp9bqlg_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0up9butr_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_10p9c01g_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_11p9c37k_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0lp9bqhs_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0gp9bmtn_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0jp9boid_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0ip9bmtn_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0tp9bul8_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0pp9bsg4_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0rp9btan_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0qp9bsul_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0np9br09_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0fp9bmtn_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0vp9bvp7_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0hp9bmtn_1_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0sp9btk8_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0kp9botr_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0op9brdj_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0mp9bqlg_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0up9butr_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_10p9c01g_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_11p9c37k_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0lp9bqhs_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0gp9bmtn_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0jp9boid_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0ip9bmtn_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0tp9bul8_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0pp9bsg4_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0rp9btan_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0qp9bsul_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0np9br09_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0fp9bmtn_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0vp9bvp7_1_1.bak
File Name: /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0hp9bmtn_1_1.bak
—进行recover备库
RMAN> recover database noredo;
Starting recover at 28-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1261 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_system_859l1ovo_.dbf
destination for restore of datafile 00015: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data08.dbf
destination for restore of datafile 00016: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data09.dbf
destination for restore of datafile 00060: /oraclenew/datadir1/crj_data50.dbf
destination for restore of datafile 00062: /oraclenew/datadir1/crj_data51.dbf
channel ORA_DISK_1: reading from backup piece /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0op9brdj_1_1.bak
channel ORA_DISK_1: piece handle=/oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0op9brdj_1_1.bak tag=FORSTANDBY0527
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_sysaux_859l29lq_.dbf
destination for restore of datafile 00017: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data10.dbf
destination for restore of datafile 00018: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data11.dbf
destination for restore of datafile 00063: /oraclenew/datadir1/crj_data52.dbf
destination for restore of datafile 00064: /oraclenew/datadir1/crj_data53.dbf
channel ORA_DISK_1: reading from backup piece /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0pp9bsg4_1_1.bak
channel ORA_DISK_1: piece handle=/oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0pp9bsg4_1_1.bak tag=FORSTANDBY0527
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/CRJNEW/datafile/o1_mf_users_859l57gz_.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/CRJNEW/datafile/dzzj_index01.dbf
destination for restore of datafile 00008: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data01.dbf
destination for restore of datafile 00054: /oraclenew/datadir1/dzzj_data02.dbf
destination for restore of datafile 00076: /oraclenew/datadir3/crjnew_bin01.dbf
channel ORA_DISK_1: reading from backup piece /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0up9butr_1_1.bak
......部分内容
hannel ORA_DISK_1: reading from backup piece /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0hp9bmtn_1_1.bak
channel ORA_DISK_1: piece handle=/oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0hp9bmtn_1_1.bak tag=FORSTANDBY0527
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00034: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data27.dbf
destination for restore of datafile 00035: /u01/app/oracle/oradata/CRJNEW/datafile/crj_data28.dbf
destination for restore of datafile 00056: /oraclenew/datadir1/dzzj_index02.dbf
destination for restore of datafile 00061: /oraclenew/datadir1/zzsb_data01.dbf
channel ORA_DISK_1: reading from backup piece /oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0qp9bsul_1_1.bak
channel ORA_DISK_1: piece handle=/oraclenew/datadir3/temp/db_incr_CRJNEW_20140527_0qp9bsul_1_1.bak tag=FORSTANDBY0527
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished recover at 28-MAY-14
如果你这个时候去看alert log，你会发现类似这样的信息：
started logmerger process
Wed May 28 14:30:22 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6147_9nv894go_.arc
Completed: alter database recover managed standby database disconnect from session
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6148_9nv88s4v_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6149_9nv88zkm_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6150_9nv894yk_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6151_9nv896bo_.arc
Wed May 28 14:30:34 2014
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6152_9nv89fv0_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_15/o1_mf_1_6153_9nv89g10_.arc
......
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_21/o1_mf_1_6208_9o9mnqhc_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/CRJNEW/archivelog/2014_04_21/o1_mf_1_6209_9obb1c7s_.arc
......
你会发现Oracle仍然会去检查，并跳过这部分差了1个多月的归档，这个过程很快的，不到10分钟完成了。
当然，这个case就算over了。
备注：oracle 11gR2（准确的说是11.2.0.2）开始，active dataguard引入了Automatic Block Repair 机制。然后该机制
需要满足的一定的条件，如下是官方文档的说明：
If ... Then ...
A corrupt data block is discovered on a primary database
A physical standby database operating in real-time query mode can be used to repair corrupt data blocks in a primary database. If possible, any corrupt data block encountered when a primary database is accessed will be automatically replaced with an uncorrupted copy of that block from a physical standby database operating in real-time query mode. An ORA-1578 error is returned when automatic repair is not possible.
A corrupt data block is discovered on a physical standby database
The server attempts to automatically repair the corruption by obtaining a copy of the block from the primary database if the following database initialization parameters are configured on the standby database:
?Configure the LOG_ARCHIVE_CONFIG parameter with a DG_CONFIG list
?Configure a LOG_ARCHIVE_DEST_n parameter for the primary database
实际上，可能还存在一些特殊的情况，当然客户这里是没有使用real-time模式
