The ABCR function of Oracle 11gr2 has been introduced. When a damaged database is retrieved, it will be automatically repaired.
Now test whether the database will be automatically repaired when the database block is damaged and the instance is forced to close.
First, test the general user table, baiyang.t'ou objs
(I) current active and standby environment
# Database version 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 # Database open status 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 synchronization status DGMGRL> show configuration Configuration - orcl Protection Mode: MaxPerformance Databases: orcl - Primary database standby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS # Standby DG synchronization SQL> select archived,max(sequence#),applied from v$archived_log group by archived,applied; ARC MAX(SEQUENCE#) APPLIED --- -------------- --------- YES 9 IN-MEMORY # Implied parameter auto BMR is currently on 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.
(II) backup the main database
RMAN> backup database;
(III) view the segment allocation of object T_OBJS
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
(IV) next, destroy and forcibly close the main warehouse
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 # Force database shutdown sys@ORCL> shutdown abort ORACLE instance shut down. # Open the database. The database can be opened normally 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.
(V) if the table is queried directly at this time, the bad block will be automatically repaired; first, use RMAN to verify the data and confirm that there are bad blocks
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
(VI) inquiry form
sys@ORCL> select count(*) from BAIYANG.T_OBJS; COUNT(*) ---------- 7513 # The following full table query will retrieve some data blocks, and the bad blocks will be fixed 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)
(7) check whether there is any bad block again
sys@ORCL> select * from v$database_block_corruption; no rows selected
The normal user table block damage does not affect the instance startup, and does not verify whether the user table is normal at startup;
When the data table is accessed in ADG mode after startup, the bad block will be automatically repaired.