Manually corrupting the data block in Linux and recovering it using BLOCKRECOVER command of RMAN
Posted by Kamran Agayev A. on March 1st, 2010
Sometimes, in order to test the RMAN’s BLOCKRECOVER command, we need to corrupt the specific data block and recover it for testing purpose. To do it in Linux, use dd command. In the following example, let’s create a table and corrupt it manually (Don’t try it on the production database 
or you’ll be retired from the job )
SQL> CREATE TABLE corruption_test (id NUMBER);
Table created.
SQL> INSERT INTO corruption_test VALUES(1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM corruption_test;
ID
———-
1
SQL> SELECT header_block FROM dba_segments WHERE segment_name=’CORRUPTION_TEST’;
HEADER_BLOCK
————
67
[oracle@localhost ~]$ dd of=/u02/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=68 << EOF
> testing corruption
> EOF
0+1 records in
0+1 records out
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> SELECT * FROM corruption_test;
select * from corruption_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 68)
ORA-01110: data file 4: ‘/u02/oradata/orcl/users01.dbf’
SQL>
Now connect to the RMAN and recover the data block (not the whole datafile or database) using BLOCKRECOVER command as follows:
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 68;
Starting blockrecover at 01-MAR-10
<… output trimmed … >
<… output trimmed … >
Finished blockrecover at 01-MAR-10
RMAN> exit
Connect to SQL*Plus and query the table:
SQL> SELECT * FROM corruption_test;
ID
———-
1
SQL>
March 1st, 2010 at 8:35 pm
What to do if your database uses ASM storage?
March 2nd, 2010 at 7:23 pm
Haven’t tested Mark
March 2nd, 2010 at 12:16 pm
Kamran thanks!!!
Perfect example, no comment.
March 2nd, 2010 at 7:22 pm
You’re welcome Ulfet
March 30th, 2010 at 5:56 pm
Hi Kamran,
How are you?
I would like to know some good material to learn for the beginners in Oracle DBA.
I am a beginner in this field having trying to gain knowledge on oracle dba.
Recently i have completed my oracle dba 11g course from one of a institute and try to do lot of practice on it.
i wanted to know how actual the dba task is in live project in real time.
If you let me know some good material or any good exercise by which i can go step by step to improve in my dba field.
Please let me know.
Best regards,
Aijaz Khan
March 30th, 2010 at 5:58 pm
2.Aijaz Khan said
March 28, 2010 at 6:39 pm
Here i would like to ask you somethg on undo tablespace.
SYS>> Sho parameter undo
here what i want to know is how i will receive an error ora 01555 in this example i tried to do it many times but i am not receiving any error. I am working on oracle 11g on redhat linux 4 on VMware.
Which parameter i should mention first on paramter file i.e. init.ora for testing undo tablespace.
undo_management=auto
undo_tablespace=undotbs
SYS>> sho Parameter undo
sys>> select tablespace_name, contents, retention from dba_tablespace;
by default undo tablespace is Noguarantee.
Here i am creating one small undo tablespace by name Undo1
sys>> create undo tablespace undo1 datafile ‘/disk2/oradata/suman/undo1.dbf’ size 4m;
sys>> sho parameter undo
by default it is undotbs
Now i want to active the recent undo1 tablespace from undotbs to undo1
sys>> alter system set undo_tablespace=undo1;
sys>> sho parameter undo
now the active undo tablespace is undo1
now i am connecting to a user by name user1
user1>>
here i am creating some sample tables.
user1>> @$ORACLE_HOME/Sqlplus/demo/demobld
user1>> select * from emp;
14 records;
user1>> insert into emp select * from emp;
now i create here a loop in order to update this statement continuously in order to fill the undo1 tablespace.
begin
loop
update emp set sal=2000 where empno=7566;
commit;
end loop;
end;
now here to the another session and try to retrieve the data.
sys>> select * from u1.emp;
if somebody wants to do the transaction since the session is very big and overwritten the earlier one then it raises an error ORA-01555.
i wanted to know how to do it step by step i am doing the same steps but not receiving the exact error can you plz help me out in this situation how to face this problem in real time experience.
Best regards,
Aijaz Khan
March 31st, 2010 at 8:58 am
Hi Kamran,
Good Day!
I am trying this activity at home. However, I am stuck in this command because I am trying to do this in wondows 7. Can you please give the equivalent command of this for windows platform?
[oracle@localhost admin]$ dd of=/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf bs=8192 conv=notrunc seek=60 < corruption
> EOF
0+1 records in
0+1 records out
Greatly appreciate it.
Thanks!
Best Regards,
Randyl V. Pulma
March 31st, 2010 at 9:37 am
Dear Randyl
For this, you need to use any hexadecimal editor. I’ve shown it on the following video tutorial on “Corrupting data block on Windows OS”
http://kamranagayev.wordpress.com/2010/03/18/rman-video-tutorial-series-performing-block-media-recovery-with-rman/
April 5th, 2010 at 8:24 am
Hi Kamran,
Thanks a lot! This is a great help for me as an aspiring DBA.
Randyl
July 8th, 2017 at 5:48 pm
Hi Kamran!
It was helpfull for me.. Thanks u a lot
September 12th, 2017 at 6:14 am
Thanks for the feedback Ceyhun. Glad it helped!