То един път ли беше, два ли…
Всеки започва от малко. Дай тука едно приложенийце да напишем, само да записва едни данни. Десетина-двайсе таблички. Хващат се 1-2-3-5 човека и драсват нещо. После-хайде да добавим и това, после и онова… След няколко години приложението вече има хиляда таблици, като най-важните от тях (обикновено са и най-старите) имат десетки и стотици милиони записи.
Да, обаче в първоначалния ентусиазъм никой не е предвиждал обемите, до които ще се стигне. Вярвайте ми, виждал съм го много пъти. Пък и в началото в екипа обикновено има един-двама (най-много), които поназнайват това-онова за базата. Другите са JABORAC фенове. Така че кой ще ти мисли за физическата структура? Дай тука таблицата, да си insert-ваме редовете. Тя базата ще се справи.
Минават години и започват едни тежки въпроси. Хайде, индекси се правят online. Обаче партишъни? Таблица, която никога не е декларирана като partitioned, не става лесно на такава. Всъщност до сега не сме намерили начин това да се случи без downtime.
Един от вариантите е dbms_redefinition. Обаче… ако говорим за таблица със размер няколко стотин гигабайта, това прави страшно много redo – всъщност прави няколко стотин гигабайта redo. И тъжното е, че няма как да ги прави „полека“, изсипва ги наведнъж, колкото поеме. Това е голям стрес за Production база. А ако имате късмета да поддържате и Logical standby или Streams, нещата стават с няколко нива по-сложни.
Има един вариант (практикували сме го), при който новата таблица се подготвя в отделна, тестова БД. Наливат се данните до момента Х и след това се закача чрез transportable tablespace. Има някои чалъми със standby базите, но за primary база това минава online. След това, с малък downtime допълваме последните данни в новата таблица и ги разменяме с rename.
Да, ама и това е тежка и пипкава работа. Вярно, не натоварва production базите, но пък в един момент има всичките данни на две места (същото важи и за dbms_redefinition). Ако таблицата е 50 GB – трябват ти още 50. Пък и цялата процедура е толкова тромава и подадлива на грешки! А, и трябва да сме сигурни, че няма update на стари данни, иначе ще имаме разминаване. Което май важи само за log таблици или таблици, които имат log (pun intended).
И така достигаме с варианта за exchange partition. Предимства
– бърз – минава с 1-2 минути downtime (при определени условия). Скоростта не зависи от размера на таблицата!
– не е твърде сложен
– данните не се пипат, стоят си на мястото
Като минуси мога да кажа, че не могат да се правят промени по структурата на таблицата и данните (нови/променени колонки и т.н.). Нали цялата идея е да не се пипат данните. А, и за да става толкова бързо, трябва всички индекси на новата таблица да са локални – поне в началото. После могат да се добавят и глобални, по обичайната процедура.
* * *
И така, това упражнение го направих преди 3 седмици. Тествано е на 11.2 с таблица от половин терабайт! (без да броим размера на индексите). Works like charm 🙂
Разбира се, предварително инвестирах достатъчно време за да изгладя скрипта за конкретния случай чрез експерименти върху тестови БД
1. Подготовка (online)
1.1. Проверка за foreign keys, сочещи към таблицата. Ако има такива, те трябва да се премахнат преди размяната и да се направят наново след нея. Иначе биха сочили към старата таблица (която остава празна)
select *
from dba_constraints
where constraint_type = 'R'
and r_owner = 'HR'
and r_constraint_name = (select constraint_name
from dba_constraints
where owner = 'HR'
and table_name = 'EMP'
and constraint_type = 'P');
В случая аз нямах такива. Оставям на любознателните читатели да си добавят съответните команди при нужда.
1.2. Проверка до къде е стигнала старата таблица. Новата таблица ще има един голям и тлъст партишън, съдържащ всички стари данни. За това трябва да го направим до или над последното достигнато ID (или дата, или както там си изберем за partition key)
select max(emp_id) from hr.emp;
1.3. Създаване на празна таблица. Структурата на данните (демек имана и типове на колонки) трябва да е същата. Индексите – също, ако искаме всичко да мине бързо. В крайния вариант индексите от старата таблица ще се превърнат в локални индексни партишъни на новата. Ако някои от индексите не можа да бъде local, той ще трябва да се изгради по време на downtime-а, което ще отнеме много време за големи таблици
-- the table
CREATE TABLE emp_new
(empid NUMBER(25),
fname VARCHAR2(20),
lname VARCHAR2(20),
sex VARCHAR2(1),
ssn VARCHAR2(9),
salary NUMBER(8),
deptno NUMBER(5)
)
partition by range (empid)
(partition EMP_OLD values less than (500000000) tablespace users,
partition EMP_520M values less than (52000000) tablespace EMP_520,
partition EMP_340M values less than (540000000) tablespace EMP_540,
...
partition EMP_max values less than (MAXVALUE) tablespace EMP_MAX);
-- primary key index
create unique index HR.EMP_NEW_PK on HR.EMP_NEW(empid) local
(partition EMP_OLD tablespace users,
partition EMP_520M tablespace EMP_520,
partition EMP_540M tablespace EMP_540,
...
partition EMP_max tablespace EMP_MAX);
-- primary key
alter table HR.EMP_NEW
add constraint EMP_PK primary key (empid);
-- foreign key index
create index HR.IFK_EMP_NEW_deptno on HR.EMP_NEW (deptno) local
(partition EMP_OLD tablespace users,
partition EMP_520M tablespace EMP_520,
partition EMP_540M tablespace EMP_540,
...
partition EMP_max tablespace EMP_MAX);
-- foreign key
alter table HR.EMP_NEW
add constraint fk_deptno FOREIGN KEY (deptno)
REFERENCES hr.dept (deptno);
-- some other index
create index HR.I_EMP_NEW_NAME on HR.EMP_NEW (fnmae, lname) local
(partition EMP_OLD tablespace users,
partition EMP_520M tablespace EMP_520,
partition EMP_540M tablespace EMP_540,
...
partition EMP_max tablespace EMP_MAX);
Тук е момента да се добавят и тригери, VPD политики и други екстри (ако има такива).
2. Размяна. Това е нещото, което изисква downtime. При добре подготвен и многократно тестван скрипт всичко приключва за под 2 минути.
2.1. Спиране на Foreign key constraints на новата, празна таблица. При размяната не могат да бъдат проверени, за това ако са активни, просто не става
alter table HR.EMP_NEW disable constraint fk_deptno_new;
2.2. Спиране на всички unique и primary key constraints на новата, празна таблица. Ако има такива, при размяната се прави негласна проверка на всички стойности от таблицата, което отнема много време.
alter table HR.EMP_NEW disable constraint EMP_NEW_PK;
2.3. Размяна на сегментите. Това нещо, ако е правилно подготвено, се случва за част от секундата, защото прави само data dictionary updates. Като резултат сегмента на старата, пълна с данни таблица става сегмент на партишън от новата (и обратно). С други думи новата таблица мигновено придобива всички данни от старата.
ALTER TABLE HR.EMP_NEW EXCHANGE PARTITION EMP_OLD WITH TABLE HR.EMP INCLUDING INDEXES WITHOUT VALIDATION;
– клаузата INCLUDING INDEXES указва старите индекси да станат партишъни на (локалните) индекси на новата таблица. Ако не дадем тази клауза, EMP_OLD партишъните на всички индекси стават UNUSABE.
– клаузата WITHOUT VALIDATION казва „довери ми се, всички данни са наистина за тоя partition“. Така си спестяваме преравянето на старата таблица, което отнема много време. Но трябва да сме сигурни, че наистина данните ни са верни. Иначе стават странни неща. Освен това, ако има работещ unique или primary key на новата таблица, тази клауза се игнорира безмълвно – то държи да си провери. Явно идеята е тук да се спести време, а после да се проверят данните по време на enable constraint. Следите ли ми мисълта? NOVALIDATE….
2.4. Размяна на имената на таблиците. Мисля, че всичко е ясно тук
alter table HR.EMP rename to EMP_OLD; -- тази вече е празна alter table HR.EMP_NEW rename to EMP; -- данните вече са тук
2.5. Размяна на имената на индексите. Това не е задължително, но така е по-подредено. Освен това помага ако някъде има hint, който забива някой индекс по име
alter index HR.EMP_PK rename to EMP_OLD_PK; alter index HR.IFK_EMP_deptno rename to IFK_EMP_OLD_deptno; alter index HR.I_EMP_NAME rename to I_EMP_OLD_NAME; alter index HR.EMP_NEW_PK rename to EMP_PK; alter index HR.IFK_EMP_NEW_deptno rename to IFK_EMP_deptno; alter index HR.I_EMP_NEW_NAME rename to I_EMP_NAME;
2.6. Включване на constraint-ите. Техните имена ги оставих както са си, защото не се сещам за вариант, в който да се реферират. Е, освен в разни съобщения за грешка.
alter table HR.EMP enable novalidate constraint fk_deptno_new; alter table HR.EMP enable novalidate constraint EMP_NEW_PK;
Тук е важна клаузата novalidate. Поставям я защото съм безразсъдно убеден, че всичките ми данни са наред. В крайна сметка те си бяха наред в старата таблица, а никой не е пипал никакви данни.
2.7. Прехвърляне на grant-овете. Който и да е имал право да ровичка данните, сега може да го прави само върху празната стара таблица. Трябва да възстановим статуквото преди да е изгърмяло някое приложение. Премахването на грантовете към старата таблица е само по желание
select 'grant '||privilege||' on HR.EMP to '||grantee||';' from dba_tab_privs where table_name = 'EMP_OLD' order by 1;
2.8. Проверка за unusabe index partitions на новата таблица. Знае ли човек….
set lines 200 select ip.index_owner, ip.index_name, partition_name, ip.status, ip.tablespace_name from dba_ind_partitions ip, dba_indexes i where i.index_name = ip.index_name and i.owner = ip.index_owner and i.table_name = 'EMP';
Тук вече може да пускаме приложенията да напъплят базата. Свършихме невероятно много работа за две минути. Време е за по бира.
(тествано на Oracle 11.2)
P.S. Статистиките на старата таблица вече са локални статистики на партишъна на новата. Може да си пуснем едно събиране на глобални статистики. Кой както е свикнал.
P.P.S. Да не забравите тригерите и VPD политиките!
