Performing Block Recovery without having RMAN backup

Article by author Kamran Agayev Agamehdi

Kamran Agayev Agamehdi  is an Oracle Certified Professional DBA (9i, 10g) with over 5 years experience with UNIX Systems and with Oracle Databases and author of Oracle Backup & Recovery by Rampant Tech Press.

• 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 (Make sure this is not done 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

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>