Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 corupted blocks  [new]
gda
Member

Откуда:
Сообщений: 986
Hi guys,

Today I had an incident on database, i did a recovery of datafile from backup and on recovery I got ORA-00600. I was able to start database with "alllow 1 corruption" options. Finally i have some corrupted blocks in datafile (i can see them here V$DATABASE_BLOCK_CORRUPTION).

I'm skipping these blocks now to avoid ORA error for DML on these tables with DBMS_REPAIR.SKIP_CORRUPT_BLOCKS and finally "validate structure" does not fail.

How can I totally repair the blocks ? I user DBMS_REPAIR.FIX_CORRUPT_BLOCKS but it does fix the blocks and I'm still see them in (V$DATABASE_BLOCK_CORRUPTION view).

  SET SERVEROUTPUT ON
  DECLARE num_fix INT;
  BEGIN
  num_fix  := 0;
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS  (
  SCHEMA_NAME  => 'SCOTT',
  OBJECT_NAME=>  'TIGGER',
  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;
  /
21 мар 20, 03:29    [22103404]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
Synoptic
Member

Откуда:
Сообщений: 143
gda, Cad a leagan ar an mbunachar sonraí a bhfuil tú ag obair le?
21 мар 20, 10:16    [22103442]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
gda
Member

Откуда:
Сообщений: 986
11g
21 мар 20, 10:43    [22103448]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
gda
Member

Откуда:
Сообщений: 986
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
21 мар 20, 10:44    [22103451]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
veep
Member

Откуда:
Сообщений: 275
To recover corrupted blocks you should use either RMAN or copy non corrupted datafiles from backup (or standby) with following recovering

https://docs.oracle.com/cd/E25054_01/backup.1111/e10642/rcmblock.htm How to restore corruptions with RMAN
21 мар 20, 12:42    [22103490]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
-2-
Member

Откуда:
Сообщений: 15279
gda
I user DBMS_REPAIR.FIX_CORRUPT_BLOCKS
Так а что указано в repair-таблице?
21 мар 20, 12:56    [22103498]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
gda
Member

Откуда:
Сообщений: 986
Hi, here is the script I used to fix corruptions

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

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

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

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGINnum_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
    SCHEMA_NAME => 'ABS',
    OBJECT_NAME=> 'RECORDS',
    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;


I see one record in REPAIR_TABLE. When I executed DBMS_REPAIR.FIX_CORRUPT_BLOCKS the result is num_fix = 0. Why it's not fixed ? What I'm doing wrong ?
23 мар 20, 18:06    [22104686]     Ответить | Цитировать Сообщить модератору
 Re: corupted blocks  [new]
gda
Member

Откуда:
Сообщений: 986
repair_table

0BJECT_ID	TABLESPACE_ID	RELATIVE_FILE_ID	BLOCK_ID	CORRUPT_TYPE	SCHEMA_NAME	OBJECT_NAME	BASEOBJECT_NAME	PARTITION_NAME	CORRUPT_DESCRIPTION	REPAIR_DESCRIPTION	MARKED_CORRUPT	CHECK_TIMESTAMP	FIX_TIMESTAMP	REFORMAT_TIMESTAMP
64271	12	14	70031	6148	ABS	RECORDS				mark block software corrupt	ИСТИНА	23/03/2020 16:54	


Сообщение было отредактировано: 23 мар 20, 22:11
23 мар 20, 22:09    [22104822]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить