Имеем следующее:
. . exporting table TGROUPSCHEMA 8 rows exported
. . exporting table THEAD
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 66, block # 107197)
ORA-01110: data file 66: '/u02/oradata/main/umc_data01.ora'
. . exporting table TMATHERIAL 156757 rows exported
Действия:
adonis:~ # su - oracle
oracle@adonis:~> dbv file=/u02/oradata/main/umc_data01.ora blocksize=8192
DBVERIFY: Release 8.1.7.4.0 - Production on Fri Jan 23 10:38:07 2015
(c) Copyright 2000 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/oradata/main/umc_data01.ora
Block Checking: DBA = 276931261, Block Type = KTB-managed data block
Found block already marked corrupted
DBVERIFY - Verification complete
Total Pages Examined : 128000
Total Pages Processed (Data) : 45601
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 70514
Total Pages Failing (Index): 0
Total Pages Processed (Other): 460
Total Pages Empty : 11425
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
oracle@adonis:~> export ORACLE_SID=main
oracle@adonis:~> sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jan 23 10:45:14 2015
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select owner, segment_name, segment_type from dba_extents
where file_id=dbms_utility.data_block_address_file(276931261)
and dbms_utility.data_block_address_block(276931261) between block_id and block_id+blocks-1 2 3
4 ;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
UMC_DBA
THEAD
TABLE
Ну это и так было понятно.
SQL> SET serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'UMC_DBA',
OBJECT_NAME => 'THEAD',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
SQL> 2 3 4 5 6 7 8 9 10 11
12 /
number corrupt: 2
PL/SQL procedure successfully completed.
SQL> SELECT object_name, block_id, corrupt_type, marked_corrupt,
corrupt_description, repair_description
FROM repair_table; 2 3
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
FOR_AUDIT 98250 6148 TRUE
mark block software corrupt
THEAD 19130 6148 TRUE
mark block software corrupt
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
THEAD 107197 6148 TRUE
mark block software corrupt
SQL> SET serveroutput on
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'UMC_DBA',
OBJECT_NAME=> 'THEAD',
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;
/SQL> 2 3 4 5 6 7 8 9 10 11 12
num fix: 0
PL/SQL procedure successfully completed.
SQL> SELECT object_name, block_id, marked_corrupt
FROM repair_table; 2
OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
FOR_AUDIT 98250 TRUE
THEAD 19130 TRUE
THEAD 107197 TRUE
SQL> select index_name from dba_indexes
where table_name = 'THEAD'
and owner = 'UMC_DBA';
/ 2 3
INDEX_NAME
------------------------------
I_HD_ACCEPTOR
I_HD_AGREE_PARTNER_LINK
I_HD_CORRECTION
I_HD_CORRECTOR
I_HD_CREATION
I_HD_CREATOR
I_HD_DIVCODE
I_HD_DOC_DATE
I_HD_DOC_NUM
I_HD_DRAFT_HEAD_ID
I_HD_OP_DT_SENDER
INDEX_NAME
------------------------------
I_HD_OP_KIND_ID
I_HD_OWNERID
I_HD_PARENT_ID
I_HD_PARTNER_ID
I_HD_PARTNER_TYPE
I_HD_SENDER
PK_HEAD
18 rows selected.
Теперь проверяем все индексы.
SQL> SET serveroutput on
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'UMC_DBA',
OBJECT_NAME => 'PK_HEAD',
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;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13
orphan key count: 46
PL/SQL procedure successfully completed.
orphan key count д.б. = 0. Как видим, тут 46. Это жопа.
По остальным:
I_HD_SENDER 46
I_HD_PARTNER_TYPE 46
I_HD_PARTNER_ID 1
I_HD_PARENT_ID 15
I_HD_OWNERID 46
I_HD_OP_KIND_ID 46
I_HD_OP_DT_SENDER 46
I_HD_DRAFT_HEAD_ID 0
I_HD_DOC_NUM 46
I_HD_DOC_DATE 46
I_HD_DIVCODE 46
I_HD_CREATOR 46
I_HD_CREATION 46
I_HD_CORRECTOR 46
I_HD_CORRECTION 46
I_HD_AGREE_PARTNER_LINK 1
I_HD_ACCEPTOR 46
Теперь у нас в ORPHAN_KEY_TABLE куча данных :)
SQL> BEGIN
DBMS_REPAIR.REBUILD_FREELISTS (
SCHEMA_NAME => 'UMC_DBA',
OBJECT_NAME => 'THEAD',
OBJECT_TYPE => dbms_repair.table_object);
END; 2 3 4 5 6
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'UMC_DBA',
OBJECT_NAME => 'THEAD',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> SELECT owner, table_name, skip_corrupt
FROM dba_tables
WHERE owner = 'UMC_DBA'
AND skip_corrupt = 'ENABLED'; 2 3 4
OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
UMC_DBA THEAD ENABLED
alter index I_HD_ACCEPTOR rebuild;
alter index I_HD_AGREE_PARTNER_LINK rebuild;
alter index I_HD_CORRECTION rebuild;
alter index I_HD_CORRECTOR rebuild;
alter index I_HD_CREATION rebuild;
alter index I_HD_CREATOR rebuild;
alter index I_HD_DIVCODE rebuild;
alter index I_HD_DOC_DATE rebuild;
alter index I_HD_DOC_NUM rebuild;
alter index I_HD_DRAFT_HEAD_ID rebuild;
alter index I_HD_OP_DT_SENDER rebuild;
alter index I_HD_OP_KIND_ID rebuild;
alter index I_HD_OWNERID rebuild;
alter index I_HD_PARENT_ID rebuild;
alter index I_HD_PARTNER_ID rebuild;
alter index I_HD_PARTNER_TYPE rebuild;
alter index I_HD_SENDER rebuild;
alter index PK_HEAD rebuild;
год назад
Комментариев нет:
Отправить комментарий