数据库的坏块经常是一件非常棘手的事情，Oracle数据库从11.2后，使用 Active Data Guard option 时，主库和备库可以自动借用对方的block来修复自身的坏块，这个过程对应用是透明的。Starting in Oracle Database 11g Release 2 (11.2), the primary database
automatically attempts to repair the corrupted block in real time by
fetching a good version of the same block from a physical standby
database. This capability is referred to as automatic block repair,
and it allows corrupt data blocks to be automatically repaired as soon
as the corruption is detected. Automatic block repair reduces the
amount of time that data is inaccessible due to block corruption. It
also reduces block recovery time by using up-to-date good blocks in
real-time, as opposed to retrieving blocks from disk or tape backups,
or from Flashback logs. Automatic block repair requires the use of the
Oracle Active Data Guard option. You can use an Oracle Active Data
Guard standby database for automatic repair of data corruptions
detected by the primary database. Additionally if the corruption is
discovered on an Active Data Guard physical standby database the
corruption will be automatically repaired with a good block from the
Primary. Both of these operations are transparent to the applications.
ALTER DATABASE DATAFILE 13 resize 2m;
create table aa tablespace ex1 as select * from dba_objects ; dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/ex1.dbf bs=8192 seek=10 count=2 conv=notrunc RMAN> host 'dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/ex1.dbf bs=8192 seek=10 count=2 conv=notrunc'; 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.00015527 s, 106 MB/s host command complete RMAN> list failure; Database Role: PRIMARY no failures found that match specification RMAN> validate datafile 15; Starting validate at Sep 09 2020 18:16:41 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=14 device type=DISK channel ORA_DISK_1: starting validation of datafile channel ORA_DISK_1: specifying datafile(s) for validation input datafile file number=00013 name=/u01/app/oracle/oradata/orcl/ex1.dbf channel ORA_DISK_1: validation complete, elapsed time: 00:00:03 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 13 FAILED 1 1 130 2488395 File Name: /u01/app/oracle/oradata/orcl/ex1.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 0 Index 0 0 Other 1 127 validate found one or more corrupt blocks See trace file /u01/app/oracle/diag/rdbms/orcl/cdr1/trace/cdr1_ora_27031.trc for details Finished validate at Sep 09 2020 18:16:45 RMAN> list failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- -------------------- ------- 2090 HIGH OPEN Sep 09 2020 18:16:44 Datafile 13: '/u01/app/oracle/oradata/orcl/ex1.dbf' contains one or more corrupt blocks RMAN> advise failure; Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- -------------------- ------- 2090 HIGH OPEN Sep 09 2020 16:43:42 Datafile 13: '/u01/app/oracle/oradata/orcl/ex1.dbf' contains one or more corrupt blocks analyzing automatic repair options; this may take some time allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=14 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== 1. No backup of block 100 in file 13 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption 2. No backup of block 101 in file 13 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption 3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair Optional Manual Actions ======================= 1. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. Then perform a Data Guard role change (failover). Available standbys: orcl1by. Automated Repair Options ======================== no automatic repair options available RMAN> run{ allocate channel c1 type 'sbt_tape' parms=' SBT_LIBRARY=/opt/scutech/dbackup3/lib/libobk.so, ENV=(URL=http://192.168.87.155:50306/d2/data/0d36020eecec11ea8000b49691622ac4/c7873e9aed0811ea8000b49691622ac4, EXTRA=X-Access-Key:ea5350ac00fa61d08533a729da808de0)' trace=0; blockrecover datafile 13 block 400 to 401; }2> 3> 4> 5> 6> 7> 8> released channel: ORA_DISK_1 allocated channel: c1 channel c1: SID=14 device type=SBT_TAPE channel c1: DBackup3 Oracle MMS Library Starting recover at Sep 09 2020 16:46:46 channel c1: restoring block(s) channel c1: specifying block(s) to restore from backup set restoring blocks of datafile 00013 channel c1: reading from backup piece 6rva0apb_1_1 channel c1: piece handle=6rva0apb_1_1 tag=TAG20200909T164323 channel c1: restored block(s) from backup piece 1 channel c1: block restore complete, elapsed time: 00:00:01 starting media recovery archived log for thread 1 with sequence 279 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_08/o1_mf_1_279_hogz5ylq_.arc archived log for thread 1 with sequence 280 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_08/o1_mf_1_280_hoh3qxk1_.arc archived log for thread 1 with sequence 281 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_08/o1_mf_1_281_hoh7bow0_.arc archived log for thread 1 with sequence 282 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_282_hohw8y8y_.arc archived log for thread 1 with sequence 283 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_283_hoj818t1_.arc archived log for thread 1 with sequence 284 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_284_hojnjpd1_.arc archived log for thread 1 with sequence 285 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_285_hok0g6kn_.arc archived log for thread 1 with sequence 286 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_286_hok0gf3q_.arc archived log for thread 1 with sequence 287 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_287_hok0gjb3_.arc media recovery complete, elapsed time: 00:00:07 Finished recover at Sep 09 2020 16:47:00 Starting recover at Sep 09 2020 16:47:01 channel c1: restoring block(s) channel c1: specifying block(s) to restore from backup set restoring blocks of datafile 00013 channel c1: reading from backup piece 6rva0apb_1_1 channel c1: piece handle=6rva0apb_1_1 tag=TAG20200909T164323 channel c1: restored block(s) from backup piece 1 channel c1: block restore complete, elapsed time: 00:00:01 starting media recovery archived log for thread 1 with sequence 279 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_08/o1_mf_1_279_hogz5ylq_.arc archived log for thread 1 with sequence 280 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_08/o1_mf_1_280_hoh3qxk1_.arc archived log for thread 1 with sequence 281 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_08/o1_mf_1_281_hoh7bow0_.arc archived log for thread 1 with sequence 282 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_282_hohw8y8y_.arc archived log for thread 1 with sequence 283 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_283_hoj818t1_.arc archived log for thread 1 with sequence 284 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_284_hojnjpd1_.arc archived log for thread 1 with sequence 285 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_285_hok0g6kn_.arc archived log for thread 1 with sequence 286 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_286_hok0gf3q_.arc archived log for thread 1 with sequence 287 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_09_09/o1_mf_1_287_hok0gjb3_.arc media recovery complete, elapsed time: 00:00:07 Finished recover at Sep 09 2020 16:47:14 released channel: c1 RMAN> list failure; Database Role: PRIMARY no failures found that match specification RMAN>
切换数据库到active apply状态
select open_mode from v$database; alter database recover managed standby database cancel; alter database open; select open_mode from v$database; alter database recover managed standby database using current logfile disconnect; SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY SQL>
再测试
SQL> select count(*) from sys.aa 2 ; select count(*) from sys.aa * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 15, block # 10) ORA-01110: data file 15: '/u01/app/oracle/oradata/orcl/ex1.dbf' SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from sys.aa; COUNT(*) ---------- 91475
在alert log里面有：
Reading datafile '/u01/app/oracle/oradata/orcl/ex1.dbf' for corruption at rdba: 0x03c0000b (file 15, block 11) Reread (file 15, block 11) found same corrupt data (no logical check) Automatic block media recovery requested for (file# 15, block# 11) Wed Sep 09 19:29:41 2020 Automatic block media recovery successful for (file# 15, block# 11) Wed Sep 09 19:29:41 2020 Automatic block media recovery successful for (file# 15, block# 11)
结论：
备库必须处于ADG的状态才能自动修复坏块
