Recover corrupted data blocks using DBVERIFY

As we know that SYSTEM – stores the data dictionary.  Being an Oracle DBA we all know the importance of SYSTEM tablespace.  In these below lines, we can have actual technical knowledge about system’s information.

SYSTEM – a tablespace that is always used to store SYSTEM data that includes data about tables, indexes, sequences, and other objects – this metadata comprises the data dictionary.

• Every Oracle database has to have a SYSTEM tablespace—it is the first tablespace created when a database is created.
• Accessing it requires a higher level of privilege.
• You cannot rename or drop a SYSTEM tablespace.
• You cannot take a SYSTEM tablespace offline.
• The SYSTEM tablespace could store user data, but this is not normally done—a good rule to follow is to never allow the storage of user segments in the SYSTEM tablespace.
• This tablespace always has a SYSTEM Undo segment.

From the above information, i hope you get the hint that why do we need a backup of system tablespace and why do we need to recover.

What does it happen when a corrupt block is related to SYSTEM datafile?
Let’s simulate this scenario:

[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/system01.dbf bs=8k conv=notrunc seek=1000 count=5

DBVERIFY detects your SYSTEM datafile is corrupt. Five data blocks are no more available.

[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/system01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Tue Mar 19 09:01:17 2013

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

DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/orcl/system01.dbf
Page 1000 is marked corrupt
Corrupt block relative dba: 0x004003e8 (file 1, block 1000)
Completely zero block found during dbv:

Page 1001 is marked corrupt
Corrupt block relative dba: 0x004003e9 (file 1, block 1001)
Completely zero block found during dbv:

Page 1002 is marked corrupt
Corrupt block relative dba: 0x004003ea (file 1, block 1002)
Completely zero block found during dbv:

Page 1003 is marked corrupt
Corrupt block relative dba: 0x004003eb (file 1, block 1003)
Completely zero block found during dbv:

Page 1004 is marked corrupt
Corrupt block relative dba: 0x004003ec (file 1, block 1004)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined : 116608
Total Pages Processed (Data) : 73223
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 18058
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11301
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 14021
Total Pages Marked Corrupt : 5
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 14866073 (0.14866073)

Let’s update the V$DATABASE_BLOCK_CORRUPTION view issuing a backup validate command.

RMAN> backup validate tablespace system;

Starting backup at 19-03-2013 09:02:43
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 FAILED 0 14021 116611 14866295
File Name: /home/oracle/app/oracle/oradata/orcl/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 73223
Index 0 18058
Other 5 11306

validate found one or more corrupt blocks
See trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc for details
channel ORA_DISK_1: starting compressed 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:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 604
Finished backup at 19-03-2013 09:03:21

The V$DATABASE_BLOCK_CORRUPTION view contains the list of corrupt blocks, all related to datafile number 1.

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
1 1000 5 0 ALL ZERO

Let’s try to recover those blocks.

RMAN> recover corruption list;

Starting recover at 19-03-2013 09:04:22
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 00001
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_17/o1_mf_nnndf_TAG20130317T224547_8nfbjnn5_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_17/o1_mf_nnndf_TAG20130317T224547_8nfbjnn5_.bkp tag=TAG20130317T224547
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:02:26

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

Finished recover at 19-03-2013 09:06:51

It worked. And the view doesn’t contain any data.

SQL> select * from v$database_block_corruption;

no rows selected

So the recovery process of a corrupt block of the SYSTEM tablespace is the same and there is no need to bounce the instance.

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp