Oracle Free Block Corruption – Test Case

Nov 3, 2013 / By Michael Dinh

Tags: , , ,

My very first blog post at Pythian – Trick or Treat?

So there I was, faced with more that 4000 block corruptions as shown from v$database_block_corruption.

The script  corrupt_seg.sql identified the corruption as Free Block Corruption (block not associated with any segments).

So how can free block corruption be removed?

One method is to format the corrupted block following – How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1)

The above mentioned procedure can be cumbersome as it requires creating trigger, dummy table, extents, etc…

Thinking, “There has to be a better way to do this”.  Remembering, “Block Media Recovery (BMR) using blockrecover”

The test case will simulate free block corruption and demonstrate how to recover from free block corruption.

Configuration

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 31 11:36:36 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

LAX:(SYS@db01)> show parameter compatible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3
LAX:(SYS@db01)> select * from v$restore_point;

no rows selected

LAX:(SYS@db01)>

Backup Database Level 0

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 31 11:47:28 2013

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

connected to target database: DB01 (DBID=1452485914)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
737     B  A  A DISK        31-OCT-2013 11:45:02 1       1       YES        AINC0_THU
738     B  0  A DISK        31-OCT-2013 11:45:07 1       1       YES        DINC0_THU
739     B  0  A DISK        31-OCT-2013 11:45:09 1       1       YES        DINC0_THU
740     B  0  A DISK        31-OCT-2013 11:45:26 1       1       YES        DINC0_THU
741     B  0  A DISK        31-OCT-2013 11:46:00 1       1       YES        DINC0_THU
742     B  0  A DISK        31-OCT-2013 11:46:11 1       1       YES        DINC0_THU
743     B  A  A DISK        31-OCT-2013 11:46:18 1       1       YES        AINC0_THU
744     B  F  A DISK        31-OCT-2013 11:46:28 1       1       NO         TAG20131031T114620

RMAN>

Create Tablespace and Index

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 31 11:48:17 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

LAX:(SYS@db01)> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
USER_DATA

LAX:(SYS@db01)> create tablespace corrupt datafile size 16m;

Tablespace created.

LAX:(SYS@db01)> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
USER_DATA
CORRUPT

6 rows selected.

LAX:(SYS@db01)> create index hr.x on hr.departments_old(last_update) tablespace corrupt;

Index created.

LAX:(SYS@db01)> alter system switch logfile;

System altered.

LAX:(SYS@db01)>

Find Blocks to Corrupt

Note: Output formatted to look nicer

LAX:(SYS@db01)> @corrupt.sql

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1051 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1043 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1047 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1045 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=1034 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2308 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2274 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2258 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2292 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2362 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2244 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2390 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2283 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2228 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2304 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2234 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=2238 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=827 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=843 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=835 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=131 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
EOF

21 rows selected.

LAX:(SYS@db01)> exit

Corrupt Blocks

Note: Output formatted to look nicer

[oracle@lax:db01]/home/oracle
$ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=827 << EOF 
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 2.5018e-05 s, 4.5 MB/s
[oracle@lax:db01]/home/oracle
$ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=843 << EOF 
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.000121112 s, 925 kB/s
[oracle@lax:db01]/home/oracle
$ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=835 << EOF 
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 9.0063e-05 s, 1.2 MB/s
[oracle@lax:db01]/home/oracle
$ dd of=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf bs=8192 conv=notrunc seek=131 << EOF 
> CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt
> EOF
0+1 records in
0+1 records out
112 bytes (112 B) copied, 0.000117363 s, 954 kB/s
[oracle@lax:db01]/home/oracle
$

RMAN Validate Tablespace

RMAN> validate tablespace corrupt;

Starting validate at 31-OCT-2013 12:07:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=45 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1914         2048            1928464
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      4              134

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2327.trc for details
Finished validate at 31-OCT-2013 12:07:21

RMAN>

Check for Corruption

There are 4 corrupted blocks, 1 Used by Index, 3 Free

LAX:(SYS@db01)> select * from v$database_block_corruption order by 1,2,3,4
  2  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    5    131      1                  0 CORRUPT
    5    827      1                  0 CORRUPT
    5    835      1                  0 CORRUPT
    5    843      1                  0 CORRUPT

