We’ll corrupt a block on a datafile and try to restore it via RMAN. First let’s create a sample tablespace:
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:
1 2 3 |
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):
1 2 3 4 5 |
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blockno FROM SAMPLE_TABLE; BLOCKNO ---------- 132 |
Before we corrupt the datablock, we’ll take a full backup.
1 |
RMAN> BACKUP DATABASE; |
After the backup completed, we’ll fill this block with zeros at the OS level.
I know thar BLOCK_SIZE of my database is 8K but let’s check it to be sure:
1 2 3 4 5 |
SELECT BLOCK_SIZE FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'SAMPLETBS'; BLOCK_SIZE ---------- 8192 |
Now let’s corrupt the block:
1 2 3 4 5 6 |
dd if=/dev/zero of=/oracle/oradata/test/sample01.dbf bs=8192 seek=132 conv=notrunc count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.00028325 seconds, 28.9 MB/s |
Now we’ll query the table after flushing buffer cache:
1 2 3 4 5 6 7 |
ALTER SYSTEM FLUSH BUFFER_CACHE; SELECT * FROM SAMPLE_TABLE; * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 8, block # 132) ORA-01110: data file 8: '/oracle/oradata/test/sample01.dbf' |
Ok now let’s fix it! First we need to list corrupted blocks:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 132; Starting recover at 25-JAN-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=33 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00008 channel ORA_DISK_1: reading from backup piece /fra/o1.bkp channel ORA_DISK_1: piece handle=/fra/o1.bkp tag=TAG20110125T210749 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 25-JAN-11 |
Let’s test it:
1 2 3 4 5 |
SELECT * FROM SAMPLE_TABLE; A --------- 1 |
Ganesh