Its all about Database

Database corruption

Database corruption in Oracle can occur due to various reasons, such as hardware failures, software bugs, or issues during database operations. It's crucial to identify and address corruption promptly to ensure data integrity. Here are some steps you can take to deal with database corruption in Oracle:

Physical corruption: This happens generally due to media corruption. In this case, the block does have an invalid checksum. It may contain zero. This is generally the result of infrastructure issues like media, network, and memory.

RMAN block recovery can be used to remove physical corruption

We see the error ORA-01578: ORACLE data block corrupted in the alert log when Oracle detects physical corruption. Oracle detects corruption by checking the block header and block footer.

Logical corruption: This happens when Oracle find the block header and footer good but it finds internal consistency in the block. Generally, it happens due to some bug in Oracle software. When Oracle detects it, it throws the error in the alert log.

ORA-08103 is a logical corruption error for a particular data block.
ORA-00600 [2662] – This error is related to block corruption and occurs due to a higher SCN than database SCN.

How to check block corruption in Oracle database

Block corruption in Oracle Database can be found using the below method

RMAN

· Checking alert log for possible occurrence

dbverify
Export
Analyze command

Using RMAN Run below command

RMAN> backup validate check logical database;

The “CHECK LOGICAL” option is used to identify both Physical and Logical Block Corruptions.

We can use the below command in case we want to check selectively

BACKUP VALIDATE DATAFILE 1;

BACKUP VALIDATE check logical DATAFILE 1;

BACKUP VALIDATE TABLESPACE TOOLS;

BACKUP VALIDATE check logical TABLESPACE TOOLS;

BACKUP VALIDATE BACKUPSET 1;

BACKUP VALIDATE CURRENT CONTROLFILE;

For Multitenant

VALIDATE CHECK LOGICAL PLUGGABLE DATABASE test1;

Select the view to identify the corrupted blocks detected by RMAN.

QL> select * from v$database_block_corruption;

For each row, find the segment impacted

SQL> SELECT TABLESPACE_NAME, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE

FROM DBA_EXTENTS

WHERE FILE_ID=&FILE_ID AND

&BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Please note that after a corrupt block is repaired, the row identifying the block is deleted from the view.

Checking alert log for possible occurrence

Check alert. log file for corrupted blocks, data file list.

For ex:- ORA-01578: ORACLE data block corrupted (file # 11, block # 45)

ORA-01110: data file 5: '/xyz/db01.dbf'

dbverify

You can also use the dbverify utility to identify Physical and Logical Intra Block Corruptions.

dbv file=datafile_name blocksize=datafile_block_size

You can use the below command to generate the dbv command for all the datafiles

sqlplus /nolog

connect / as sysdba

set feedback off

set head off

set echo off

set linesize 200

set pagesize 2500

spool /tmp/dbvlogs/dbvchk.sh

select 'dbv file=' || name || ' blocksize='|| block_size || ' logfile=' ||

substr(name, instr(name, '/', -1, 1) +1) ||

'.' || file# || '.log' from v$datafile

spool off

In the case of RAW device

select 'dbv file='||name||' blocksize='||block_size||' end='||

(bytes/block_size)||' logfile='||

substr (name, instr (name, '/', -1, 1) + 1)||'.'||file#||'.log'

from  v$datafile;

In the case of ASM storage

select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=sys/&SYS_PASSWORD logfile=' ||

substr(name, instr(name, '/', -1, 1) +1) ||

'.' || file# || '.log' from v$datafile

Export

Using the export of a table or schema will also find the corruption in the table or schema

But the big limitation with export is that It fails immediately after encountering the first corruption., so the complete picture will be missed.

ANALYZE command

ANALYZE has been referred to as the best utility that performs maximum checks.

This is the easy utility to check the corruptions associated with an index by any means.

To check the corruption with a table do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE;

To check the corruption with an index do the following:

ANALYZE INDEX <OWNER.INDEX_NAME> VALIDATE STRUCTURE;

To check the corruption with the table and its index(s) to perform the cross-reference checkings do the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE;

For partition tables, we need to use the ANALYZE command with the INTO INVALID_ROWS option, similar to the following:

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;

How to Correct the Block Corruption using RMAN Blockrecover

1. Recovering Data blocks By Using All Available Backups

Run the BLOCKRECOVER command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks

RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15;

Recover multiple blocks in a single command

RMAN>BLOCKRECOVER DATAFILE 11 BLOCK 15 DATAFILE 2 BLOCK 10

2. Recovering Data blocks Using Selected Backups

Run the BLOCKRECOVER command at the RMAN prompt, Specifying the data file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, specify what type of backup should be used to restore the blocks.

# restore from backupset


RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM BACKUPSET;

# Restore from datafile image copy

RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM DATAFILECOPY;

# Restore from backup set with tag “Sunday”



RMAN> BLOCKRECOVER DATAFILE 11 BLOCK 15 FROM TAG = Sunday

3 . Recovering blocks listed in V$DATABASE_BLOCK_CORRUPTION view

Run the below command to recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION

RMAN> BLOCKRECOVER CORRUPTION LIST;

Restores blocks from backup sets created more than 7 days ago

RMAN> BLOCKRECOVER CORRUPTION LIST FROM BACKUPSET RESTORE UNTIL TIME 'SYSDATE-7';

How to Correct the Block Corruption Using Backup

Using data file recovery from backup or standby (if you have one)

Complete database recovery