Performing Block Recovery without having RMAN backup
Posted by Kamran Agayev A. on March 10th, 2010
It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups. Look at the following demonstration. Here I:
– Create a new user and a table in that schema
– Take OS backup (hot backup) of the users01.dbf where the table resides
– Corrupt the data in that table and get block corruption error (Don’t wish you to get such errors in your production database! 
)
– Connect with RMAN and try to use BLOCKRECOVER command. As we haven’t any backup, we get an error
– Catalog the “hot backup” to the RMAN repository
– Use BLOCKRECOVER command and recover the corrupted data block using cataloged “hot backup” of the datafile
– Query the table and get the data back!
Here is the scenario
[sourcecode language=”css”]
SQL> CREATE USER usr IDENTIFIED BY usr;
User created.
SQL> GRANT DBA TO usr;
Grant succeeded.
SQL> CONN usr/usr
Connected.
SQL> CREATE TABLE tbl_corrupt_test (id NUMBER);
Table created.
SQL> INSERT INTO tbl_corrupt_test VALUES(1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> COLUMN segment_name FORMAT a45
SQL> SELECT segment_name, tablespace_name from dba_segments WHERE segment_name=’TBL_CORRUPT_TEST’;
SEGMENT_NAME TABLESPACE_NAME
——————————————— ——————————
TBL_CORRUPT_TEST USERS
SQL> COLUMN segment_name FORMAT a15
SQL> COLUMN tablespace_name FORMAT a15
SQL> COLUMN name FORMAT a45
SQL> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b
WHERE a.header_file=b.file# AND a.segment_name=’TBL_CORRUPT_TEST’;
SEGMENT_NAME TABLESPACE_NAME NAME
————— ————— ———————————–
TBL_CORRUPT_TEST USERS /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf
SQL> ALTER TABLESPACE users BEGIN BACKUP;
Tablespace altered.
SQL> host cp /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf
SQL> ALTER TABLESPACE users END BACKUP;
Tablespace altered.
SQL> SELECT header_block FROM dba_segments WHERE segment_name=’TBL_CORRUPT_TEST’;
HEADER_BLOCK
————
59
[oracle@localhost admin]$ dd of=/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf bs=8192 conv=notrunc seek=60 <<EOF
> corruption
> EOF
0+1 records in
0+1 records out
[oracle@localhost admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Mar 9 03:35:32 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONN usr/usr
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> SELECT * FROM tbl_corrupt_test;
SELECT * FROM tbl_corrupt_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 60)
ORA-01110: data file 4:
‘/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01.dbf’
SQL> EXIT
[oracle@localhost admin]$ rman target sys
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Mar 9 03:35:58 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
target database Password:
connected to target database: NEWDB (DBID=2953562798)
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 60;
Starting blockrecover at 09-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 03/09/2010 03:36:13
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN> CATALOG DATAFILECOPY ‘/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf’;
cataloged datafile copy
datafile copy filename=/u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf recid=1 stamp=713158624
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 60;
Starting blockrecover at 09-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /u01/oracle/product/10.2.0/db_1/oradata/newdb/users01_backup.dbf
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 09-MAR-10
RMAN> EXIT
Recovery Manager complete.
[oracle@localhost admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Mar 9 03:37:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONN usr/usr
Connected.
SQL> SELECT * FROM tbl_corrupt_test;
ID
———-
1
SQL>
[/sourcecode]
March 27th, 2010 at 12:53 pm
kamran from where i can purchase your book titled “Expert secrets for using RMAN and Data Pump” can you give me the link or is there any possibility to buy the book in india
May 25th, 2010 at 2:31 pm
Still this book not released, I hope It will release as soon as , If you’re from chennai, india, I think so the rampant publications book selling by Kannan publications, tnagar, chennai and Landmark ,spencer Chennai. You can get this book from this shop after release this book
August 16th, 2010 at 2:24 am
Thanks Kamran for this useful blog post.
I didn’t knew this
Regards,
Marko
September 12th, 2010 at 10:08 pm
Thank you so much for your precious information about RMAN Recovery Manager.
November 3rd, 2010 at 12:09 am
Kamran,
Please tell us the tentative date for the release of your book
-Kamesh
November 3rd, 2010 at 9:43 am
Dear Kamesh
It should be released in 3-4 months
March 14th, 2012 at 4:32 pm
Sir,
1. If i lost all controlfile and i have no backup of binary and trace and more the one database are running on the same server.so how
can start my database ?
April 21st, 2013 at 6:32 am
Hi,
This is really good one. I need to know if there are no any backup then how could i recover it.
Thanks
March 24th, 2014 at 12:19 pm
Hi Kamran,
i don’t have any backup of the database there is a error in the log that block is corrupted pls help me how to resolve this.
SQL> !rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Fri Mar 21 19:44:20 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: NEERAJ (DBID=321306765)
RMAN> blockrecover datafile 4 block 523;
Starting recover at 21-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/21/2014 19:44:46
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
June 13th, 2014 at 4:16 am
Hi Neeraj
You don’t have backup of the file number 4, so you’re not able to recover the file. In order to perform a block recovery, you should have backup of that block
September 6th, 2017 at 4:13 pm
We are getting below; how to fix this error
Oracle 10.2.0.4 on HPUNIX
RMAN> BLOCKRECOVER DATAFILE 432 BLOCK 152875;
Starting blockrecover at 06-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /var/opt/dwhproddb006/oradata/DWHPROD/dbf/system_04_backup.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 09/06/2017 18:10:37
ORA-19587: error occurred reading 16384 bytes at block number 0
ORA-27091: unable to queue I/O
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 395393
ORA-19600: input file is datafile copy 0 (/var/opt/dwhproddb006/oradata/DWHPROD/dbf/system_04_backup.dbf)
RMAN>
September 12th, 2017 at 6:12 am
I’m wondering if the your backup is VALID. Could you please validate it using RESTORE DATABASE PREVIEW command