Export/ImportPL/SQL
DBA's DictionaryБыстрая Архивация Данных
Во всем мире объем данных, хранящихся в базах данных Oracle, продолжает неуклонно расти. Одновременно снижается и стоимость дисковой памяти, тем не менее вопрос архивации и вывода устаревших данных из системы остается актуальным.
Во многих системах, использующих БД Oracle в качестве хранилища данных, бизнес требует периодической массовой загрузки и дальнейшей обработки большого объема данных. Периодичность поступления новых данных в систему может варьироваться в диапазоне от секунд до месяцев, размер - от десятков мегабайт до сотен гигабайт за период. Но в общем случае важно то, что природа таких данных имеет явную или неявную привязку ко времени. Это означает, что по истечении некоторого времени данные, обработанные в системе, теряют свою актуальность и вероятность их повторного использования уменьшается со временем. В качестве примера можно привести системы обработки телеметрической информации, биллинговые системы.
Общим решением задачи вывода данных из системы является построение отдельного Хранилища Данных (Data Warehouse), куда с некоторой периодичностью переносят архивные данные. Однако такой подход решает не только и не столько задачу архивации, он может не оправдать себя по следующим причинам:
- это дорого
- возможность построения статистических отчетов не нужна или пока не нужна
- в общем подход не предполагает в случае необходимости обратного восстановления данных в систему (data restoring)
В Oracle7 эта задача решается через создание дополнительных архивных таблиц. С некоторой периодичностью устаревшие данные переносят в эти таблицы. Когда возникает потребность, часть архивных данных копируют обратно в базовые таблицы. Недостатки такого подхода очевидны:
- Отсутствует архивация в истинном значении этого слова. Данные продолжают "жить" в системе, продолжают потреблять ресурсы дисковой памяти
- Возникают проблемы поддержания уникальности и ссылочной целостности на общем наборе данных (оперативные + архивные данные)
- Это медленно, слишком медленно, поскольку "архивация" и обратная интеграция данных в базовые таблицы подразумевает DML операции (insert, delete). Не приемлемо для систем, в которых критично время восстановления данных (restoring time). Не приемлемо для систем, где производится архивация больших объемов данных.
- Операции архивации/восстановления потребляют значительные ресурсы ЦПУ и оперативной памяти.
- Массовое удаление данных из базовых таблиц приводит к фрагментации индексов, что ухудшает их эффективность. Как следствие, DBA должен заниматься частым перестроением индексов для поддержания эффективного индексного доступа к данным базовых таблиц.
Введем несколько терминов, чтобы формализовать дальнейшее описание.
- Партицирование - разделение данных таблицы на физическом уровне на отдельные части (партиции)
- Transportable Tablespaces - механизм переноса отдельных табличных пространств между серверами баз данных Oracle.
- Transportable Tablespace Set - набор файлов табличных пространств, данные которых подлежат архивированию, и файл метаданных, необходимый для возможной обратной интеграции набора в БД.
- Offline-хранение - хранение данных вне БД на offline-носителях (ленты, оптика, др.)
- Период offline-хранения - интервал времени, определяющий период хранения архивных данных на offline-носителях
- Online-хранение - хранение данных в БД
- Период online-хранения - интервал времени, определяющий время хранения данных, подлежащих архивации, в системе
- Архивация - процесс перевода данных из режима online-хранения в режим offline-хранения
- Восстановление данных - процесс перевода данных из режима offline-хранения в режим online-хранения
- Время доступа - время, необходимое для восстановления архивных данных
Партицирование является прозрачным для пользовательских операций с данными. На размер партиции не накладывается жестких ограничений и, в общем случае, он должен определяться индивидуально для каждой системы с учетом объема данных, поступающих в систему, наличия свободного дискового пространства, требований к эффективности запросов по диапазону дат, ограничений offline-носителей. Почему мы обязаны использовать partitioning для быстрой архивации данных станет ясно чуть позже. Однако сейчас нужно упомянуть, что размер отдельной партиции по сути и определяет размер соответствующего архива. C учетом бизнес требований и объема дискового пространства, доступного для хранения звонковых данных, в системе определяется период online-хранения данных в базовой таблице(ах).
Пример. Допустим, с учетом требований бизнеса заказчика в системе необходимо хранить звонковые данные за последние 2 месяца и выбрано суточное партицирование. Тогда в общем случае данный период будет покрыт 61 партицией. Каждые сутки звонковые данные самой старой партиции переводятся в offline-хранение, создается партиция для загрузки новых данных. Таким образом период online-хранения звонковых данных, покрываемый набором партиций остается неизменным. Это похоже на "временное окно", которое охватывает постоянный объем данных (в единицах времени) и двигается по времени с дискретным шагом, равным суткам. Период offline-хранения данных определяется исключительно потребностями бизнеса и может составлять значительный период времени.
В зависимости от объема архивируемых данных и требований, предъявляемых к времени доступа, возможно применение двух базовых вариантов архивации/восстановления данных:
Вариант 1.Время доступа к архивным данным некритично, есть дополнительное свободное дисковое пространство. Относительно небольшие объемы данных, подлежащих архивации.
Архивация
- Экспорт данных удаляемой партиции.
- Удаление/truncate партиции.
- Сохранение файла экспорта на offline-носителе.
Восстановление
- Перенос файла экспорта на сервер
- Импорт данных в партицию
Я не буду подробно останавливаться на этом варианте в силу его простоты. Наша цель - Быстрая Архивация!
Вариант 2.
Большие объемы данных, подлежащих архивации. Жесткие требования к времени восстановления данных.
Предполагает использование механизма Transportable Tablespaces.
Архивация
- Создание вспомогательной таблицы.
- "Перенос" данных партиции в nonpartioned table.
- Проверка на самодостаточность и создание Transportable Tablespace Set.
- Перенос файлов на offline-носитель.
Таблица CALLS_TMP состоит из двух партиций CLD20000901, CLD20000902, в которых хранятся звонки за двое суток. Таблица имеет первичный ключ PK_CALLS_TMP. Необходимо провести архивацию звонков за 1ое сентября 2000 года (партиция CLD20000901)
SQL> select partition_name, tablespace_name 2 from user_tab_partitions 3 where table_name = 'CALLS_TMP'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ---------------- CLD20000901 CLD_20000901 CLD20000902 CLD_20000902 SQL> SQL> select partition_name, tablespace_name 2 from user_ind_partitions 3 where index_name = 'PK_CALLS_TMP'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ---------------- CLD20000901 CLI_20000901 CLD20000902 CLI_20000902 SQL> SQL> create tablespace cld20000901_dummy 2 datafile '/export/home/edward/oradata/REED/cld20000901d_01.dbf' size 100K; Tablespace created.
Обратите внимание на размер файла!
SQL> alter user edward 2 quota unlimited on cld20000901_dummy; User altered.
1. Создание вспомогательной таблицы для развязки c таблицей CALLS_TMP
SQL> SQL> create table cld20000901_archive_tab 2 storage(initial 10K next 10K minextent 1 pctincrease 0) 3 tablespace cld20000901_dummy 4 as select * from calls_tmp where 1 = 2; Table created. SQL> SQL> alter table cld20000901_archive_tab add constraint 2 pk_cld20000901_archive primary key (call_date, id) 3 using index storage(initial 10K) tablespace cld20000901_dummy; Table altered.
2. "Перенос" данных партиции в таблицу. Здесь важно заметить, что команда
SQL> alter table calls_tmp exchange partition cld20000901
2 with table cld20000901_archive_tab including indexes without validation;
Table altered.
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'CALLS_TMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ---------------------
CLD20000901 CLD20000901_DUMMY
CLD20000902 CLD_20000902
SQL> select partition_name, tablespace_name
2 from user_ind_partitions
3 where index_name = 'PK_CALLS_TMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------
CLD20000901 CLD20000901_DUMMY
CLD20000902 CLI_20000902
SQL> select count(*) from cld20000901_archive_tab;
COUNT(*)
---------
749900
SQL>
SQL> select count(*) from calls_tmp partition (cld20000901);
COUNT(*)
---------
0
SQL> select tablespace_name
2 from user_tables
3 where table_name = 'CLD20000901_ARCHIVE_TAB';
TABLESPACE_NAME
------------------------------
CLD_20000901
SQL>
SQL> select tablespace_name
2 from user_indexes
3 where index_name = 'pk_cld20000901_archive';
TABLESPACE_NAME
------------------------------
CLI_200009013. Проверка на самодостаточность и создание переносимого набора (
SQL>
SQL> execute sys.dbms_tts.transport_set_check('CLD_20000901, CLI_20000901', TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select * from sys.transport_set_violations;
no rows selected
SQL>
SQL> alter tablespace cld_20000901 read only;
Tablespace altered.
SQL> alter tablespace cli_20000901 read only;
Tablespace altered.3. Создание файла метаданных
/export/home/edward/exp>cat exp_params.dat transport_tablespace=y tablespaces=(cld_20000901,cli_20000901) triggers=n constraints=y grants=y file=expdat.dmp /export/home/edward/exp>exp parfile=exp_params.dat Export: Release 8.1.7.0.0 - Production on Fri Apr 6 10:40:23 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production Export done in CL8ISO8859P5 character set and CL8ISO8859P5 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace CLD_20000901 ... . exporting cluster definitions . exporting table definitions . . exporting table CLD20000901_ARCHIVE_TAB For tablespace CLI_20000901 ... . exporting cluster definitions . exporting table definitions . exporting referential integrity constraints . end transportable tablespace metadata export Export terminated successfully without warnings. /export/home/edward/exp> SQL> alter tablespace cld_20000901 offline; Tablespace altered. SQL> alter tablespace cli_20000901 offline; Tablespace altered. SQL> drop tablespace cld_20000901 including contents; Tablespace dropped. SQL> drop tablespace cli_20000901; Tablespace dropped. SQL> SQL> select tablespace_name 2 from dba_tablespaces 3 where tablespace_name like '%2000090_'; TABLESPACE_NAME ------------------------------ CLD_20000902 CLI_20000902
4. Перемещение файлов табличных пространств cld_20000901, cli_20000901 на offline-носитель.
Восстановление
- Копирование файлов с offline-носителя.
- Интеграция перемещаемых табличных пространств в БД
Необходимо восстановить архивные данные за сентябрь 2000 года.
1. Копирование файлов табличных пространств cld_20000901, cli_20000901 с offline-носителя в соответствующие каталоги сервера.
2. Интеграция перемещаемых табличных пространств в БД.
/export/home/edward/exp>cat imp_params.dat
transport_tablespace=y
datafiles=('/export/home/edward/oradata/REED/cld_20000901_01.dbf',
'/export/home/edward/oradata/REED/cli_20000901_01.dbf')
tablespaces=('CLD_20000901', 'CLI_20000901')
/export/home/edward/exp>imp parfile=imp_params.dat
Import: Release 8.1.7.0.0 - Production on Fri Apr 6 11:47:09 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
About to import transportable tablespace(s) metadata...
import done in CL8ISO8859P5 character set and CL8ISO8859P5 NCHAR character set
. importing SYS's objects into SYS
. importing EDWARD's objects into EDWARD
. . importing table "CLD20000901_ARCHIVE_TAB"
About to enable constraints...
Import terminated successfully without warnings.
SQL> select tablespace_name, status
2 from dba_tablespaces
3 where tablespace_name like '%2000090_';
TABLESPACE_NAME STATUS
------------------------------ ---------
CLD_20000901 READ ONLY
CLD_20000902 ONLINE
CLI_20000901 READ ONLY
CLI_20000902 ONLINE
SQL>
SQL> alter tablespace CLD_20000901 read write;
Tablespace altered.
SQL> alter tablespace CLI_20000901 read write;
Tablespace altered.
SQL>
SQL> select segment_name
2 from user_segments
3 where tablespace_name = 'CLD_20000901';
SEGMENT_NAME
-----------------------
CLD20000901_ARCHIVE_TAB
SQL> alter table calls_tmp
2 exchange partition cld20000901
3 with table cld20000901_archive_tab
4 including indexes without validation;
Table altered.
SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'CALLS_TMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLD20000901 CLD_20000901
CLD20000902 CLD_20000902
SQL> select partition_name, tablespace_name
2 from user_ind_partitions
3 where index_name = 'PK_CALLS_TMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLD20000901 CLI_20000901
CLD20000902 CLI_20000902
SQL> select count(*) from calls_tmp partition (cld20000901);
COUNT(*)
---------
749900
SQL> select count(*) from cld20000901_archive_tab;
COUNT(*)
---------
0
Таким образом, в любой момент времени таблица, данные которой подлежат архивации, будет представлена:
- набором партиций, хранящих реальные данные. Этот набор должен покрывать период online-хранения
- набором партиций-заглушек, не содержащих данные. Этот набор покрывает период offline-хранения
Например, бизнес требует иметь оперативный доступ к данным за последние полгода, хранить данные за
2 года, а шаг партицирования определен как ежемесячный. Тогда архивируемая таблица будет представлена
6 "реальными" партициями и 18 партициями-заглушками.
Минимальный размер партиции-заглушки в общем случае зависит от размера блока и кол-ва индексов на архивируемой
таблице. Вы можете подобрать его экспериментально для каждого конкретного случая
Заключение
В этой статье я попытался передать общую идею процесса архивации, не учитывая второстепенных моментов. Конечно,
здесь есть поле для размышлений. Вы можете архивировать индексы или нет, хранить партиции-заглушки или же создавать
их на лету и т.д. Любое решение в базах данных Oracle - есть разумный компромисс! Однако, в любом случае, решение
быстрой архивации данных базируется на трех основных принципах:
| Oracle documentation: | Partitioned Tables and Indexes |
| Managing Partitioned Tables and Indexes | |
| Transporting Tablespaces Between Databases |
Статья Валерия Юринского в Russian Oracle Magazine: "Перемещаем табличные пространства" - временно не доступна до переноса материалов журнала по новому адресу.