Oracle Block corruption detection

Posted by PDSERVICE on Nov 10, 2016 In

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline: +86 13764045638 E-mail: service@parnassusdata.com

The CHECK_OBJECT procedure checks and reports block corruptions for a specified object. It does the following:

Perform block checking for a specified table, partition, or index

Index entries that point to corrupt rows.

Populating a repair table

How to use (table)

Create the repair table:

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'USERS');

END;

Check for corruption i.e:

SET SERVEROUTPUT ON

DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'DEPT',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

CORRUPT_COUNT => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

Query the repair table before marking the block as corrupted

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,

CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION

FROM REPAIR_TABLE;

Fix the block, that was detected by CHECK_OBJECT procedure:

SET SERVEROUTPUT ON

DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME=> 'DEPT',

OBJECT_TYPE => dbms_repair.table_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

FIX_COUNT=> num_fix);

DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));

END;

Confirm that the repair was done

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT

FROM REPAIR_TABLE;

How to use (index)

Create an orphan key table

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'ORPHAN_KEY_TABLE',

TABLE_TYPE => dbms_repair.orphan_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'USERS');

END;

Find orphaned keys:

SET SERVEROUTPUT ON

DECLARE num_orphans INT;

BEGIN

num_orphans := 0;

DBMS_REPAIR.DUMP_ORPHAN_KEYS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'PK_DEPT',

OBJECT_TYPE => dbms_repair.index_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',

KEY_COUNT => num_orphans);

DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));

END;

Fix corrupt blocks.

BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SCOTT',

OBJECT_NAME => 'DEPT',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.skip_flag);

END;

2. Validate object structure

Verifies the integrity of the structure of an index, table, or cluster

Checks or verifies that tables and indexes are synchronized.

To validate index structure only to verifies the integrity of each data block in the index and checks for block corruption

ANALYZE INDEX <index name> VALIDATE STRUCTURE;

To validate table structure only offline, to verifies the integrity of each of the data blocks and rows

ANALYZE TABLE <table name> VALIDATE STRUCTURE CASCADE;

To validate table structure only online, to verifies the integrity of each of the data blocks and rows and confirm that each row in the table has an index entry or that each index entry points to a row in the table

ANALYZE TABLE customers VALIDATE STRUCTURE CASCADE ONLINE;

3. DB_BLOCK_CHECKING

Corrupt blocks are identified before they are marked corrupt.

Checks are performed when changes are made to a block

Can prevent memory and data corruption

causes 1% to 10% overhead, depending on workload and the value

How it works:

- OFF - disable block checking (defailt).

- LOW - basic block header checks are performed after block contents change in memory (for example, after UPDATE or INSERT statements, on-disk reads, or inter-instance block transfers in RAC)

- MEDIUM - all LOW checks are performed, as well as semantic block checking for all non-index-organized table blocks

- FULL - all LOW and MEDIUM checks are performed, as well as semantic checks for index blocks (that is, blocks of subordinate objects that can actually be dropped and reconstructed when faced with corruption)

ALTER SESSION|SYSTEM set DB_BLOCK_CHECKING=OFF | LOW | MEDIUM | FULL

4. DBVERIFY (DBV)

DBV is a tool that provide level of confidence that a datafile is free from physical corruption.

Checks the following:

The validity of datafile header

The validity/correctness of each block identity “wrapper”

The consistency of DATA and INDEX blocks

The consistency of other blocks internally (such as rollback segment blocks)

How it works:

Open the file read only mode

Cannot change the contents of the file being checked.

How to use:

Non-ASM

dbv file=/dev/rdsk/r1.dbf

dbv file=/u01/oradata/users.dbf

ASM

dbv file=+DG1/ORCL/datafile/system01.dbf userid=scott/tiger

Note userid used to log into the ASM instance and must have sysdba privilege added to the password file, i.e “grand sysdba to scott”

5. Full database export to a null location

A full database export (dump file location to /dev/null on UNIX and to nul on windows platform, if space is a constraint) can be used to check the consistency of a database:

exp system/manager full=y log=db_chk.log file=/dev/null

6. Recovery Manager

RMAN read all of the specified database files that would be input for a specific backup task, without actually producing any backups as output, however ensure that they are intact and not corrupted.

verify that database files exist

in the correct locations

no physical or logical corruptions that would prevent RMAN from creating backups of them

populate V$DATABASE_BLOCK_CORRUPTION view if any corruption detected

How to:

To check entire database

backup check logical validate database;

To check specific datafile

backup check logical validate datafile 1;

To check a set of datafiles

backup check logical validate datafile 1, 2;

7. Apply archive log delay

Setup time lag in Minutes when setup LOG_ARCHIVE_DEST_n using DELAY attribute to specify time delay between when redo data is archived on a standby site and when the archived redo log file is applied to the standby database.

For example:

LOG_ARCHIVE_DEST_3='SERVICE=stby DELAY=120'

This means the archive log at the standby destination are not available for recovery (2 hours) until the specified time interval has expired

protect a standby database from corrupted or erroneous primary data by providing enough time to allow user errors to be discovered before they are propagated to the standby database.

however failover perfromance impact tradeoff is expected.

To override the delay attribute:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISSCONNECT FROM SESSION;

8. References

283053.1 How To Use RMAN To Check For Logical & Physical Database

428570.1 Best Practices for Avoiding and Detecting Corruption

28814.1 Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g

33405.1 Extracting Data from a Corrupt Table using SKIP_CORRUPT_BLOCKS

or Event 10231

68013.1 DBMS_REPAIR example

61685.1 Extracting Data from a Corrupt Table using ROWID Range Scans in

Oracle8 and higher

471261.1 Detect And Correct Corruption (1578/Corrupt Block) on 8i/9i/10g/11g

472231.1 How to Find All the Corrupted Objects in Your Database.

35512.1 DBVERIFY - Database file Verification Utility (7.3.2 - 10.2)

277620.1 ORA-600 [3020] On Standby When Adding a Datafile On Primary

361172.1 Resolving ORA-600[3020] Raised During Recovery

You are here

Oracle Block corruption detection