Wednesday, September 7, 2011

Block recovery by RMAN an example ...

Block recovery by RMAN an example ...


SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 24165)
ORA-01110: data file 4: 'E:\ORACLE\ORADATA\TENG\USERS01.DBF'

Recover the block

We now run a validate database command which
- Checks datafiles for physical and logical corruption
- Confirms that all database files exist and are in the correct locations


RMAN> run {BACKUP VALIDATE DATABASE;}

Starting backup at 19-FEB-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=91 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=E:\ORACLE\ORADATA\TENG\USERS01.DBF
input datafile fno=00001 name=E:\ORACLE\ORADATA\TENG\SYSTEM01.DBF
input datafile fno=00002 name=E:\ORACLE\ORADATA\TENG\UNDOTBS01.DBF
input datafile fno=00003 name=E:\ORACLE\ORADATA\TENG\SYSAUX01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:11
Finished backup at 19-FEB-05


SQL> select * from V$backup_corruption;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MAR CO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------
1 550688483 550688389 46 1 4 24165 1 0 YES CORRUPT

The above output confirms that block 24165 in file 4 is indeed corrupt!
We can recover the same using the following command.

RMAN> run {blockrecover datafile 4 block 24165;}

Starting blockrecover at 19-FEB-05
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=E:\ORACLE\FLASH_RECOVERY_AREA\TENG\BACKUPSET\2005_02_19\O1_MF_NNNDF_TAG20050219T164615_11FNO9BQ_.BKP tag=TAG20050219T164615
channel ORA_DISK_1: block restore complete

starting media recovery
media recovery complete

Finished blockrecover at 19-FEB-05

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

No comments:

Post a Comment