Manually Corrupting Oracle Data Block & Recover using BLOCKRECOVER
Posted by Mir Sayeed Hassan on October 8th, 2017
Manually Corrupting Oracle Data Block & Recover using BLOCKRECOVER
The below-given example is for the testing & learning purpose only, don’t use the production databases.
- I have manually corrupted the data block for my own testing ENV & verified
- Create the separate tablespace & the new schema/user to perform this action.
Create the tablespace as shown below
SQL> create tablespace corrupt_block_test datafile '/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf' size 100m;
Create the Schema as shown below:
SQL> create user corrupt_block_test identified by corrupt_block_test; User created.
SQL> alter user corrupt_block_test default tablespace corrupt_block_test; User altered.
SQL> grant create session, resource to corrupt_block_test; Grant succeeded.
Create a table & insert some record into the table
[oracle@testdb ~]$ !sq
SQL> connect corrupt_block_test/corrupt_block_test; Connected.
Insert some record using the “for loop” block
SQL> create table emp(eno number(30));
SQL> begin for i in 1..10000 loop insert into emp values(i); end loop; end; PL/SQL procedure successfully completed.
SQL> select count(*) from emp; COUNT(*) ———- 10000
Verify the existing tablespaces & datafiles
RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name TESTDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 31180 SYSTEM *** /u01/app/oracle/oradata/testdb/system01.dbf 2 5616 SYSAUX *** /u01/app/oracle/oradata/testdb/sysaux01.dbf 3 100 UNDOTBS1 *** /u01/app/oracle/oradata/testdb/undotbs01.dbf 4 500 USERS *** /u01/app/oracle/oradata/testdb/users01.dbf 5 50 EXAMPLE *** /u01/app/oracle/oradata/testdb/example01.dbf 6 10 CORRUPT_BLOCK_TEST *** /u01/app/oracle/oradata/testdb/corrupt_block_test.dbf 7 250 TEST_PARTITION *** /u01/app/oracle/oradata/testdb/test_partition.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 6087 TEMP 32767 /u01/app/oracle/oradata/testdb/temp01.dbf 2 100 TEMP 100 /u01/app/oracle/oradata/testdb/temp02.dbf
Take the backup of the tablespace which is created to test the corrupt block “CORRUPT_BLOCK_TEST”
RMAN> backup tablespace corrupt_block_test; Starting backup at 08-OCT-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=482 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/testdb/ corrupt_block_test.dbf channel ORA_DISK_1: starting piece 1 at 08-OCT-17 channel ORA_DISK_1: finished piece 1 at 08-OCT-17 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/backupset/2017_10_08/o1_mf_nnndf_TAG20171008T164020_dxn8xwtx_.bkp tag=TAG20171008T164020 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 08-OCT-17 Starting Control File and SPFILE Autobackup at 08-OCT-17 piecehandle=/u01/app/oracle/fast_recovery_area/TESTDB/autobackup/2017_10_08/o1_mf_s_956853622_dxn8xy8f_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 08-OCT-17 Starting Control File and SPFILE Autobackup at 08-OCT-17 piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/autobackup/2017_10_08/o1_mf_s_956853622_dxn8xy8f_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 08-OCT-17
Hence the backup of the TBS is done successfully
Now check the block details as shown below:
SQL> SELECT header_block FROM dba_segments WHERE segment_name='EMP'; HEADER_BLOCK ------------ 130
As you could see the segment(table) is block 130,If the extended block like 131,132 etc are get corrupted then you could see the below error in the datafile with the corrupted block.
The below command is to manual corrupt the oracle block
[oracle@testdb ~]$ dd of=/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf bs=8192 conv=notrunc seek=131 << EOF > testing corruption > EOF 0+1 records in 0+1 records out 19 bytes (19 B) copied, 0.000133601 s, 142 kB/s
SQL> ater system flush buffer_cache; System altered.
SQL> connect corrupt_block_test/corrupt_block_test; Connected
Check the table which is corrupted as shown below
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 6, block # 131) ORA-01110: data file 6: '/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf' The query returns complains of block corruption in file 6, and the block numbered 131 is being reported as corrupt.
Now let’s identify the corrupted blocks by using dbv (DBVERIFY) utility and recover them by using the RMAN Backup
[oracle@testdb ~]$ dbv file=/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf blocksize=8192 DBVERIFY: Release 11.2.0.4.0 - Production on Sun Oct 8 16:47:56 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/testdb/corrupt_block_test.dbf Page 131 is marked corrupt
Corrupt block relative dba: 0x01800083 (file 6, block 131) — (This is corrupted block)
Bad header found during dbv:
Data in bad block:
type: 116 format: 5 rdba: 0x20676e69
last change scn: 0x7075.72726f63 seq: 0x74 flg: 0x69
spare1: 0x73 spare2: 0x74 spare3: 0xa
consistency value in tail: 0x97990601
check value in block header: 0x6e6f
block checksum disabled
DBVERIFY – Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 19
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 131
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1129
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 943560601 (53.943560601)
Start the recover process by using RMAN & Recover the corrupted blocks in database
The RMAN will recovers only corrupted blocks instead of the complete datafile
RMAN> blockrecover datafile 6 block 131; Starting recover at 08-OCT-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=475 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00006 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TESTDB/backupset/2017_10_08/o1_mf_nnndf_TAG20171008T164020_dxn8xwtx_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TESTDB/backupset/2017_10_08/o1_mf_nnndf_TAG20171008T164020_dxn8xwtx_.bkp tag=TAG20171008T164020 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:0 Finished recover at 08-OCT-17
Let us verify the table along with data init
[oracle@testdb ~]$ !sq
SQL> connect corrupt_block_test/corrupt_block_test; Connected
SQL> select * from emp; ENO ———- 1 - 10000 10000 rows selected.
SQL> select count(*) from emp; COUNT(*) ---------- 10000
Table is recovered successfully