Oracle Dataguard 下块修复技术-用户数据表(二)

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

Oracle Dataguard 下块修复技术-用户数据表(二)

笱局长 2018-12-08 14:14:04 浏览844
展开阅读全文
已经介绍了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情况下访问数据表时,会自动修复坏块。

网友评论

登录后评论
0/500
评论
笱局长
+ 关注
所属云栖号: 袋鼠云技术团队