LAX:(SYS@db01)>
LAX:(SYS@db01)> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2         , greatest(e.block_id, c.block#) s_blk#
  3         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
  4         , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blk_corrupt
  6        , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13         , header_block s_blk#
 14         , header_block e_blk#
 15         , 1 blk_corrupt
 16         , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22         , greatest(f.block_id, c.block#) s_blk#
 23         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
 24         , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blk_corrupt
 26         , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  order by file#, s_blk#
 32  ;

OWNER                SEGMENT_TYPE       SEGMENT_NAME              PARTITION_NAME            FILE# S_BLK#    E_DBLK# BLK_CORRUPT DESCRIPTION
-------------------- ------------------ ------------------------- ------------------------- ----- ------ ---------- ----------- --------------
HR                   INDEX              X                                                       5    131        131           1
                                                                                                5    827        827           1 Free Block
                                                                                                5    835        835           1 Free Block
                                                                                                5    843        843           1 Free Block

LAX:(SYS@db01)>

Recreate Corrupted Index

LAX:(SYS@db01)> drop index hr.x;

Index dropped.

LAX:(SYS@db01)> create index hr.x on hr.departments_old(last_update) tablespace corrupt;

Index created.

LAX:(SYS@db01)>

RMAN Validate Tablespace

Let’s use New Feature RMAN 11G : Data Recovery Advisor

RMAN> validate tablespace corrupt;

Starting validate at 31-OCT-2013 12:30:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=37 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1914         2048            1929833
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      3              133

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ora_2681.trc for details
Finished validate at 31-OCT-2013 12:30:47

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected        Summary
---------- -------- --------- -------------------- -------
19301      HIGH     OPEN      31-OCT-2013 12:07:15 Datafile 5: '/media/sf_linux_x64/corrupt.dbf' contains one or more corrupt blocks

RMAN> list failure 19301 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected        Summary
---------- -------- --------- -------------------- -------
19301      HIGH     OPEN      31-OCT-2013 12:07:15 Datafile 5: '/media/sf_linux_x64/corrupt.dbf' contains one or more corrupt blocks
  Impact: Some objects in tablespace   might be unavailable
  List of child failures for parent failure ID 19301
  Failure ID Priority Status    Time Detected        Summary
  ---------- -------- --------- -------------------- -------
  25665      HIGH     OPEN      31-OCT-2013 12:07:18 Block 843 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
    Impact: Object   owned by   might be unavailable
  25659      HIGH     OPEN      31-OCT-2013 12:07:17 Block 835 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
    Impact: Object   owned by   might be unavailable
  25653      HIGH     OPEN      31-OCT-2013 12:07:16 Block 827 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
    Impact: Object   owned by   might be unavailable

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected        Summary
---------- -------- --------- -------------------- -------
19301      HIGH     OPEN      31-OCT-2013 12:07:15 Datafile 5: '/media/sf_linux_x64/corrupt.dbf' contains one or more corrupt blocks
  Impact: Some objects in tablespace   might be unavailable
  List of child failures for parent failure ID 19301
  Failure ID Priority Status    Time Detected        Summary
  ---------- -------- --------- -------------------- -------
  25665      HIGH     OPEN      31-OCT-2013 12:07:18 Block 843 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
    Impact: Object   owned by   might be unavailable
  25659      HIGH     OPEN      31-OCT-2013 12:07:17 Block 835 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
    Impact: Object   owned by   might be unavailable
  25653      HIGH     OPEN      31-OCT-2013 12:07:16 Block 827 in datafile 5: '/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf' is media corrupt
    Impact: Object   owned by   might be unavailable

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
using channel ORA_DISK_2
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/lax_db01/db01/hm/reco_2597877360.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/lax_db01/db01/hm/reco_2597877360.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 5 offline';
   restore datafile 5;
   recover datafile 5;
   sql 'alter database datafile 5 online';

RMAN>

Notice the recommendation: OFFLINE DATAFILE will create downtime.

I don’t like it. This is a 99999 enviroment. hehe

Also, there is no backup since the tablespace was created

Backup check logical datafile

Is this going to work?

RMAN> recover corruption list;

Starting recover at 31-OCT-2013 12:35:06
using channel ORA_DISK_1
using channel ORA_DISK_2

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/31/2013 12:35:07
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore

RMAN> backup check logical datafile 5;

Starting backup at 31-OCT-2013 12:35:23
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
channel ORA_DISK_1: starting piece 1 at 31-OCT-2013 12:35:24
channel ORA_DISK_1: finished piece 1 at 31-OCT-2013 12:35:27
piece handle=/oracle/backup/bkup_baonpjkc_1_1 tag=TAG20131031T123523 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 31-OCT-2013 12:35:27

Starting Control File and SPFILE Autobackup at 31-OCT-2013 12:35:27
piece handle=/oracle/flashrecovery/LAX_DB01/autobackup/2013_10_31/o1_mf_s_830262927_975d83z4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-OCT-2013 12:35:42

RMAN> recover corruption list;

Starting recover at 31-OCT-2013 12:35:59
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /oracle/backup/bkup_baonpjkc_1_1
channel ORA_DISK_1: piece handle=/oracle/backup/bkup_baonpjkc_1_1 tag=TAG20131031T123523
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
media recovery complete, elapsed time: 00:00:04

Finished recover at 31-OCT-2013 12:36:06

RMAN> validate tablespace corrupt;

Starting validate at 31-OCT-2013 12:36:17
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1914         2051            1929833
  File Name: /oracle/datafilecopy/LAX_DB01/datafile/o1_mf_corrupt_9759jo6d_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              1
  Other      0              133

Finished validate at 31-OCT-2013 12:36:18

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
737     B  A  A DISK        31-OCT-2013 11:45:02 1       1       YES        AINC0_THU
738     B  0  A DISK        31-OCT-2013 11:45:07 1       1       YES        DINC0_THU
739     B  0  A DISK        31-OCT-2013 11:45:09 1       1       YES        DINC0_THU
740     B  0  A DISK        31-OCT-2013 11:45:26 1       1       YES        DINC0_THU
741     B  0  A DISK        31-OCT-2013 11:46:00 1       1       YES        DINC0_THU
742     B  0  A DISK        31-OCT-2013 11:46:11 1       1       YES        DINC0_THU
743     B  A  A DISK        31-OCT-2013 11:46:18 1       1       YES        AINC0_THU
744     B  F  A DISK        31-OCT-2013 11:46:28 1       1       NO         TAG20131031T114620
745     B  F  A DISK        31-OCT-2013 11:55:35 1       1       NO         TAG20131031T115530
746     B  F  A DISK        31-OCT-2013 12:35:24 1       1       YES        TAG20131031T123523
747     B  F  A DISK        31-OCT-2013 12:35:33 1       1       NO         TAG20131031T123527

RMAN>

YEAH! It works and is an ONLINE operation.

Check Index

LAX:(SYS@db01)> select owner, index_name, table_owner, table_name, status from dba_indexes where index_name='X';

OWNER                INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     STATUS
-------------------- ------------------------------ ------------------------------ ------------------------------ --------
HR                   X                              HR                             DEPARTMENTS_OLD                VALID

LAX:(SYS@db01)>

Q.E.D.

But, but I don’t have 11g.

This is a good reason to make a test case for upgrading.

References:

Unused Block Compression

During unused block compression, RMAN does not check each block.
Instead, RMAN reads the bitmaps that indicate what blocks are currently allocated and then only reads the blocks that are currently allocated.

Unused block compression is turned on automatically when all of the following five conditions are true:
1. The COMPATIBLE initialization parameter is set to 10.2 or higher.
2. There are currently no guaranteed restore points defined for the database.
3. The data file is locally managed.
4. The data file is being backed up to a backup set as part of a full backup or a level 0 incremental backup.
5. The backup set is created on disk, or Oracle Secure Backup is the media manager.

Null Block Compression

During null block compression, RMAN checks every block to see if it has ever contained data.
Blocks that have never contained data are not backed up. Blocks that have contained data, either currently or in the past, are backed up.

Retrieved from Oracle® Database Backup and Recovery User’s Guide11g Release 2 (11.2) on October 31, 2013

Metalink notes for reference

Master Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)
How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)
How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1)
RMAN : Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)
New Rman Blockrecover command in 11g (Recover corruption list) (Doc ID 1390759.1)
RMAN 11G : Data Recovery Advisor – RMAN command line example (Doc ID 762339.1)

corrupt.sql


set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
       to_number(S.HEADER_BLOCK + 1) || ' << EOF',
       'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
       'EOF'
  FROM DBA_SEGMENTS s, dba_data_files f
 WHERE f.tablespace_name = 'CORRUPT'  AND s.segment_name like 'X%'
;

corrupt_seg.sql


set lines 200 pages 10000 echo on
col OWNER for a20
col FILE# for 999
col BLOCK# for 99999
col BLOCKS for 99999
col s_blk# for 99999
col e_blk# for 99999
col CORRUPTION_CHANGE# for 999999999999
col SEGMENT_NAME for a25
col PARTITION_NAME for a25
select instance_name from v$instance
;
select * from v$database_block_corruption order by 1,2,3,4
;

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
       , greatest(e.block_id, c.block#) s_blk#
       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk#
       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
       - greatest(e.block_id, c.block#) + 1 blk_corrupt
      , null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
       , header_block s_blk#
       , header_block e_blk#
       , 1 blk_corrupt
       , 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
       , greatest(f.block_id, c.block#) s_blk#
       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk#
       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
       - greatest(f.block_id, c.block#) + 1 blk_corrupt
       , 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#
order by file#, s_blk#
;

One Response to “Oracle Free Block Corruption – Test Case”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>