Пользователь
0,0
рейтинг
23 октября 2013 в 09:41

Разработка → Раздуваем таблицы и пожираем tablespaces из песочницы


Картинка для придания нужного настроения

Доброго времени суток!


В интернете вообще и на Хабре в частности немало публикаций о ремонте битых блоков. Например, здесь о выдергивании неповрежденных данных, а тут история победы над битым 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 sql я использую SQL Developer. В нем есть все что нужно, и не наблюдается того, что не нужно.

Чего мы хотим?
Переформатировать все свободные блоки tablespace. Для этого будем создавать таблицы, и добавлять им экстенты пока место не закончится.

Вот такой скриптик PL\SQL нормально делает то, что нам нужно.
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;
   WHILE true LOOP
    begin
      execute immediate 'alter table '||USER_SCHEMA||'.'||TBLS(n)||' allocate extent';
      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);
  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 должно быть чистым как… ну вы поняли.

Эпилог

А есть ли способ короче и быстрее? Должен быть. Обязательно. Я начал писать на PL\SQL три недели назад. И в первую очередь я специалист SAP BASIS, а не DBA.
Мне вполне хватает и этого. Но было бы интересно посмотреть на решение этой задачи от профи.
Всеволод @Vantela
карма
3,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (23)

  • 0
    Но есть в большинстве этих статей общая черта. Когда данные спасены (или уничтожены, как получится), предлагается победить поврежденный free block захватом всего свободного места в tablespace.

    На самом деле уже не важно, что будет с Tablespace, когда данные спасены. Только время простоя.
    А по сути статьи, я все таки предпочитаю иметь копию и возможность восстановить повреждённый блок с помощью Rman.
    Не критичные неточности, которые увидел при беглом прочтении: в команде alter table allocate extent можно указывать размер экстента, возможно будет быстрее, а так же DDL не требует commit.
    • 0
      Спасибо! Попробую.
      Я пробовал играться с параметрами создания таблицы, но оракл их воспринимает как рекомендации и все равно делает как хочет.
      • 0
        Я пробовал играться с параметрами создания таблицы, но оракл их воспринимает как рекомендации и все равно делает как хочет.


        Обратите внимание на параметры, с которыми создано табличное пространство — ASSM (Automatic Segment Space Management) или MSSM (Manual Segment Space Management).
        • 0
          Эх. Если бы все в этой жизни зависело от нас.:)
          TS у меня ASSM по умолчанию. Их так создает SAP.
    • 0
      Коммиты убрал — действительно лишние! Еще раз спасибо.
      Как то стал писать execute совместно с commit — так и повелось.
  • +1
    Неплохо для «не-DBA». Потому что во всех мануалах предлагается создавать «big table» методом CTAS до тех пор, пока нужные блоки не затрутся (читай — не переформатируются). И эти советы основаны на идее, что Оракл переформатирует блок при первом обращении. Ваш же способ быстрее в N-раз, т.к. нет нужды читать исходную таблицу и писать в результирующую. Специально посмотрел в документации:

    If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list.
    • 0
      Да!
      CTAS я тоже пробовал. На том же оборудовании, той же базе — таблички надувались в течении почти часа для почти 100гигов.
      • 0
        А теперь попробуйте с initial сразу нужного размера( не забудьте отключить deffered segment creaton)
        • 0
          Хотя, честно говоря, довольно муторно это будет делать, т.к. непрерывные куски ее проанализировать надо
          • 0
            Не успел ответить, вы сами исправились. :)

            Надо было, конечно, все свои опыты в статье отобразить. А то я сразу туда написал решение к которому пришел.

            И вот как сверху советовали alter table allocate extent (size… ); я тоже попробовал.
            Проблема та же. Муторно подбирать размер который оно проглотит. А писать прогу которая сначало будет вычислять максимальный размер на который можно расшириться, а потом постепенно понижать — глупо как то…
            Сложность и размер скрипта сильно возрастет, а что мы выйграем? 45 секунд из 150?

            • 0
              Ну вообще-то я попроще имел ввиду — через dba_free_space
              • 0
                Я вот о чем:
                ALTER SYSTEM set DEFERRED_SEGMENT_CREATION=false;
                

                Затем:
                SELECT round(sum(bytes)/1048576,2) from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3702';
                

                Результат: 10668,75

                И после этого:
                create table SAPSR3.TESTTABLE (id number(10), USER_NAME varchar2(10), CREATE_DATE date) tablespace PSAPSR3702
                storage    (
                            initial          10570M
                            NEXT             10M
                            MAXSIZE          UNLIMITED
                            MINEXTENTS       1
                            );
                

                Не проходит. И, разумеется, большие размеры, которые ближе к максимальному, тоже не катят.
                Жалуется на ORA-01659(Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.)

                Вот размер 10500M создать можно.
                А сидеть и подбирать этот размер на который оно таки может создаться — мне видится глупым.

                Или я не так все таки понял?
                • 0
                  Неправильно, не suM(bytes) надо, а max(bytes). Вообще эта вьюха дает размеры свободных кусков, поэтому в принципе их можно просто перебрать по уменьшению
                  • 0
                    Хм. Спасибо! Попробую так.
                  • 0
                    Не получается.:( А очень жаль.
                    Т.е. написал прогу которая по max(bytes) создает с таким начальным екстентом табличку — и для 100G свободных шикарно отработало за несколько секунд.
                    Но получилось не универсально.

                    Вот для другого TS со свободным размером чуть меньше гига вручную повторил:
                    alter system set DEFERRED_SEGMENT_CREATION=false;
                    

                    system SET altered.
                    SELECT max(bytes) from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3USR';
                    

                    868089856
                    create table SAPSR3.TESTTABLE (id number(10), USER_NAME varchar2(10), CREATE_DATE date) tablespace PSAPSR3USR
                    storage ( initial 868089856);
                    


                    И уппс: ORA-01659: не могу выделить MINEXTENTS выше 19 в разделе PSAPSR3USR

                    Пока думаю с чем такое может быть связано. Свободный кусок есть… но не совсем свободный.
                    • 0
                      Думаю, нужно уменьшить блока на 4 и снова попробовать. Посмотри тут: jonathanlewis.wordpress.com/2013/02/25/free-space-2/
                      • 0
                        SELECT max(bytes)/1024 from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3USR';
                        

                        847744

                        И максимальное количество килобайт с которым таблица создалась: 844800
                        Пришлось отступить на 368 блоков…
                        • 0
                          А в dba_recyclebin ничего не лежит случайно из этого ts?
                          • 0
                            Не, корзина вообще выключена.
                            • 0
                              Ну тогда весьма и весьма занятно… После создания таблицы остался кусок свободный с этими 368 блоками в dba_free_space? Попробуй сдампить. И на всяк пожарный и по dba_extents посмотреть было бы нужно по всему этому диапазону. Для удобства и более быстрого общения можно мне по мылу это прислать
                              • 0
                                Все хитрее:) Остаются два куска.

                                После создания таблички 844800К делаем:
                                SELECT bytes/1024 from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3USR';
                                

                                896
                                2048

                                И вот с этими размерами уже можно создать две таблицы и они реально покроют всю TS.
                                Покопаться в dba_extents пока не получилось. Обращение долгое, а мне пора бежать… Завтра продолжу.

                                Что и как дампнуть не понял.:(

                                Да, ладно зачем уходить в приват. Я столько полезностей вычитал из подобных обсуждений:)
                                • 0
                                  Ну тогда подозреваю, что сработало ограничение bmb — больше не влезло. Вообще размер блока надо обязательно показывать в данном случае, и вообще желательно все в блоках мерить, т.к. именно они сейчас играют роль а не размер в байтах
                                  • 0
                                    Размер блока 8K
                                    Т.е. вначале было 105968 блоков, но сразу все сожрать нельзя.
                                    А после выделения 105600 остается 112 и 256 блоков. И их уже можно до полного заполнения.

                                    Ну и alter table SAPSR3.TESTTABLE allocate extent(size ...); разумеется тоже не проходит. Жаль, очень бы быстро отрабатывало.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.