И вечный бой, покой нам только снится.
Oracle 8.1.7.4 на SLES8 SP3. В alert логе найдена вот такая запись:
***
Corrupt block relative dba: 0x1281748b (file 74, block 95371)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x1281748b
last change scn: 0x0000.0004ef13 seq: 0x1 flg: 0x02
consistency value in tail: 0x49e90601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x1281748b (file 74, block 95371) found same corrupted data
Ишем объект, в котором битый блок:
select tablespace_name, segment_type, owner, segment_name
from dba_extents
where file_id =74
and 95371 between block_id and block_id + blocks - 1;
и видим, что это таблица.
Первое что делаем - верификацию файлов данных.
alexsf@24412fa: ssh alexsf@adonis
alexsf@adonis's password:
alexsf@adonis: sudo -s
Password:
adonis:/home/alexsf # su - root
adonis:~ # su - oracle
oracle@adonis: export ORACLE_SID=main
oracle@adonis: sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Dec 8 08:16:56 2014
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect system
Enter password:
Connected.
SQL> select tablespace_name, segment_type, owner, segment_name
from dba_extents
where file_id =74
and 95371 between block_id and block_id + blocks - 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
ZARPLATA TABLE ZARPHERSON
FOR_AUDIT
SQL> select file_name from dba_data_files
where tablespace_name ='ZARPLATA';
FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/main/zarplata01.ora
/u03/oradata/main/zarplata02.ora
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
oracle@adonis:~> dbv file=/u02/oradata/main/zarplata01.ora blocksize=8192
DBVERIFY: Release 8.1.7.4.0 - Production on Mon Dec 8 08:25:00 2014
(c) Copyright 2000 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/oradata/main/zarplata01.ora
DBVERIFY - Verification complete
Total Pages Examined : 196608
Total Pages Processed (Data) : 80699
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 57324
Total Pages Failing (Index): 0
Total Pages Processed (Other): 418
Total Pages Empty : 58166
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
oracle@adonis:~> dbv file=/u03/oradata/main/zarplata02.ora blocksize=8192
DBVERIFY: Release 8.1.7.4.0 - Production on Mon Dec 8 08:25:44 2014
(c) Copyright 2000 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = /u03/oradata/main/zarplata02.ora
DBVERIFY - Verification complete
Total Pages Examined : 196608
Total Pages Processed (Data) : 77223
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 63612
Total Pages Failing (Index): 0
Total Pages Processed (Other): 540
Total Pages Empty : 55233
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
И наблюдаем чудо - все у нас хорошо :)
Дальше делаем валидацию структуры таблицы:
oracle@adonis:~> sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Dec 8 08:34:29 2014
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect system
Enter password:
Connected.
SQL> ANALYZE TABLE zarpherson.for_audit VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE zarpherson.for_audit VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 74, block # 95371)
ORA-01110: data file 74: '/u02/oradata/main/zarplata01.ora'
О, це вже шо то...
Создаем системные таблицы для проведения работ по починке:
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
Проверяем битые блоки:
SQL> SET serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'FOR_AUDIT',
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 number corrupt: 1
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 95371 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 => 'ZARPHERSON',
OBJECT_NAME=> 'FOR_AUDIT',
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 95371 TRUE
Теперь надо найти индексы, ссылающиеся на битый блок:
SQL> select index_name from dba_indexes
where table_name = 'FOR_AUDIT'
and owner = 'ZARPHERSON';
/
2 3
INDEX_NAME
------------------------------
I_FOR_AUDIT_AUDIT_TABLES_ID
I_FOR_AUDIT_AUDIT_USERS_ID
I_FOR_AUDIT_IDKADRY
I_FOR_AUDIT_TIMESTAMP
PK_FOR_AUDIT
SQL> SET serveroutput on
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'PK_FOR_AUDIT',
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: 0
PL/SQL procedure successfully completed.
И так по очереди со всеми. Если "orphan key count: 0", значит все хорошо.
В нашем случае везде был 0.
Однако, если бы что-то было найдено - не беда. Надо просто перестроить индексы.
Ребилдим фрилист таблицы. По сути исключаем битые блоки из доступных:
SQL> BEGIN
DBMS_REPAIR.REBUILD_FREELISTS (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'FOR_AUDIT',
OBJECT_TYPE => dbms_repair.table_object);
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
Ну и делаем блоки невидимыми для DML операций:
SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'FOR_AUDIT',
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 = 'ZARPHERSON'
AND skip_corrupt = 'ENABLED'; 2 3 4
OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
ZARPHERSON FOR_AUDIT ENABLED
Что имеем в итоге - таблицу можно использовать, но проблема не устранена. Блок то битый остался, мы просто наворотили для него механизм невидимости. Но при этом если повторно сделать валидацию структуры - ничего не поменялось. Нам повезло в одном - это таблица аудита. Со всеми вытекающими. В нашем случае делаем так:
connect zarpherson@main
create table for_audit_tmp
as select * from for_audit;
truncate table for_audit;
insert into for_audit
select * from for_audit_tmp;
truncate table for_audit_tmp;
commit;
Делаем анализ структуры и видим:
SQL> ANALYZE TABLE zarpherson.for_audit VALIDATE STRUCTURE CASCADE;
Table analyzed.
То есть битый блок исчез. Ну а теперь же надо вернуть все в зад, чтоб битые блоки не пропускались при DML операциях... Дальше учитывайте, что в REPAIR_TABLE у нас только одна запись!!!
SQL> truncate table repair_table;
Table truncated.
SQL> SET serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'FOR_AUDIT',
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 /
number corrupt: 1
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
АЧЧЧЧЧПУЕЕЕЕЕТЬ!!!!!
Откуда, мля, я же только проверил, НИЧЕГО НЕ БЫЛО!!!!!
SQL> ANALYZE TABLE zarpherson.for_audit VALIDATE STRUCTURE CASCADE;
Table analyzed.
А-а-а-а-а-а!!! АЧПУЕТЬ АЧПУЕННЕЕ ЧЕМ АЧПУЕЛ!!! КАК???!!!
Та-а-а-ак...
INSERT INTO for_audit_tmp SELECT * FROM for_audit;
DROP TABLE for_audit
/
CREATE TABLE for_audit
(timestamp DATE,
user_name VARCHAR2(30),
table_name VARCHAR2(30),
action VARCHAR2(1),
data_values VARCHAR2(2000),
id NUMBER ,
audit_tables_id NUMBER,
audit_users_id NUMBER,
idkadry NUMBER)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE zarplata
STORAGE (
INITIAL 223846400
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
NOMONITORING
NOPARALLEL
LOGGING
/
-- Grants for Table
GRANT SELECT ON for_audit TO zp_zarpherson_buh_bez_prop
/
GRANT SELECT ON for_audit TO zp_zarpherson_operator
/
GRANT SELECT ON for_audit TO zp_zarpherson_buhdostup
/
GRANT SELECT ON for_audit TO zp_zarpherson_buhgalter
/
GRANT SELECT ON for_audit TO zp_zarpherson_read_only
/
GRANT SELECT ON for_audit TO zp_zarpherson_read_by_podr
/
GRANT SELECT ON for_audit TO zp_zarpherson_buh_bp_by_podr
/
GRANT SELECT ON for_audit TO plan_1
/
GRANT SELECT ON for_audit TO zp_zarpherson_admin
/
GRANT SELECT ON for_audit TO zp_zarpherson_read_only_dostup
/
-- Indexes for FOR_AUDIT
CREATE INDEX i_for_audit_audit_users_id ON for_audit
(
audit_users_id ASC
)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE zarplata
STORAGE (
INITIAL 1048576
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
LOGGING
/
CREATE INDEX i_for_audit_idkadry ON for_audit
(
idkadry ASC
)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE zarplata
STORAGE (
INITIAL 1048576
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
LOGGING
/
CREATE INDEX i_for_audit_timestamp ON for_audit
(
timestamp ASC
)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE zarplata
STORAGE (
INITIAL 1048576
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
LOGGING
/
CREATE INDEX i_for_audit_audit_tables_id ON for_audit
(
audit_tables_id ASC
)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE zarplata
STORAGE (
INITIAL 1048576
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOPARALLEL
LOGGING
/
-- Constraints for FOR_AUDIT
ALTER TABLE for_audit
ADD CONSTRAINT nn_for_audit_action CHECK (ACTION IS NOT NULL)
/
ALTER TABLE for_audit
ADD CONSTRAINT nn_for_audit_audit_tables_id CHECK (audit_tables_id IS NOT NULL)
/
ALTER TABLE for_audit
ADD CONSTRAINT nn_for_audit_data_values CHECK (DATA_VALUES IS NOT NULL)
/
ALTER TABLE for_audit
ADD CONSTRAINT nn_for_audit_table_name CHECK (TABLE_NAME IS NOT NULL)
/
ALTER TABLE for_audit
ADD CONSTRAINT nn_for_audit_timestamp CHECK (TIMESTAMP IS NOT NULL)
/
ALTER TABLE for_audit
ADD CONSTRAINT nn_for_audit_user_name CHECK (USER_NAME IS NOT NULL)
/
ALTER TABLE for_audit
ADD CONSTRAINT pk_for_audit PRIMARY KEY (id)
USING INDEX
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE zarplata
STORAGE (
INITIAL 2097152
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
-- Triggers for FOR_AUDIT
CREATE OR REPLACE TRIGGER tr_for_audit_bi
BEFORE
INSERT
ON for_audit
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:new.timestamp := SYSDATE;
:new.user_name := USER;
BEGIN
SELECT id
INTO :new.audit_users_id
FROM audit_users
WHERE user_name = user;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO audit_users(user_name)
VALUES(USER)
RETURNING id INTO :new.audit_users_id;
END;
BEGIN
SELECT id
INTO :new.audit_tables_id
FROM audit_tables
WHERE table_name = :new.table_name;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO audit_tables(date_start, date_end, table_name)
VALUES(trunc(sysdate), to_date('01013000', 'ddmmyyyy'), :new.table_name)
RETURNING id INTO :new.audit_tables_id;
END;
SELECT sq_for_audit.nextval
INTO :new.id
FROM dual;
END;
/
-- Comments for FOR_AUDIT
COMMENT ON TABLE for_audit IS 'Таблица с информацией о действиях пользователя над данными'
/
COMMENT ON COLUMN for_audit.action IS 'Действие:
U - UPDATE
I - INSERT
D - DELETE'
/
COMMENT ON COLUMN for_audit.audit_tables_id IS 'Идентификатор таблицы из AUDIT_TABLES'
/
COMMENT ON COLUMN for_audit.audit_users_id IS 'Идентификатор пользователя из AUDIT_USERS'
/
COMMENT ON COLUMN for_audit.data_values IS 'Значение до действия над данными и после'
/
COMMENT ON COLUMN for_audit.id IS 'Идентификатор записи'
/
COMMENT ON COLUMN for_audit.idkadry IS 'Идентификатор работника из T_KADRY'
/
COMMENT ON COLUMN for_audit.table_name IS 'Наименование таблицы'
/
COMMENT ON COLUMN for_audit.timestamp IS 'Дата '
/
COMMENT ON COLUMN for_audit.user_name IS 'Имя пользователя'
/
-- End of DDL Script for Table ZARPHERSON.FOR_AUDIT
-- Foreign Key
ALTER TABLE for_audit
ADD CONSTRAINT fk_for_audit_audit_tables_id FOREIGN KEY (audit_tables_id)
REFERENCES audit_tables (id)
/
-- End of DDL script for Foreign Key(s)
Проверяем, кто тут поц:
SQL> truncate table repair_table;
Table truncated.
SQL> SET serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'FOR_AUDIT',
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 /
number corrupt: 0
PL/SQL procedure successfully completed.
НИКОГО!!! А вот так:
conn zarpherson
insert into for_audit
select * from for_audit_tmp;
commit;
conn sys as sysdba
SQL> SET serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'ZARPHERSON',
OBJECT_NAME => 'FOR_AUDIT',
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 /
number corrupt: 1
PL/SQL procedure successfully completed.
SQL>
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
И хрен его знает, кто тут поц и где тут подлива... Таблица пересоздана, и при этом при внесении в нее данных ломается тот же самый блок... При этом валидация структуры проходит нормально...
Ладно, ждем перезапуска базы и анализа всех объектов по счедулеру. А пока проверим, а все ли объекты проходят валидацию структуры???:
set trimspool on
set pagesize 0
set heading off
spool _tmp_1.sql
select 'ANALYZE TABLE '||owner||'.'||table_name||' VALIDATE STRUCTURE CASCADE;'
from dba_tables
where owner not in ('SYS','OUTLN','SYSTEM')
order by owner, table_name;
spool off
@_tmp_1.sql
И вот что в результате надыбал:
ANALYZE TABLE COUNTERSREAD.OSTABLE VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
Из trace file
** 2014-12-08 12:10:13.037
*** SESSION ID:(44.1327) 2014-12-08 12:10:13.037
row not found in index tsn: 9 rdba: 0x03414909
env: (scn: 0x0000.018348c7 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00)col 0; len 8; (8): 34 37 30 35 35 37 32 37
col 1; len 6; (6): 03 01 90 ef 00 09
Block header dump: 0x030190ef
Object id on Block? Y
seg/obj: 0x29ce csc: 0x00.12365be itc: 1 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
и куча мути дальше, похожей да дамп куска области памяти.
По нескольким объектам валидация не прошла, потому что ORA-00054: resource busy and acquire with NOWAIT specified
Читать далее
