This discussion is archived
10 Replies Latest reply: Nov 20, 2012 12:25 AM by kamilp RSS

rman backup with corrupted block

kamilp Newbie
Currently Being Moderated
Hello,

Firstly - I have problem on non-production database 11.2.0.1.0, so I am not deeply worried about data. But I need to understand what happened with database backups and how to prevent such things in future.
So - I have EM scheduled weekly full backup and daily incremental backups. Later there was problem with hardware and some corrupted blocks in database were found. The weekly backup ran without error and obsolete backups were deleted. Now it is not possible to "recover corruption list" because no backup without corruption exists (RMAN-06023: no backup or copy of datafile 6 found to restore). I am not worried about the lost data, but I need to find out how come the backup contains corrupted block.

I have checked the data file using dbv utility
DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/orcl/users03.dbf

DBV-00200: Block, DBA 27525766, already marked corrupt
csc(0x0001.7b01729f) higher than block scn(0x0000.00000000)
Page 2359942 failed with check code 6054


DBVERIFY - Verification complete

Total Pages Examined         : 3840000
Total Pages Processed (Data) : 453896
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 2959104
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 424025
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2975
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2156227446 (1.2156227446)
As you can see the datafile 6 - user03.dbf has errors. Also backups now contain errors.

1) So how is it possible that the EM scheduled backup ran without problems and the backup now contains corrupted blocks. How to prevent this in future ? I know there is setting MAXCORRUPT. How can I check its current value ? How can I configure it using EM scheduled backups ?

2) Secondly, meanwhile I studied the RMAN commands. So I have suspended EM backup jobs, and executed follwing command. And backup ran without error again. How is this possible, if data file users06.dbf has corrupted block ?


Thanks !
RMAN> run {
 set MAXCORRUPT for datafile 6 to 0;
 backup as compressed backupset datafile 6;
 }
2> 3> 4>
executing command: SET MAX CORRUPT

Starting backup at 07-NOV-12
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/oracle/oradata/orcl/users03.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-12
channel ORA_DISK_1: finished piece 1 at 07-NOV-12
piece handle=/opt/oraBackup/rman/nrnpo0sg_1_1 tag=TAG20121107T200120 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 01:13:05
Finished backup at 07-NOV-12

