In this Article, you will be
learning how to recover a corrupted block/blocks using RMAN.
Steps are involved here are
:
1.I am going to create a
table in "USERS" tablespace .
2.Just Identify the
blocks belonging to that newly created table("mytab").
3.Corrupt all or some of
those blocks using the Unix dd command.
4.Flush the buffer cache to
ensure we read blocks from disk and not from memory(buffer cache).
5.Verify block corruptions from
V$DATABASE_BLOCK_CORRUPTION
Let's Create a Table in "USERS" tablespace. =========================================================
SQL> create table mytab
tablespace users as select * from tab; Table created. SQL> select count(*)
from mytab; COUNT(*)
----------
4741
Just Identify the blocks belonging to that newly created table("mytab"). ============================================================
SQL> select * from (select
distinct dbms_rowid.rowid_block_number(rowid)from myt ab)where rownum < 6;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
2123 2124 2125 2126 2127
Now check if any blocks got corrupted before we make it corrupt.(Intentionally we are going to corrupt one block here ) ===================================================================
SQL> select * from
v$database_block_corruption;
no rows selected There no
blocks corrupted as of now. now we will corrupt one block. ===========================================================
[oracle@machine1 ~]$ dd
of=/u01/app/oracle/oradata/TEST/users01.dbf bs=8192 seek=2127 conv=notrunc
count=1 if=/dev/zero 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied,
0.0107044 s, 765 kB/s [oracle@machine1 ~]$
Flush the buffer cache to ensure we read blocks from disk and not from memory(buffer cache).
=========================================================
SQL> alter system flush
buffer_cache; System altered.
SQL> select * from mytab;
TNAME TABTYPE CLUSTERID
------------------------------
------- ----------
ACCESS$ TABLE ALERT_QT
TABLE ALL$OLAP2_AWS VIEW ALL_ALL_TABLES VIEW ALL_APPLY VIEW
ALL_APPLY_CHANGE_HANDLERS VIEW ALL_APPLY_CONFLICT_COLUMNS VIEW
ALL_APPLY_DML_HANDLERS VIEW ALL_APPLY_ENQUEUE VIEW ALL_APPLY_ERROR VIEW
ALL_APPLY_EXECUTE VIEW ALL_APPLY_KEY_COLUMNS VIEW ALL_APPLY_PARAMETERS VIEW
ALL_APPLY_PROGRESS VIEW ALL_APPLY_TABLE_COLUMNS VIEW ALL_ARGUMENTS VIEW
ALL_ASSEMBLIES VIEW . . . so on...
At the end of the line we will
see error like =========================================================
ERROR: ORA-01578: ORACLE data block corrupted (file # 4, block # 2127) ORA-01110: data file 4: '/u01/app/oracle/oradata/TEST/users01.dbf' 930 rows selected .
Now we are seeing one block id #2127 got corrupted
===========================================================
SQL> select * from
v$database_block_corruption;
FILE# BLOCK# BLOCKS
CORRUPTION_CHANGE# CORRUPTIO
---------- ----------
---------- ------------------ ---------
4 2127 1 0 ALL ZERO
SQL>
Recover the corrupted blocks
using the rman : ========================================================
[oracle@machine1 ~]$ export
ORACLE_SID=TEST
[oracle@machine1 ~]$ rman
target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 19 17:13:33 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target
database: TEST (DBID=2204857542)
RMAN> recover datafile 4 block 2127;
Starting recover at 19-MAR-2016 17:13:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=46 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=49 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 00004 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T002440_cgrmqp9m_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T002440_cgrmqp9m_.bkp tag=TAG20160319T002440 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 archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_5_cgrmvn31_.arc archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_6_cgt8jbjz_.arc archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_7_cgtbg5x2_.arc archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2016_03_19/o1_mf_1_8_cgtbj5dn_.arc media recovery complete, elapsed time: 00:00:08 Finished recover at 19-MAR-2016 17:14:03
RMAN> SQL> select * from v$database_block_corruption;
no rows selected
SQL>
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment