Fix Oracle 11g Database Block Corruption? - Recovery Using RMAN Backup ORA-01578: ORACLE data block corrupted


Error Description:
Oracle database aler.log file shows following error. RMAN logs also reported the same error.
ORA-01578: ORACLE data block corrupted (file # 6, block # 881945)
ORA-01110: data file 6: '/u01/app/oracle/data/DEVQA/datafile/mgmt.dbf'


Solution Description:
Step 1:
          Run the dbverify utility (dbv) and mark the corrupted block
$ dbv file=/u01/app/oracle/data/DEVQA/datafile/mgmt.dbf FEEDBACK=10000 > dbfilechk.log

DBVERIFY: Release 11.2.0.2.0 - Production on Mon Nov 5 01:18:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
................................................................................
................................................................................
..................................................
DBV-00200: Block, DBA 25936658, already marked corrupt
csc(0x0000.2e078166) higher than block scn(0x0000.00000000)
Page 770834 failed with check code 6054
..............................
...................................................

DBVERIFY - Verification complete

Total Pages Examined         : 3814400
Total Pages Processed (Data) : 174350
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 3338151
Total Pages Failing   (Index): 1
Total Pages Processed (Other): 72996
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 228903
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 780990065 (0.780990065)

Step 2:
Validate the same using RMAN & SQLPLUS utilities. Three methods – validate datafile or validate database or validate blockwise.
Method 1: Validate datafile
$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Nov 5 01:56:27 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEVQA (DBID=1264024642)

RMAN> backup validate check logical datafile 6;

Starting backup at 05-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1280 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:39:26
List of Datafiles
=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     1              228903       3814400         781090580
  File Name: /u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              174350
  Index      0              3338166
  Other      0              72981

Finished backup at 05-NOV-12
Method 2: Validate datafile block
RMAN> VALIDATE DATAFILE 6 BLOCK 881945;

Starting validate at 05-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=518 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:01:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     1              0            1               0
  File Name: /u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              1

Finished validate at 05-NOV-12
Method 3: Validate database level

RMAN> backup validate check logical database;

Starting backup at 05-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=778 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/data/DEVQA/datafile/o1_mf_undotbs1_6o40zq5z_.dbf
input datafile file number=00006 name=/u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
input datafile file number=00007 name=/u01/app/oracle/data/DEVQA/datafile/mgmt_ad4j.dbf
input datafile file number=00001 name=/u01/app/oracle/data/DEVQA/datafile/o1_mf_system_6o40zq17_.dbf
input datafile file number=00002 name=/u01/app/oracle/data/DEVQA/datafile/o1_mf_sysaux_6o40zq38_.dbf
input datafile file number=00004 name=/u01/app/oracle/data/DEVQA/datafile/o1_mf_users_6o40zq7l_.dbf
input datafile file number=00005 name=/u01/app/oracle/data/DEVQA/datafile/mgmt_ecm_depot1.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 01:11:07
List of Datafiles
=================
.......................................................................
.......................................................................

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     1              228903       3814400         781122267
  File Name: /u01/app/oracle/data/DEVQA/datafile/mgmt.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              174366
  Index      0              3338155
  Other      0              72976

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              2804
Finished backup at 05-NOV-12
Method 4: Using SQLPLUS
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6     770834          1                  0 FRACTURED

Step 3:
Verify your RMAN backups before recovery.
RMAN> LIST BACKUP OF DATAFILE 6 SUMMARY;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
11067968 B  0  A DISK        19-OCT-12       1       1       YES        TAG20121019T010037
11075749 B  0  A DISK        26-OCT-12       1       1       YES        TAG20121026T010322

Step 4:

Start datafile block recovery using RMAN
RMAN> blockrecover datafile 6 block 881945;

Starting recover at 05-NOV-12
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 00006
channel ORA_DISK_1: reading from backup piece /gmount/DEVQAdbbackup/irnonhlj
channel ORA_DISK_1: piece handle=/gmount/DEVQAdbbackup/irnonhlj_1_1 tag=TAG20121026T010322
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:31:36

starting media recovery

archived log for thread 1 with sequence 6451 is already on disk as file /gmount/DEVQAarchive/DEVQA/DEVQA/archivelog/2012_10_28/o1_mf_51_88vhzw7s_.arc

................................................................................
................................................................................
channel ORA_DISK_1: restore complete
media recovery complete
Finished blockrecover at 05-NOV-12
released channel: ORA_DISK_1

Recovery Manager complete.

Step 5:
Validate the datafile using RMAN as mentioned in step2.

Step 6:
Verify the block has been recovered properly or not using SQLPLUS
SQL> select * from v$database_block_corruption;

no rows selected
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google