Starting Control File and SPFILE Autobackup at 07-NOV-12
piece handle=/opt/oraBackup/rman/c-1253245572-20121107-03 comment=NONE
Finished Control File and SPFILE Autobackup at 07-NOV-12
  • 1. Re: rman backup with corrupted block
    mseberg Guru
    Currently Being Moderated
    Hello;

    Have you checked which object is corrupted ? ( if any )
    Select * from v$database_block_corruption;
    Use RMAN Blockrecover with no catalog to recover if you find something.
    RMAN> BLOCKRECOVER DATAFILE 6 BLOCK ??;
    Best Regards

    mseberg
  • 2. Re: rman backup with corrupted block
    kamilp Newbie
    Currently Being Moderated
    SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
    
         FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
             6    2359942          1                  0 FRACTURED
            25    1855622          1                  0 FRACTURED
    
    SQL> select * from v$backup_corruption;
    
         RECID      STAMP  SET_STAMP  SET_COUNT     PIECE#      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------ --- ---------
             1  798730797  798726377       9971          1          6    2359942          1                  0 NO  CORRUPT
             2  798758063  798753680       9979          1          6    2359942          1                  0 NO  CORRUPT
  • 3. Re: rman backup with corrupted block
    mseberg Guru
    Currently Being Moderated
    Sorry for delay

    Please run this
    select segment_type,owner||'.'||segment_name
    from dba_extents
    where file_id = 6 and 2359942 between block_id and block_id+blocks -1;
    and this and post results
    select segment_type,owner||'.'||segment_name
    from dba_extents
    where file_id = 25 and 1855622 between block_id and block_id+blocks -1;
    Best Regards

    mseberg
  • 4. Re: rman backup with corrupted block
    kamilp Newbie
    Currently Being Moderated
    Hi,
    here is the result. I know how to find which objects are affected, however this is not interresting me. My main interrest is why the backup ran without error ? And how to prevent it. When datafile has corrupted blocks the backup must give error.
    SQL> select segment_type,owner||'.'||segment_name
    from dba_extents
    where file_id = 6 and 2359942 between block_id and block_id+blocks -1;  2    3
    
    SEGMENT_TYPE       OWNER||'.'||SEGMENT_NAME
    ------------------ ----------------------------------------------------------------------------------------------------------------
    TABLE              IMS_388NEW.MESSAGES2
    
    SQL> select segment_type,owner||'.'||segment_name
    from dba_extents
    where file_id = 25 and 1855622 between block_id and block_id+blocks -1;
    
    
      2    3
    
    SEGMENT_TYPE       OWNER||'.'||SEGMENT_NAME
    ------------------ ----------------------------------------------------------------------------------------------------------------
    LOBSEGMENT         IMS_388NEW.SYS_LOB0000511180C00003$$
  • 5. Re: rman backup with corrupted block
    mseberg Guru
    Currently Being Moderated
    Hello;

    "When datafile has corrupted blocks the backup must give error"

    I agree, it should. But when RMAN finds corrupt block it does two things :

    It writes an error to the alert.log file.
    
    It populates the V$DATABASE_BLOCK_CORRUPTION view.
    That's it. Maybe the designers did not want a large backup to fail because of one block. But I don't know. If its in the alert.log and V$DATABASE_BLOCK_CORRUPTION then its working as expected.

    *Bug 9203076 - RMAN does not stop for certain corruptions [ID 9203076.8]*

    Best Regards

    mseberg
  • 6. Re: rman backup with corrupted block
    341279 Newbie
    Currently Being Moderated
    Hi,
    can you check MOS for Bug 9203076 : RMAN DOES NOT DETECT THE CORRUPTION ?

    Regards.
    R.Vilcoq
  • 7. Re: rman backup with corrupted block
    kamilp Newbie
    Currently Being Moderated
    That's it. Maybe the designers did not want a large backup to fail because of one block. But I don't know. If its in the alert.log and V$DATABASE_BLOCK_CORRUPTION then its working as expected.
    When I tested on another non-production server 11.2.0.3.0 and I manually corrupted 1 block in users01.dbf file, the rman backup failed with error.
    If it does not fail, then there is problem with DELETE OBSOLETE command - it will delete previous backups that could be possibly used for corrupted block recovery - this actually happened to me.
  • 8. Re: rman backup with corrupted block
    kamilp Newbie
    Currently Being Moderated
    Romain VILCOQ wrote:
    Hi,
    can you check MOS for Bug 9203076 : RMAN DOES NOT DETECT THE CORRUPTION ?

    Regards.
    R.Vilcoq
    Behavior, is as the bug describes. Corruption is reported in v$backup_corruption also by dbv. But not in alert.log and RMAN does not stop backup.
    I have set "_undo_block_compression"=FALSE but it does not help and backup stil completes sucessfully.
    I will try to update database to 11.2.0.3.
    SQL> ALTER SYSTEM SET "_undo_block_compression"=FALSE scope=both;
    
    System altered.
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    oracle@orcl-cluster:~> rman target /
    
    Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 8 12:18:48 2012
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: ORCL (DBID=1253245572)
    
    RMAN> run {
     set MAXCORRUPT for datafile 6 to 0;
     backup as compressed backupset datafile 6;
     }2> 3> 4> ;
    
    executing command: SET MAX CORRUPT
    using target database control file instead of recovery catalog
    
    Starting backup at 08-NOV-12
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=421 device type=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: SID=1997 device type=DISK
    allocated channel: ORA_DISK_3
    channel ORA_DISK_3: SID=102 device type=DISK
    allocated channel: ORA_DISK_4
    channel ORA_DISK_4: SID=468 device type=DISK
    allocated channel: ORA_DISK_5
    channel ORA_DISK_5: SID=1176 device type=DISK
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00006 name=/opt/oracle/oradata/orcl/users03.dbf
    channel ORA_DISK_1: starting piece 1 at 08-NOV-12
    
    channel ORA_DISK_1: finished piece 1 at 08-NOV-12
    piece handle=/opt/oraBackup/rman/osnppq8n_1_1 tag=TAG20121108T122039 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 01:13:15
    Finished backup at 08-NOV-12
    
    Starting Control File and SPFILE Autobackup at 08-NOV-12
    piece handle=/opt/oraBackup/rman/c-1253245572-20121108-01 comment=NONE
    Finished Control File and SPFILE Autobackup at 08-NOV-12
    Edited by: kamilp on Nov 8, 2012 4:41 AM

    Edited by: kamilp on Nov 8, 2012 4:42 AM
  • 9. Re: rman backup with corrupted block
    341279 Newbie
    Currently Being Moderated
    OK, sounds good, do not hesitate to open a TAR because bugs are not all public...

    Keep us informed.

    bye.
  • 10. Re: rman backup with corrupted block
    kamilp Newbie
    Currently Being Moderated
    I have updated database to 11.2.0.3
    However, the problem still persists. rman backup went ok on broken file
    oracle@orcl-cluster:~> sqlplus
    
    SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 20 09:24:11 2012
    
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
    Enter user-name: system
    Enter password:
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
    
         FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
             6    2359942          1                  0 FRACTURED
            25    1855622          1                  0 FRACTURED
    oracle@orcl-cluster:~> rman target /
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Tue Nov 20 08:04:57 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: ORCL (DBID=1253245572)
    
    RMAN> backup as compressed backupset datafile 6;
    
    Starting backup at 20-NOV-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1596 device type=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: SID=1568 device type=DISK
    allocated channel: ORA_DISK_3
    channel ORA_DISK_3: SID=2357 device type=DISK
    allocated channel: ORA_DISK_4
    channel ORA_DISK_4: SID=2341 device type=DISK
    allocated channel: ORA_DISK_5
    channel ORA_DISK_5: SID=86 device type=DISK
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00006 name=/opt/oracle/oradata/orcl/users03.dbf
    channel ORA_DISK_1: starting piece 1 at 20-NOV-12
    channel ORA_DISK_1: finished piece 1 at 20-NOV-12
    piece handle=/opt/oraBackup/rman/2rnqovpp_1_1 tag=TAG20121120T080513 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 01:10:35
    Finished backup at 20-NOV-12
    
    Starting Control File and SPFILE Autobackup at 20-NOV-12
    piece handle=/opt/oraBackup/rman/c-1253245572-20121120-00 comment=NONE
    Finished Control File and SPFILE Autobackup at 20-NOV-12
    
    RMAN> backup validate datafile 6;
    
    Starting backup at 20-NOV-12
    using channel ORA_DISK_1
    using channel ORA_DISK_2
    using channel ORA_DISK_3
    using channel ORA_DISK_4
    using channel ORA_DISK_5
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00006 name=/opt/oracle/oradata/orcl/users03.dbf
    channel ORA_DISK_1: backup set complete, elapsed time: 00:03:05
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    6    OK     1              2975         3840000         6489027926
      File Name: /opt/oracle/oradata/orcl/users03.dbf
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              453912
      Index      0              2959091
      Other      0              424022
    
    Finished backup at 20-NOV-12
    Edited by: kamilp on Nov 20, 2012 12:23 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points