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; 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 running 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 synchronization status SQL> select archived,max(sequence#),applied from v$archived_log group by archived,applied; ARC MAX(SEQUENCE#) 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; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 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.