Oracle Partitioning: Оперативное перемещение и восстановление исторических данных

1. Введение

При решении задачи хранения и обеспечения доступа к историческим данным очень часто возникает задача выгрузки архивных данных на резервный носитель (например, на магнитную ленту) с возможностью оперативного восстановления этой информации и обеспечения доступа к ней пользователей. Эта проблема наиболее актуальна для хранилищ данных, хотя может применяться и для обработки архивных данных OLTP-систем.

В данной статье описывается способ решения этой проблемы с помощью опции Partitioning базы данных Oracle Database.

Ниже представлена иллюстрация данного подхода, который включает в себя: идентификацию исторических данных, их перемещение во временную таблицу, экспорт и копирование на резервный носитель.

Перемещение табличного пространства (файла базы данных Oracle) содержащего исторические данные через временную таблицу

Иллюстрация подхода перемещения исторических данных

Первым шагом является определение секций, содержащих исторические данные. Исторические данные – это данные за прошлые периоды, над которыми в будущем не будут проводиться операции изменения. Затем секции, содержащие исторические данные, перемещаются в заранее подготовленную временную таблицу. Следующим шагом производится экспорт метаданных для Transport Table Space (TTS). В заключении производится перенос файла с метаданными и файла табличного пространства на резервный носитель.

Далее будет детально рассматриваться процесс экспорта и импорта табличного пространства для одного раздела секционированной таблицы CALLS (информация о телефонных звонках клиентов) схемы DWH. 



