corrupt blocks

analyze TABLE table_name validate structure cascade;

select segment_type,owner'.'segment_name
from dba_extents
where file_id = 4 and 29830 between block_id and block_id+blocks -1;

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
where file_id =4
and block_ID =4151

V$backup_corruption
select * from v$database_block_corruption;

************************************************


*******************************************

C:\Documents and Settings\Administrator>dbv file=C:\esas\oracle\oradata\XE\esasbranch.dbf feedback=100

***********************************************
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'ESASBRANCH');
END;
/

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'ESASBRANCH');
END;
/


SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'ESASBRANCH',
OBJECT_NAME => 'WAYBILL',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' TO_CHAR (num_corrupt));
END;
/

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;


SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'ESASBRANCH',
OBJECT_NAME=> 'ACCOUNT',
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;
/

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;

******FOR ÝNDEXES***********

SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'ESASBRANCH',
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;
/



BEGIN
DBMS_REPAIR.REBUILD_FREELISTS (
SCHEMA_NAME => 'ESASBRANCH',
OBJECT_NAME => 'ACCOUNTADDRESS',
OBJECT_TYPE => dbms_repair.table_object);
END;
/

*******Enabling or Disabling the Skipping of Corrupt Blocks************

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'ESASBRANCH',
OBJECT_NAME => 'WAYBILL',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/


SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES
WHERE OWNER = 'ESASBRANCH';





***********************************************************************************************************
/* Fix the bitmap status for all the blocks in table mytab in schema sys */
EXECUTE DBMS_REPAIR.SEGMENT_FIX_STATUS('ESASBRANCH', 'ACCOUNT');

/* Mark block number 45, filenumber 1 for table mytab in sys schema as FULL.*/

EXECUTE DBMS_REPAIR.SEGMENT_FIX_STATUS('ESASBRANCH', 'ACCOUNT', TABLE_OBJECT,4, 372115, 1);

**************************************************************************
run {
allocate channel ch1 type disk;
blockrecover datafile 9 block 13 datafile 2 block 19;
}

BLOCKRECOVER DATAFILE 9 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

SQL> select * from v$database_block_corruption;

*************************
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

query the V$DATABASE_BLOCK_CORRUPTION

RMAN> blockrecover corruption list;
**************************
RMAN> BACKUP VALIDATE DATABASE;
RMAN> BLOCKRECOVER CORRUPTION LIST;
****************************