block media recovery – Gokhan Atil's Blog

Performing Block Media Recovery with RMAN

We’ll corrupt a block on a datafile and try to restore it via RMAN. First let’s create a sample tablespace:

CREATE TABLESPACE SAMPLETBS DATAFILE '/oracle/oradata/test/sample01.dbf' 
SIZE 10M AUTOEXTEND OFF;

1 2	CREATE TABLESPACE SAMPLETBS DATAFILE '/oracle/oradata/test/sample01.dbf' SIZE 10M AUTOEXTEND OFF;

Then let’s create a table in this tablespace and insert a sample row:

CREATE TABLE SAMPLE_TABLE ( A NUMBER ) TABLESPACE SAMPLETBS;
INSERT INTO SAMPLE_TABLE VALUES ( 1 );
COMMIT;

CREATE TABLE SAMPLE_TABLE ( A NUMBER ) TABLESPACE SAMPLETBS;

INSERT INTO SAMPLE_TABLE VALUES ( 1 );

COMMIT;

Now we need to find the block number of this record (so we can corrupt it):

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blockno FROM SAMPLE_TABLE;

BLOCKNO          
----------
132

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blockno FROM SAMPLE_TABLE;

BLOCKNO

----------

132

Before we corrupt the datablock, we’ll take a full backup.

RMAN> BACKUP DATABASE;

1	RMAN> BACKUP DATABASE;

After the backup completed, we’ll fill this block with zeros at the OS level.