Картинка для придания нужного настроения
Доброго времени суток!
В интернете вообще и на Хабре в частности немало публикаций о ремонте битых блоков. Например, здесь о выдергивании неповрежденных данных, а тут история победы над битым LOB сегментом.
Приводить легионы ссылок на инет с подобными статьями не буду. Но есть в большинстве этих статей общая черта. Когда данные спасены (или уничтожены, как получится), предлагается победить поврежденный free block захватом всего свободного места в tablespace. И почему то это предложение описательно.
Так добавим конкретики!
Напишем скрипт, который скушает (почти как рыбки на картинке), а потом освободит всё свободное место Oracle DB.
Подготовительная часть.
Посмотрели на наши битые блоки:
select * from v$database_block_corruption;
Убедились, что они есть.
Посмотрели точно ли все наши блоки «free»(вдруг еще один сломанный сегмент затерялся?):
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
Не забудем отключить autoextend для всех файлов нашего TS с битыми free блоками. Последствия забывчивости могут оказаться не очень приятными.
Да, «сжать» файлы обратно можно успеть. Но зачем нам лишние проблемы?
select
'alter database datafile '||
file_name||
' '||
' autoextend off;'
from
dba_data_files where TABLESPACE_NAME='PSAPSR3';
Получили список команд. Запустили их. Потом таким же образом сформируем список команд, которые вернут autoextend.
Но здесь надо быть внимательным. Может датафайлы располагаются на разных дисках? И у них разные настройки расширения? Здесь стоит быть аккуратнее.
(Да, моя специальность «Специалист SAP BASIS». Этим и объясняется выбор имени для tablespace. Ведь ошибки я вынужден чинить именно там.)
И только теперь, когда подготовительные шаги завершены.
Основная часть
Для работы с Oracle в sqlplus я использую SQL Developer. В нем есть все что нужно, и не наблюдается того, что не нужно.
Чего мы хотим?
Переформатировать все свободные блоки tablespace. Для этого будем создавать таблицы, и добавлять им экстенты пока место не закончится.
Вот такой скриптик PLSQL нормально делает то, что нам нужно.
SET SERVEROUTPUT ON
DECLARE
type ARR_TABLE is table of varchar2(13);
TBLS ARR_TABLE:=ARR_TABLE();
I number;
SPACE_AVAILABLE float;
--Константа с целевым tablespace
TABLESPACE_FOR_FULL CONSTANT varchar2(20) := 'PSAPSR3';
--Константа со схемой. Почему бы нет?
USER_SCHEMA CONSTANT varchar2(20) := 'SAPSR3';
--Вычисляем свободное место в TS
function TABLESPACE_FREESIZE(TN varchar2) return number
as si number;
begin
SELECT round(sum(bytes)/1048576,2) into si from DBA_FREE_SPACE where TABLESPACE_NAME = TN;
return SI;
end TABLESPACE_FREESIZE;
--Создание и раздувание очередной таблицы пока не выскочит ora-1653
procedure create_new_tables as
N number;
I number;
UNABLE_TO_EXTEND EXCEPTION;
PRAGMA EXCEPTION_INIT(UNABLE_TO_EXTEND,-1653);
BEGIN
N:=TBLS.COUNT;
N:=N+1;
TBLS.extend;
TBLS(N):='TESTTABLE'||N;
execute immediate 'create table '||USER_SCHEMA||'.'||TBLS(n)||' (id number(10), USER_NAME varchar2(10), CREATE_DATE date) tablespace '||TABLESPACE_FOR_FULL;
commit;
WHILE true LOOP
begin
execute immediate 'alter table '||USER_SCHEMA||'.'||TBLS(n)||' allocate extent';
commit;
EXCEPTION
when UNABLE_TO_EXTEND then
EXIT;
end;
END LOOP;
end create_new_tables;
BEGIN
--Подготовка
DBMS_OUTPUT.PUT_LINE( 'Time start: '||TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
SPACE_AVAILABLE:=TABLESPACE_FREESIZE(TABLESPACE_FOR_FULL);
DBMS_OUTPUT.PUT_LINE('Space available='||SPACE_AVAILABLE);
--Запуск
WHILE SPACE_AVAILABLE>0.001 LOOP
CREATE_NEW_TABLES();
SPACE_AVAILABLE:=TABLESPACE_FREESIZE(TABLESPACE_FOR_FULL);
--Когда функция TABLESPACE_FREESIZE начнет выдавать NULL вместо числа - цикл остановится.
end LOOP;
--Очистка созданных таблиц
for I in 1..TBLS.COUNT LOOP
execute immediate 'drop table '||USER_SCHEMA||'.'||TBLS(I);
commit;
end LOOP;
DBMS_OUTPUT.PUT_LINE( 'Time end: '||TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
end;
(Прошу обратить внимание на два лишних слеша "". В строках с «create table» и «drop table». Они для того, чтобы выделение цветом не ехало. Перед боевым использованием их надо снести. А менять кавычки на другие я не хочу. Очень раздражает последующее выискивание «неверных».)
Опять же, заданные константы TABLESPACE_FOR_FULL и USER_SCHEMA как бы намекают на специфику моих баз.
Комментировать алгоритм работы мне видится излишним.
Скорость работы такой методы меня устраивает.
anonymous block completed
Time start: 22-10-2013 13:10:10
Space available=827,88
Time end: 22-10-2013 13:10:11
anonymous block completed
Time start: 22-10-2013 13:10:27
Space available=10668,75
Time end: 22-10-2013 13:10:46
anonymous block completed
Time start: 22-10-2013 13:11:26
Space available=99266,81
Time end: 22-10-2013 13:14:37
Чуть менее 100Gb за 3 минуты. Ясно, что оборудование играет очень большую роль, но там где оборудование слабое и размеров особых не будет. Тем более что Tb свободного места достаточно странная ситуация.
Завершающие шаги
Теперь вернув autoextend можно заняться проверкой базы.
rman target /
И
BACKUP VALIDATE database;
После чего представление v$database_block_corruption должно быть чистым как… ну вы поняли.
Эпилог
А есть ли способ короче и быстрее? Должен быть. Обязательно. Я начал писать на PLSQL три недели назад. И в первую очередь я специалист SAP BASIS, а не DBA.
Мне вполне хватает и этого. Но было бы интересно посмотреть на решение этой задачи от профи.
Автор: Vantela
