本文共 6518 字，大约阅读时间需要 21 分钟。
现在测试一下，当数据库块损坏并强制关闭实例的情况下，数据库还会不会做自动修复。
首先测试普通用户表 BAIYANG.T_OBJS(一)当前主备环境# 数据库版本sys@ORCL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production# 数据库打开状态sys@ORCL> select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database; DBID CURRENT_SCN RESETLOGS_CHANGE# FLASHBACK_ON---------- ----------- ----------------- ------------------1520403684 3039036 2917000 YES# DG同步状态DGMGRL> show configurationConfiguration - orcl Protection Mode: MaxPerformance Databases: orcl - Primary database standby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESS# 备库DG同步SQL> select archived,max(sequence#),applied from v$archived_log group by archived,applied;ARC MAX(SEQUENCE#) APPLIED--- -------------- ---------YES 9 IN-MEMORY# 隐含参数 _auto_bmr 当前是开启的col NAME for a30col VALUE for a30col describ for a40 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm LIKE '%_auto_bmr%'; NAME VALUE DESCRIB------------------------------ ------------------------------ ----------------------------------------_auto_bmr ENABLED enable/disable Auto BMR_auto_bmr_req_timeout 60 Auto BMR Requester Timeout_auto_bmr_sess_threshold 30 Auto BMR Request Session Threshold_auto_bmr_pub_timeout 10 Auto BMR Publish Timeout_auto_bmr_fc_time 60 Auto BMR Flood Control Time_auto_bmr_bg_time 3600 Auto BMR Process Run Time_auto_bmr_sys_threshold 100 Auto BMR Request System Threshold_auto_bmr_max_rowno 1024 x$krbabrstat Max number of rows8 rows selected. （二）备份主库
RMAN> backup database;
（三）查看对象T_OBJS的segment分配
sys@ORCL> select segment_name,header_file , header_block,blocks from dba_segments where OWNER ='BAIYANG' and segment_name ='T_OBJS';SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS--------------------------------------------------------------------------------- ----------- ------------ ----------T_OBJS 4 298 48sys@ORCL> select name ,file# from v$datafile;NAME FILE#---------------------------------------------------------------------------------------------------- ----------/u01/app/oracle/oradata/standby/datafile/system01.dbf 1/u01/app/oracle/oradata/standby/datafile/sysaux01.dbf 2/u01/app/oracle/oradata/standby/datafile/undotbs01.dbf 3/u01/app/oracle/oradata/standby/datafile/users01.dbf 4/u01/app/oracle/product/11.2.0/db_1/dbs/lxx2.dbf 7
（四）接下来搞破坏并强制关闭主库
dd if=/dev/zero of=/u01/app/oracle/oradata/standby/datafile/users01.dbf bs=8192 seek=298 count=3 conv=notrunc2+0 records in2+0 records out16384 bytes (16 kB) copied, 0.000394806 s, 41.5 MB/sdd if=/dev/zero of=/u01/app/oracle/oradata/standby/datafile/users01.dbf bs=8192 seek=300 count=2 conv=notrunc2+0 records in2+0 records out16384 bytes (16 kB) copied, 0.000394806 s, 41.5 MB/s# 强制关闭数据库sys@ORCL> shutdown abortORACLE instance shut down.# 打开数据库，数据库可以正常打开sys@ORCL> startupORACLE instance started.Total System Global Area 2455228416 bytesFixed Size 2255712 bytesVariable Size 620758176 bytesDatabase Buffers 1811939328 bytesRedo Buffers 20275200 bytesDatabase mounted.Database opened.
（五）如果这时直接查询表，那么坏块会被自动修复；先使用RMAN验证下数据，并确认有坏块
RMAN> backup validate database datafile 4;sys@ORCL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO---------- ---------- ---------- ------------------ --------- 4 304 2 0 ALL ZERO 4 299 3 0 ALL ZERO
（六）查询表
sys@ORCL> select count(*) from BAIYANG.T_OBJS; COUNT(*)---------- 7513# 以下全表查询时会检索有所的数据块，此时会修复坏块 sys@ORCL> select * from BAIYANG.T_OBJS;………… DBA_STREAMS_STMT_HANDLERS_DBA_STREAMS_STMTSDBA_STREAMS_STMTSDBA_STREAMS_STMTS_DBA_APPLY_CHANGE_HANDLERSDBA_APPLY_CHANGE_HANDLERSDBA_APPLY_CHANGE_HANDLERS8001 rows selected.# alter_orcl.logCorrupt Block Found TSN = 4, TSNAME = USERS RFN = 4, BLK = 299, RDBA = 16777515 OBJN = 87356, OBJD = 87356, OBJECT = T_OBJS, SUBOBJECT = SEGMENT OWNER = BAIYANG, SEGMENT TYPE = Table SegmentCorrupt Block Found TSN = 4, TSNAME = USERS RFN = 4, BLK = 300, RDBA = 16777516 OBJN = 87356, OBJD = 87356, OBJECT = T_OBJS, SUBOBJECT = SEGMENT OWNER = BAIYANG, SEGMENT TYPE = Table SegmentSat Dec 08 13:50:31 2018Automatic block media recovery successful for (file# 4, block# 301)Sat Dec 08 13:50:31 2018Automatic block media recovery successful for (file# 4, block# 299)Automatic block media recovery successful for (file# 4, block# 299)………………Corrupt Block Found TSN = 4, TSNAME = USERS RFN = 4, BLK = 304, RDBA = 16777520 OBJN = 87356, OBJD = 87356, OBJECT = T_OBJS, SUBOBJECT = SEGMENT OWNER = BAIYANG, SEGMENT TYPE = Table SegmentAutomatic block media recovery successful for (file# 4, block# 305)Automatic block media recovery successful for (file# 4, block# 304)Automatic block media recovery successful for (file# 4, block# 304)Automatic block media recovery successful for (file# 4, block# 305)
（七）再次查询还有没有坏块
sys@ORCL> select * from v$database_block_corruption;no rows selected
普通用户表块损坏不影响实例启动，启动时不验证用户表是否正常；
启动后在ADG情况下访问数据表时，会自动修复坏块。转载地址：http://njiga.baihongyu.com/