Oracle Dataguard 下块修复技术-系统数据表(三)

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

Oracle Dataguard 下块修复技术-系统数据表(三)

笱局长 2018-12-08 14:39:35 浏览789
展开阅读全文
来测试以下系统表OBJ$ 有坏块时的场景,数据库是否可以自动修复坏块?

(一)数据库环境

# 数据库版本
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


# 备库同步状态
SQL> select archived,max(sequence#),applied from v$archived_log group by archived,applied;

ARC MAX(SEQUENCE#) APPLIED
--- -------------- ---------
YES             24 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;

(四)查看对象OBJ$的segment状态

sys@ORCL> select segment_name,header_file , header_block,blocks from dba_segments where OWNER ='SYS' and  segment_name ='OBJ$';

SEGMENT_NAME                                                                      HEADER_FILE HEADER_BLOCK     BLOCKS
--------------------------------------------------------------------------------- ----------- ------------ ----------
OBJ$                                                                                        1          240       1152

(五)搞破坏,并强制重启数据库

dd if=/dev/zero of=/u01/app/oracle/oradata/standby/datafile/system01.dbf bs=8192 seek=240 count=2 conv=notrunc
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.000247249 s, 66.3 MB/s

sys@ORCL> shutdown abort

sys@ORCL> startup

Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 241)
ORA-01110: data file 1: '/u01/app/oracle/oradata/standby/datafile/system01.dbf'
Process ID: 533
Session ID: 5 Serial number: 3

(六)使用RMAN验证数据文件状态

# 启动到mount状态
idle> startup mount
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.
# 使用RMAN验证文件状态
RMAN> backup validate database datafile 1;

idle> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         1        240          2                  0 ALL ZERO
         

(七)修复并打开数据库

# 修复数据块
RMAN> blockrecover datafile 1 block 241;

Starting recover at 08-DEC-18
using channel ORA_DISK_1
searching flashback logs for block images until SCN 3125428
finished flashback log search, restored 1 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-DEC-18

# 打开数据库
idle> alter database open;

Database altered.

# 查询坏块已经被修复
idle> select * from v$database_block_corruption;

no rows selected

若遇到数据库坏块启动不了的问题,先检查下坏块的是什么对象,若是系统表,就可以利用RMAN进行修复;若是索引则可以考虑删除或者重建;
备份很重要。
在这个测试场景中,还可以直接闪回数据库到正常状态。

网友评论

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