Thinking Out Loud

March 3, 2008

Block Corruption and blockrecover

Filed under: oracle — mdinh @ 5:29 am

Please do not try this unless you can afford to lose the database.

Create table in USERS tablespace.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production      

With the Partitioning, OLAP and Data Mining options  

SQL> create table t 
  2  tablespace users as 
  3  select * from dba_objects;      

Table created.

Determine block to corrupt.

SQL> select header_block 
  2  from dba_segments 
  3  where segment_name='T';  

HEADER_BLOCK 
------------ 
         139

Backup database.

JAZZ:/home/oracle/scripts$ rman target /      

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:07:30 2008 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  

connected to target database: JAZZ (DBID=1044608836)      

RMAN> backup database;      

Starting backup at 02-MAR-2008 13:07:35 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=33 devtype=DISK 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
input datafile fno=00001 name=/ora_data01/JAZZ/system01.dbf 
input datafile fno=00002 name=/ora_data02/JAZZ/undotbs01.dbf 
input datafile fno=00003 name=/ora_data01/JAZZ/sysaux01.dbf 
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf 
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:07:37 
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:02 
piece handle=/oracle/10gr2/db_1/dbs/01ja8nop_1_1 tag=TAG20080302T130736 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
including current control file in backupset 
including current SPFILE in backupset   channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:08:04 
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:05 
piece handle=/oracle/10gr2/db_1/dbs/02ja8npi_1_1 tag=TAG20080302T130736 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 
Finished backup at 02-MAR-2008 13:08:05  

Corrupt the block.

JAZZ:/home/oracle/scripts$ dd if=/dev/zero of=/ora_data04/JAZZ/users01.dbf bs=8192 conv=notrunc seek=140 count=1 
1+0 records in 
1+0 records out 
8192 bytes (8.2 kB) copied, 9.7241e-05 seconds, 84.2 MB/s

RMAN tablespace backup reveals block corruption.

JAZZ:/home/oracle/scripts$ rman target /      

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:08:54 2008 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  

connected to target database: JAZZ (DBID=1044608836)      

RMAN> backup tablespace users;      

Starting backup at 02-MAR-2008 13:09:03 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=34 devtype=DISK 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf 
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:09:05 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/02/2008 13:09:06 
ORA-19566: exceeded limit of 0 corrupt blocks for file /ora_data04/JAZZ/users01.dbf

Corruption found in alert log but not v$backup_corruption.

JAZZ:/home/oracle/scripts$ tail -10 /ora_admin/JAZZ/bdump/alert_JAZZ.log  

Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data 
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data 
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data 
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data 
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data 
Sun Mar  2 13:09:46 2008 
Shutting down archive processes 
Sun Mar  2 13:09:51 2008 
ARCH shutting down 
ARC2: Archival stopped      

SQL> set serverout on 
SQL> exec print_table('select * from v$backup_corruption'); 
PL/SQL procedure successfully completed.

But what if RMAN is not used for backup?

It is still possible to check for block corruption by using

backup validate check logical tablespace users;

SQL> set serverout on 
SQL> exec print_table('select * from v$backup_corruption'); 
.RECID                        : 1 
.STAMP                        : 648306720 
.SET_STAMP                    : 648306720 
.SET_COUNT                    : 4 
.PIECE#                       : 1 
.FILE#                        : 4 
.BLOCK#                       : 140 
.BLOCKS                       : 1 
.CORRUPTION_CHANGE#           : 0 
.MARKED_CORRUPT               : YES 
.CORRUPTION_TYPE              : ALL ZERO 
-----------------     

PL/SQL procedure successfully completed.

Perform blockrecover and backup tablespace.

RMAN> blockrecover datafile 4 block 140;    

Starting blockrecover at 02-MAR-2008 14:26:03 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=35 devtype=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 /oracle/10gr2/db_1/dbs/05ja8o45_1_1 
channel ORA_DISK_1: restored block(s) from backup piece 1 
piece handle=/oracle/10gr2/db_1/dbs/05ja8o45_1_1 tag=TAG20080302T131341 
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01    

starting media recovery 
media recovery complete, elapsed time: 00:00:03    

Finished blockrecover at 02-MAR-2008 14:26:08    

RMAN> backup tablespace users;    

Starting backup at 02-MAR-2008 14:26:15 
using channel ORA_DISK_1 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf 
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 14:26:15 
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 14:26:16 
piece handle=/oracle/10gr2/db_1/dbs/0aja8sc7_1_1 tag=TAG20080302T142615 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 
Finished backup at 02-MAR-2008 14:26:16
About these ads

1 Comment »

  1. […] previous post on corruption is here The corrupt.sql script was stolen from here exec SYS.DBMS_BACKUP_RESTORE.resetCfileSection(35) was […]

    Pingback by Playing with Oracle Free Block Corrupting | Thinking Out Loud — October 30, 2013 @ 7:04 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 300 other followers

%d bloggers like this: