Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for March 10th, 2010

Performing Block Recovery without having RMAN backup

Posted by Kamran Agayev A. on 10th March 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]

Posted in RMAN Backup and Recovery | 12 Comments »