How to Corrupt Oracle Database Block Manually

<<Back to Oracle ASM Main Page

Corrupting Oracle Database Block Intensely

Note:- The post is only intended for educational and knowledge purpose, must not be tried in Live (prod as well as non-prod) database.

CASE1: When ASM is Used
Let's say I want to corrupt  Block#132 of  datafile# 9 stored in ASM File#256 and ASM DG#4
I already know
DB_BLOCK_SIZE  is 8k and ASM AU_SIZE => 1M for the tablespace holding this block. Check out  Trace Down Your Data from Database to Disk to find out the above details. That's said let's proceed
1M of AU can hold 128 DB Blocks of 8K size and therefore with simple math calculation we can find that the block number 132 is the 4th block in 2nd ASM Extent.
So Let us find the Disk holding this block physically and corrupt it using dd.

Login to ASM Instance and run below query to find the AU distribution across the disk.
SQL> select XNUM_KFFXP,
DISK_KFFXP,
AU_KFFXP
from X$KFFXP
where NUMBER_KFFXP=256
AND GROUP_KFFXP=4
AND XNUM_KFFXP<=2;
XNUM_KFFXP    DISK_KFFXP   AU_KFFXP
---------- ----------                            ----------
         0                    0                                 61
         0                    1                                 61
         1                    1                                 62<= 2nd Extent (Primary AU)
         1                    0                                 62<=2nd Extent (Secondary AU)
         2                    0                                 63
         2                    1                                 63
6 rows selected.

So from the output we can see that the data is physically stored on Disk#0 (Secondary Copy) and Disk#1 (Primary Copy).

Lets Find these Disk on OS
SQL>  select DISK_NUMBER, NAME from V$ASM_DISK where DISK_NUMBER in (0,1) and GROUP_NUMBER=4;
DISK_NUMBER NAME
----------- ------------------------------
          0 DATA_TEST
          1 DATA_TEST1
$ asmcmd afd_lsdsk |grep TEST
DATA_TEST                   ENABLED   /dev/xvde
DATA_TEST1                  ENABLED   /dev/xvdf

Lets Corrupt It.
#dd if=corrupt of=/dev/xvde  bs=8k  count=1 seek=7940

Verify the Corruption from ASM Instance

SQL> alter diskgroup TEST_DATA scrub file '+TEST_DATA/WDTESTDBA/DATAFILE/TRACE_DATA.256.984068463';

Diskgroup altered.

Examine the ASM Alert Log

SQL> alter diskgroup TEST_DATA scrub file '+TEST_DATA/WDTESTDBA/DATAFILE/TRACE_DATA.256.984068463'
Starting background process SCRB
2018-08-16T12:33:10.586097+02:00
SCRB started with pid=30, OS id=25618
2018-08-16T12:33:10.587580+02:00
SUCCESS: alter diskgroup TEST_DATA scrub file '+TEST_DATA/WDTESTDBA/DATAFILE/TRACE_DATA.256.984068463'
2018-08-16T12:33:23.795765+02:00
Starting background process ASMB
2018-08-16T12:33:23.808254+02:00
ASMB started with pid=33, OS id=25728
2018-08-16T12:33:23.871583+02:00
NOTE: ASMB0 registering with ASM instance as Standard client 0xffffffffffffffff (reg:3966494159) (new connection)
2018-08-16T12:33:23.995566+02:00
NOTE: Standard client +ASM:+ASM:ASM registered, osid 25730, mbr 0x0, asmb 25728 (reg:3966494159)
2018-08-16T12:33:23.995884+02:00
NOTE: ASMB0 (25728) connected to ASM instance +ASM, osid: 25730 (Flex mode; client id 0xffffffffffffffff)
2018-08-16T12:33:24.032471+02:00
NOTE: Corrupted block 132 found in file +TEST_DATA/WDTESTDBA/DATAFILE/TRACE_DATA.256.984068463
2018-08-16T12:33:24.834625+02:00

CASE2: When File System is Used.
Create a Tablespace test_corruption
SQL> create tablespace test_corruption datafile '/u01/dbatst1/c1_test01.dbf' size 20m;Tablespace created.
Create a table test_corruption in tablespace test_corruption
SQL> create table test_corruption(name varchar(20)) tablespace test_corruption ;Table created.
Inset some data in table test_corruption
SQL> insert into test_corruption values ('TESTING');
SQL> insert into test_corruption values ('TESTING');
SQL> insert into test_corruption values ('TESTING');
SQL> insert into test_corruption values ('TESTING');
SQL> commit;
Commit complete.
Find the header Blok of the table 
SQL> SELECT header_block FROM dba_segments WHERE segment_name='TEST_CORRUPTION';

HEADER_BLOCK
------------
         130
Do the Corruption in Data Block (Skip the header Block) 
$ dd of=/u01/dbatst1/c1_test01.dbf bs=8192 conv=notrunc seek=1030 << EOF
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
113 bytes (113 B) copied, 0.000206179 s, 548 kB/s
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
Validate the Corruption
$ rman target /
RMAN> validate tablespace test_corruption ;
Starting validate at 16-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00011 name=/u01/dbatst1/c1_test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11   FAILED 0              2424         2560            2100178
  File Name: /u01/dbatst1/c1_test01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5
  Index      0              0
  Other      1              131
validate found one or more corrupt blocks
See trace file /u01/dbatst1/diag/rdbms/wdtestdba/WDTESTDBA/trace/WDTESTDBA_ora_20899.trc for details
Finished validate at 16-AUG-18
SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
        11       1030          1                  0 CORRUPT            1