10 Replies Latest reply: Nov 20, 2012 2:25 AM by kamilp RSS

    rman backup with corrupted block

    kamilp
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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