Oracle Dataguard next block repair technology system data table

To test the scenario when the following system table OBJ $has bad blocks, can the database automatically repair the bad blocks?

(I) database environment

# Database version
sys@ORCL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for Linux: Version - Production
NLSRTL Version - Production

# Database running status
sys@ORCL>  select dbid,current_scn,RESETLOGS_CHANGE#,flashback_on from v$database;

---------- ----------- ----------------- ------------------
1520403684     3039036           2917000 YES

# DG synchronization status
DGMGRL> show configuration

Configuration - orcl

  Protection Mode: MaxPerformance
    orcl    - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

# Standby synchronization status
SQL> select archived,max(sequence#),applied from v$archived_log group by archived,applied;

--- -------------- ---------
YES             24 IN-MEMORY

(II) keep the implicit parameter auto BMR 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.

(III) backup database

RMAN> backup database;

(IV) view the segment status of OBJ $

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

(V) sabotage and force database restart

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

(VI) use RMAN to verify data file status

# Boot to mount state
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.
# Verify file status using RMAN
RMAN> backup validate database datafile 1;

idle> select * from v$database_block_corruption;

---------- ---------- ---------- ------------------ ---------
         1        240          2                  0 ALL ZERO

(VII) repair and open the database

# Repair data block
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

# Open database
idle> alter database open;

Database altered.

# Query bad block has been fixed
idle> select * from v$database_block_corruption;

no rows selected

If you encounter the problem that the bad block of the database cannot be started, first check what object the bad block is. If it is a system table, you can use RMAN to repair it. If it is an index, you can consider to delete or rebuild it;
Backup is important.
In this test scenario, you can also directly flash back to the normal state of the database.

Tags: Database Oracle rman SQL

Posted on Sat, 07 Dec 2019 21:57:53 -0500 by stuartshields