SQL> CREATE TABLE DWH.CALLS (

  2    CALLS_ID                NUMBER (15)   NOT NULL,

  3    STRT_DT_KEY             DATE          NOT NULL,

  4    BSN_EV_TP_ID            NUMBER (5)    NOT NULL,

  5    STRT_TM                 DATE          NOT NULL,

  6    END_TM                  DATE          NOT NULL,

  7    CTY_FR                  NUMBER (15)   NOT NULL,

  8    CTY_TO                  NUMBER (15)   NOT NULL,

  9    A_NUM                   VARCHAR2 (20) NOT NULL,

 10    B_NUM                   VARCHAR2 (20) NOT NULL,

 11    PRICE_AMT               NUMBER (15,4) NOT NULL,

 12    CHG_AMT                 NUMBER (15,4) NOT NULL,

 13    CHG_CALL_DUR            NUMBER (15)   NOT NULL,

 14    CALL_DUR                NUMBER (15)   NOT NULL,

 15    IS_DEL_IND              NUMBER (1)    NOT NULL,

 16    UPD_DT                  DATE          NOT NULL,

 17    PPN_DT                  DATE          NOT NULL,

 18    SRC_STM_ID              NUMBER (5)    NOT NULL

 19  )

 20    TABLESPACE TBS_CALLS

 21    PARTITION BY RANGE (STRT_DT_KEY)

 22    SUBPARTITION BY LIST (BSN_EV_TP_ID)

 23    SUBPARTITION TEMPLATE (

 24      SUBPARTITION "SP_BSNEV1" values ( 1 ),

 25      SUBPARTITION "SP_BSNEV2" values ( 2 ),

 26      SUBPARTITION "SP_BSNEV3" values ( 3 ),

 27      SUBPARTITION "SP_BSNEV4" values ( 4 ),

 28      SUBPARTITION "SP_BSNEV5" values ( 5 ),

 29      SUBPARTITION "SP_BSNEV6" values ( 6 ),

 30      SUBPARTITION "SP_BSNEV7" values ( 7 ),

 31      SUBPARTITION "SP_BSNEV8" values ( 8 ),

 32      SUBPARTITION "SP_BSNEV9" values ( 9 ))

 33  (

 34    PARTITION P_0106 VALUES LESS THAN (TO_DATE('2006-02-01 00:00:00',

        'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) 

        TABLESPACE TBS_CALLS_0106_1,

 35    PARTITION P_0206 VALUES LESS THAN (TO_DATE('2006-03-01 00:00:00',

        'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) 

        TABLESPACE TBS_CALLS_0206_1,

 36    PARTITION P_0306 VALUES LESS THAN (TO_DATE('2006-04-01 00:00:00',

        'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) 

        TABLESPACE TBS_CALLS_0306_1,

 37    PARTITION P_MAXV VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_CALLS_PMAXV

 38  );

 

Table created

Описанный подход был принят как основной для задач перемещение и восстановление исторических данных хранилища корпоративной информации компании “ОАО Ростелеком”.

2. Определение исторических данных

Для выявления исторических данных, то есть тех данных которые не будут больше изменяться, администратор должен ежемесячно проводить мониторинг их появления. Перечень данных, которые следует признавать историческими, определяют бизнес-требования. Часто правило определения исторических данных сводится к такому условию: историческими признаются те данные, срок хранения которых превышает определенный лимит, например, 5 лет от текущего момента.

Для автоматизации выявления исторических данных в конкретной таблице фактов, возможно выполнение следующего запроса (обращение к словарю Oracle Database): 



select

    COUNT_DAY, TABLE_OWNER,  TABLE_NAME, PARTITION_NAME     

from

 (select  

   TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'MM.YYYY'), 'MM.YYYY') -

   TO_DATE(substr(t.partition_name, 3, 2)||'.20'||

                  substr(t.partition_name,5,2),'MM.YYYY')) 

       AS COUNT_DAY,

   T.TABLE_OWNER, T.TABLE_NAME, T.PARTITION_NAME     

   from 

     all_tab_partitions t 

   )

where

  COUNT_DAY > 1825 /* 5 лет в днях */;

Данный запрос вернет перечень разделов (см. поле PARTITION_NAME) по таблицам, данные в которых являются историческими (срок хранения превышает 5 лет). Эти данные необходимо архивировать и перенести на резервный носитель.

3. Перемещение исторических данных

Для перемещения раздела таблицы с историческими данными будет использована технология перемещаемых табличных пространств (Transportable Tablespace). Для перемещения табличных пространств необходимо провести следующие действия:

  1. Создать временную таблицу, в которую будут перемещены исторические данные.
  2. Переместить во временную таблицу исторические данные путем смены разделов (exchange partition).
  3. Убрать все логические и физические связи табличного пространства и раздела таблицы со всеми объектами кроме временной таблицы.
  4. Сделать табличное пространство доступным только для чтения (read only).
  5. Сделать экспорт метаданных табличного пространства раздела с историческими данными (для успешного выполнения экспорта и импорта необходимо, чтобы пользователь, из-под которого выполняются данные операции, обладал правами exp_full_database и imp_full_database соответственно).
  6. Скопировать файл с метаданными и файлы данных табличного пространства с историческими данными в папку для переноса на резервный носитель.
  7. Сделать архив, включив в него: файл с метаданными, файлы табличного пространства, дополнительный файл с описанием.
  8. Удалить табличное пространство с историческими данными из БД.

Ниже приведена последовательность действий по перемещению исторических данных из раздела P_0106 таблицы CALLS.

Данные раздела P_0106 хранятся в табличном пространстве TBS_CALLS_0106_1, которое в свою очередь, состоит из двух файлов: TBS_CALLS_0106_1_001.dbf и TBS_CALLS_0106_1_002.dbf.

Ниже все скрипты будут выполняться из-под пользователя system.

4. Создание временной таблицы

Создадим временную таблицу, в которую в последствии переместим раздел с историческими данными.



SQL> create table DWH.CALLS$EXP$P_0106

  2   TABLESPACE TBS_CALLS_0106_HIST

  3    PARTITION BY LIST ("BSN_EV_TP_ID")

  4        (

  5   PARTITION "SP_BSNEV1" values ( 1 ) TABLESPACE TBS_CALLS_0106_HIST,

  6   PARTITION "SP_BSNEV2" values ( 2 ) TABLESPACE TBS_CALLS_0106_HIST,

  7   PARTITION "SP_BSNEV3" values ( 3 ) TABLESPACE TBS_CALLS_0106_HIST,

  8   PARTITION "SP_BSNEV4" values ( 4 ) TABLESPACE TBS_CALLS_0106_HIST,

  9   PARTITION "SP_BSNEV5" values ( 5 ) TABLESPACE TBS_CALLS_0106_HIST,

 10   PARTITION "SP_BSNEV6" values ( 6 ) TABLESPACE TBS_CALLS_0106_HIST,

 11   PARTITION "SP_BSNEV7" values ( 7 ) TABLESPACE TBS_CALLS_0106_HIST,

 12   PARTITION "SP_BSNEV8" values ( 8 ) TABLESPACE TBS_CALLS_0106_HIST,

 13   PARTITION "SP_BSNEV9" values ( 9 ) TABLESPACE TBS_CALLS_0106_HIST

 14        )

 15        as select * from DWH.CALLS where 1=2;

 

Table created

5. Перемещение данных во временную таблицу

Выполняем команду смены раздела (exchange paertition) P_0106 (раздел с историческими данными) между таблицей CALLS и временной таблицей CALLS$EXP$P_0106.



SQL> alter table DWH.CALLS exchange partition P_0106 

                      with table DWH.CALLS$EXP$P_0106 without validation;

 

Table altered

6. Удаление связей

Сделать экспорт метаданных табличного пространства можно только тогда, когда оно не связано с другими объектами базы данных.

Для проверки наличия связей необходимо выполнить следующие процедуру и запрос (их необходимо выполнять из-под пользователя SYS):



SQL> conn sys/pass@DWH as sysdba

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 

Connected as SYS

SQL>



SQL> EXECUTE DBMS_TTS.transport_set_check('TBS_CALLS_0106_1', TRUE);

 

PL/SQL procedure successfully completed

 

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

 

VIOLATIONS

--------------------------------------------------------------------------------

Default Partition (Table) Tablespace TBS_CALLS for CALLS not contained in transp

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not

 

19 rows selected

 

SQL>

Если запрос к представлению TRANSPORT_SET_VIOLATIONS возвращает записи, то это значит, что взаимосвязи раздела с другими объектами базы данных существуют. Необходимо, чтобы запрос к данному представлению НЕ возвращал строк. Для этого необходимо изменить табличные пространства для раздела P_0106 таблицы CALLS – переместить раздел в табличное пространство TBS_CALLS_0106_HIST и переместить метаданные о таблице CALLS$EXP$P_0106 в табличное пространство TBS_CALLS_0106_1: 



SQL> ALTER TABLE DWH.CALLS MODIFY default attributes FOR PARTITION P_0106 

                                                 tablespace TBS_CALLS_0106_HIST;

 

Table altered

SQL> ALTER TABLE DWH.CALLS$EXP$P_0106 MODIFY default attributes 

                                                 tablespace TBS_CALLS_0106_1;

 

Table altered

 

SQL>

Выполним проверку наличия взаимосвязей повторно.



SQL> EXECUTE DBMS_TTS.transport_set_check('TBS_CALLS_0106_1', TRUE);

 

PL/SQL procedure successfully completed

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

 

VIOLATIONS

--------------------------------------------------------------------------------

 

SQL>

В представлении TRANSPORT_SET_VIOLATIONS записи отсутствуют – взаимосвязей нет.

7. Атрибут "только для чтения"

Сделать экспорт метаданных табличного пространства можно только тогда, когда оно находится в режиме "только для чтения". Сделать табличное пространство доступным только для чтения можно, выполнив следующую команду: 



SQL> ALTER TABLESPACE TBS_CALLS_0106_1 READ ONLY;

 

Tablespace altered

 

SQL>

8. Экспорт табличного пространства

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

В командной строке необходимы выполнить команду экспорта (см. скрипт – export.sh) в директорию определенною в переменной DATA_PUMP_DIR базы данных.



$ expdp system/pass@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP 

TRANSPORT_TABLESPACES=TBS_CALLS_0106_1  

TRANSPORT_FULL_CHECK=Y LOGFILE= TBS_CALLS_0106_1.log;



Export: Release 10.2.0.4.0 - 64bit Production on Friday, 17 April, 2009 10:39:42



Copyright (c) 2003, 2005, Oracle.  All rights reserved.



Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02":  system/********@DWH 

DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP 

TRANSPORT_TABLESPACES=TBS_CALLS_0106_1 

TRANSPORT_FULL_CHECK=Y LOGFILE= TBS_CALLS_0106_1.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully loaded/unloaded

**************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is:

  /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully completed at 10:40:11



$

Перейдем в директорию, которую определяет переменная DATA_PUMP_DIR.



$ cd /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/

$

Просмотрим ее содержимое.



$ ls

TBS_CALLS_0106_1.DMP  TBS_CALLS_0106_1.log 

$

9. Копирование файлов

Скопируем файл с метаданными TBS_CALLS_0106_1.DMP и файлы данных БД TBS_CALLS_0106_1_001.dbf, TBS_CALLS_0106_1_002.dbf в директорию /backup/DWH/TBS_CALLS_0106_1_HIST, предназначенную для временного хранения архивов, перед переносом на резервный носитель. Предварительно директорию TBS_CALLS_0106_1_HIST необходимо создать в /backup/DWH/.



$ cd /backup/DWH/

$ mkdir TBS_CALLS_0106_1_HIST

$ ls

TBS_CALLS_0106_1_HIST 

$

$

cp /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP 

/backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1.DMP

$

cp /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf 

/backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_001.dbf

$

cp /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf 

/backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_002.dbf

$

$ cd TBS_CALLS_0106_1_HIST/

$ ls

TBS_CALLS_0106_1.DMP    TBS_CALLS_0106_1_001.dbf    TBS_CALLS_0106_1_002.dbf

$

Рекомендуется создать текстовый файл /backup/DWH/TBS_CALLS_0106_1.txt, в котором описать месторасположение файлов с данными экспортируемого табличного пространства. И затем включить данный текстовый файл в архив.

Для создания файла с описанием можно выполнить следующие действия (в операционной системе Unix):

  • Создать файл: touch TBS_CALLS_0106_1.txt.
  • Открыть файл на редактирование: cat > TBS_CALLS_0306_1.txt.
  • Внести в файл текст.
  • По окончанию редактирования файла нажать Cntr+D.


$ touch TBS_CALLS_0106_1.txt

$ cat > TTBS_CALLS_0106_1.txt

/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf

/wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf

$

10. Создание архива

Создадим архив с содержимым директории TBS_CDR_0306_1_HIST, используя утилиту tar. Этот архив, впоследствии, и будет перемещен на резервный носитель.



$ cd backup/DWH/

$ tar -cf - TBS_CALLS_0106_1_HIST | gzip -c > TBS_CALLS_0106_1_HIST.tar.gz

$ ls

TBS_CALLS_0106_1_HIST           TBS_CALLS_0106_1_HIST.tar.gz

$

Архив создан. Теперь можно удалить исторические данные из таблицы БД.

11. Удаление табличного пространства

Удалим табличное пространство TBS_CALLS_0106_1



SQL> drop tablespace TBS_CALLS_0106_1 including contents and datafiles;

 

Tablespace dropped

SQL>

Вместе с табличным TBS_CALLS_0106_1 пространством удалится и временная таблица CALLS$EXP$P_0106.

Для облегчения в дальнейшем процесса восстановления в таблице с данными (в нашем примере это таблица CALLS) раздел, в котором были исторические данные, лучше оставить.

12. Восстановление исторических данных

Для восстановления исторических данных из архива необходимо провести следующие действия:

  1. Скопировать архив с историческими данными с резервного носителя в директорию для восстановления.
  2. Распаковать архив.
  3. Скопировать файл с метаданными в папку для восстановления и файлов с данными в папку (или папку) сервера базы данных, где они находились до проведения экспорта.
  4. Импорт исторических данных во временную таблицу.
  5. Смена табличных пространств.

13. Копирование и распаковка архива

Скопируем архив с историческими данными с резервного носителя в директорию для восстановления. В нашем примере это будет директория /backup/Restore. Обычно эту функцию выполняет администратор системы резервного копирования.

Подключимся к серверу, на котором работает наша СУБД, под пользователем операционной системы oracle, используя командную строку.



login as: oracle

Using keyboard-interactive authentication.

Password:

Извлечём файлы из архива.



$ cd /backup/Restore/

$ gunzip -c TBS_CALLS_0106_1_HIST.tar.gz | tar -xf -

$

14. Копирование файлов

Скопирем файл с метаданными TBS_CALLS_0106_1.DMP в директорию /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/,

файл данных TBS_CALLS_0106_1_001.dbf в директорию /wh/oracle/disk1/DWH/;

файл данных TBS_CALLS_0106_1_002.dbf в директорию /wh/oracle/disk0/DWH/.



$ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1.DMP 

/u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP

$

$ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_001.dbf 

/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf

$

$ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_002.dbf 

/wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf

$

15. Импорт исторических данных

Выполним команду экспорта метаданных табличного пространства (см. скрипт – import.sh) в директорию, определенную в переменной DATA_PUMP_DIR базы данных. 



$ impdp system/pass@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP 

TRANSPORT_DATAFILES=/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf, 

/wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf;



Import: Release 10.2.0.4.0 - 64bit Production on Friday, 17 April, 2009 11:08:44



Copyright (c) 2003, 2005, Oracle.  All rights reserved.



Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@DWH 

DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP 

TRANSPORT_DATAFILES=/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf, 

/wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:08:51



$

После окончания импорта метаданных табличного пространства в схеме DWH появится таблица CALLS$EXP$P_0106.

16. Смена табличных пространств

Осуществим смену (partitio6 exchange) между таблицей CALLS$EXP$P_0106 и таблице CALLS.



SQL> alter table DWH.CALLS exchange partition P_0106 

                           with table DWH.CALLS$EXP$P_0106 without validation;

 

Table altered

 

SQL>



В случае, если это необходимо, можно изменить атрибут «только для чтения». 



SQL> ALTER TABLESPACE TBS_CALLS_0106_1 READ WRITE;

 

Tablespace altered

 

SQL>

17. Заключение

База данных Oracle Database предоставляет гибкий механизм управления табличными пространствами секционированных таблиц, что позволяет достаточно просто организовать управление архивными данными, как в OLTP-системах, так и в хранилищах данных.

Полный архив скриптов можно загрузить по данной ссылке.

18. Дополнительная информация

  • Oracle Database Utilities 10g Release 2 (10.2) Part Number B14215-01 (раздел посвященный DataPump).
  • Doc ID: 09585.1 от 04.09.2002 на Oracle Metalink.
  • Doc ID: 114915.1 от 30.03.2008 на Oracle Metalink.

Oracle Magazine Online - Русское издание. Ковтун М.В. 2009

Хранилище данных
Oracle Tech
Data Warehouse