已经介绍了Oracle 11gr2 的ABCR功能，当有损坏的数据库被检索到时，会自动修复。
现在测试一下，当数据库块损坏并强制关闭实例的情况下，数据库还会不会做自动修复。
首先测试普通用户表 BAIYANG.T_OBJS
(一)当前主备环境
# 数据库版本
sys@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL 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 configuration
Configuration - orcl
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
standby - Physical standby database
Fast-Start Failover: DISABLED
Configuration 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 a30
col VALUE for a30
col 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 rows
8 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 48
sys@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=notrunc
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.000394806 s, 41.5 MB/s
dd if=/dev/zero of=/u01/app/oracle/oradata/standby/datafile/users01.dbf bs=8192 seek=300 count=2 conv=notrunc
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.000394806 s, 41.5 MB/s
# 强制关闭数据库
sys@ORCL> shutdown abort
ORACLE instance shut down.
# 打开数据库，数据库可以正常打开
sys@ORCL> startup
ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
Database 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_STMTS
DBA_STREAMS_STMTS
DBA_STREAMS_STMTS
_DBA_APPLY_CHANGE_HANDLERS
DBA_APPLY_CHANGE_HANDLERS
DBA_APPLY_CHANGE_HANDLERS
8001 rows selected.
# alter_orcl.log
Corrupt 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 Segment
Corrupt 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 Segment
Sat Dec 08 13:50:31 2018
Automatic block media recovery successful for (file# 4, block# 301)
Sat Dec 08 13:50:31 2018
Automatic 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 Segment
Automatic 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情况下访问数据表时，会自动修复坏块。
